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

/*保存结果SQL的表，先建立好，以免后面编译出错*/
CREATE TABLE T_COMPARE_COLS_SCRIPTS (SCRIPTS VARCHAR2(500))
/

 

/*该过程针对一个table得出字段整改SQL*/

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;
/

 

 

 /*该过程先遍历出tablelist，然后fatch出tablename，循环调用上面的过程*/
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;

 

/*查看SQL结果，并copy出来在schemaB端执行*/

SELECT * FROM T_COMPARE_COLS_SCRIPTS ORDER BY SCRIPTS

 

/*两个schema结构已经一致，就可以insert了，当然是用动态SQL，不过为了加快效率，我用了 append与nologning*/
select
'insert /*+append*/ 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