与循环遍历不同,使用BULK COLLECT子句可以批量检索结果,即一次性将结果集绑定到一个表变量中,并从SQL引擎发送到PLSQL引擎,大大减少循环处理的成本。需要注意的是,BULK COLLECT必须将结果集绑定到表类型的变量中。通常可以在SELECT INTO、FETCH INTO以及RETURNING INTO子句中使用BULK COLLECT。
一、SELECT BULK COLLECT INTO
DECLARE
TYPE emp_rcd_type IS RECORD(
empno emp.empno%TYPE
,ename emp.ename%TYPE);
TYPE emp_tbl_type IS TABLE OF emp_rcd_type;
t_emp emp_tbl_type := emp_tbl_type();
BEGIN
SELECT e.empno, e.ename BULK COLLECT INTO t_emp FROM emp e;
END;
在SELECT BULK COLLECT INTO中,不会报NO_DATA_FOUND异常,更不会报TOO_MANY_ROWS异常。
二、FETCH BULK COLLECT INTO
DECLARE
TYPE emp_rcd_type IS RECORD(
empno emp.empno%TYPE
,ename emp.ename%TYPE);
TYPE emp_tbl_type IS TABLE OF emp_rcd_type;
t_emp emp_tbl_type := emp_tbl_type();
CURSOR cur_emp IS
SELECT e.empno, e.ename FROM emp e;
BEGIN
OPEN cur_emp;
FETCH cur_emp BULK COLLECT
INTO t_emp;
CLOSE cur_emp;
END;
过大的数据量一次性批量FETCH存在性能下降的风险,此时一般采用LIMIT子句来限制每次绑定的数据行数。
DECLARE
TYPE number_tbl_type IS TABLE OF NUMBER;
t_trx number_tbl_type := number_tbl_type();
CURSOR cur_trx IS
SELECT mmt.transaction_id
FROM mtl_material_transactions mmt
WHERE mmt.organization_id = 100
AND mmt.transaction_type_id = 33
AND mmt.invoiced_flag = 'N';
BEGIN
OPEN cur_trx;
LOOP
FETCH cur_trx BULK COLLECT
INTO t_trx LIMIT 500; --fetch 500 records each time
EXIT WHEN t_trx.count = 0;
END LOOP;
CLOSE cur_trx;
END;
三、RETURNING BULK COLLECT INTO
DECLARE
TYPE number_tbl_type IS TABLE OF NUMBER;
t_empno number_tbl_type := number_tbl_type();
BEGIN
UPDATE emp e
SET e.comm = 500
WHERE e.sal < 1500
RETURNING e.empno BULK COLLECT INTO t_empno;
END;