oracle数据表数据同步公用方法

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了oracle数据表数据同步公用方法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3276字，纯文字阅读大概需要5分钟。

内容图文

 自己写了个数据同步的方法，两个数据库之间的数据同步，自己可以通过调用存储过程，添加作业实现定时同步数据。

CREATE OR REPLACE PROCEDURE Data_sync_Common(tableName in varchar2) is
  v_sql             VARCHAR2(20000); --????SQL
  pk_col_name       VARCHAR2(800); --主键SQL
  insert_col_name_A VARCHAR2(20000); --A表字段
  insert_col_name_B VARCHAR2(20000); --B表字段
  update_col_name   VARCHAR2(20000); --更新字段
  dsql              VARCHAR2(20000);
  local_col_in      VARCHAR2(20000);
  TYPE cur_type IS REF CURSOR;
  cur_not_contain_col cur_type;
  CURSOR CR1 IS

  -- select ‘alter table ‘||table_name||‘ disable constraint ‘||constraint_name as dsql
    SELECT table_name, constraint_name
      FROM user_constraints
     WHERE constraint_type = ‘R‘
       AND table_name = upper(tableName);
BEGIN
  SELECT to_char(WMSYS.WM_CONCAT(‘‘‘‘ || column_name || ‘‘‘‘))
    INTO local_col_in
    FROM user_tab_cols
   WHERE table_name = upper(tableName);

  dsql := ‘SELECT * FROM user_tab_cols@testjob_dblink1 WHERE table_name=‘‘‘ ||
          upper(tableName) || ‘‘‘ AND column_name not IN(‘ || local_col_in || ‘)‘;
  dbms_output.put_line(dsql);
  OPEN cur_not_contain_col FOR dsql;

  SELECT REPLACE(to_char(WMSYS.WM_CONCAT(‘ AND a.‘ || cu.COLUMN_NAME ||
                                         ‘=b.‘ || cu.COLUMN_NAME)),
                 ‘,‘,
                 ‘‘)
    INTO pk_col_name
    FROM user_cons_columns cu, user_constraints au
   WHERE cu.constraint_name = au.constraint_name
     AND au.constraint_type = ‘P‘
     AND au.table_name = upper(tableName);

  IF (pk_col_name IS NULL) OR (LENGTH(pk_col_name) < 6) THEN
    SELECT REPLACE(to_char(WMSYS.WM_CONCAT(‘ and a.‘ || cu.COLUMN_NAME ||
                                           ‘=b.‘ || cu.COLUMN_NAME)),
                   ‘,‘,
                   ‘‘)
      INTO pk_col_name
      FROM user_cons_columns cu, user_constraints au
     WHERE cu.constraint_name = au.constraint_name
       AND au.constraint_type = ‘U‘
       AND au.table_name = upper(tableName);
  END IF;

  SELECT to_char(WMSYS.WM_CONCAT(‘b.‘ || column_name))
    INTO insert_col_name_B
    FROM user_tab_cols
   WHERE table_name = upper(tableName);

  SELECT to_char(WMSYS.WM_CONCAT(‘a.‘ || column_name))
    INTO insert_col_name_A
    FROM user_tab_cols
   WHERE table_name = upper(tableName);

  SELECT to_char(WMSYS.WM_CONCAT(‘a.‘ || column_name || ‘=b.‘ ||
                                 column_name))
    INTO update_col_name
    FROM user_tab_cols
   WHERE table_name = upper(tableName)
     AND column_name NOT IN
         (SELECT cu.COLUMN_NAME
            FROM user_cons_columns cu, user_constraints au
           WHERE cu.constraint_name = au.constraint_name
             AND au.constraint_type = ‘P‘
             AND au.table_name = upper(tableName));

  v_sql := ‘MERGE INTO ‘ || upper(tableName) || ‘@testjob_dblink1 a USING ‘ || upper(tableName) ||
           ‘ b‘ || ‘
            ON (‘ || substr(pk_col_name, 6, length(pk_col_name)) || ‘)‘;
   dbms_output.put_line(update_col_name);
  IF (update_col_name IS NOT NULL) AND (LENGTH(update_col_name) > 0) THEN
    v_sql := v_sql || ‘ when matched then
        update set ‘ || update_col_name;
  END IF;
  v_sql := v_sql || ‘
           when not matched then
           insert (‘ || insert_col_name_A || ‘) values( ‘ ||
           insert_col_name_B || ‘)‘;


  dbms_output.put_line(v_sql);
  EXECUTE immediate(v_sql);
  EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);

END Data_sync_Common;
-----------------------------调用上述存储过程
CREATE OR REPLACE PROCEDURE DATA_sync is
BEGIN
data_sync_common(‘TEST_SYNC‘);
COMMIT;
EXCEPTION 
  WHEN  OTHERS  THEN 
    ROLLBACK ;
END  DATA_sync;

 

oracle数据表数据同步公用方法

标签：

本文系统来源：http://www.cnblogs.com/zchunhua/p/5409276.html

内容总结

以上是互联网集市为您收集整理的oracle数据表数据同步公用方法全部内容，希望文章能够帮你解决oracle数据表数据同步公用方法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---