看了http://zhouwf0726.itpub.net/post/9689/204480的文章,自己平时使用时虽然注意了很多,但到底差多少自己心里还是没底.
今天测试了一下, 测试pl/sql如下:
set serveroutput on;
declare
t integer;
cnt integer;
st number;
et number;
begin
select count(1) into t from tab where tname='tab1';
if t = 1 then
execute immediate 'drop table tab1';
end if;
execute immediate 'create table tab1(a int)';
cnt := 10000;
st := dbms_utility.get_time;
for i in 1 .. cnt loop
execute immediate 'insert into tab1 values(' || i || ')';
end loop;
et := dbms_utility.get_time;
dbms_output.put_line('insert direct used time: ' || to_char(et-st) || ' hsecs');
execute immediate 'truncate table tab1';
st := dbms_utility.get_time;
for i in 1 .. cnt loop
execute immediate 'insert into tab1 values(:1)' using i;
end loop;
et := dbms_utility.get_time;
dbms_output.put_line('using bind variable used time: ' || to_char(et-st) || ' hsecs');
-- 将一个表的数据insert到另外一个表中
select count(1) into t from tab where tname='tab2';
if t = 1 then
execute immediate 'drop table tab2';
end if;
execute immediate 'create table tab2(a int, c varchar2(100))';
st := dbms_utility.get_time;
execute immediate 'insert into tab2 select a, rowid from tab1';
et := dbms_utility.get_time;
dbms_output.put_line('not used bind variable used time: ' || to_char(et-st) || ' hsecs');
execute immediate 'truncate table tab2';
st := dbms_utility.get_time;
for cval in (select a col1, rowid col2 from tab1) loop
execute immediate 'insert into tab2 values(:1, :2)' using cval.col1, cval.col2;
end loop;
et := dbms_utility.get_time;
dbms_output.put_line('used bind variable used time: ' || to_char(et-st) || ' hsecs');
end;
/
|
测试结果：
===============
insert direct used time: 841 hsecsusing bind variable used time: 327 hsecsnot used bind variable used time: 3 hsecsused bind variable used time: 421 hsecspl/sql 过程已成功完成。看来使用 cursor还是不如一个直接的sql好. 多站在oracle的角度考虑一下该问题还是很容易想的到的.
阅读(1821) | 评论(0) | 转发(0) |
