存储过程的四种写法(转)

一个存储过程的四种写法
create table test(
id number(8),
name varchar2(32),
constraint pk_test_id primary key (id)
);

begin
for i in 1..20000 loop
insert into test values(i,'abc');
end loop;
commit;
end;
/




create or replace procedure up_test1
as
cursor cur_tests is select id from test;
begin
for cur_test in cur_tests loop
update test t set name=name||'a' where t.id=cur_test.id;
end loop;
commit;
end;
/
create or replace procedure up_test2
as
cursor cur_tests is select rowid from test;
begin
for cur_test in cur_tests loop
update test t set name=name||'a' where rowid=cur_test.rowid;
end loop;
commit;
end;
/
create or replace procedure up_test3
as
type tv_rowid is table of varchar2(32);
v_rowid tv_rowid;
cursor cur_tests is select rowid from test;
begin
open cur_tests;
fetch cur_tests bulk collect into v_rowid;
for i in 1..v_rowid.count loop
update test t set name=name||'a' where rowid=chartorowid(v_rowid(i));
end loop;
commit;
close cur_tests;
end;
/
create or replace procedure up_test4
as
type tv_rowid is table of varchar2(32);
v_rowid tv_rowid;
cursor cur_tests is select rowid from test;
begin
open cur_tests;
fetch cur_tests bulk collect into v_rowid;
forall i in 1..v_rowid.count
update test t set name=name||'a' where rowid=chartorowid(v_rowid(i));
commit;
close cur_tests;
end;
/

alter session set events '10046 trace name context forever,level 12';
exec up_test1;
exec up_test2;
exec up_test3;
exec up_test4;
alter session set events '10046 trace name context off';

Elapsed: 00:00:06.43
Elapsed: 00:00:06.96
Elapsed: 00:00:05.84
Elapsed: 00:00:04.22

http://xzh2000.itpub.net/post/96/42227