【有奖讨论】PL/SQL编程要点和注意点

oracle1521

○:建议的做法；

X ：不建议的做法。

others 异常信息应包括 SQLCODE、SQLERRM

在通用异常信息中应尽可能多的记录环境信息（SQLCODE、SQLERRM）、业务信息，最好系统中
有一个日志表记录能够将环境、业务、时间等信息一一记录,在系统出现异常时能够最大程度
的减少问题排查时间（对于联机交易出现异常，留给大家的排查时间一般不会很多）。
×:BEGIN
   EXCEPTION
       WHEN others THEN
            
            return;
   END;
○: BEGIN
     ..
    EXCEPTION
        WHEN others THEN
           oi_flag := -1;
           os_msg := ‘错误码’||’|’|| SQLCODE||
                     ‘错误信息’||’|’|| SQLERRM ||
                     ‘业务信息|特殊人员大额停保数据转换失败’;
           --记录日志(日志实现略)
           prc_log (prm_name,--操作序号
oi_flag,--原文件名
os_msg --错误文件名
);
           return;
    END;

oracle1521

★在每个规则内，我会做如下提示：

○:建议的做法；

X ：不建议的做法。

循环处理 DML 操作时每 10000 次执行一次 COMMIT
未提交的数据都会被回滚段“记录”，一旦失败将耗费大量资源
COMMIT 仅仅是修改 SCN 并同步到数据文件和重做日志文件中，频繁的进行不会降低整体的负
荷。
例如：
×:FOR i in .. 1000000
   LOOP
       UPDATE emp SET sal=sal+1 WHERE empno=i;
   END LOOP;
   COMMIT;--没有足够UNDO 空间的情况下会报错
×:FOR i in .. 1000000
   LOOP
       UPDATE emp SET sal=sal+1 WHERE empno=i;
       COMMIT;--性能降低
   END LOOP;
○:FOR i in .. 1000000
   LOOP
       UPDATE emp SET sal=sal+1 WHERE empno=i;
       IF i%10000=0 THEN
         COMMIT;
       END IF;
END LOOP;
COMMIT;--最后别忘加上一个commit;

oracle1521

○:建议的做法；

X ：不建议的做法。

减少使用 SYSDATE
SYSDATE 是函数，调用 SYSDATE 会产生开销，在时间要求精度不高的情况下（如精确到日），
同一个存储过程中完全可以仅调用一次
×: BEGIN
      
       UPDATE  sa390001 SET  sa390001001='001'
      
       WHERE  update_time=sysdate;
      
       UPDATE  sa390002 SET  sa390001002='002'
      
       WHERE  update_time=sysdate;
   
    END;
○: DECLARE
     
     --定义到一个变量中，以后每次引用这个变量就行，不必每次都反复调用 sysdate 函
数
      
       my_timer := sysdate;
   
    BEGIN
      
       UPDATE  sa390001 SET  sa390001001='001'
      
       WHERE  update_time=my_timer;
      
       UPDATE  sa390002 SET  sa390001002='002'
      
       WHERE  update_time= my_timer;
END;

oracle1521

○:建议的做法；

X ：不建议的做法。

使用%TYPE、%ROWTYPE 声明变量
程序应该有较好的容错性，利用%TYPE、%ROWTYPE适应数据结构的变更，硬编码方式不提倡。
×: vs_empno  number(2);--没有与表结构绑定
○: vs_empno  emp.empno%type;
例如：CURSOR  emplist IS SELECT  empno,sal FROM  emp;
×: TYPE  emplist IS RECORD(
empno  emp.empno%type,
sal emp.sal%type
)
○: emplist %rowtype;

newkid

oracle1521 发表于 2012-8-16 22:17
多表连接时要给出表的别名,明确是哪个表中的列
错误: SELECT  ,abd003
FROM  ab001 ,ab020

赞同这个好习惯。

newkid

oracle1521 发表于 2012-8-16 22:18
隐式游标要捕获 NO_DATA_FOUND、TOO_MANY_ROWS 两类异常
×:BEGIN
SELECT ename INTO vs_ename from emp ...

如果是用的主键或唯一键（通常如此，要不就得有ROWNUM=1, 这是用SELECT INTO的人头脑中必须清楚的），TOO_MANY_ROWS没有必要

newkid

oracle1521 发表于 2012-8-16 22:20
○:建议的做法；X ：不建议的做法。
others 异常信息应包括 SQLCODE、SQLERRM

WHEN OTHERS 不带RAISE, 可以视为BUG（除非极少数情况下你很清楚该错误可以忽略）
万一要捕获WHEN OTHERS, SQLERRM是不够的，必须把DBMS_UTILITY.FORMAT_ERROR_BACKTRACE记录下来。

newkid

oracle1521 发表于 2012-8-16 22:21
★在每个规则内，我会做如下提示：○:建议的做法；X ：不建议的做法。

循环处理 DML 操作时每 10000 次执 ...

这有一个前提，就是你的操作不要求视为同一个事务，而且你的操作是可以从断点继续的，即中间失败后你有一种办法能够跳过已经成功并提交的数据。

oracle1521

newkid 发表于 2012-8-16 22:37
如果是用的主键或唯一键（通常如此，要不就得有ROWNUM=1, 这是用SELECT INTO的人头脑中必须清楚的），TOO ...

有道理，如果是安斑竹的意思，确实没有必要
但是对于一般的新手是很容易不去捕获异常信息的，而且在比较复杂的数据处理过程中
在真实的业务中很少去查那些有唯一➹的数据。。

oracle1521

newkid 发表于 2012-8-16 22:42
这有一个前提，就是你的操作不要求视为同一个事务，而且你的操作是可以从断点继续的，即中间失败后你有一 ...

这要的操作一般是用来 批量的做一些数据的处理。。。当然为了事务的一致性，最好还是要加poin做回滚的。