FORALL 之 SAVE EXCEPTIONS 子句应用一例

3121321321

  对于大批量的DML操作中出现的错误，除了使用DML error logging特性来记录在DML期间出现的错误之外，使用批量SQL语句FORALL的SAVE
EXCEPTIONS是不错的选择之一。DML error logging特性的使用较FORALL之 SAVE EXCEPTIONS相对简单，也存在一些不足，如每一个被操作的DML
对象需要创建相应的对应的日志表，不利于集中管理。本文对DML error logging这个不利于集中管理的特性使用FORALL 之 SAVE EXCEPTIONS
方式来完成。

   下面的示例来自一个实际的应用，撇开原始的表名与实际应用中的处理过程，仅仅通过简单示例来展现。
      1、两个不同的DB，假定设定为数据库A和数据库B(为简化，在同一个DB上来演示)。
      2、需要将数据库A的一些表的数据同步到数据库B对应的目的表
      3、如果同步的过程中出现某条特定的记录错误，则写该记录产生的错误信息(含表名，主键)到日志表，并将其原表同步状态更新为N，否则更新为Y
      4、如果非由于INSERT产生的错误信息，则要求写过程名及对应的错误信息到日志表

   如对于批量SQL较为熟悉，请直接阅读下文，否则，请参阅阅读本文所需要的相关知识：

      批量SQL之 FORALL 语句
      批量SQL之 BULK COLLECT 子句
      PL/SQL 集合的初始化与赋值
      PL/SQL 联合数组与嵌套表
      PL/SQL 变长数组
      PL/SQL --> PL/SQL记录

一、创建演示环境
为简化，下面的演示代码在同一个数据库上完成，在不同的DB上来完成仅仅是需要设定DB LINK而已。


    -->创建用于演示的源表emp_source，其数据来自scott.emp  
    scott@CNMMBO> create table emp_source as select empno,ename,sal from emp;   
      
    Table created.  
      
    -->为源表增加一个字段is_sync，用于记录是否同步成功  
    scott@CNMMBO> alter table emp_source add is_sync char(1);  
      
    Table altered.  
      
    -->创建目的表  
    scott@CNMMBO> create table emp_dest as select empno,ename,sal from emp_source where 1=0;  
      
    Table created.  
      
    -->创建记录错误信息的日志表  
    scott@CNMMBO> create table err_log_tbl(log_seq number(12) not null, log_time date not null,  
      2  sp_name varchar2(100),table_name varchar2(30),table_pk varchar2(30),err_msg varchar2(2000));  
      
    Table created.  
      
    -->为表emp_dest 添加约束用于在insert过程中触发错误产生  
    scott@CNMMBO> alter table emp_dest add constraint ck_sal check(sal>500);  
      
    Table altered.  
      
    scott@CNMMBO> alter table emp_dest modify(ename constraint nn_ename not null);  
      
    Table altered.  
      
    -->创建一个用于错误日志表上的sequence  
    CREATE SEQUENCE SCOTT.ERR_SEQ  
      START WITH 20  
      MAXVALUE 999999999999999999999999999  
      MINVALUE 0  
      NOCYCLE  
      NOCACHE  
      NOORDER;  
      
    -->创建一个函数用于获取sequence  
    CREATE OR REPLACE FUNCTION SCOTT.gen_new_err_seq  
       RETURN err_log_tbl.log_seq%TYPE  
    IS  
       newrecid   err_log_tbl.log_seq%TYPE;  
    BEGIN  
       SELECT ERR_SEQ.NEXTVAL INTO newrecid FROM DUAL;  
      
       RETURN newrecid;  
    END;  
    /  

