ITPub博客

首页 > 应用开发 > IT综合 > REF CURSOR 小结 (4)

REF CURSOR 小结 (4)

原创 IT综合 作者:zhyuh 时间:2005-10-27 11:32:23 0 删除 编辑

用REF CURSOR实现BULK功能


1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.

[@more@]

SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;
Table created

-------------------------------------------------------

create or replace procedure REF_BULK is
type empcurtyp is ref cursor;
type idlist is table of emp.empno%type;
type namelist is table of emp.ename%type;
type sallist is table of emp.sal%type;

emp_cv empcurtyp;
ids idlist;
names namelist;
sals sallist;

row_cnt number;
begin
open emp_cv for
select empno, ename, sal from emp;
fetch emp_cv BULK COLLECT
INTO ids, names, sals; --BULK COLLECT INTO instead of INTO
close emp_cv;

for i in ids.first .. ids.last loop
dbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||
' salary=' || sals(i));
end loop;

forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOP
insert into tab2 values (ids(i), names(i), sals(i));
commit;

select count(*) into row_cnt from tab2;
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('The row number of tab2 is ' || row_cnt);
end REF_BULK;

------------------------------------------------------------

执行:

SQL> exec ref_bulk;

id=7369 name=SMITH salary=800
id=7499 name=ALLEN salary=1600
id=7521 name=WARD salary=1250
id=7566 name=JONES salary=2975
id=7654 name=MARTIN salary=1250
id=7698 name=BLAKE salary=2850
id=7782 name=CLARK salary=2450
id=7788 name=SCOTT salary=3000
id=7839 name=KING salary=5000
id=7844 name=TURNER salary=1500
id=7876 name=ADAMS salary=1100
id=7900 name=JAMES salary=950
id=7902 name=FORD salary=3000
id=7934 name=MILLER salary=1300
-----------------------------------
The row number of tab2 is 14

PL/SQL procedure successfully completed

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

上一篇: REF CURSOR 小结 (3)
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2027053