存储过程示例代码

一、返回结果集

      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;