自动生成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