oracle 存储过程实例

create or replace procedure vat_invoice_run_tiso
is
 report_end_date   varchar2(20);
begin
  SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)), 'YYYYMM')
    INTO REPORT_END_DATE
    FROM DUAL;
  INSERT INTO  VMS_TRANS_RUN
  select cc.mapping_inst_id instId,
       cc.voucherDate accountPeriod,
       to_char(sum(cc.x),'fm9999999990.00'),
        to_char(sum(cc.y),'fm9999999990.00'),
       to_char(cast(nvl(sum(cc.y), 0) / (sum(cc.x)) as decimal(18, 12)),'fm9999999990.0000000') transferRatio
  from (select distinct pp.mapping_inst_id,
                        aa.centerCode,
                        aa.voucherDate,
                        sum(aa.aafeeCny) as x,
                        sum(bb.bbfeeCny) as y
          from (select centerCode, voucherDate, sum(feeCny) aafeeCny
                  from (select t.centercode centerCode,
                               to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
                                       'YYYYMM') voucherDate,
                               nvl(k.planfeecny, t.sumplanfeecny) feeCny,
                               nvl(k.taxrate, t.taxrate) as taxRate
                          from VMS_INSURANCE_TRADE_INFO t
                          left join VMS_INSURANCE_KIND_INFO k
                            on k.trans_id = t.certiid
                         where t.hisflag = 'N'
                           and to_char(to_date(t.voucherdate, 'yyyy-mm-dd'),
                                       'YYYYMM') = report_end_date
                           )
                 group by centerCode, voucherDate) aa
          left join (select centerCode,
                           voucherDate,
                           sum(feeCny) as bbfeeCny,
                           taxRate
                      from (select t.centercode centerCode,
                                   to_char(to_date(t.voucherdate,
                                                   'yyyy-mm-dd'),
                                           'YYYYMM') voucherDate,
                                   nvl(k.planfeecny, t.sumplanfeecny) feeCny,
                                   nvl(k.taxrate, t.taxrate) as taxRate
                              from VMS_INSURANCE_TRADE_INFO t
                              left join VMS_INSURANCE_KIND_INFO k
                                on k.trans_id = t.certiid
                             where t.hisflag = 'N'
                               and to_char(to_date(t.voucherdate,
                                                   'yyyy-mm-dd'),
                                           'YYYYMM') = report_end_date
                               )
                     where taxRate = 0
                     group by centerCode, voucherDate, taxRate) bb
            on aa.centerCode = bb.centerCode
           and aa.voucherDate = bb.voucherDate
          left join u_base_mapping_inst pp
            on pp.inst_id = aa.centerCode
         group by pp.mapping_inst_id, aa.centerCode, aa.voucherDate
         order by pp.mapping_inst_id) cc
 group by cc.mapping_inst_id, cc.voucherDate
 order by cc.mapping_inst_id;
 COMMIT;
end vat_invoice_run_tiso;
上面这个存储过程大概意思就是说 建立一个每个月月初-1即上个月固定时间的 查询方法