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

你的名字 2022-04-04 02:55 273阅读 0赞

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

原创 [Linux操作系统][Linux] 作者：[yafeishi][] 时间：2012-03-16 16:34:25  5325  0

最近在写一个存储过程，从一个大表中取数据。用到了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;

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;

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  转载请标明出处。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23008765/viewspace-718792/，如需转载，请注明出处，否则将追究法律责任。

[http://blog.itpub.net/23008765/viewspace-718792/][http_blog.itpub.net_23008765_viewspace-718792]

[Linux]: http://blog.itpub.net/linux/
[yafeishi]: http://blog.itpub.net/23008765/
[http_blog.itpub.net_23008765_viewspace-718792]: http://blog.itpub.net/23008765/viewspace-718792/