Oracle--执行SQL段（批量生成协议配置）-CSDN博客

本文链接：https://blog.csdn.net/iteye_4927/article/details/82551545
开发者博客：www.developsearch.com
--------------------------------------------------
-- 批量生成协议配置
-- 2013-11-29
-- 陈昕
--------------------------------------------------
DECLARE
  CURSOR C_CDH_CUSTOMER IS
  SELECT C.* FROM CDH_CUSTOMER C WHERE C.CUSTOMER_CODE IN ('0650000718','7556054455');
  
  V_CDH_CUSTOMER      CDH_CUSTOMER%ROWTYPE;
  
  v_period_type       CDH_ACCOUNT.Period_Type%TYPE;
  v_account_code      CDH_ACCOUNT.Billing_Code%TYPE;
  v_account_period    CDH_ACCOUNT.Account_Period%TYPE;

  V_PROTOCOL_ID       CDH_NEWRBT_PROTOCOL.PROTOCOL_ID%TYPE;
  V_CREATOR           CDH_NEWRBT_PROTOCOL.CREATOR%TYPE;

  V_START_DT          CDH_NEWRBT_FREIGHT_CFG.START_DT%TYPE;
  V_END_DT            CDH_NEWRBT_FREIGHT_CFG.END_DT%TYPE;
  
  -- 时间分段
  v_time_segment_id   CDH_NEWRBT_TIME_SEGMENT.Time_Segment_Id%TYPE;
  TYPE one_time_segment IS RECORD( 
       START_DT CDH_NEWRBT_FREIGHT_CFG.START_DT%TYPE,
       END_DT   CDH_NEWRBT_FREIGHT_CFG.START_DT%TYPE
  );
  TYPE time_segment_array IS TABLE OF one_time_segment INDEX BY BINARY_INTEGER;
  segment_rec_array  time_segment_array;
  segment_rec        one_time_segment;
  
  -- 组合
  v_freight_set_id CDH_NEWRBT_FREIGHT_SET.Freight_Set_Id%TYPE;
  type set_array is varray(3) of Varchar2(60);
  var_set_array set_array := set_array('QAAFJ100380','Q-1FJ100381','LCN100440');
  v_set_id CDH_NEWRBT_FREIGHT_SET.Set_Id%TYPE;
  
  v_rebate_rate  CDH_NEWRBT_FREIGHT_RATE.Rebate_Rate%TYPE;
  
  V_COUNT       NUMBER;

