oracle中使用绑定变量的好处的例子

oracle中使用绑定变量的好处

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as scott

SQL> set timing on;

Executed in 0.468 seconds

SQL> create table test(id varchar2(8)); 

Table created

Executed in 0 seconds

SQL> 
SQL> declare
  2   s varchar2(300);
  3   begin
  4   for i in 1..100000 loop
  5   s:='insert into test (id) values('||i||')';
  6   execute immediate s;
  7   end loop;
  8  end;
  9  /


PL/SQL procedure successfully completed

Executed in 84.516 seconds

SQL> 
SQL> declare
  2   s varchar2(300);
  3   begin
  4   for i in 1..100000 loop
  5   s:='insert into test (id) values(:1)';
  6   execute immediate s using i;
  7   end loop;
  8  end;
  9  /


PL/SQL procedure successfully completed

Executed in 3.141 seconds

SQL>