数组?就是用ref cursor类型就行: CREATE OR REPLACE PACKAGE CURSPKG AS TYPE T_CURSOR IS REF CURSOR; PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR); PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR); END CURSPKG; / CREATE OR REPLACE PACKAGE BODY CURSPKG AS PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR; BEGIN IF N_EMPNO <> 0 THEN OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO; ELSE OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; END IF; IO_CURSOR := V_CURSOR; END OPEN_ONE_CURSOR; PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOR OUT T_CURSOR) IS V_CURSOR1 T_CURSOR; V_CURSOR2 T_CURSOR; BEGIN OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT; EMPCURSOR := V_CURSOR1; DEPTCURSOR := V_CURSOR2; END OPEN_TWO_CURSORS; END CURSPKG; /------------------------ --ref cursor òsö\ùpfor,æⁿö\ÄgùpòüÆ╩loopC:oracleora92sqlplusdemo>sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on É»è·ÿZ 4îÄ 2 11:09:06 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn scott/tiger@orcl connected. SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type, 2 out_curEmp out SYS_REFCURSOR) as 3 4 begin 5 open out_curEmp for 6 SELECT * FROM emp WHERE deptno = in_deptNo ; 7 EXCEPTION 8 WHEN OTHERS THEN 9 RAISE_APPLICATION_ERROR(-20101, 10 'Error in getEmpByDept' || SQLCODE ); 12 end getEmpByDept; 13 /¢▀îÜùºÆ÷Åÿ.SQL> var rset refcursor; SQL> exec getEmpByDept(10,:rset);PL/SQL Æ÷ÅÿÅçùÿè«É¼.SQL> print rset;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 23-1îÄ -82 1300 10 7782 CLARK MANAGER 7839 09-1îÄ -81 2450 10 7839 KING PRESIDENT 17-11îÄ-81 5000 10SQL>µSêΩÿóùßÄqüFCREATE OR REPLACE PROCEDURE P_RETR_CURSOR (P_ID IN NUMBER, P_RECSET OUT SYS_REFCURSOR) AS --Created by xsb on 2004-11-09 For: æ╢¿P╟ÅÆ÷ò╘ë±░ïU_ÅW BEGIN OPEN P_RECSET FOR SELECT * FROM t WHERE c1 = P_ID; END;-------------------------------------------------------------- DECLARE --Created by xsb on 2004-11-09 For: æ╢¿P╟ÅÆ÷ò╘ë±░ïU_ÅWKm╒ï --set serveroutput on I NUMBER; S VARCHAR2(100); M SYS_REFCURSOR; BEGIN FOR J IN 1 .. 10 LOOP P_RETR_CURSOR(J, M); FETCH M INTO S, I; DBMS_OUTPUT.PUT(I || '-'); DBMS_OUTPUT.PUT_LINE(S); CLOSE M; END LOOP; END; /
CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR);
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
/ CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
IO_CURSOR IN OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
IF N_EMPNO <> 0
THEN
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = N_EMPNO; ELSE
OPEN V_CURSOR FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO; END IF;
IO_CURSOR := V_CURSOR;
END OPEN_ONE_CURSOR; PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN
OPEN V_CURSOR1 FOR SELECT * FROM EMP;
OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END OPEN_TWO_CURSORS;
END CURSPKG;
/------------------------
--ref cursor òsö\ùpfor,æⁿö\ÄgùpòüÆ╩loopC:oracleora92sqlplusdemo>sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on É»è·ÿZ 4îÄ 2 11:09:06 2005Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> conn scott/tiger@orcl
connected.
SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,
2 out_curEmp out SYS_REFCURSOR) as
3
4 begin
5 open out_curEmp for
6 SELECT * FROM emp WHERE deptno = in_deptNo ;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE_APPLICATION_ERROR(-20101,
10 'Error in getEmpByDept' || SQLCODE );
12 end getEmpByDept;
13 /¢▀îÜùºÆ÷Åÿ.SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);PL/SQL Æ÷ÅÿÅçùÿè«É¼.SQL> print rset;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1îÄ -82 1300 10
7782 CLARK MANAGER 7839 09-1îÄ -81 2450 10
7839 KING PRESIDENT 17-11îÄ-81 5000 10SQL>µSêΩÿóùßÄqüFCREATE OR REPLACE PROCEDURE P_RETR_CURSOR
(P_ID IN NUMBER,
P_RECSET OUT SYS_REFCURSOR) AS
--Created by xsb on 2004-11-09 For: æ╢¿P╟ÅÆ÷ò╘ë±░ïU_ÅW
BEGIN
OPEN P_RECSET FOR
SELECT *
FROM t
WHERE c1 = P_ID;
END;--------------------------------------------------------------
DECLARE
--Created by xsb on 2004-11-09 For: æ╢¿P╟ÅÆ÷ò╘ë±░ïU_ÅWKm╒ï
--set serveroutput on
I NUMBER;
S VARCHAR2(100);
M SYS_REFCURSOR;
BEGIN
FOR J IN 1 .. 10 LOOP
P_RETR_CURSOR(J, M);
FETCH M
INTO S, I;
DBMS_OUTPUT.PUT(I || '-');
DBMS_OUTPUT.PUT_LINE(S);
CLOSE M;
END LOOP;
END;
/