----------------------------------------------------------------------------
  -- 过程中返回SYS_REFCURSOR，
  -- 1、子过程中使用refcursor
  -- 2、主过程输出refcursor内容
  -- myfriend2010
  -- 2011-11-25
  ----------------------------------------------------------------------------

----------------主过程输出refcursor内容----------------
declare
  v_REFCURSOR   SYS_REFCURSOR;
  v_NSRSBH      VARCHAR2(20);
  v_NSRMC       VARCHAR2(200);
  v_NYNSE       NUMBER(18, 2);
  v_BQSBSE      NUMBER(18, 2);
  v_NSR_SWJG_MC VARCHAR2(80);
  v_ZGSWRY_MC   VARCHAR2(20);
  v_TJ_NDYF     CHAR(6);
  v_WHD_BZ      varchar2(30);
  v_count       number;
  v_count1      number;
begin
  --调用过程，返回refcursor
  CP_FT_YWYJ_DBSY_TDSYH(to_date('20110305', 'yyyymmdd'),
                        to_date('20110305', 'yyyymmdd'),
                        'czr',
                        '1',
                        v_REFCURSOR);
  v_count  := 0;
  v_count1 := 15;
  loop
    fetch v_REFCURSOR
      into v_NSRSBH,
           v_NSRMC,
           v_NYNSE,
           v_BQSBSE,
           v_NSR_SWJG_MC,
           v_ZGSWRY_MC,
           v_TJ_NDYF,
           v_WHD_BZ;
    exit when v_REFCURSOR%NOTFOUND;
    v_count := v_count + 1;
    dbms_output.put_line(v_NSRMC);
    if v_count >= v_count1 then
      exit;
    end if;
  end loop;
end;

----------------子过程中使用refcursor----------------

CREATE OR REPLACE PROCEDURE CP_FT_YWYJ_DBSY_TDSYH ( QRQ IN DATE, --年月(20110705)
                                                    ZRQ IN DATE,
                                                    CZR IN VARCHAR2,--操作人员
                                                    WHD IN CHAR,
                                                    P_CUR  OUT SYS_REFCURSOR) IS --LINYQ_2011-07-22

QRQ_TMP CHAR(8);--起日期
ZRQ_TMP CHAR(8);--止日期

BEGIN  --判断是否为闰年

QRQ_TMP := TO_CHAR(QRQ,'YYYYMM');
ZRQ_TMP := TO_CHAR(ZRQ,'YYYYMM');

OPEN P_CUR FOR

SELECT *
  FROM a, b
 WHERE a.x=b.x;

 END CP_FT_YWYJ_DBSY_TDSYH;