oracle 存储过程一例

留个备忘,以备将来参考

create or replace procedure GET_ALM_NDS_DD is

  par_last_run_date fm_system.last_run_date%TYPE;

  p_ccy                 rb_acct.ccy%TYPE;
  p_branch              rb_acct.branch%TYPE;
  p_cur_net_rate        alm_nds_dd.cur_net_rate%TYPE;
  p_cr_int_type         alm_nds_dd.cr_int_type%TYPE;
  p_dr_int_type         alm_nds_dd.dr_int_type%TYPE;
  p_prev_day_actual_bal rb_acct.prev_day_actual_bal%TYPE;
  p_internal_key        rb_acct.internal_key%TYPE;
  v_num                 number := 0;
  v_actual_rate         number := 0;
  v_balance             number := 0;

  CURSOR cur IS
    SELECT ccy, branch, cr_int_type, dr_int_type from T_TEMP;

  CURSOR cur_nds IS
    SELECT branch,
           currency_cd,
           cr_int_type,
           dr_int_type,
           nds_cur_book_bal,
           nds_id_number
      from alm_nds_dd
     where alm_nds_dd.rate_ladder = 'S';

  CURSOR cur_rate IS
    SELECT DISTINCT fbv.branch,
                    fbv.int_type,
                    fbv.ccy,
                    fbv.effect_date,
                    fbv.balance as balance,
                    fbv.min_rate,
                    fbv.max_rate,
                    fbv.day_basis,
                    (NVL(fbv.actual_rate, 0) + NVL(fiv.variance_rate, 0)) actual_rate,
                    NVL(fiv.var_effect_date, fbv.effect_date) var_effect_date
      FROM (SELECT fb.branch,
                   fit.int_type,
                   fir.ccy,
                   fir.effect_date,
                   fir.balance,
                   fir.min_rate,
                   fir.max_rate,
                   fir.day_basis,
                   NVL(fir.actual_rate, 0) actual_rate
              FROM fm_int_rate fir, fm_int_type fit, fm_branch_tbl fb
             WHERE fir.int_type = fit.int_type
               AND fir.status = 'A'
               AND fb.branch = p_branch
               AND fb.tran_br_ind = 'Y'
               AND fir.ccy = p_ccy
               AND fir.int_type IN (p_cr_int_type, p_dr_int_type)) fbv,
           (SELECT kiv.branch,
                   kiv.variance_rate,
                   kiv.effect_date,
                   kiv.ccy,
                   kiv.int_type,
                   kiv.balance,
                   kiv.var_effect_date
              FROM fm_int_variance kiv, fm_int_rate kir
             WHERE kiv.int_type = kir.int_type
               AND kiv.ccy = kir.ccy
               AND kiv.effect_date = kir.effect_date
               AND kiv.balance = kir.balance
               AND kiv.status = 'A'
               AND kir.status = 'A'
               AND kiv.branch = p_branch
               AND kir.int_type IN (p_cr_int_type, p_dr_int_type)
               AND kir.ccy = p_ccy) fiv
     WHERE fbv.branch = fiv.branch
       AND fbv.int_type = fiv.int_type
       AND fbv.balance = fiv.balance
       AND fbv.ccy = fiv.ccy
       AND fbv.effect_date = fiv.effect_date(+)
       AND ROWNUM = 1
     ORDER BY fbv.branch,
              fbv.int_type,
              fbv.ccy,
              fbv.effect_date DESC,
              fbv.balance;

  CURSOR cur_rate_s IS
 
    SELECT ff.balance, ff.actual_rate
      FROM (
           
            SELECT DISTINCT rownum as row_num,
                             fbv.branch,
                             fbv.int_type,
                             fbv.ccy,
                             fbv.effect_date,
                             fbv.balance as balance,
                             fbv.min_rate,
                             fbv.max_rate,
                             fbv.day_basis,
                             (NVL(fbv.actual_rate, 0) +
                             NVL(fiv.variance_rate, 0)) actual_rate,
                             NVL(fiv.var_effect_date, fbv.effect_date) var_effect_date
              FROM (SELECT fb.branch,
                            fit.int_type,
                            fir.ccy,
                            fir.effect_date,
                            fir.balance,
                            fir.min_rate,
                            fir.max_rate,
                            fir.day_basis,
                            NVL(fir.actual_rate, 0) actual_rate
                       FROM fm_int_rate fir, fm_int_type fit, fm_branch_tbl fb
                      WHERE fir.int_type = fit.int_type
                        AND fir.status = 'A'
                        AND fb.branch = p_branch
                        AND fb.tran_br_ind = 'Y'
                        AND fir.ccy = p_ccy
                        AND fir.int_type IN (p_cr_int_type, p_dr_int_type)) fbv,
                    (SELECT kiv.branch,
                            kiv.variance_rate,
                            kiv.effect_date,
                            kiv.ccy,
                            kiv.int_type,
                            kiv.balance,
                            kiv.var_effect_date
                       FROM fm_int_variance kiv, fm_int_rate kir
                      WHERE kiv.int_type = kir.int_type
                        AND kiv.ccy = kir.ccy
                        AND kiv.effect_date = kir.effect_date
                        AND kiv.balance = kir.balance
                        AND kiv.status = 'A'
                        AND kir.status = 'A'
                        AND kiv.branch = p_branch
                        AND kir.int_type IN (p_cr_int_type, p_dr_int_type)
                        AND kir.ccy = p_ccy) fiv
             WHERE fbv.branch = fiv.branch
               AND fbv.int_type = fiv.int_type
               AND fbv.balance = fiv.balance
               AND fbv.ccy = fiv.ccy
               AND fbv.effect_date = fiv.effect_date(+)
             ORDER BY fbv.branch,
                       fbv.int_type,
                       fbv.ccy,
                       fbv.effect_date DESC,
                       fbv.balance) ff
     where ff.row_num < 3;

