存储过程+动态SQL+通过游标返回数据集

Question

CREATE OR REPLACE PACKAGE zhcx
IS
   TYPE tcursor IS REF CURSOR;   FUNCTION QUERY (
      p_voption1     IN   VARCHAR2,
      p_voption2     IN   VARCHAR2,
      p_nkind        IN   NUMBER,
      p_begin_date   IN   DATE,
      p_end_date     IN   DATE
   )
      RETURN tcursor;/* Formatted on 2003/06/13 15:10 (Formatter Plus v4.7.0) *//* Formatted on 2003/06/13 15:10 (Formatter Plus v4.7.0) */CREATE OR REPLACE PACKAGE BODY zhcx
IS
   PROCEDURE PRINT (p_vmessage VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (p_vmessage);
   END PRINT;   FUNCTION QUERY (
      p_voption1     IN   VARCHAR2,
      p_voption2     IN   VARCHAR2,
      p_nkind        IN   NUMBER,
      p_begin_date   IN   DATE,
      p_end_date     IN   DATE
   )
      RETURN tcursor
   IS
      curdb            tcursor;
      sql_str          VARCHAR2 (3000);
      sql_date_range   VARCHAR2 (300);
   BEGIN
      IF curdb%ISOPEN
      THEN
         CLOSE curdb;
         insert into text values('ok');
         commit;
      END IF;      sql_date_range :=
         ' AND ( b.dsk_time BETWEEN :p_begin_date
                            AND :p_end_date
                          OR b.dtk_time BETWEEN :p_begin_date
                           AND :p_end_date
                         ) ';
      CASE p_nkind
         WHEN 1
         THEN
            --sky xml all
            sql_str :=
                   'SELECT   a.vczy_name AS "收款员", b.vsj# AS "收据号",
                         d.vsfxm_mc AS "项目类", c.fee AS "金额",
                         DECODE (c.nsfxm_id,
                                 1, b.nscale,
                                 2, b.nscale,
                                 3, b.nscale,
                                 1
                                 ) AS "比例",
                         b.dsk_time AS "收款时间",
                         DECODE (b.vyx_flag,
                                ''00'', e.vczy_name,
                                ''03'', ''作废''
                                ) AS "退款员",
                         b.dtk_time AS "退款时间",
                         DECODE (b.vyx_flag,
                                 ''00'', ''退款'',
                                 ''01'', ''正常'',
                                ''03'', ''作废''
                               ) AS "有效标志"
                    FROM c_operator_info a,
                         c_receipt b,
                         c_receipt_detail c,
                         c_charge_item d,
                         c_operator_info e
                   WHERE a.nczy_id = b.nsky_id
                     AND e.nczy_id(+) = b.ntky_id
                     AND b.vsj# = c.vzpsj
                     AND c.nsfxm_id = d.nsfxm_id
                     and c.nsfxm_id in '
                || p_voption2
                || sql_date_range
                || ' ORDER BY a.nczy_id, c.nsfxm_id, b.dsk_time';
         WHEN 2
         THEN
            --sky xml select
            sql_str :=
                   'SELECT   a.vczy_name AS "收款员", b.vsj# AS "收据号",
                         d.vsfxm_mc AS "项目类", c.fee AS "金额",
                         DECODE (c.nsfxm_id,
                                 1, b.nscale,
                                 2, b.nscale,
                                 3, b.nscale,
                                 1
                                 ) AS "比例",
                         b.dsk_time AS "收款时间",
                         DECODE (b.vyx_flag,
                                ''00'', e.vczy_name,
                                ''03'', ''作废''
                                ) AS "退款员",
                         b.dtk_time AS "退款时间",
                         DECODE (b.vyx_flag,
                                 ''00'', ''退款'',
                                 ''01'', ''正常'',
                                ''03'', ''作废''
                               ) AS "有效标志"
                    FROM c_operator_info a,
                         c_receipt b,
                         c_receipt_detail c,
                         c_charge_item d,
                         c_operator_info e
                   WHERE a.nczy_id = b.nsky_id
                     AND e.nczy_id(+) = b.ntky_id
                     AND b.vsj# = c.vzpsj
                     AND c.nsfxm_id = d.nsfxm_id
                     and a.nczy_id in '
                || p_voption1
                || ' and c.nsfxm_id in '
                || p_voption2
                || sql_date_range
                || ' ORDER BY a.nczy_id, c.nsfxm_id, b.dsk_time';
         .........
      END CASE;      /*INSERT INTO text
           VALUES (sql_str);
           commit;*/      IF (p_nkind = 21) OR (p_nkind = 22) OR (p_nkind = 27) OR (p_nkind = 28)
      THEN
         OPEN curdb FOR sql_str
            USING p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_end_date,
                 p_end_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date;
      ELSIF    (p_nkind = 13)
            OR (p_nkind = 14)
            OR (p_nkind = 17)
            OR (p_nkind = 18)
            OR (p_nkind = 23)
            OR (p_nkind = 24)
            OR (p_nkind = 29)
            OR (p_nkind = 30)
      THEN
         OPEN curdb FOR sql_str
            USING p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_end_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date;
      ELSIF    (p_nkind = 11)
            OR (p_nkind = 12)
            OR (p_nkind = 19)
            OR (p_nkind = 20)
            OR (p_nkind = 25)
            OR (p_nkind = 26)
      THEN
         OPEN curdb FOR sql_str
            USING p_begin_date,
                 p_end_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date,
                 p_begin_date,
                 p_end_date;
      ELSE
         OPEN curdb FOR sql_str
            USING p_begin_date, p_end_date, p_begin_date, p_end_date;
      END IF;
      
      RETURN curdb;
   END;
END;

Answer 1

CREATE OR REPLACE PACKAGE test
AS
   TYPE myrctype IS REF CURSOR;
END test;
/create procedure pro(c out test.myrctype)
as
str varchar2(200);
begin
str:='select * from table_name where id =.....';
open c for str;
end;
/

Answer 2

举例：CREATE OR REPLACE PACKAGE 你的包名
AS
   TYPE myrctype IS REF CURSOR;   procedure sms_GetSendInfo(p_rc OUT myrctype);
END;
/CREATE OR REPLACE PACKAGE BODY 你的包名
AS
  procedure sms_GetSendInfo (p_rc OUT myrctype)
  as
  minautoid int default 0;
  sqlstr varchar2(100);
  begin
    select max(autoid) into minautoid from sms_interface;
    sqlstr:='select autoid ,mobile,mobilepayed,servicetype,feetype,feevalue,moflag,content,spnumber from sms_interface where autoid =:minautoid';
    OPEN p_rc FOR sqlstr USING minautoid;
    insert into sms_interface_history select * from sms_interface where autoid=minautoid;
    delete sms_interface where autoid=minautoid;
    commit;
  end;
end;
/

Answer 3

CREATE OR REPLACE PACKAGE pkg_test
AS
   TYPE myrctype IS REF CURSOR;   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
   PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
   IS
      sqlstr   VARCHAR2 (500);
   BEGIN
      IF p_id = 0 THEN
         OPEN p_rc FOR
            SELECT ID, NAME, sex, address, postcode, birthday
              FROM student;
      ELSE
         sqlstr :=
            'select id,name,sex,address,postcode,birthday
           from student where id=:w_id';
         OPEN p_rc FOR sqlstr USING p_id;
      END IF;
   END get;
END pkg_test;
/