----不固定的条件
declare
vc_column varchar2(10);
v_sql1 varchar2(4000);
v_temp1 number;
vc_name varchar2(10);
begin
vc_name:= 'empno';
v_sql1:='delete from emp where ' ||vc_name || ' = :1 returning ename into :2';
execute immediate v_sql1 using 8000 returning into vc_name;
DBMS_OUTPUT.PUT_LINE(vc_name);
commit;
end;
批量绑定
declare
cur_emp sys_refcursor ;
v_sql varchar2(4000);
type namelist is table of varchar2(10);
names namelist;
cn_batch_size constant pls_integer :=1000;
begin
v_sql :='select ename from emp where empno> :1';
open cur_emp for v_sql using 7900;
loop
fetch cur_emp bulk collect into names limit cn_batch_size;
for i in 1 .. names.count loop
dbms_output.put_Line(names(i));
end loop;
exit when names.count <cn_batch_size;
end loop;
close cur_emp;
end;
Oracle里的共享游标
Shared cursor之间的共享，就是重用存储在child cursor中的解析树和执行计划，避免不用从头开始硬解析
常用游标共享，参数cursor_sharing
select * from v$parameter where;
839 cursor_sharing 2 EXACT
Exact 默认值，Oracle不会用系统产生的绑定变量来替换目标sql的sql文本中where条件或者values字句中的具体输入值。
自适应游标共享 11g 引入