自己写了个数据同步的方法,两个数据库之间的数据同步,自己可以通过调用存储过程,添加作业实现定时同步数据。
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;