Oracle SQL 中绑定变量小测试

看了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 hsecs
using bind variable used time: 327 hsecs
Not used bind variable used time: 3 hsecs
Used bind variable used time: 421 hsecs

PL/SQL 过程已成功完成。

看来使用 cursor还是不如一个直接的SQL好. 多站在Oracle的角度考虑一下该问题还是很容易想的到的.