oracle中bulk collect into用法小结 - 度量快速开发平台-专业、快速的软件定制快开平台

在oracle数据库中，通过bulk collect减少loop处理的开销，采用bulk collect可以将查询结果一次性的加载到collections中，而不是通过cursor的fetch一条条处理，可以在select into、fetch into、returning into语句使用bulk collect，注意在使用bulk collect时，所有的变量都必须是collections。

--在select into语句中使用bulk collect
DECLARE
TYPE sallist IS TABLE OF emp.sal%TYPE;
sals sallist;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp WHERE rownum <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
END;
/
--在fetch into中使用bulk collect
DECLARE TYPE deptrectab IS TABLE OF dept%ROWTYPE;
dept_recs deptrectab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT
INTO dept_recs;
END;
/
--在returning into中使用bulk collect
CREATE TABLE emp2 AS
SELECT * FROM employees;
DECLARE
TYPE numlist IS TABLE OF employees.employee_id%TYPE;
enums numlist;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
names namelist;
BEGIN
DELETE FROM emp2
WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.first .. enums.last LOOP
dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/ drop TABLE emp2;

复制代码