1.前言。
oracle10g后,使用绑定变量更能提高效率。
以下转自http://www.educity.cn/develop/555687.html的文章。
oracle10g后,使用绑定变量更能提高效率。
以下转自http://www.educity.cn/develop/555687.html的文章。
让Oracle自己绑定变量
set serverout on;
set timing on;
declare
l_sql varchar();
l_count number;
l_param varchar();
l_param varchar();
begin
l_param:=a;
l_param:=b;
select count(*) into l_count from table where col_=l_param and col_=l_param;
dbms_outputput_line(l_count);
end;
/
在上面的情况Oracle会自己绑定变量即如果参数保存在一个数组中select语句放在一个循环中
select 语句只会编译一次
像这样
for i in
loop
select count(*) into l_count from table where col_=l_param and col_=l_param and col_=i;
dbms_outputput_line(l_count);
end loop
不绑定变量
set serverout on;
set timing on;
declare
l_sql varchar();
l_count number;
l_param varchar();
l_param varchar();
begin
l_param:=a;
l_param:=b;
l_sql:=select count(*) into :x from table where col_=||l_param|| and col_=||l_param;
Execute Immediate l_sql into l_count;
dbms_outputput_line(l_count);
end;
/
动态绑定变量
set serverout on;
set timing on;
declare
l_sql varchar();
l_count number;
l_param varchar();
l_param varchar();
begin
l_param:=a;
l_param:=b;
l_sql:=select count(*) into :x from table where col_=:y and col_=:z ;
Execute Immediate l_sql into l_count using l_paraml_param;
dbms_outputput_line(l_count);
end;
/
:x:y:z相当于占位符即
用:p:p:p是一样的
用:x:x:x也是一样的
需要的绑定变量按顺序排在执行语句后面就可以了into的除外
不过还是用pp好些至少可以看出绑定了多少个变量