oracle 存储过程返回结果集

今天 做项目要一数据统计，写一存储过程。因为绑定变量，让我头头疼半天。现在终于解决，写出来以为后记！

表结构：

  create table "CXUSER"."FSM_E_PRESS_SUBS"(
        "ID" VARCHAR2(36) not null,
       "E_PRESS_ID" VARCHAR2(36),
       "MEMBERID" VARCHAR2(36),
       "SUBS_TIME" DATE,
       "TAG" VARCHAR2(1) default '0',
       "CANCEL_TIME" DATE,
        constraint "PK_FSM_E_PRESS_SUBS" primary key ("ID")
    );

 

--创建包,声明
  CREATE OR REPLACE PACKAGE pkg_test
        AS
         TYPE myrctype IS REF CURSOR;
            PROCEDURE  pkg_pro(p_id in varchar2,p_id1 in varchar2,p_rc OUT myrctype);
           END pkg_test;

－－----创建包体

 CREATE OR REPLACE  package  body pkg_test AS
         PROCEDURE pkg_pro(p_id in varchar2,p_id1 in varchar2, p_rc OUT myrctype)
         IS
             SQLSTR VARCHAR2(30000);
          
       BEGIN
  
         SQLSTR :='SELECT m.orderCount ,
                          m.e_press_id,m.years as orderyear,
                          n.cancelCount ,
                          n.years as cancelyears
               FROM
               (
                   select count(*) as orderCount,
                          t.e_press_id ,
                          to_char(t.subs_time,''yyyy-mm'') as years
                   from fsm_e_press_subs t
                   where     t.tag  = 0

                        and t.e_press_id = :w_id

            //and t.e_press_id =p_id 错误的

          group by t.e_press_id, to_char(t.subs_time,''yyyy-mm'')) m

           LEFT JOIN
                (
          select count(*) as cancelCount,
                        t.e_press_id ,
                        to_char(t.subs_time,''yyyy-mm'') as years
                 from fsm_e_press_subs t
           where   t.tag  = 1
                   and t.e_press_id = :w_id1

              //and t.e_press_id=p_id；错误的
                  group by t.e_press_id, to_char(t.subs_time,''yyyy-mm'')) n
           ON  m.e_press_id = n.e_press_id and m.years = n.years';
          OPEN p_rc FOR SQLSTR using p_id,p_id1;

     // open p_rc for SQLSTR using p_id;  错误的
       END pkg_pro;
   END pkg_test;

 

----测试
declare
s1 varchar2(30);
s2 varchar2(30);
s3 varchar2(30);
s4 varchar2(30);
s5 varchar2(30);
cc pkg_test.myrctype ;
begin
 pkg_test.pkg_pro('0000027c','0000027c',cc);
 LOOP
 FETCH cc INTO  s1,s2,s3,s4,s5;
 EXIT WHEN cc%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(' status   '||s1);
 END LOOP;
 end;

－－－－－－－－首先在语句体内绑定变量与存储过程传入的参数个数要匹配，语句体内的

绑定变量不能重名。而且输入的参数个数与USING后的参数要相同。