oracle中bulk collect into用法小结

万望

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

下面有几个例子可以加以说明：

1. --在select into语句中使用bulk collect
   
2. DECLARE
   
3.   TYPE sallist IS TABLE OF emp.sal%TYPE;
   
4.   sals sallist;
   
5. BEGIN
   
6.   -- Limit the number of rows to 100.
   
7.   SELECT sal BULK COLLECT INTO sals FROM emp WHERE rownum <= 100;
   
8.   -- Retrieve 10% (approximately) of the rows in the table.
   
9.   SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
   
10. END;
    
11. /
    
12. --在fetch into中使用bulk collect
    
13. DECLARE TYPE deptrectab IS TABLE OF dept%ROWTYPE;
    
14. dept_recs deptrectab;
    
15. CURSOR c1 IS
    
16.   SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
    
17. BEGIN
    
18.   OPEN c1;
    
19.   FETCH c1 BULK COLLECT
    
20.     INTO dept_recs;
    
21. END;
    
22. /
    
23. --在returning into中使用bulk collect
    
24. CREATE TABLE emp2 AS
    
25.   SELECT * FROM employees;
    
26. DECLARE
    
27.   TYPE numlist IS TABLE OF employees.employee_id%TYPE;
    
28.   enums numlist;
    
29.   TYPE namelist IS TABLE OF employees.last_name%TYPE;
    
30.   names namelist;
    
31. BEGIN
    
32.   DELETE FROM emp2
    
33.    WHERE department_id = 30
    
34.   RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
    
35.   dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
    
36.   FOR i IN enums.first .. enums.last LOOP
    
37.     dbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));
    
38.   END LOOP;
    
39. END;
    
40. / drop TABLE emp2;

复制代码

陈晓龙

陈晓龙

高端