我们继续解说批量绑定相关内容。本章针对是“FORALL语句中批量绑定变量的配列索引值是不连续做法”进行解释。
在批量绑定中处理DML(INSERT、UPDAATE、DELETE)时,之前已经说明了FORALL的语法结构。
但是之前介绍的FORALL句法只能使用配列的索引值是连续的情况。如果配列的索引不连续就会出错。
FORALL语法中,配列不连续的时候语法结构如下:
–配列索引是连续的时候—
FORALL 索引变量 IN 下限值…上限值
使用配列数组的一个DML语句;
–配列索引是不连续的时候—
FORALL 索引变量 IN INDICES OF 配列名称
使用配列数组的一个DML语句;
这里,INDICES表示用的是配列数组所以索引的集合,可以连续可以不连续。
首先,像往常一样,使用TEST01表进行验证。
SQL> DESC TEST01
名称格式
A NUMBER
B VARCHAR2(10)
SQL> SELECT * FROM TEST01;
没有选择记录。
目前TEST01表中没有数据。
那么,A列用的配列和B列用的配列分别保存在PAC1包里的A_TAB、B_TAB的变量里。
例子:
SQL> CREATE OR REPLACE PACKAGE PAC1
2 //
3 -制作包
4 //
5 IS
6 /******************************/
7 --A列用的配列
8 /******************************/
9 TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
10 INDEX BY BINARY_INTEGER;
11 A_TAB A_TAB_TYPE;
12 /******************************/
13 --B列用的配列
14 /******************************/
15 TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
16 INDEX BY BINARY_INTEGER;
17 B_TAB B_TAB_TYPE;
18 END;
19 /
包已创建。
本讲解为了简化起见,一直是按列配列的,A列用的配列,B列用的配列2个配列。
但是A列、B列使用一个记录型配列也是可以的。
假如我们使用A_TAB、B_TAB存储数据,索引1号改为到3号,是连续的。
SQL> BEGIN
2 PAC1.A_TAB(1) := 10; PAC1.A_TAB(2) := 20; PAC1.A_TAB(3) := 30
3 PAC1.B_TAB(1) := ‘A’; PAC1.B_TAB(2) := ‘B’; PAC1.B_TAB(3) := ‘C’;
4 END;
5 /
PL/SQL过程成功完成。
正如您所看到的,A_TAB、B_TAB的索引值1号到3号连续的值。
我们把索引2的值删除,让配列改成非连续的。
SQL> BEGIN
2 PAC1.A_TAB.DELETE(2);
3 PAC1.B_TAB.DELETE(2);
4 END;
5 /
PL/SQL过程成功完成。
这样,A_TAB、B_TAB的索引是2的数据都被删除了。配列状态成为不连续的了。
这样用FORALL的语法在TEST01表上进行出入的化,就会出错。
SQL> BEGIN
2 FORALL I IN 1…3
3 INSERT INTO TEST01 (A,B)
4 VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
5 END;
6 /
BEGIN
*
行1发生错误。:
ORA-22160:索引[2]不存在
ORA-06612:行2
配列索引不连续,因为有不存在的索引,所以发生了错误。
配列不连续的时候,需要改成如下语法格式,就不会发生错误了:
SQL> BEGIN
2 FORALL I IN INDICES OF PAC1.A_TAB
3 INSERT INTO TEST01 (A,B)
4 VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
5 END;
6 /
PL/SQL过程成功完成。
这次处理就正常了。
这里,代码第2行的“IN INDICES OF PAC1.A_TAB”的意思是“使用PAC1.A_TAB配列的索引的集合”。
也就是配列有啥就用啥:用的是索引1和3进行FORALL的。
另外,由于B_TAB配列和A_TAB的索引都是相同的,所以第二行的A_TAB改成B_TAB,“IN INDICES OF PAC1.B_TAB”,也是相同的。
我们确认一下TEST01表中的数据:
SQL> SELECT * FROM TEST01;
A B
10 A
30 C
如上所述,在FORALL语句中,配列索引不连续的时候,需要用特殊的语法。
不连续的配列也叫“稀疏数组”。
假如配列中有存在我们想要处理的数据,也存在不需要处理的数据,
这时候有又该怎么过滤我们想要的数据的。这是我们下次要解释的内容。