二、使用下面的PL/SQL块演示


    -->下面的演示代码可以封装到包  
    DECLARE  
       c_sp_name        CONSTANT VARCHAR2 (50) := 'anonymity_plsql_block';  
       c_process_name   CONSTANT VARCHAR2 (20) := 'ins_emp_dest';  
       c_table_name              VARCHAR2 (30) := 'emp_dest';  
       debugpos                  bo_common_pkg.debug_pos_type := 0;  
       v_limit                   PLS_INTEGER := 5;    -->定义游标fetch时提取数量的限制数，由于emp_source记录较少，此处设定为5  
       err_msg                   VARCHAR2 (1000);  
      
       CURSOR cur_emp                                 -->声明游标从源表取数据  
       IS  
          SELECT empno, ename, sal FROM emp_source WHERE is_sync IS NULL;  
      
       TYPE emp_tab_type IS TABLE OF cur_emp%ROWTYPE;  
      
       emp_tab                   emp_tab_type;      -->声明基于游标的嵌套表  
      
       TYPE err_rec_type IS TABLE OF err_log_tbl%ROWTYPE;     
      
       err_tab                   err_rec_type := err_rec_type (); -->声明基于err_log_tab的嵌套表并初始化  
       sub_proc_exp              EXCEPTION;     -->定义了两个异常  
       bulk_error                EXCEPTION;  
       PRAGMA EXCEPTION_INIT (bulk_error, -24381);  
    BEGIN  
       debugpos   := 10;  
      
       OPEN cur_emp;  
      
       LOOP  
          BEGIN  
             debugpos            := 20;  
      
             FETCH cur_emp                -->使用游标fetch数据并存放到嵌套表  
             BULK COLLECT INTO emp_tab  
             LIMIT v_limit;  
      
             EXIT WHEN emp_tab.COUNT = 0;  
             debugpos            := 30;  -->下面几个赋值语句用于模拟insert产生error信息  
             emp_tab (2).ename   := RPAD (emp_tab (2).ename, 15, '*');   
             emp_tab (3).sal     := 100;  
             emp_tab (7).sal     := NULL;  
             debugpos            := 40;  
      
             FORALL i IN 1 .. emp_tab.COUNT  -->FORALL子句插入数据，使用SAVE EXCEPTIONS子句  
             SAVE EXCEPTIONS  
                INSERT INTO emp_dest  
                     VALUES emp_tab (i);  
          EXCEPTION  
             WHEN bulk_error  
             THEN                            -->下面是对bulk_error时的处理  
                debugpos   := 50;  
      
                FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT  
                LOOP                        -->FOR 循环中对用于生成插入err_log_tbl所需的数据信息  
                   err_tab.EXTEND;  
                   err_tab (i).log_seq      := gen_new_err_seq;  
                   err_tab (i).log_time     := SYSDATE;  
                   err_tab (i).sp_name      := c_process_name;  
                   err_tab (i).table_name   := c_table_name;  
                   err_tab (i).table_pk     := TO_CHAR (emp_tab (SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).empno);  
                   err_tab (i).err_msg      := SUBSTR (SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE), 1, 300);  
                END LOOP;  
      
                debugpos   := 60;  
      
                FORALL i IN 1 .. err_tab.COUNT   -->将error信息插入到err_log_tbl  
                   INSERT INTO err_log_tbl  
                        VALUES err_tab (i);  
      
                debugpos   := 70;  
      
                FOR i IN 1 .. err_tab.COUNT     -->对于未成功插入的记录，更新源表以表明同步失败  
                LOOP  
                   UPDATE emp_source e  
                      SET is_sync   = 'N'  
                    WHERE EXISTS  
                             (SELECT 1  
                                FROM err_log_tbl d  
                               WHERE e.empno = d.table_pk);  
                END LOOP;  
             WHEN OTHERS  
             THEN  
                err_msg   := SUBSTR (SQLERRM, 1, 200);  
                RAISE sub_proc_exp;  
          END;  
       END LOOP;  
      
       CLOSE cur_emp;  
      
       debugpos   := 80;  
      
    -->Author: Robinson Cheng  
    -->Blog  : http://blog.iyunv.com/robinson_0612  
      
       UPDATE emp_source e     -->对于所有成功同步的数据更新源表的标志位  
          SET is_sync   = 'Y'  
        WHERE EXISTS  
                 (SELECT 1  
                    FROM emp_dest d  
                   WHERE e.empno = d.empno)  
              AND is_sync IS NULL;  
    EXCEPTION       -->外层exception处理error信息，并写入到err_log_tbl日志文件  
       WHEN sub_proc_exp  
       THEN  
          err_msg      :=  
                SUBSTR ('<<Err @ ' || c_sp_name || ' - Debug Pos : ' || TO_CHAR (debugpos) || '>> - ' || SQLERRM, 1, 500)  
             || ' '  
             || err_msg;  
      
          INSERT INTO err_log_tbl  
               VALUES (gen_new_err_seq,  
                       SYSDATE,  
                       c_sp_name,  
                       NULL,  
                       NULL,  
                       err_msg);  
      
          DBMS_OUTPUT.put_line ('Error msg is : ' || err_msg);  
       WHEN OTHERS  
       THEN  
          err_msg      :=  
             SUBSTR ('<<Err @ ' || c_sp_name || ' - Debug Pos : ' || TO_CHAR (debugpos) || '>> - ' || SQLERRM, 1, 500);  
      
          INSERT INTO err_log_tbl  
               VALUES (gen_new_err_seq,  
                       SYSDATE,  
                       c_sp_name,  
                       NULL,  
                       NULL,  
                       err_msg);  
      
          DBMS_OUTPUT.put_line ('Error msg is : ' || err_msg);  
    END;  
    /  
      
    1、非INSERT插入异常的处理  
    scott@CNMMBO> @test_bulk_ins_err       -->上面的演示代码被保存到@test_bulk_ins_err.sql文件  
    Error msg is : <<Err @ anonymity_plsql_block - Debug Pos : 30>> - User-Defined Exception ORA-06502:   
    PL/SQL: numeric or value error: character string buffer too small  
    上面的错误提示是由于我们声明的emp_tab嵌套表基于源表，因此出现数据缓冲太小的错误  
      
    PL/SQL procedure successfully completed.  
      
    2、内层exception中bulk_error处产生的异常  
       将代码debugpos:= 30; 之后的3行注释掉  
    scott@CNMMBO> alter table emp_source add constraint ck_is_sync check (is_sync in ('C','E'));  
      
    Table altered.  
      
    scott@CNMMBO> @test_bulk_ins_err  
    Error msg is : <<Err @ anonymity_plsql_block - Debug Pos : 80>> - ORA-02290: check constraint (SCOTT.CK_IS_SYNC) violated  
      
    PL/SQL procedure successfully completed.     
      
    上面由于源表的is_sync列有约束限制，只允许出现C或E值，而bulk_error中是更新为N，所以给出错误提示  
    我们来看看err_log_tbl，两条源表到目标表在非insert时产生的错误信息已经被记录到日志表  
    scott@CNMMBO> select * from err_log_tbl;  
      
    LOG_SEQ LOG_TIME  SP_NAME                TABLE_NAME   TABLE_PK   ERR_MSG  
    ------- --------- ---------------------- ------------ ---------- ---------------------------------------------  
         21 11-AUG-12 anonymity_plsql_block                          <<Err @ anonymity_plsql_block - Debug Pos : 3  
                                                                     0>> - User-Defined Exception ORA-06502: PL/SQ  
                                                                     L: numeric or value error: character string b  
                                                                     uffer too small  
      
         22 11-AUG-12 anonymity_plsql_block                          <<Err @ anonymity_plsql_block - Debug Pos : 8  
                                                                     0>> - ORA-02290: check constraint (SCOTT.CK_I  
                                                                                S_SYNC) violated  
                                                                                    
    3、FORALL子句在insert时产生的异常  
      
    scott@CNMMBO> alter table emp_source drop constraint ck_is_sync;  -->删除emp_source上is_sync列的check约束  
      
    Table altered.  
      
    scott@CNMMBO> @test_bulk_ins_err                                  -->将源表记录同步到目标表  
      
    PL/SQL procedure successfully completed.  
      
    scott@CNMMBO> select * from emp_dest;  
      
         EMPNO ENAME             SAL  
    ---------- ---------- ----------  
          7369 SMITH             800  
          7499 ALLEN            1600  
          7521 WARD             1250  
          7566 JONES            2975  
          7654 MARTIN           1250  
          7698 BLAKE            2850  
          7782 CLARK            2450  
          7788 SCOTT        34171.88  
          7839 KING             5000  
          7844 TURNER           1500  
          7876 ADAMS            1100  
          7900 JAMES             950  
          7902 FORD             3000  
          7934 MILLER           1300  
      
    14 rows selected.  
      
    scott@CNMMBO> select * from emp_source;                           -->可以看到目标表已存在数据且源表状态列被更新  
      
         EMPNO ENAME             SAL I  
    ---------- ---------- ---------- -  
          7369 SMITH             800 Y  
          7499 ALLEN            1600 Y  
          7521 WARD             1250 Y  
          7566 JONES            2975 Y  
          7654 MARTIN           1250 Y  
          7698 BLAKE            2850 Y  
          7782 CLARK            2450 Y  
          7788 SCOTT        34171.88 Y  
          7839 KING             5000 Y  
          7844 TURNER           1500 Y  
          7876 ADAMS            1100 Y  
          7900 JAMES             950 Y  
          7902 FORD             3000 Y  
          7934 MILLER           1300 Y  
      
    14 rows selected.  
      
    -->下面为源表增加3条记录以模仿在INSERT过程中出现的异常  
    scott@CNMMBO> insert into emp_source select 1111,'Robinson',2000,null from dual;  
      
    1 row created.  
      
    scott@CNMMBO> insert into emp_source select 2222,null,1000,null from dual;  
      
    1 row created.  
      
    scott@CNMMBO> insert into emp_source select 3333,'Jackson',100,null from dual;  
      
    1 row created.  
      
    scott@CNMMBO> commit;  
      
    Commit complete.  
      
    scott@CNMMBO> @test_bulk_ins_err   -->再次执行时发现只有empno号为1111的记录被插入，而2222和3333都由于错误而未被同步的目标表  
      
    PL/SQL procedure successfully completed.  
      
    scott@CNMMBO> select * from emp_dest where empno in (1111,2222,3333);  
      
         EMPNO ENAME             SAL  
    ---------- ---------- ----------  
          1111 Robinson         2000  
      
    scott@CNMMBO> select * from emp_source where empno in (1111,2222,3333);  
      
         EMPNO ENAME             SAL I  
    ---------- ---------- ---------- -  
          1111 Robinson         2000 Y  
          2222                  1000 N  
          3333 Jackson           100 N  
      
    -->检查错误日志，未成功插入的到目标表的记录写入到日志，含有表名以及主键，插入时对应的错误信息   
    scott@CNMMBO> select * from err_log_tbl;  
      
    LOG_SEQ LOG_TIME  SP_NAME                TABLE_NAME  TABLE_PK   ERR_MSG  
    ------- --------- ---------------------- ----------- ---------- ---------------------------------------------  
         21 11-AUG-12 anonymity_plsql_block                         <<Err @ anonymity_plsql_block - Debug Pos : 3  
                                                                    0>> - User-Defined Exception ORA-06502: PL/SQ  
                                                                    L: numeric or value error: character string b  
                                                                    uffer too small  
      
         22 11-AUG-12 anonymity_plsql_block                         <<Err @ anonymity_plsql_block - Debug Pos : 8  
                                                                    0>> - ORA-02290: check constraint (SCOTT.CK_I  
                                                                    S_SYNC) violated  
      
         23 11-AUG-12 ins_emp_dest           emp_dest    2222       ORA-01400: cannot insert NULL into ()  
         24 11-AUG-12 ins_emp_dest           emp_dest    3333       ORA-02290: check constraint (.) violated      

三、总结
1、上面的代码成功的实现了数据之间的同步问题(不同数据库使用DB LINK)，且将同步期间的错误信息记录到日志表
2、对于多表需要同步的情形，上述方法便于集中管理错误信息。其次是源表使用状态标志位便于判断相应的记录同步成功的情况
3、同时结合了FORALL 与BULK COLLECT INTO批量SQL方法，且在使用游标打开集合时使用LIMIT子句来减小内存过度开销
4、使用了通过标识错误异常位置的debugpos变量来便于查找那个地方或那几行代码引发异常
5、注意处理error信息时，通过TO_CHAR (emp_tab (SQL%BULK_EXCEPTIONS (i).ERROR_INDEX).empno)得到表上主键的值