存储过程示例代码

一、返回结果集

      ParamsHelperOracle paramHelper = new ParamsHelperOracle();

            paramHelper.Add("UserName", "sys");

            paramHelper.Add("returnResult", OracleType.Cursor, ParameterDirection.Output);

--包头
CREATE OR REPLACE PACKAGE Package_TEST IS
  TYPE RESULT IS REF CURSOR;
  PROCEDURE Select_Test(UserName VARCHAR2,returnResult OUT RESULT);
END Package_TEST;

--包体
CREATE OR REPLACE PACKAGE BODY Package_TEST IS
       PROCEDURE Select_Test(UserName VARCHAR2,returnResult OUT RESULT)
       AS
       sqlstr VARCHAR2(1000);
       BEGIN
        sqlstr:='select * from all_tables';
        IF UserName IS NOT NULL THEN
         sqlstr:=sqlstr||' WHERE owner=UPPER('''||UserName||''')';
        END IF;
        
        OPEN returnResult FOR sqlstr;            --返回结果集
     
       END Select_Test;
 END Package_TEST;     

 

 

二、使用游标

CREATE OR REPLACE PROCEDURE TEST123 AS

CURSOR cur IS SELECT pp.Note FROM Phoneinfo pp;

BEGIN

      FOR  pi IN cur LOOP

            BEGIN

                  dbms_output.put_line(pi.Note);

            END;

      END LOOP;

END TEST123;

 

--执行存储过程

BEGIN

      TEST123();

END;