|
4#
楼主 |
发表于 2005-10-27 13:15
|
只看该作者
用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.
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 |
|