PLSQL入门与精通（第66章：批量绑定异常处理）

本次介绍一下“批量绑定”发生异常的时候的处理。

使用数组数据对DML进行FORALL统一处理时，数据内容可能会违反制约等而导致的执行错误。这种情况下，FORALL处理失败后会全部被回滚。

但是，发生错误的时候提示了一个简单的错误信息，具体时间数组中的哪个数据发生了什么样的错误是无法判断的。
但是，实际上我们调查异常的时候，需要知道是哪条数据出什么样的错误了，系统也提供了相应的方法。

我们准备一个出错的FORALL的例子：

TEST01表中设定一些限制。

先删除TEST01表。

SQL> DROP TABLE TEST01;

表已删除。

重新制作有限制的TEST01表。

SQL>
CREATE TABLE TEST01
( A NUMBER CONSTRAINT TEST01_PK PRIMARY KEY ,
B VARCHAR2(10) CONSTRAINT TEST01_CHK CHECK ( B IN ( ‘AA’ , ‘BB’ , ‘CC’ )))
/

表创建成功。

A列是主键。

B列增加了一个限制，它得值必须是“AA”、“BB”、“CC”里边的一个值。

那么，像之前一样，在包上创建2个列的配列。

SQL>

CREATE OR REPLACE PACKAGE PAC1
//
–制作包
//
IS
/******************************/
–A列用的配列
/******************************/
TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
INDEX BY BINARY_INTEGER;
A_TAB A_TAB_TYPE;

/******************************/
--B列用的配列
/******************************/
TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
INDEX BY BINARY_INTEGER;
B_TAB B_TAB_TYPE;
END ;

/
包已创建。

在这个包定义了2个配列A_TAB和B_TAB配列。
接着，我们定义使用的数据，该数据中有些数据违反了A列和B列的约束规则的数据。

SQL>
BEGIN

--索引值1O的数据
PAC1.A_TAB(1) := 10 ; PAC1.B_TAB(1) := 'AA' ;

--索引值2O的数据
PAC1.A_TAB(2) := 20 ; PAC1.B_TAB(2) := 'BB' ;

--索引值3的数据，违反了A列不能为空的规则
PAC1.A_TAB(3) := NULL ; PAC1.B_TAB(3) := 'BB' ;

--索引值4O的数据
PAC1.A_TAB(4) := 30 ; PAC1.B_TAB(4) := 'CC' ;

--索引值5的数据，违反了A列为主键不能重复的规则
PAC1.A_TAB(5) := 10 ; PAC1.B_TAB(5) := 'CC' ;

--索引值6O的数据
PAC1.A_TAB(6) := 40 ; PAC1.B_TAB(6) := 'CC' ;

--索引值7的数据，违反了B列必须是AA BB CC的某个值的规则
PAC1.A_TAB(7) := 50 ; PAC1.B_TAB(7) := 'EE' ;

END ;
/

PL/SQL过程成功完成。

这样一来，PAC1包的A_TAB配列、B_TAB配列中保存了索引值1～7的数据，
索引值3、5、7的数据违反了规则，往TEST01表中INSERT时会发生错误。

理由如下：

索引值3，A列的值是NULL。A列为主键，NULL为错误。
索引值5，A列的值是10。在索引值1中A列的值为10的数据已经被插入到表里了，因此会发生主键冲突的错误。
索引值7，B列的值是EE。B列有限制，允许的值只有“AA”、“BB”、“CC”。
因此，“EE”是不被允许的值，因此在往TEST01表中INSERT时会出错。

我们用这个配列进行批量绑定插入一下，看看结果吧：

SQL>
BEGIN
FORALL I IN PAC1.A_TAB. FIRST …PAC1.A_TAB. LAST
INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
END ;
/

BEGIN
*
行1发生错误。:
ORA-01400：（“SCOTT”.“TEST01”.“A”）不能插入NULL。
ORA-06612:行2

