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

Oracle 同时被 2 个专栏收录
101 篇文章 1 订阅
61 篇文章 0 订阅

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

 

 

 

  • 0
    点赞
  • 0
    评论
  • 4
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值