oracle 大批量执行sql,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;