和预想的一样，发生了错误。

这个错误，是因为索引值3的地方A列的值是NULL。
由于索引值3的错误，FORALL文本身会被回滚，所以索引1和2的INSERT处理会被回滚。

索引值4以后的处理就不会继续进行了。

因此没有数据插入到表里：

SQL> SELECT * FROM TEST01;

没有选择记录。

假如这些数据里边，及时有些数据出错，其他正常的数据还想继续插入到表里，该怎么办呢？

这种情况下，在FORALL文的DML文之前，加上“SAVE EXCEPTIONS”的关键字就可以了。

BEGIN
FORALL I IN PAC1.A_TAB. FIRST …PAC1.A_TAB. LAST
SAVE EXCEPTIONS – ★★
INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
END ;
/

ERROR:
ORA-24381:DML配列有错误。
ORA-06612:行2

果然，这个也出错了。

上述PL/SQL块的代码在第3行中添加了“SAVE EXCEPTIONS”关键字，意思就是说即使数组数据有错误，
也让处理继续，把数组数据处理完为止。

但是该处理虽然处理完了，但是还是有错误信息提示：“ORA-24381:DML配列有错误。”，这样我们就会知道有错误的数据了。
如果没有显示此错误消息，则不知道数组数据中有错误。

还有，由于有该错误存在，PL/SQL块的结束状态是异常的状态，处历史结束了，但是该块内的处理还是会被回滚的，
TEST01表中没有一行数据。

因此，如果使用“SAVE EXCEPTONS”关键字，要想把正常数据插入到表里，必须准备“ORA-24381”错误的例外处理程序，
即使发生错误，也必须让PLSQL块正常结束。

在这种情况下，我们通常会收集发生错误的数组数据信息，
还需要知道哪个配列的数据发生了错误。

代码如下：

SQL>
DECLARE
ERR_COUNT number;
BEGIN

FORALL I IN PAC1.A_TAB. FIRST ..PAC1.A_TAB. LAST
SAVE EXCEPTIONS
INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));

EXCEPTION
WHEN others THEN
IF SQLCODE=-24381 THEN --当错误为ORA-24318时候，进行以下错误处理
ERR COUNT:=SQL%BULK EXCEPTIONS.COUNT；–获取错误数据的件数
DBMS_OUTPUT.PUT_LINE（‘错误数据数量：’ || ERR COUNT）；
FOR i IN 1…ERR_COUNT LOOP
–显示错误数据的索引值
DBMS_OUTPUT.PUT（‘索引值：’|| SQL%BULK EXCEPTIONS（i）.ERROR INDEX’ || ’ ’ || ）；
–错误消息（错误代码⇒sqlerrm功能）
DBMS_OUTPUT.PUT_LINE(sqlerrm(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END IF;
END ;
/

错误数据数量：3
索引值：3O RA-01400：（）中不能插入NULL。
索引值：7O RA-022290：违反了检查限制（.）
索引值：5O RA-00001:违反唯一约束（.）

PL/SQL过程成功完成。

简单地解说一下
上边例子中的“SQL%BULK EXCEPTIONS（i）.ERROR CODE”是Oracle的错误编号，
sqlerrm:将该编号乘以“-1”变成正数，取到编号，用改编号通过sqlerrm函数，获取错误消息，并显示到控制台上。

通常，SQL%BULK EXCEPTIONS（i）.ERROR CODE会返回错误编号，错误编号为负数，因此需要乘以“-1”。

由于以上对应PL/SQL块是正常结束的，所以TEST01表中应该存储了正常的数据。

数据插入结果如下：

SQL> SELECT * FROM TEST01;
A B

---

10 AA
20 BB
30 CC
40 CC

正常的数据被插入到表里了。

这样一来，即使数组数据的一部分数据有错误，也可以行FORALL，把其他正常的数据
插入到表里，而且还可以知道出错数据的索引值以及相对应的错误。

这就是批量绑定出错是的处理。