FORALL AND BULK COLLECT的测试

测试脚本在SCOTT用户下运行[@more@]set serveroutput on
drop table emp_bak;
create table emp_bak(empno number(4),ename varchar2(10),note varchar2(30));
drop procedure bulktest;
create or replace procedure bulktest is
type NumTab is table of emp.empno%type;
type NameTab is table of emp.ename%type;
--type NumTab is table of emp.empno%type index by binary_integer;
--type NameTab is table of emp.ename%type index by binary_integer;
enums NumTab;
names NameTab;
cursor c is select empno,ename from emp;
begin
execute immediate 'truncate table emp_bak';
dbms_output.put_line('Implicit Cursor ...');
select empno,ename bulk collect
into enums,names
from emp;
dbms_output.put_line('forall');
for i in enums.first..enums.last loop
dbms_output.put_line(enums(i) || ' ' || names(i));
end loop;
dbms_output.put_line('for .. in .. loop');
forall i in enums.first..enums.last
insert into emp_bak values (enums(i),names(i),'Implicit');
dbms_output.put_line('================================');
dbms_output.put_line('Explicit Cursor ...');
open c;
fetch c bulk collect into enums,names;
dbms_output.put_line('for .. in .. loop');
for i in enums.first..enums.last loop
dbms_output.put_line(enums(i) || ' ' || names(i));
end loop;
close c;
dbms_output.put_line('forall');
forall i in enums.first..enums.last
insert into emp_bak values (enums(i),names(i),'Explicit');
end;
/
begin
bulktest;
end;
/
select * from emp_bak;

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

转载于:http://blog.itpub.net/802415/viewspace-862733/