【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/