2.怎样使用绑定变量?
下面举例说明:
2.1.让Oracle自己绑定变量(也叫静态绑定变量)

set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2;
dbms_output.put_line(l_count);
end;
/
在上面的情况，Oracle会自己绑定变量，即，如果参数保存在一个数组中，select语句放在一个循环中，
select 语句只会编译一次。

2.2 .动态绑定变量
set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';
Execute Immediate l_sql into l_count using l_param1,l_param2;
dbms_output.put_line(l_count);
end;
/

2.3. dbms_output的绑定变量使用
Set echo on;
Set serveroutput on;
Set timming on;
declare
cursor_id integer;
i number;
xSql Varchar2(200);
xOut varchar2(200);
l_start number default dbms_utility.get_time;
xRow  integer;
Begin
  cursor_id:=DBMS_Sql.open_cursor;
  For i in  1..1000 Loop
     DBMS_Sql.parse(cursor_id,'insert into t values(:username,:user_id,Sysdate)',DBMS_SQL.V7);
     DBMS_Sql.bind_variable(cursor_id,'username','test'||to_char(i));
     DBMS_Sql.bind_variable(cursor_id,'user_id',i);
     xRow:=DBMS_Sql.execute(cursor_id);
     --insert into t values('test'||to_char(i),i,Sysdate);
     --xSql:='insert into t values(:username,:user_id,Sysdate)';
     --execute immediate xSql using 'test'||to_char(i),i;
  End loop;
  DBMS_sql.close_cursor(cursor_id);
  dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2) ||'seconds...');
  --xOut:=to_char(round((dbms_utility.get_time-l_start)/100,2)) ||'seconds...';
  --xOut:='seconds...';
  --return xout;
end;

这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;