存储过程 带游标

CREATE OR REPLACE PROCEDURE SP_REPORT_DBZ_HZCX( as_date_b   date,
                                                as_date_e   date,
                                                ai_dbzid    numeric,
                                                ai_dbzfaid  number,
                                                as_hzxm     varchar2,
                                                as_tjfs     varchar2,
                                                ai_forgid number,
                                                out_cur   out his_zyjs.ref_cur) as
  /*功能:查询
  参数:as_date_b,as_date_e 开始结束时间,ai_dbzid ID,ai_dbzfaid方案ID
       as_hzxm患者姓名,as_tjfs 0自然时间1日结时间
  返回:
  调用:-汇总查询
  创建: */

  vd_begin    date;
  vd_end      date;

begin
  if substr(to_char(as_date_b, 'yyyymmdd hh24:mi:ss'), 10, 17) = '00:00:00' then
    vd_begin := to_date(to_char(as_date_b, 'yyyy-MM-dd') || ' 00:00:00',
                        'yyyy-mm-dd hh24:mi:ss');
  else
    vd_begin := as_date_b;
  end if;
  if substr(to_char(as_date_e, 'yyyymmdd hh24:mi:ss'), 10, 17) = '00:00:00' then
    vd_end := to_date(to_char(as_date_e, 'yyyy-MM-dd') || ' 23:59:59',
                      'yyyy-mm-dd hh24:mi:ss');
  else
    vd_end := as_date_e;
  end if;

  if as_tjfs = '0' then
    --自然时间-----------------------------------
    open out_cur for
      select t3.zdy_name,--名称
             t4.bzzfje,--标准自费金额
             t4.bzzje, --标准总金额
             sum(t1.beprice) as beprice, --实结算费用
             sum(t4.bzzje - t4.bzzfje) as kbje,--可报金额
             sum(t1.beprice - t4.bzzfje) as hzsbje,--患者实报金额
             sum(t4.bzzje - t1.beprice) as yyykje  --医院盈亏金额  
        from hosp_pay_history t1,
             patinfo_cy t2,
             dictmedi_dbz t3,
             cp_base_pathdy t4
      where t1.hospid = t2.hospid
        and t2.dbzfaid = t4.pathid
        and t4.dbzid = t3.dbzid
        and t1.forgid = t2.forgid
        and t2.forgid = t4.forgid
        and t4.forgid = t3.forgid
        and t1.invalid = 0
        and t1.beprice > 0
        and t1.ddate >= vd_begin
        and t1.ddate <= vd_end
        and t2.name like '%'||as_hzxm||'%'
        and (t2.dbzid = ai_dbzid or ai_dbzid = 0)
        and (t2.dbzfaid = ai_dbzfaid or ai_dbzfaid = 0)
        and t1.forgid = ai_forgid
      group by t3.zdy_name,t4.bzzfje,t4.bzzje
      order by t3.zdy_name;
      
  else
    --日结时间-----------------------------------------------------
    open out_cur for
    select t3.zdy_name,
             t4.bzzfje,
             t4.bzzje,
             sum(t1.beprice) as beprice,
             sum(t4.bzzje - t4.bzzfje) as kbje,
             sum(t1.beprice - t4.bzzfje) as hzsbje,

             sum(t4.bzzje - t1.beprice) as yyykje            
        from hosp_pay_history t1,
             patinfo_cy t2,
             dictmedi_dbz t3,
             cp_base_pathdy t4
      where t1.hospid = t2.hospid
        and t2.dbzfaid = t4.pathid
        and t4.dbzid = t3.dbzid
        and t1.forgid = t2.forgid
        and t2.forgid = t4.forgid
        and t4.forgid = t3.forgid
        and t1.invalid = 0
        and t1.beprice > 0
        and t1.ddate >= vd_begin
        and t1.ddate <= vd_end
        and t2.name like '%'||as_hzxm||'%'
        and (t2.dbzid = ai_dbzid or ai_dbzid = 0)
        and (t2.dbzfaid = ai_dbzfaid or ai_dbzfaid = 0)
        and t1.forgid = ai_forgid
      group by t3.zdy_name,t4.bzzfje,t4.bzzje
      order by t3.zdy_name;
   end if;
end;