存储过程 返回一个集合(列表)我是这么做的.分两步:
1、创建包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;2、创建过程create or replace procedure sp_proc(spno in number,p_cursor out tespackage.test_cursor) is
begin
open p_cursor for select * from emp;
end;在调用这个存储过程 怎么处理输出参数(out)exec sp_proc.....?请把这个结果写下,或有另外的方法?请写下(只在PLSQL Developer中操作啊!)?
1、创建包
create or replace package tespackage as
type test_cursor is ref cursor;
end tespackage;2、创建过程create or replace procedure sp_proc(spno in number,p_cursor out tespackage.test_cursor) is
begin
open p_cursor for select * from emp;
end;在调用这个存储过程 怎么处理输出参数(out)exec sp_proc.....?请把这个结果写下,或有另外的方法?请写下(只在PLSQL Developer中操作啊!)?
v_cur tespackage.test_cursor;
begin
sp_proc(1,v_cur);
end;
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> 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 20SQL>
--下面是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.
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;