Oracle--执行SQL段（批量生成协议配置）
---------------------------------------------------- 批量生成协议配置-- 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;
?
