简单的存储过程帮忙讲解一下
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 refcursorSQL> exec test1(:c1);PL/SQL 过程已成功完成。SQL> print :c1; ID NAME---------- -------------------- 1 watson 2 aliceSQL>
