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;