两表不在同一数据库之间的复制数据

最新推荐文章于 2025-03-03 19:48:38 发布

原创最新推荐文章于 2025-03-03 19:48:38 发布 · 604 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #insert #generator #import #null #date

oracle 专栏收录该内容

19 篇文章

订阅专栏

本文提供了一个Oracle数据库中使用PL/SQL编写的示例脚本，该脚本通过游标遍历EMS_CUSTOMS_BROKER表中的记录，并根据每个客户的代码查询BLACK_CUSTOM_LOG表来获取进出黑名单的时间。然后，将这些信息连同客户的基本信息插入到目标表中。此外，还展示了如何处理客户别名数据及ID生成器的更新。

--依靠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 ;