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

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;