FORALL 之 SAVE EXCEPTIONS 子句应用一例
对于大批量的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.empscott@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.-->创建一个用于错误日志表上的sequenceCREATE SEQUENCE SCOTT.ERR_SEQ START WITH 20 MAXVALUE 999999999999999999999999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER;-->创建一个函数用于获取sequenceCREATE OR REPLACE FUNCTION SCOTT.gen_new_err_seq RETURN err_log_tbl.log_seq%TYPEIS 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.csdn.net/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_errError msg is : <<Err @ anonymity_plsql_block - Debug Pos : 80>> - ORA-02290: check constraint (SCOTT.CK_IS_SYNC) violatedPL/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 130014 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 Y14 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 2000scott@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)得到表上主键的值
四、更多参考
DML Error Logging 特性
PL/SQL --> 游标
PL/SQL --> 隐式游标(SQL%FOUND)
批量SQL之 FORALL 语句
批量SQL之 BULK COLLECT 子句
PL/SQL 集合的初始化与赋值
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划