oracle游标批量提交,【oracle】从游标中批量取数据：fetch .. bulk collect into .. limit N...

最近在写一个存储过程，从一个大表中取数据。用到了fetch .. bulk collect into .. limit N

可以实现批量取出数据，进行操作。

下面通过实验说明用法：

SQL> create or replace procedure test_pro as

2 type t_cur is REF cursor;

3

4 c_table t_cur;

5 type tab_rowid is table of rowid;

6 v_rowid tab_rowid;

7 v_table varchar2(100);

8 v_newtab varchar2(100);

9 v_sql varchar2(2000);

10

11 begin

12 v_table:=’emp’;

13 v_newtab:=’empcp’;

14 open c_table for ‘select rowid from ‘||v_table;

15 loop

16 v_sql:=’insert into ‘||v_newtab||’ select * from ‘||v_table||’ where ROWID =:a’;

17 fetch c_table bulk collect into v_rowid limit 3;

18 for i in 1 .. v_rowid.count loop

19 execute immediate v_sql using in v_rowid(i);

20 dbms_output.put_line(v_sql);

21 end loop;

22 commit;

23 exit when c_table%notfound;

24 end loop;

25 close c_table;

26 end;

27 /

Procedure created

创建过程。

SQL> select * from empcp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

开始表为空。

SQL> execute test_pro;

PL/SQL procedure successfully completed。

过程执行成功。查询 empcp：

SQL> select * from empcp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7839 KING PRESIDENT 1981/11/17 5000.00 10

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

1234 dang clerk 10000.00

9299 Steven 3948.00

16 rows selected

下一步，更改 exit when。。。的位置。

清空empcp：

SQL> truncate table empcp;

Table truncated

SQL> select * from empcp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

再次创建过程：

SQL> create or replace procedure test_pro as

2 type t_cur is REF cursor;

3

4 c_table t_cur;

5 type tab_rowid is table of rowid;

6 v_rowid tab_rowid;

7 v_table varchar2(100);

8 v_newtab varchar2(100);

9 v_sql varchar2(2000);

10

11 begin

12 v_table:=’emp’;

13 v_newtab:=’empcp’;

14 open c_table for ‘select rowid from ‘||v_table;

15 loop

16 v_sql:=’insert into ‘||v_newtab||’ select * from ‘||v_table||’ where ROWID =:a’;

17 fetch c_table bulk collect into v_rowid limit 3;

18 exit when c_table%notfound;

19 for i in 1 .. v_rowid.count loop

20 execute immediate v_sql using in v_rowid(i);

21 dbms_output.put_line(v_sql);

22 end loop;

23 commit;

24

25 end loop;

26 close c_table;

27 end;

28 /

Procedure created

执行过程：

SQL> execute test_pro;

PL/SQL procedure successfully completed

查询 empcp:

SQL> select * from empcp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7839 KING PRESIDENT 1981/11/17 5000.00 10

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

1234 dang clerk 10000.00

15 rows selected

发现和之前的结果不一样。更改limit 后面的数字，再次测试:

SQL> create or replace procedure test_pro as

2 type t_cur is REF cursor;

3

4 c_table t_cur;

5 type tab_rowid is table of rowid;

6 v_rowid tab_rowid;

7 v_table varchar2(100);

8 v_newtab varchar2(100);

9 v_sql varchar2(2000);

10

11 begin

12 v_table:=’emp’;

13 v_newtab:=’empcp’;

14 open c_table for ‘select rowid from ‘||v_table;

15 loop

16 v_sql:=’insert into ‘||v_newtab||’ select * from ‘||v_table||’ where ROWID =:a’;

17 fetch c_table bulk collect into v_rowid limit 10;

18 exit when c_table%notfound;

19 for i in 1 .. v_rowid.count loop

20 execute immediate v_sql using in v_rowid(i);

21 dbms_output.put_line(v_sql);

22 end loop;

23 commit;

24

25 end loop;

26 close c_table;

27 end;

28 /

Procedure created

SQL> truncate table empcp;

Table truncated

SQL> execute test_pro;

PL/SQL procedure successfully completed

SQL> select * from empcp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

—– ———- ——— —– ———– ——— ——— ——

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7839 KING PRESIDENT 1981/11/17 5000.00 10

10 rows selected

发现只有10条记录。

这两次测试发现，当exit和 fetch挨着的时候，取出的数据就小于等于 limit N 中 N的整数倍。

当把 exit 写在 for 循环外面时 ，就能完整取出所有数据。

mark。

本文链接： http://www.yafeishi.net/blog/2012/03/16/oracle-fetch-b…-collect-limit.html  转载请标明出处。