oracle 存储过程

哎 数据库好菜.....写个存储过程写了好久才写出来（还是别人的帮忙），所以做做笔记。
    查询分页：

       create or replace procedure
--住院病人费用清单
SP_PATIENT_CHARGE_BILL(v_begDate in varchar2,--输入参数
                        v_endDate in varchar2,--输入参数
                        v_inhos   in varchar2,--输入参数
                        v_start   in number,--输入参数
                        v_limit   in number,--输入参数
                     v_result  out sghis.his_brsf.HIS_CURSOR) is --输出参数(游标)
begin
  open v_result for
 
  select * from (select row_.*, rownum rownum_ from (--这句是为了分页
 
    select invoice_name as invoice_code,
           fee_name,
           spe,
           amount,
           unit,
           price,
           taxmoney,
           date1 || '至' || date2 as exec_time,
           remark
      from (select case sort_id when '01' then '01' when '70' then '01' else sort_id  end as invoice_code,
                   bb.pham_name as fee_name,
                   bb.packing as spe,
                   aa.amount,
                   bb.split_unit as unit,
                   case aa.amount when 0 then  0  else round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(list_date, 'yyyy-mm-dd')) as date1,
                           max(to_char(list_date, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee r
                     where pham_sign = '0'
                       and to_char(pay_date, 'yyyy-mm-dd') >= v_begDate
                       and to_char(pay_date, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pay_sign = '1'
                     group by fee_code, price) aa,
                   sghis.pa_pham_info bb
             where aa.fee_code = bb.pham_code(+)
           
            union all
           
            select case sort_id when '01' then '01'when '70' then '01' else sort_id end as invoice_code,
                   bb.pham_name as fee_name,
                   bb.packing as spe,
                   aa.amount,
                   bb.split_unit as unit,
                   case aa.amount when 0 then 0 else  round(aa.taxmoney / aa.amount, 2)end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           -1 * nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           -1 * nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(deal_time, 'yyyy-mm-dd')) as date1,
                           max(to_char(deal_time, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee_self t
                     where pham_sign = '0'
                       and to_char(deal_time, 'yyyy-mm-dd') >= v_begDate
                       and to_char(deal_time, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and cancel_sign = '1'
                       and (deal_sign = '0' or deal_sign = '2')
                     group by fee_code, price) aa,
                   sghis.pa_pham_info bb
             where aa.fee_code = bb.pham_code(+)
           
            union all
           
            select bb.invoice_code,
                   bb.item_name as fee_name,
                   bb.spe,
                   aa.amount,
                   bb.unit,
                   case aa.amount  when 0 then  0  else  round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(list_date, 'YYYY-MM-DD')) as date1,
                           max(to_char(list_date, 'YYYY-MM-DD')) as date2
                      from sghis.ih_advice_fee
                     where to_char(pay_date, 'yyyy-mm-dd') >= v_begDate
                       and to_char(pay_date, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pham_sign = '1'
                       and pay_sign = '1'
                     group by fee_code, price) aa,
                   sghis.hd_price_dict bb
             where aa.fee_code = bb.item_code(+)
           
            union all
           
            select bb.invoice_code,
                   bb.item_name as fee_name,
                   bb.spe,
                   aa.amount,
                   bb.unit,
                   case aa.amount when 0 then 0 else round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           -1 * nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           -1 * nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(deal_time, 'yyyy-mm-dd')) as date1,
                           max(to_char(deal_time, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee_self
                     where to_char(deal_time, 'yyyy-mm-dd') >= v_begDate
                       and to_char(deal_time, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pham_sign = '1'
                       and cancel_sign = '1'
                       and (deal_sign = '0' or deal_sign = '2')
                     group by fee_code, price) aa,
                   sghis.hd_price_dict bb
             where aa.fee_code = bb.item_code(+)) a,
           sghis.hd_invoice_dict d
     where a.invoice_code = d.invoice_code(+)
   
     order by a.invoice_code, a.fee_code
    
     ) row_ where rownum<= v_start+v_limit) where rownum_>v_start;--这句为了分页

end;


    查询总记录数：
        create or replace procedure
--住院病人费用清单总记录数
SP_PATIENT_CHARGE_BILL_COUNT
             (          v_begDate in varchar2,
                        v_endDate in varchar2,
                        v_inhos   in varchar2,
                        v_count   out number) is
begin
 
    select count(*) into v_count   --把查出来的总记录数插入到输出参数中

      from (select case sort_id when '01' then '01' when '70' then '01' else sort_id  end as invoice_code,
                   bb.pham_name as fee_name,
                   bb.packing as spe,
                   aa.amount,
                   bb.split_unit as unit,
                   case aa.amount when 0 then  0  else round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(list_date, 'yyyy-mm-dd')) as date1,
                           max(to_char(list_date, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee r
                     where pham_sign = '0'
                       and to_char(pay_date, 'yyyy-mm-dd') >= v_begDate
                       and to_char(pay_date, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pay_sign = '1'
                     group by fee_code, price) aa,
                   sghis.pa_pham_info bb
             where aa.fee_code = bb.pham_code(+)
           
            union all
           
            select case sort_id when '01' then '01'when '70' then '01' else sort_id end as invoice_code,
                   bb.pham_name as fee_name,
                   bb.packing as spe,
                   aa.amount,
                   bb.split_unit as unit,
                   case aa.amount when 0 then 0 else  round(aa.taxmoney / aa.amount, 2)end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           -1 * nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           -1 * nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(deal_time, 'yyyy-mm-dd')) as date1,
                           max(to_char(deal_time, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee_self t
                     where pham_sign = '0'
                       and to_char(deal_time, 'yyyy-mm-dd') >= v_begDate
                       and to_char(deal_time, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and cancel_sign = '1'
                       and (deal_sign = '0' or deal_sign = '2')
                     group by fee_code, price) aa,
                   sghis.pa_pham_info bb
             where aa.fee_code = bb.pham_code(+)
           
            union all
           
            select bb.invoice_code,
                   bb.item_name as fee_name,
                   bb.spe,
                   aa.amount,
                   bb.unit,
                   case aa.amount  when 0 then  0  else  round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(list_date, 'YYYY-MM-DD')) as date1,
                           max(to_char(list_date, 'YYYY-MM-DD')) as date2
                      from sghis.ih_advice_fee
                     where to_char(pay_date, 'yyyy-mm-dd') >= v_begDate
                       and to_char(pay_date, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pham_sign = '1'
                       and pay_sign = '1'
                     group by fee_code, price) aa,
                   sghis.hd_price_dict bb
             where aa.fee_code = bb.item_code(+)
           
            union all
           
            select bb.invoice_code,
                   bb.item_name as fee_name,
                   bb.spe,
                   aa.amount,
                   bb.unit,
                   case aa.amount when 0 then 0 else round(aa.taxmoney / aa.amount, 2) end as price,
                   aa.taxmoney,
                   date1,
                   date2,
                   aa.fee_code,
                   bb.remark as remark
              from (select fee_code,
                           -1 * nvl(sum(amount), 0) as amount,
                           avg(price) as price,
                           -1 * nvl(sum(round(amount * price, 2)), 0) as taxmoney,
                           min(to_char(deal_time, 'yyyy-mm-dd')) as date1,
                           max(to_char(deal_time, 'yyyy-mm-dd')) as date2
                      from sghis.ih_advice_fee_self
                     where to_char(deal_time, 'yyyy-mm-dd') >= v_begDate
                       and to_char(deal_time, 'yyyy-mm-dd') <= v_endDate
                       and inhos_no = v_inhos
                       and pham_sign = '1'
                       and cancel_sign = '1'
                       and (deal_sign = '0' or deal_sign = '2')
                     group by fee_code, price) aa,
                   sghis.hd_price_dict bb
             where aa.fee_code = bb.item_code(+)) a,
           sghis.hd_invoice_dict d
     where a.invoice_code = d.invoice_code(+)
   
     order by a.invoice_code, a.fee_code;
end;