17,088
社区成员
发帖
与我相关
我的任务
分享
--通用
declare
cur tespackage.test_cursor;
spno number;
c_emp emp%rowtype;
begin
spno:=xxxx;
sp_proc(spno,cur);
fetch cur into c_emp;
while cur%found loop
dbms_output.put_line(c_emp.empno||' '||c_emp.ename||' '||....);--
fetch cur into c_emp;
end loop;
close cur;
end;
--上面是sqlplus中执行的,
--下面是developer中这样执行
declare
v_cur tespackage.test_cursor;
rs emp%rowtype;
begin
sp_proc('7369',v_cur);
loop
fetch v_cur into rs;
exit when v_cur%notfound;
Dbms_Output.put_line('ename='||rs.ename);
end loop;
close v_cur;
end;
PL/SQL block, executed in 0 sec.
ename=SMITH
Total execution time 0.015 sec.
Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production
SQL> set serveroutput ON
SQL> set linesize 32767
SQL> create or replace package tespackage as
2 type test_cursor is ref cursor;
3 end tespackage;
4 /
Package created.
SQL> create or replace procedure sp_proc(spno in number,p_cursor out tespackage.test_cursor) is
2 begin
3 open p_cursor for select * from emp where empno=spno;
4 end;
5 /
Procedure created.
SQL> var cur refcursor
SQL> exec sp_proc('7369',:cur);
PL/SQL procedure successfully completed.
SQL> print cur
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- -----
7369 SMITH CLERK 7902 17-12月-80 12000 20
SQL>