简单的存储过程帮忙讲解一下

Question

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语句的知识点...因为是菜鸟...这三个变量也不知道是做什么用的       
OUT_CUR                     OUT           ResultType.CURSORTYPE,
OUT_RTN                     OUT           INTEGER,
OUT_MSG                     OUT           VARCHAR2

Answer 1

全部为输出参数
OUT_CUR                    OUT          ResultType.CURSORTYPE, --输出游标
OUT_RTN                    OUT          INTEGER, --输出整型
OUT_MSG                    OUT          VARCHAR2 --输出字符

Answer 2

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 aliceSQL>