pl/sql编程注意点,记下来,以后也可以随时参考

SQL & PL/SQL禁忌

一共分为四种不同的缺点，主要包括如下：

       ·数据中断的威胁—可能会导致数据丢失；

       ·使系统难以使用—如可能会减少重用性或者使系统难以维护；

       ·降低性能—通常是浪费存储和CPU；

       ·安全威胁；

 

主要包括如下：

1.       直接在条件中使用函数：降低性能；
    non-deterministic函数不依赖于where子句使用的当前行值，可能依赖于上下文从不同的表推进数据，或在数据上执行操作，这些函数并不包含在select列表中。
    函数会执行过多的次数，可能对于每个查询只需要执行一次即可，但是Oracle可能为结果集的每一行都执行了一次，甚至数据源的每一行执行了一次；
    ·如果不依赖于任何行值，将其放在select function() from dual中；
    ·如果函数依赖于引用的数据，则将其移到子查询中，然后连接子查询和主查询；
    ·如果对于相同的值返回相同的结果，则将函数标记为deterministic；
    ·使用Oracle 11g的结果集缓存功能；

2.       捕获异常处理块使用WHEN OTHERS THEN：数据中断的威胁；
    通常用来忽略所有不影响事务的错误（如唯一索引不能包含重复数据，插入错误应该被log）；
    when others将会防止其他不可预计错误传播，如一个触发器导致的突发表错误可能被隐藏，而造成事务实际上没有完成它应该做的而在数据库中事实上已经完成了。
    ·对于检查特定的错误不要使用when others，如no_data_found。
    ·不要假设你已经考虑到了任何问题，存储问题，突发表问题应该反馈给客户端，而不是仅仅的抛弃；

3.       客户端以对象属主访问：安全威胁；

4.       客户端访问表：安全威胁；
    为不同的应用程序建立视图，避免直接访问表。

5.       将复杂的嵌入PL/SQL：使系统难以使用；
    --大块的SQL代码，通常是一个复杂的SELECT语句，使用内置函数/过程；
    --在内置PL/SQL过程/函数中使用优化器提示；
    将复杂的SQL代码从PL/SQL中提取出来，放到一个视图中，然后在过程中从视图上选择，将优化器提示从过程上移到视图上。

6.       使用数字处理错误：使系统难以使用；
    在PL/SQL中使用RAISE_APPLICATION_ERROR，而没有为错误码声明PL/SQL异常；
    ·在pl/sql包头中，声明异常码和异常，使用这些异常而不是应用程序错误号；
    ·发送异常时顺便带上一条消息，使用RAISE_APPLICATION_ERROR和恰当的异常码；
    ·为单独的过程创建一个包，该包专门用来声明异常；

7.       使用输出参数处理错误：数据中断的威胁；
    过程和函数生命返回码作为输出参数，通知调用者问题；
    建议使用声明的异常处理错误，异常必须由调用者处理，而不会被不可预料的忽略，并且可以同时声明错误码和消息，不需要OUTPUT参数；

8.       在视图中格式化数据：使系统难以使用；
    --在视图的DATE和NUMBER列上使用to_char函数；
    --在视图中结合字符串；
    在终端进行数据格式化，而不是数据库中，视图不应该进行任何的格式化；

9.       硬编码本地变量尺寸：使系统难以使用；
    -- PL/SQL函数/过程中声明本地Varchar2类型存储表的值，硬编码长度；
    --视图以硬编码格式声明Varchar2长度；
    使用%type引用基层表对象。

10.    忽略异常：数据中断的威胁；
    -- "Exception When others then NULL;"
    可能会导致如空间错误之类的严重错误被忽略。
    ·如果不希望任何错误影响事务，则使用自治事务，并将错误log到错误表中；
    ·如果要处理某些具体的错误，则使用特定的exception；

11.    不使用绑定变量更改参数：降低可用性；
    经常使用相同的语句执行，不使用绑定变量，而使用文本值；
    会导致每次都编译查询；

12.    在触发器中依赖于条件列值，数据中断的威胁；
    if updating('ColumnName') then；
    在大多数GUI情况下，该方法不能正确的工作，因为GUI层保留所有字段，而不是单独的字段，如下：
create table tbl(col1 number, col2 varchar2(10));
CREATE OR REPLACE TRIGGER trig1
BEFORE UPDATE ON tbl
FOR EACH ROW
BEGIN
if not updating('col1') then
raise_application_error (-20001,'Col1 was not updated');
end if;
END;
/
insert into tbl values (1,'test');
update tbl set col1=1;
    比较:new.column和:old.column，如果列为NULL，确保使用了DECODE。

13.    依赖于在包变量中的上下文信息：数据中断的威胁；
    依赖对象结构更改会导致全部变量的清空。
    ·使用sys_context存储简单的交叉调用上下文；
    ·使用临时表存储复杂的内部事务上下文；
    ·使用表存储复杂的内部调用上下文；

14.    依赖于外部上下文初始化：数据中断的威胁；
    视图和过程依赖于sys_context()得到上下文信息，上下文通常要求调用者调用存储过程设置。
    数据库和应用程序之间的网络会断，大多数现代网络都会自动重新连接，但是初始化过程不会调用并且旧的上下文将会丢失。
    ·使用登陆触发器设置上下文；

15.    分布式触发器的二次处理：数据中断的威胁；
    传播数据更新远程系统；
    在第二个子系统中使用LOG表，在触发器中使用When others LOG任何异常，使用dbms_utility.format_error_stack或sqlerrm捕获详细信息；

16.    存储rowid在之后引用，数据中断的威胁；
    ROWID值存储在表或客户端，以在之后引用；
    ROWID会更改，使用主键引用；

17．存储空的，降低性能；

        在CLOB字段存储Empty CLOB而不是NULL；
            Oracle为EMPTY CLOBs分配空间；

18．太多层次的视图：降低性能；
            优化器可能会放弃使用索引。

        只要可能考虑使用连接代替子查询；

        使用物化视图或者维护一个聚集表；

19．非自治事务中的事务控制，数据中断的威胁；
        在无PRAGMA AUTONOMOUS TRANSACTION的事务中commit和rollback。

20．触发器依赖于执行的顺序

        触发器依赖于其他触发器，如log/audit由其他触发器完成；
            打包相互依赖的商业逻辑到存储过程中，在然后在触发器中调用过程；

        将内部相互依赖的过程组合成一个触发器；

        确保语句级触发器在行级触发器后执行。

21．使用Sequence nextval而没有curval：数据中断的威胁；
            不要使用currval作为上下文读取，显示传递ID或使用sys_context；
            序列是批分配的，currval+1不一定等于nextval。

22．使用序列作为计数器，数据中断的威胁；

23．在常量上使用绑定变量：降低性能；

24．对存在的检查使用计算列（聚集）：降低性能；
        在内查询中计算聚集，然后在外查询再将计算的值与某些值比较，这会导致聚集值被抛弃；

25．为流程控制使用异常：数据中断的威胁；

        --使用exception when XXX then blocks而不是错误处理；
            exception中仅处理错误。

26．日期列（截去时间）比较，数据中断的威胁；

27．对不存在的值使用0-1之类的数字：使系统难以使用；

        对于一些判断情况，通常使用0，1，-1之类的表示，很难理解；

        使用null表示不存在，未知。

28．对于连续的作业不使用专用包：使系统难以使用；

    作业的内容是使包体中的一个过程，函数；

29．将任何东西都打包到存储过程中，Ref Cursor用来读数据，使系统难以使用；