java cursor_cursor

declare

type rc is ref cursor;

cursor c is

select * from dual; --静态光标

l_cursor rc; --定义ref cursor,这里没有具体定义cursor的内容，而是在begin中根据判断条件进行定义

begin

if (to_char(sysdate, 'dd') = 30) then

open l_cursor for 'select * from emp'; --动态打开cursor

elsif (to_char(sysdate, 'dd') = 29) then

open l_cursor for

select * from dept;

else

open l_cursor for

select * from dual;

end if;

open c;

end;

/

declare

type info_rc is record(

empno varchar2(32),

ename varchar2(30),

sal number(5));

type i_ref_cur is ref cursor return info_rc;

my_cur i_ref_cur;

my_rec info_rc;

begin

open my_cur for

select empno, ename, sal from emp;

loop

fetch my_cur

into my_rec;

exit when my_cur%notfound;

dbms_output.put_line(my_rec.empno || ' ' || my_rec.ename || ' ' ||

my_rec.sal);

end loop;

close my_cur;

end;

/

--返回值为cursor

create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,

out_curEmp out sys_refcursor) as

begin

open out_curEmp for

select * from emp where deptno = in_deptNo;

exception

when others then

raise_application_error(-20001, 'Error in getEmpByDept' || sqlcode);

end getEmpByDept;

declare

type cur_rc is ref cursor;

rset cur_rc;

rec emp%rowtype;

begin

getEmpByDept(10, rset);

loop fetch rset into rec;

exit when rset%notfound;

dbms_output.put_line(rec.ename);

end loop;

end;

/

--带参数的游标

declare

cursor cur_emp(v_deptno number) is

select * from emp where deptno = v_deptno;

v_cur cur_emp%rowtype;

begin

for v_cur in cur_emp(10) loop

dbms_output.put_line(v_cur.ename);

end loop;

end;