CREATE OR REPLACE PROCEDURE COM.SP_COM_MAIN030R
(
       IN_USER_ID                   IN           COM_MAIN030.USER_ID       %TYPE,
       --  RETURN VALUE
       OUT_CUR                     OUT           ResultType.CURSORTYPE,
       OUT_RTN                     OUT           INTEGER,
       OUT_MSG                     OUT           VARCHAR2
)
IS

 BEGIN
 /*<<?????>> ----------------------------------------------*/
     OPEN OUT_CUR FOR
     SELECT 
           A.USER_ID,
           A.USER_NM,
           A.PWD,
           A.RESNO,
           A.USER_NO,          
           A.USER_FG,
           A.CAMP_FG,
           A.EMAIL,
           A.PWD_NO_CHG_DT,
           A.USE_FG,
           A.USE_FR_DT,
           A.USE_END_DT,           
           A.INPT_ID,
           A.INPT_DT,
           A.INPT_IP,
           A.UPDT_ID,          
           A.UPDT_DT,
           A.UPDT_IP
      FROM COM_MAIN030 A
     WHERE A.USER_ID = IN_USER_ID;

/*???? EJB?? ?? ROLLBACK------------------------------------------*/
/*
 EXCEPTION
  WHEN OTHERS THEN
      OUT_RTN     := -1;
      OUT_MSG     := TO_CHAR(SQLCODE)|| ' : ' || SQLERRM;
   RETURN;
  /*???? EJB?? ?? COMMIT---------------------------------------------*/

  OUT_RTN     := 1;
  OUT_MSG     := '处理成功.';
  RETURN;

END SP_COM_MAIN030R;

SQL> create table test(id int,name varchar(20))
  2  /

表已创建。

SQL> insert into test values(1,'watson');

已创建 1 行。

SQL> insert into test values(2,'alice');

已创建 1 行。

SQL> create or replace procedure test1(mycursor out sys_refcursor) is 
  2      begin
  3         open mycursor for select * from test;
  4      end test1;
  5  /

过程已创建。

SQL> var c1 sys_refcursor;
用法: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
                    BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> var c1 refcursor
SQL> exec test1(:c1);

PL/SQL 过程已成功完成。

SQL> print :c1;

        ID NAME
---------- --------------------
         1 watson
         2 alice

SQL>