用游标
DECLARE TYPE cur_type IS REF CURSOR;
mcb_c cur_type;mcb_id NUMBER(10);v_created_time DATE;v_created_by VARCHAR2(30);v_lastupdated_time DATE;v_lastupdated_by VARCHAR2(30);v_code VARCHAR2(10);v_name VARCHAR2(60);v_is_in_black_list VARCHAR2(2);
mcba_c cur_type;mcba_id NUMBER(10);v_a_created_time DATE;v_a_created_by VARCHAR2(30);v_a_code VARCHAR2(10);v_a_name VARCHAR2(60);v_broker_id NUMBER(10);
begin /*复制数据到CUSTOMS_BROKER*/ /*应先在ID_GENERATOR表中新增ID KEY的记录*/ SELECT ID_VALUE INTO mcb_id FROM ID_GENERATOR WHERE ID_KEY='CUSTOMS_BROKER_ID'; OPEN mcb_c FOR SELECT CREATE_DATETIME, CREATE_BY_ACTOR, UPDATE_DATETIME, UPDATE_BY_ACTOR, CUSTOM_CODE, CUSTOMS_BROKER, BLACK_STATUS FROM EMS_CUSTOMS_BROKER WHERE CUSTOM_FLAG = 'C'; LOOP FETCH mcb_c INTO v_created_time, v_created_by, v_lastupdated_time, v_lastupdated_by, v_code, v_name, v_is_in_black_list; EXIT WHEN mcb_c%NOTFOUND;
/*重设CUSTOMS_BROKER主键*/ mcb_id := mcb_id + 1; INSERT INTO MEMS_CUSTOMS_BROKER(ID, CODE, NAME, IS_IN_BLACK_LIST, OWED_PAYMENT_BILL_COUNT, CREATED_BY, CREATED_TIME, LASTUPDATED_BY, LASTUPDATED_TIME, VERSION) VALUES (mcb_id, v_code, v_name, CASE WHEN v_is_in_black_list = 'Y' THEN 1 ELSE 0 END, 0, v_created_by, CAST(v_created_time AS TIMESTAMP), v_lastupdated_by, CAST(v_lastupdated_time AS TIMESTAMP), 0); END LOOP; CLOSE mcb_c; mcb_id := mcb_id + 5; UPDATE ID_GENERATOR SET ID_VALUE = mcb_id WHERE ID_KEY='CUSTOMS_BROKER_ID'; COMMIT;
/*复制数据到CUSTOMS_BROKER_ALIAS*/ /*应先在ID_GENERATOR表中新增ID KEY的记录*/ SELECT ID_VALUE INTO mcba_id FROM ID_GENERATOR WHERE ID_KEY='CUSTOMS_BROKER_ALIAS_ID'; OPEN mcba_c FOR SELECT CREATE_DATETIME, CREATE_BY_ACTOR, CUSTOMS_BROKER, CUSTOM_CODE FROM EMS_CUSTOMS_DETAIL; LOOP FETCH mcba_c INTO v_a_created_time, v_a_created_by, v_a_name, v_a_code; EXIT WHEN mcba_c%NOTFOUND;
select b.id into v_broker_id from MEMS_CUSTOMS_BROKER b where b.CODE = v_a_code;
/*重设CUSTOMS_BROKER_ALIAS主键*/ mcba_id := mcba_id + 1; INSERT INTO MEMS_CUSTOMS_BROKER_ALIAS(ID, BROKER_ALIAS, CUSTOMS_BROKER_ID, CREATED_BY, CREATED_TIME, VERSION) VALUES (mcba_id, v_a_name, v_broker_id, v_a_created_by, CAST(v_a_created_time AS TIMESTAMP), 0); END LOOP; CLOSE mcba_c; mcba_id := mcba_id + 5; UPDATE ID_GENERATOR SET ID_VALUE = mcba_id WHERE ID_KEY='CUSTOMS_BROKER_ALIAS_ID'; COMMIT;end;
