TYPE type1 IS TABLE OF NUMBER;
v type1:=type1(1, 2, 3, 4);
--
BULK_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(BULK_ERROR, -24381);
BEGIN
FORALL idx IN v.FIRST..v.LAST SAVE EXCEPTIONS
DELETE FROM t_bulk3 WHERE fid=v(idx);
--
FOR idx IN v.FIRST..v.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Statement: #'||idx||', '||SQL%BULK_ROWCOUNT(idx)||' rows were impacted.');
END LOOP;
--
EXCEPTION
WHEN BULK_ERROR THEN
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)||', Statement: #'||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
END LOOP;
WHEN OTHERS THEN
NULL;
END;
Statement: #1, 1 rows were impacted.
Statement: #2, 2 rows were impacted.
Statement: #3, 4 rows were impacted.
Statement: #4, 0 rows were impacted.
PL/SQL procedure successfully completed
（2）BULK COLLECT
假设有一条SQL查询，返回的记录中包含5行，那么如果在PL/SQL中执行此查询，会有5次的PL/SQL – SQL交互，如果使用BULK COLLECT，可以降低到1次。
BULK COLLECT子句可以出现在以下语句中：
SELECT INTO
FETCH
RETURNING INTO
create table t_bulk4(fid number, fval varchar2(20));
insert into t_bulk4 values(1,'abc');
insert into t_bulk4 values(2,'def');
insert into t_bulk4 values(3,'xyz');
insert into t_bulk4 values(4,'xxx');
insert into t_bulk4 values(5,'123');
commit;
DECLARE
TYPE type1 IS TABLE OF t_bulk4%ROWTYPE;
v type1;
BEGIN
SELECT * BULK COLLECT INTO v FROM t_bulk4;
--
FOR i IN 1..v.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v(i).fid||' '||v(i).fval);
END LOOP;
END;
1 abc
2 def
3 xyz
4 xxx
5 123
PL/SQL procedure successfully completed
另一个INDEX BY集合的示例（实际上使用跟上例一样的FOR循环也可以）：
DECLARE
TYPE type1 IS TABLE OF t_bulk4%ROWTYPE INDEX BY PLS_INTEGER;
v type1;
idx PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO v FROM t_bulk4;
--
idx:=v.FIRST;
WHILE(idx IS NOT NULL) LOOP
DBMS_OUTPUT.PUT_LINE(v(idx).fid||' '||v(idx).fval);
idx := v.NEXT(idx);
END LOOP;
END;
FETCH cursor BULK COLLECT INTO的使用与上述例子都类似，不多写了。
关于RETURNING INTO + BULK COLLECT，我们来一个综合的例子：
create table t_bulk5(fid number);
DECLARE
TYPE type1 IS TABLE OF NUMBER;
v type1:=type1(1, 2, 3, 5);
--
TYPE type2 IS TABLE OF t_bulk5.fid%TYPE;
v2 type2;
BEGIN
FORALL idx IN v.FIRST..v.LAST
INSERT INTO t_bulk5 VALUES(v(idx)) RETURNING fid BULK COLLECT INTO v2;
--
DBMS_OUTPUT.PUT_LINE(v2.COUNT);
END;
4
PL/SQL procedure successfully completed
顺便比较一下使用FOR循环时是什么结果：
DECLARE
TYPE type1 IS TABLE OF NUMBER;
v type1:=type1(1, 2, 3, 5);
--
TYPE type2 IS TABLE OF t_bulk5.fid%TYPE;
v2 type2;
BEGIN
FOR idx IN v.FIRST..v.LAST LOOP
INSERT INTO t_bulk5 VALUES(v(idx)) RETURNING fid BULK COLLECT INTO v2;
END LOOP;
--
DBMS_OUTPUT.PUT_LINE(v2.COUNT);
END;
1
PL/SQL procedure successfully completed
这实际上也好理解，因为上面的INSERT语句每次影响的只有一行，所以第二例中，保留的是循环中最后一次执行的INSERT所影响的行数，当然是1；而由于FORALL语句会将所有语句一次性提交到数据库，这也使得我们可以使用RETURNING INTO + BULK COLLECT获取所有插入的数据。
作者 Snowtoday MSN:MyYe110w@hotmail.com