oracle如何保证两个表 数据的同步

发布网友 发布时间：2022-04-25 16:32

我来回答

共2个回答

懂视网 时间：2022-04-09 02:18

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

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数据表数据同步公用方法

标签：

热心网友 时间：2022-04-08 23:26

推荐你使用触发器