绑定变量,主要好处,是提高程序执行速度,特别是大批量执行时。
declare
vc_name varchar2(10);
begin
execute immediate 'select ename from emp where empno= :1' into vc_name using 7876; --多个值用逗号分开
dbms_output.put_line(vc_name);
end;
所以在pl/sql中绑定变量的标准语法为:
execute immediate 【使用绑定变量的语句】 using 对应绑定变量的具体输入值;
declare
v1_name varchar2(10);
v2_name varchar2(10);
begin
--select user_name,USER_ADDRESS from USER_INFO_MAIN where user_code=:1
execute immediate 'select user_name,USER_ADDRESS from USER_INFO_MAIN where user_code=:1 and RELA_CELL=:2' into v1_name,v2_name using 81010001,14786528841; --多个值用逗号分开
--execute immediate 'select ename from emp where empno= :1' into vc_name using 7876; --多个值用逗号分开
dbms_output.put_line(v1_name);
dbms_output.put_line(v2_name);
end;
插入语句的使用
declare
vc_sql_1 varchar2(4000);
vc_sql_2 varchar2(4000);
n_temp_1 number;
n_temp_2 number;
begin
vc_sql_1 := 'insert into sap(num_1,num_2) values(:1,:2)';
execute immediate vc_sql_1 using 7370,7788;
n_temp_1 := sql%rowcount;
vc_sql_2 := 'insert into sap(num_1,num_2) values(:1,:1)';
execute immediate vc_sql_2 using 7371,7799;
n_temp_2 := sql%rowcount;
dbms_output.put_line(to_char(n_temp_1+n_temp_2));
commit;
end;
--using 根据位置传入相关变量参数值
--动态sql可以使用绑定变量,returning 可以和带绑定变量的目标sql连用,目的把受该sql影响的行记录的对应字段值给取出来.
--eg
declare
vc_column varchar2(10);
vc_sql varchar2(4000);
n_temp number;
vc_name varchar2(10);
begin
vc_column := 'empno';
vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
execute immediate vc_sql using 7369 returning into vc_name;
dbms_output.put_line(vc_ename);
commit;
end;
主要优势,一次处理一批数据。
可以见到地将PL/SQL引擎看做专门用来处理PL/SQL代码块中除了sql之外的所有部分(eg:变量、复制、循环等)子系统,SQL引擎用来处理sql语句的子系统。 这里的PL/SQL引擎和SQL引起上下文切换就是指他们之间的交互。
--减少交互,提高性能
fetch cursorname bulk collect into [自定义的属组] <limit CN_BATCH_SIZE>
--eg "forall" 表示一次执行一批sql
declare
cur_emp sys_refcursor;
vc_sql varchar2(4000);
type namelist is table of varchar2(10);
enames namelist;
CN_BATCH_SIZE constant pls_integer :=1000;
begin
vc_sql := 'select ename from emp where empno > :1';
open cur_emp for vc_sql using 7900;
loop
fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
for i in 1..enames.count loop
dbms_output.put_line(enames(i));
end loop;
exit when enames.count < CN_BATCH_SIZE;
end loop;
close cur_emp;
end;
****************************
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 andcol_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;
/
这里强烈推荐使用静态绑定变量,有兴趣的话可以自己比较;
自己记录下,部分转自网络