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 [email protected]_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 isBEGINdata_sync_common(‘TEST_SYNC‘);COMMIT;EXCEPTION   WHEN  OTHERS  THEN     ROLLBACK ;END  DATA_sync;

 

oracle資料表資料同步公用方法