Oracle之绑定变量的使用

对于静态sql,where过滤部分如是这种语法格式:where a.id=vv_id,则Oracle会自动使用绑定变量;

对于动态sql，若没有using部分，则没有使用绑定变量;只有使用了using 此语法格式,才会进行变量绑定;

举例如下;

静态sql自动进行变量绑定

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;

/

动态sql进行变量绑定的方法

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;

/