create package body pck_refcursor_open_dmep as
genri_cv gentype;--defalut 2  不正确
procedure open_cv(genri_cv in out gentype ,choice int);
genri_cv gentype; ---default 4
begin
null
end ;
end pck_refcursor_open_dmep;

批量fetch数据
declare
type empcurtype is ref cursor return emp%rowtype;
emp_cv empcurtype;
emp_rec emp%rowtype;
begin
open emp_cv for select * from emp where rownum<11;
loop
fetch emp_cv into emp_rec;
exit when emp_cv%notfound ;
dbms_output.put_Line('name = '||emp_rec.ename);
end loop;
close emp_cv;
end;

-----
declare
type empcurtype is ref cursor;
type namelist is table of emp.ename%type;
emp_cv empcurtype;
names namelist;
begin
open emp_cv for select ename from emp where rownum<11;

fetch emp_cv bulk collect into names;
close emp_cv;

for i  in names.first .. names.last
loop
dbms_output.put_Line('name = '||names(i));
end loop;
end;
Oracle里的绑定变量
占位符
绑定变量的典型用法
SQL> var x number;
SQL> var 1 number;
SP2-0553: Illegal variable name "1".
SQL> var xyz number;
SQL> exec :x :=7369;
PL/SQL procedure successfully completed.
SQL> select ename from emp where empno=:x;
ENAME
--------------------
SMITH
SQL> select ename from emp where empno=:xyz;
ENAME
--------------------
SMITH
1 在plsql中select语句的绑定变量的典型用法
declare
vc_name varchar2(20);
begin
 execute immediate 'select ename from emp where empno=:1' into vc_name using 7369;
  DBMS_OUTPUT.PUT_LINE('name = '||vc_name);
  end;
2 plsql中dml语句
declare
v_sql1 varchar2(4000);
v_sql2 varchar2(4000);
v_temp1 number;
v_temp2 number;
begin
v_sql1:='insert into emp(empno,ename) values(:1,:2)';
 execute immediate v_sql1  using 8000,'hongquan';
 v_temp1:=sql%rowcount;
 v_sql2:='insert into emp(empno,ename) values(:1,:1)';
  execute immediate v_sql2  using 8001,'hongquan2';
  v_temp2:=sql%rowcount;
 DBMS_OUTPUT.PUT_LINE(to_char(v_temp1+v_temp2));
  end;

----不固定的条件
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 name='cursor_sharing';

839 cursor_sharing 2 EXACT

Exact 默认值，Oracle不会用系统产生的绑定变量来替换目标sql的sql文本中where条件或者values字句中的具体输入值。

自适应游标共享 11g 引入