BEGIN

  V_START_DT   := TO_DATE('2013-11-15', 'yyyy-mm-dd');
  V_END_DT     := TO_DATE('2014-2-14', 'yyyy-mm-dd');
  V_CREATOR    := '337068';
  
  segment_rec.START_DT := TO_DATE('2013-11-15', 'yyyy-mm-dd');
  segment_rec.END_DT := TO_DATE('2013-11-30', 'yyyy-mm-dd');
  segment_rec_array(1) := segment_rec;
  
  segment_rec.START_DT := TO_DATE('2013-12-01', 'yyyy-mm-dd');
  segment_rec.END_DT := TO_DATE('2013-12-31', 'yyyy-mm-dd');
  segment_rec_array(2) := segment_rec;
  
  segment_rec.START_DT := TO_DATE('2014-01-01', 'yyyy-mm-dd');
  segment_rec.END_DT := TO_DATE('2014-01-31', 'yyyy-mm-dd');
  segment_rec_array(3) := segment_rec;
  
  segment_rec.START_DT := TO_DATE('2014-02-01', 'yyyy-mm-dd');
  segment_rec.END_DT := TO_DATE('2014-02-14', 'yyyy-mm-dd');
  segment_rec_array(4) := segment_rec;
   

  OPEN C_CDH_CUSTOMER;
  LOOP
    FETCH C_CDH_CUSTOMER
      INTO V_CDH_CUSTOMER;
    EXIT WHEN C_CDH_CUSTOMER%NOTFOUND;

    SELECT A.BILLING_CODE,A.PERIOD_TYPE,A.ACCOUNT_PERIOD 
      INTO v_account_code,v_period_type,v_account_period
      FROM CDH_ACCOUNT A
     WHERE A.BILLING_CODE = V_CDH_CUSTOMER.CUSTOMER_CODE;
     
    -- 判断折扣协议是否存在。
    SELECT COUNT(1)
      INTO V_COUNT
      FROM CDH_NEWRBT_PROTOCOL P
     WHERE P.CUSTOMER_CODE = V_CDH_CUSTOMER.CUSTOMER_CODE;
     
    -- 如果协议存在，使用已有协议ID。
    IF V_COUNT = 1 THEN
      SELECT P.PROTOCOL_ID
        INTO V_PROTOCOL_ID
        FROM CDH_NEWRBT_PROTOCOL P
       WHERE P.CUSTOMER_CODE = V_CDH_CUSTOMER.CUSTOMER_CODE;
       
      -- 判断标准运费配置是否存在（如果存在，不作处理）
      SELECT COUNT(1)
        INTO V_COUNT
        FROM CDH_NEWRBT_FREIGHT_CFG C
       WHERE C.PROTOCOL_ID = V_PROTOCOL_ID;
      IF V_COUNT = 0 THEN
          
          -- 插入标准运费
          INSERT INTO cdh_newrbt_freight_cfg
             (ID, protocol_id, start_dt, end_dt, valid_flg, is_segment, rebate_type, creator, create_tm, modifier, modify_tm)
          VALUES
             (SEQ_CDH_NEWRBT.Nextval, v_protocol_id, v_start_dt, v_end_dt, '1', '1', NULL, V_Creator, SYSDATE, V_Creator, SYSDATE);                  
          
          -- 插入时间分段
          FOR i IN 1..segment_rec_array.count LOOP
              SELECT SEQ_CDH_NEWRBT.NEXTVAL INTO v_time_segment_id FROM DUAL;
              INSERT INTO cdh_newrbt_time_segment
                (time_segment_id, protocol_id, start_dt, end_dt, rebate_type, session_id, create_tm)
              VALUES
                (v_time_segment_id, v_protocol_id, segment_rec_array(i).START_DT, segment_rec_array(i).END_DT, 1, NULL, SYSDATE);
                
              -- 插入组合
              for i in 1..var_set_array.count loop
                  SELECT SEQ_CDH_NEWRBT.NEXTVAL INTO v_freight_set_id FROM DUAL;
                  SELECT t.set_id INTO v_set_id FROM CDH_NEWRBT_SET t WHERE t.name = var_set_array(i);

                  INSERT INTO cdh_newrbt_freight_set
                      (freight_set_id, protocol_id, time_segment_id, set_id, session_id, create_tm)
                  VALUES
                      (v_freight_set_id, v_protocol_id, v_time_segment_id, v_set_id, NULL, SYSDATE);
                  
                  --dbms_output.put_line('start:'||v_rebate_rate);
                  IF  var_set_array(i) = 'QAAFJ100380' THEN v_rebate_rate := 0.9;  END IF;
                  IF  var_set_array(i) = 'Q-1FJ100381' THEN v_rebate_rate := 0.95; END IF;
                  IF  var_set_array(i) = 'LCN100440' THEN v_rebate_rate := 0.8;  END IF;
                  --dbms_output.put_line('end:'||v_rebate_rate);
                        
                  -- 插入折扣率
                  INSERT INTO cdh_newrbt_freight_rate
                    (rate_id, protocol_id, time_segment_id, freight_set_id, min_total_amt, max_total_amt, min_amt, max_amt, rebate_rate_type, rebate_rate, session_id, create_tm)
                  VALUES
                    (SEQ_CDH_NEWRBT.NEXTVAL, v_protocol_id, v_time_segment_id, v_freight_set_id, 0, 100000000, 0, 100000000, 3, v_rebate_rate, NULL, SYSDATE);
              end loop;   
              
          END LOOP; 
                            
      END IF;
      
    -- 如果协议不存在
    ELSIF V_COUNT = 0 THEN

          -- 插入协议
          SELECT SEQ_CDH_NEWRBT.NEXTVAL INTO V_PROTOCOL_ID FROM DUAL;
          INSERT INTO cdh_newrbt_protocol
            (protocol_id, customer_id, customer_code, cus_valid_flag, account_code, dept_code, period_type, account_period, balance_cycle, creator, create_tm, modifier, modify_tm)
          VALUES
            (V_PROTOCOL_ID, V_CDH_CUSTOMER.customer_id, V_CDH_CUSTOMER.customer_code, '0', v_account_code, V_CDH_CUSTOMER.Current_Department,v_period_type, v_account_period, '1', v_creator, SYSDATE, v_creator, SYSDATE);

          -- 插入标准运费
          INSERT INTO cdh_newrbt_freight_cfg
             (ID, protocol_id, start_dt, end_dt, valid_flg, is_segment, rebate_type, creator, create_tm, modifier, modify_tm)
          VALUES
             (SEQ_CDH_NEWRBT.Nextval, v_protocol_id, v_start_dt, v_end_dt, '1', '1', NULL, V_Creator, SYSDATE, V_Creator, SYSDATE);                  

          -- 插入时间分段
          FOR i IN 1..segment_rec_array.count LOOP
              SELECT SEQ_CDH_NEWRBT.NEXTVAL INTO v_time_segment_id FROM DUAL;
              INSERT INTO cdh_newrbt_time_segment
                (time_segment_id, protocol_id, start_dt, end_dt, rebate_type, session_id, create_tm)
              VALUES
                (v_time_segment_id, v_protocol_id, segment_rec_array(i).START_DT, segment_rec_array(i).END_DT, 1, NULL, SYSDATE);

              -- 插入组合
              for i in 1..var_set_array.count loop
                  SELECT SEQ_CDH_NEWRBT.NEXTVAL INTO v_freight_set_id FROM DUAL;
                  SELECT t.set_id INTO v_set_id FROM CDH_NEWRBT_SET t WHERE t.name = var_set_array(i);
                  
                  INSERT INTO cdh_newrbt_freight_set
                      (freight_set_id, protocol_id, time_segment_id, set_id, session_id, create_tm)
                  VALUES
                      (v_freight_set_id, v_protocol_id, v_time_segment_id, v_set_id, NULL, SYSDATE);
                  
                  IF  var_set_array(i) = 'QAAFJ100380' THEN v_rebate_rate := 0.9;  END IF;
                  IF  var_set_array(i) = 'Q-1FJ100381' THEN v_rebate_rate := 0.95; END IF;
                  IF  var_set_array(i) = 'LCN100440' THEN v_rebate_rate := 0.8;  END IF;
                        
                  -- 插入折扣率
                  INSERT INTO cdh_newrbt_freight_rate
                    (rate_id, protocol_id, time_segment_id, freight_set_id, min_total_amt, max_total_amt, min_amt, max_amt, rebate_rate_type, rebate_rate, session_id, create_tm)
                  VALUES
                    (SEQ_CDH_NEWRBT.NEXTVAL, v_protocol_id, v_time_segment_id, v_freight_set_id, 0, 100000000, 0, 100000000, 3, v_rebate_rate, NULL, SYSDATE);
              end loop;   
              
          END LOOP; 

    END IF;
  
    COMMIT;
  END LOOP;
  CLOSE C_CDH_CUSTOMER;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END;
开发者博客：www.developsearch.com