自動生成Scripts ,同步相似schema中table字段結構

         最近公司項目繁忙，實在沒有時間寫博，内心充滿不安。

好在本次遇到一個需要“對比兩個相似schema中table字段結構，并生成Scripts” 的需求，特整理于此，以平民憤 -_-，

背景如下：

1. schemaA是我們的新版本資料庫架構

2. schemaB是我們的舊系統，包含了若幹資料

3. 甲方要求更新必須保留原有交易資料

4. 顯然schemaA、schemaB 從索引結構、序列配置、字段配置 是有多處不同的

我該怎麼處理呢？

我的思路：

1. 直接使用 schemaA  資料庫結構，這樣可以忽略 索引結構、序列配置 的過程

2. 需要将資料從 schemaB    insert 至 schemaA 中

3. 但是相對于schemaB ，schemaA已經做了太多字段變更與改進。直接insert 會有一大堆ora- 出來

4. 我需要在insert之前，将schemaB字段調整與schemaA一緻，如果一個個去找，再拼接腳本，我會瘋掉的。。。

5. 那麼我們需要關注 all_tab_cols 這個資料字典

我的腳本：

腳本的設計思路很簡單：

1. 先使用對比的辦法，針對一個table分别生成 add 與 drop SQL，再union。   為過程A

2. 使用遊标周遊兩個schema的table得到table_list , 并循環調用過程A

3. 将得到的SQL儲存至table以便讀取

先檢視一下tablename的情況吧

select * from

(select table_name from user_tables) a

full outer join

(select table_name from all_tables where owner='JS_TEMP') b

on a.table_name=b.table_name

where a.table_name is null or b.table_name is null

sysdba:

grant select any table to jsec;

jsec:

CREATE TABLE T_COMPARE_COLS_SCRIPTS (SCRIPTS VARCHAR2(500))

/

CREATE OR REPLACE PROCEDURE P_COMPARE_COLS_SCRIPTS     

(V_TABLE_NAME IN VARCHAR2,

 V_SCHEMA_A IN VARCHAR2,

 V_SCHEMA_B IN VARCHAR2)

AS

BEGIN

INSERT INTO T_COMPARE_COLS_SCRIPTS

SELECT 'ALTER TABLE  '||V_SCHEMA_A||'.'||V_TABLE_NAME||'  ADD  '||B.COLUMN_NAME||' '||B.DATA_TYPE

||CASE WHEN B.DATA_TYPE IN ('DATE','CLOB','BLOB') THEN ''

  ELSE CASE WHEN B.DATA_LENGTH>0 THEN '(' ELSE '' END||B.DATA_LENGTH||CASE WHEN B.DATA_LENGTH>0 THEN ')'

       ELSE '' END

  END

--||CASE WHEN B.NULLABLE='N' THEN ' NOT NULL' ELSE '' END                --我暫時忽略了是否為null的定義，如果需要可以解注

--||CASE WHEN B.DATA_DEFAULT IS NOT NULL THEN '' ELSE '' END       

 --我這裡暫時還不能處理好預設值的問題，因為資料字典中是LONG類型，不能直接轉換為字元串，但是可以在create table中to_lob ，再to_char

||';'

AS SCRIPTS

  FROM

  (SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH,NULLABLE,DATA_DEFAULT

   FROM ALL_TAB_COLS

   WHERE OWNER = V_SCHEMA_A

   AND TABLE_NAME = V_TABLE_NAME

   AND VIRTUAL_COLUMN!='YES') A

  FULL OUTER JOIN

  (SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH,NULLABLE,DATA_DEFAULT

   FROM ALL_TAB_COLS

   WHERE OWNER = V_SCHEMA_B

   AND TABLE_NAME = V_TABLE_NAME

   AND VIRTUAL_COLUMN!='YES') B

    ON A.COLUMN_NAME = B.COLUMN_NAME

 WHERE A.COLUMN_NAME IS NULL

UNION ALL

SELECT 'ALTER TABLE  '||V_SCHEMA_A||'.'||V_TABLE_NAME||'  DROP COLUMN  '||A.COLUMN_NAME||';'

AS SCRIPTS

  FROM

  (SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH

   FROM ALL_TAB_COLS

   WHERE OWNER = V_SCHEMA_A

   AND TABLE_NAME = V_TABLE_NAME

   AND VIRTUAL_COLUMN!='YES') A

  FULL OUTER JOIN

  (SELECT COLUMN_NAME,DATA_TYPE,TO_CHAR(DATA_LENGTH) DATA_LENGTH

   FROM ALL_TAB_COLS

   WHERE OWNER = V_SCHEMA_B

   AND TABLE_NAME = V_TABLE_NAME

   AND VIRTUAL_COLUMN!='YES') B

    ON A.COLUMN_NAME = B.COLUMN_NAME

 WHERE B.COLUMN_NAME IS NULL;

END;

/

CREATE OR REPLACE PROCEDURE P_COMPARE_COL_DRIVER

(V_SCHEMA_DRIVER_A VARCHAR2,

 V_SCHEMA_DRIVER_B VARCHAR2)

 AS

  V_TABLE_DRIVER_NAME VARCHAR2(50);

BEGIN

DELETE FROM T_COMPARE_COLS_SCRIPTS;

  DECLARE

    CURSOR C_TABLE_LIST IS

      SELECT A.TABLE_NAME AS TABLE_NAME_A

        FROM (SELECT TABLE_NAME

                FROM ALL_TABLES

               WHERE OWNER = V_SCHEMA_DRIVER_A) A

       INNER JOIN (SELECT TABLE_NAME

                     FROM ALL_TABLES

                    WHERE OWNER = V_SCHEMA_DRIVER_B) B

          ON A.TABLE_NAME = B.TABLE_NAME

       ORDER BY TABLE_NAME_A;

  BEGIN

    OPEN C_TABLE_LIST;

    FETCH C_TABLE_LIST

      INTO V_TABLE_DRIVER_NAME;

    WHILE C_TABLE_LIST%FOUND LOOP

      DBMS_OUTPUT.PUT_LINE(V_TABLE_DRIVER_NAME);

      BEGIN

        P_COMPARE_COLS_SCRIPTS(V_TABLE_NAME => V_TABLE_DRIVER_NAME,

                               V_SCHEMA_A   => V_SCHEMA_DRIVER_A,

                               V_SCHEMA_B   => V_SCHEMA_DRIVER_B);

      END;

      FETCH C_TABLE_LIST

        INTO V_TABLE_DRIVER_NAME;

    END LOOP;

    COMMIT;

  END;

END;

/

begin

  p_compare_cols_scripts (v_table_name => 'BARGAINOR',v_schema_A => 'JSEC',v_schema_B => 'JS_TEMP');

end;

begin

  P_COMPARE_COL_DRIVER(v_schema_driver_A => 'JS_TEMP',v_schema_driver_B => 'JSEC');

end;

SELECT * FROM T_COMPARE_COLS_SCRIPTS ORDER BY SCRIPTS

select

'insert into jsec.'||table_name||' select * from js_temp.'||table_name||' nologging;'

from user_tables

where table_name in (select table_name from all_tables where owner='JSEC')

order by table_name