--依靠DBMS_OUTPUT.PUT_LINE导出的语句,再执行导出的语句
--SET SERVEROUTPUT ON;
DECLARE
CURSOR customerCursor IS SELECT * FROM EMS_CUSTOMS_BROKER WHERE CUSTOM_FLAG = 'C';
v_customer_pk NUMBER;
v_customer_rowCount NUMBER;
v_in_bklst_time DATE;
v_out_bklst_time DATE;
v_in_bklst_time_str VARCHAR2(30);
v_out_bklst_time_str VARCHAR2(30);
v_customer EMS_CUSTOMS_BROKER%ROWTYPE;
v_customerDtl_pk NUMBER;
v_customerDtl_rowCount NUMBER;
v_customerDtl EMS_CUSTOMS_DETAIL%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('SET SERVEROUTPUT ON;');
DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT.PUT_LINE(''start import customer broker data...'');');
v_customer_pk := 1;
v_customer_rowCount :=0;
v_customerDtl_pk :=1;
v_customerDtl_rowCount := 0;
OPEN customerCursor;
LOOP
FETCH customerCursor INTO v_customer;
EXIT WHEN customerCursor%NOTFOUND;
SELECT MAX(b.CREATE_DATETIME) INTO v_in_bklst_time FROM BLACK_CUSTOM_LOG b
WHERE b.CUSTOM_CODE = v_customer.CUSTOM_CODE AND b.BLACK_FLAGE = 'L';
SELECT MAX(b.CREATE_DATETIME) INTO v_out_bklst_time FROM BLACK_CUSTOM_LOG b
WHERE b.CUSTOM_CODE = v_customer.CUSTOM_CODE AND b.BLACK_FLAGE = 'R';
IF v_in_bklst_time IS NOT NULL
THEN v_in_bklst_time_str := to_char(v_in_bklst_time,'yyyy-MM-dd hh24:mi:ss');
END IF;
IF v_out_bklst_time IS NOT NULL
THEN v_out_bklst_time_str := to_char(v_out_bklst_time,'yyyy-MM-dd hh24:mi:ss');
END IF;
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO MEMS_CUSTOMS_BROKER('||
'ID, CODE, NAME, IS_IN_BLACK_LIST, '||
'IN_BKLST_TIME, OUT_BKLST_TIME, '||
'OWED_PAYMENT_BILL_COUNT, CREATED_BY, '||
'CREATED_TIME, VERSION) '
);
DBMS_OUTPUT.PUT_LINE(
'VALUES(' || v_customer_pk || ', ''' ||
v_customer.CUSTOM_CODE || ''', ''' ||
v_customer.CUSTOMS_BROKER || ''', '||
CASE WHEN v_customer.BLACK_STATUS = 'Y' THEN 1 ELSE 0 END || ', ' ||
CASE WHEN v_customer.BLACK_STATUS = 'Y' AND v_in_bklst_time IS NOT NULL
THEN 'TO_DATE('''||v_in_bklst_time_str||''',''yyyy-MM-dd hh24:mi:ss'')'
ELSE 'NULL' END || ', ' ||
CASE WHEN v_customer.BLACK_STATUS = 'N' AND v_out_bklst_time IS NOT NULL
THEN 'TO_DATE('''||v_out_bklst_time_str||''',''yyyy-MM-dd hh24:mi:ss'')'
ELSE 'NULL' END || ', ' ||
'0, ''MEMSSYS'', sysdate, 0);'
);
DECLARE
CURSOR customerDtlCursor IS SELECT * FROM EMS_CUSTOMS_DETAIL WHERE CUSTOM_CODE = v_customer.CUSTOM_CODE;
BEGIN
OPEN customerDtlCursor;
LOOP
FETCH customerDtlCursor INTO v_customerDtl;
EXIT WHEN customerDtlCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO MEMS_CUSTOMS_BROKER_ALIAS('||
'ID ,BROKER_ALIAS ,CUSTOMS_BROKER_ID ,'||
'CREATED_BY ,CREATED_TIME ,VERSION)'
);
DBMS_OUTPUT.PUT_LINE(
'VALUES('|| v_customerDtl_pk || ', '''
|| v_customerDtl.CUSTOMS_BROKER || ''', '
|| v_customer_pk || ', ''MEMSSYS'', sysdate, 0);'
);
v_customerDtl_pk := v_customerDtl_pk + 1;
v_customerDtl_rowCount := v_customerDtl_rowCount + 1 ;
END LOOP;
CLOSE customerDtlCursor;
END;
v_customer_pk := v_customer_pk + 1;
v_customer_rowCount := v_customer_rowCount + 1;
END LOOP;
CLOSE customerCursor;
DECLARE
CURSOR customerDtlNoIdCursor IS SELECT * FROM EMS_CUSTOMS_DETAIL d
WHERE d.CUSTOM_CODE NOT IN (SELECT CUSTOM_CODE FROM EMS_CUSTOMS_BROKER)
OR d.CUSTOM_CODE IS NULL;
BEGIN
OPEN customerDtlNoIdCursor;
LOOP
FETCH customerDtlNoIdCursor INTO v_customerDtl;
EXIT WHEN customerDtlNoIdCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO MEMS_CUSTOMS_BROKER_ALIAS('||
'ID ,BROKER_ALIAS ,'||
'CREATED_BY ,CREATED_TIME ,VERSION)'
);
DBMS_OUTPUT.PUT_LINE(
'VALUES('|| v_customerDtl_pk || ', '''
|| v_customerDtl.CUSTOMS_BROKER || ''', '
|| '''MEMSSYS'', sysdate, 0);'
);
v_customerDtl_pk := v_customerDtl_pk + 1;
v_customerDtl_rowCount := v_customerDtl_rowCount + 1 ;
END LOOP;
CLOSE customerDtlNoIdCursor;
END;
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO ID_GENERATOR(ID_KEY,ID_VALUE) VALUES(''CUSTOMS_BROKER_ALIAS_ID'' , '|| v_customerDtl_pk||');'
);
DBMS_OUTPUT.PUT_LINE(
'INSERT INTO ID_GENERATOR(ID_KEY,ID_VALUE) VALUES(''CUSTOMS_BROKER_ID'' , ' || v_customer_pk || ');'
);
DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT.PUT_LINE(''import customer broker alias row count: '|| v_customerDtl_rowCount ||''');');
DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT.PUT_LINE(''import customer broker row count: '|| v_customer_rowCount ||''');');
DBMS_OUTPUT.PUT_LINE('DBMS_OUTPUT.PUT_LINE(''import customer broker end.'');');
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('import customer broker error:' || SQLERRM);
ROLLBACK;
END ;