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

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

原创 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;

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

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

http://blog.itpub.net/23008765/viewspace-718792/