一个小时过去了200条记录都还没写进去效率低的可怕，求解

0 0
一个小时过去了200条记录都还没写进去效率低的可怕，求解0

create or replace procedure SP_MSS_AGING is
  amount Number(18, 2);-- 未逾期
  overdue1 Number(18, 2); --逾期0-6个月
  overdue2 Number(18, 2); --逾期6-12月
  overdue3 Number(18, 2); --逾期12-18个月
  overdue4 Number(18, 2); --逾期18-24月
  overdue5 Number(18, 2); --逾期以上两年
begin
  declare
    CURSOR companyRow IS
      select bf01.scc_company_id,
             bf01.SETTLE_TYPE_NO
        from TB_MSS_SETT_CONTRACT   bf01,
             TB_MSS_SC_ACC_RECV_DET bf06,
             TB_MSS_SETT_INVOICE    bf02,
             TB_SCC_WF_PROC_MAIN    ac03,
             TB_MSS_SC_ACC_RECV     bt01
       where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
         and ac03.target_table_id = bf02.mss_sett_invoice_id
         and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
         and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
         and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
         and bf01.NOTE_STATE = 2
         and ac03.STATUS = '4'
         and not (bt01.AR_STATUS = '2')
         and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
             to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
       group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
    aging companyRow%rowtype;
  
  begin
    for aging in companyRow loop
     begin
        select sum(bt01.AR_AMOUNT)
          into amount
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '0'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          amount := 0;
      end;  
    
    
    
      begin
        select sum(bt01.AR_AMOUNT)
          into overdue1
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '1'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
           and months_between(sysdate, ACC_RECV_DATE) <= 6
           and months_between(sysdate, ACC_RECV_DATE) >= 0
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          overdue1 := 0;
      end;
      
        begin
        select sum(bt01.AR_AMOUNT)
          into overdue2
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '1'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
           and months_between(sysdate, ACC_RECV_DATE) <= 12
           and months_between(sysdate, ACC_RECV_DATE) >= 6
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          overdue2 := 0;
      end;
      
       begin
        select sum(bt01.AR_AMOUNT)
          into overdue3
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '1'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
           and months_between(sysdate, ACC_RECV_DATE) <= 18
           and months_between(sysdate, ACC_RECV_DATE) >= 12
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          overdue3 := 0;
      end;
      
      begin
        select sum(bt01.AR_AMOUNT)
          into overdue4
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '1'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
           and months_between(sysdate, ACC_RECV_DATE) <= 24
           and months_between(sysdate, ACC_RECV_DATE) >= 18
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          overdue4 := 0;
      end;
      
      begin
        select sum(bt01.AR_AMOUNT)
          into overdue5
          from TB_MSS_SETT_CONTRACT   bf01,
               TB_MSS_SC_ACC_RECV_DET bf06,
               TB_MSS_SETT_INVOICE    bf02,
               TB_SCC_WF_PROC_MAIN    ac03,
               TB_MSS_SC_ACC_RECV     bt01
         where ac03.target_table_name = 'TB_MSS_SETT_INVOICE'
           and ac03.target_table_id = bf02.mss_sett_invoice_id
           and bf02.mss_sett_contract_id = bf01.mss_sett_contract_id
           and bf01.mss_sett_contract_id = bf06.mss_sett_contract_id
           and bf06.MSS_SC_ACC_RECV_ID = bt01.mss_sc_acc_recv_id
           and bt01.OVERDUED = '1'
           and bf01.NOTE_STATE = 2
           and ac03.STATUS = '4'
           and not (bt01.AR_STATUS = '2')
           and bf01.scc_company_id = aging.scc_company_id
           and bf01.SETTLE_TYPE_NO = aging.SETTLE_TYPE_NO
           and to_char(trunc(ACC_RECV_DATE), 'yyyy-mm') =
               to_char(add_months(trunc(sysdate), -1), 'yyyy-mm')
           and months_between(sysdate, ACC_RECV_DATE) >= 24
         group by bf01.scc_company_id, bf01.SETTLE_TYPE_NO;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          overdue5 := 0;
      end;
          insert into TB_MSS_AGENCY_OD_AGING
            (MSS_AGENCY_OD_AGING_ID,
             SCC_COMPANY_ID,
             YEAR_MONTH,
             SETTLE_TYPE_NO,
             UN_DUE_AMOUNT,
             OD_6_MONTHS,
             OD_12_MONTHS,
             OD_18_MONTHS,
             OD_24_MONTHS,
             OD_2_YEARS,
             OD_SUMMARY)
          values
            (sq_mss_agency_od_aging.NEXTVAL,
             aging.scc_company_id,
             to_char(add_months(trunc(sysdate), -1), 'yyyy-mm'),
             aging.SETTLE_TYPE_NO,
             amount,
             overdue1,
             overdue2,
             overdue3,
             overdue4,
             overdue5,
             overdue1 + overdue2 + overdue3 + overdue4 + overdue5);
        end loop;
      end;
    end SP_MSS_AGING;