begin
  EXECUTE IMMEDIATE 'truncate   table   alm_nds_dd';
  EXECUTE IMMEDIATE 'truncate table t_temp';

  select fs.last_run_date into par_last_run_date from fm_system fs;

  INSERT INTO alm_nds_dd
    (fms_product_id,
     --     fms_gl_account_id,
     fms_org_unit_id,
     nds_id_number,
     --     nds_as_of_dt,
     nds_cur_book_bal,
     accrual_basis_cd,
     adjustable_type_cd,
     --     cur_net_rate,
     currency_cd,
     cif_key,
     cif_type,
     cif_sub_type,
     branch,
     acct_type)
 
    SELECT fpm.global_product_id,
           --           '2待定',
           fb.cny_business_unit,
           ra.internal_key,
           --           fs.last_run_date,
           ABS(ra.prev_day_actual_bal),
           DECODE(fc.day_basis, 360, 2, 365, 6, 2),
           50,
           --           '9待定',
           ra.ccy,
           ra.client_no,
           ra.client_type,
           fmc.class_4,
           ra.branch,
           ra.acct_type
      FROM rb_acct            ra,
           fm_product_mapping fpm,
           fm_branch_tbl      fb,
           --           fm_system          fs,
           fm_currency fc,
           fm_client   fmc
     WHERE ra.deposit_type <> 'T'
       AND ra.prev_day_actual_bal < 0
       AND ra.acct_status <> 'C'
       AND fpm.product_type = ra.acct_type
       AND ra.branch = fb.branch
       AND fc.ccy = ra.ccy
       AND ra.client_no = fmc.client_no
       AND fb.tran_br_ind = 'Y';
  commit;

  --update 'nds_as_of_dt'
  update alm_nds_dd
     set nds_as_of_dt = to_char(par_last_run_date, 'YYYYMMDD');

  --update 'fms_gl_account_id' when fms is exist
  update alm_nds_dd a
     set a.fms_gl_account_id = (select c.nas_gl_code
                                  from rb_acct_type b, fm_da_glcode_mapping c
                                 where a.acct_type = b.acct_type
                                   AND b.gl_code_l = c.nds_gl_code
                                   AND a.cif_key = c.client_no
                                   AND a.branch = c.branch);

  --update 'fms_gl_account_id' when fms is not exist, get nds
  update alm_nds_dd a
     set a.fms_gl_account_id = (select b. gl_code_l
                                  from rb_acct_type b
                                 where a.acct_type = b.acct_type
                                   AND a.fms_gl_account_id is null)
   where a.fms_gl_account_id is null;

  --update 'CR_INT_TYPE','DR_INT_TYPE'
  update alm_nds_dd a
     set a.cr_int_type = (select cr_int_type
                            from rb_prod_default rpd
                           where a.acct_type = rpd.acct_type);

  update alm_nds_dd a
     set a.dr_int_type = (select dr_int_type
                            from rb_prod_default rpd
                           where a.acct_type = rpd.acct_type);

  --update 'rate_ladder'
  update alm_nds_dd a
     set a.rate_ladder = (select c.rate_ladder
                            from rb_int_detail b, fm_int_type c
                           where a.nds_id_number = b.internal_key
                             AND b.cr_int_type = c.int_type);

  --get the   cur_net_rate when the rate_ladder <>'s'
  --execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
  --     ccy VARCHAR2(3),        
  --     branch VARCHAR2(6),
  --     cr_int_type VARCHAR2(3),
  --     dr_int_type VARCHAR2(3)
  --    
  --) ON COMMIT PRESERVE ROWS';

  INSERT INTO T_TEMP
    (ccy, branch, cr_int_type, dr_int_type)
    select distinct a.currency_cd, a.branch, a.cr_int_type, a.dr_int_type
      from alm_nds_dd a;

  For tmp_cur in cur loop
    p_ccy         := tmp_cur.ccy;
    p_branch      := tmp_cur.branch;
    p_cr_int_type := tmp_cur.cr_int_type;
    p_dr_int_type := tmp_cur.dr_int_type;
 
    for tmp_cur_rate in cur_rate loop
      p_cur_net_rate := tmp_cur_rate.actual_rate;
   
      update alm_nds_dd a
         set a.cur_net_rate = p_cur_net_rate
       where a.currency_cd = p_ccy
         and a.branch = p_branch
         and a.cr_int_type = p_cr_int_type
         and a.dr_int_type = p_dr_int_type
         and a.rate_ladder <> 'S'
         and p_cur_net_rate >= 0;
   
    END LOOP;
 
  END LOOP;

  For tmp_cur_nds in cur_nds loop
    p_ccy                 := tmp_cur_nds.currency_cd;
    p_branch              := tmp_cur_nds.branch;
    p_cr_int_type         := tmp_cur_nds.cr_int_type;
    p_dr_int_type         := tmp_cur_nds.dr_int_type;
    p_prev_day_actual_bal := tmp_cur_nds.nds_cur_book_bal;
    p_internal_key        := tmp_cur_nds.nds_id_number;
 
    For i in cur_rate_s loop
      If v_balance <> 0 then
        If p_prev_day_actual_bal <= v_balance then
          v_num := p_prev_day_actual_bal * v_actual_rate;
          Exit;
        ELSE
          v_num := (p_prev_day_actual_bal - v_balance) * i.
                   actual_rate + v_balance * v_actual_rate;
        END IF;
      END IF;
      v_balance     := i.balance;
      v_actual_rate := i.actual_rate;
    END LOOP;
 
    p_cur_net_rate := v_num / p_prev_day_actual_bal;
 
    update alm_nds_dd a
       set a.cur_net_rate = p_cur_net_rate
     where a.currency_cd = p_ccy
       and a.branch = p_branch
       and a.cr_int_type = p_cr_int_type
       and a.dr_int_type = p_dr_int_type
       and a.rate_ladder = 'S';
 
  END LOOP;

end GET_ALM_NDS_DD;