專案原因導致出現兩個開發環境，主資料庫環境因需求變更每天都需要進行指令碼的修改，而報表伺服器的指令碼也需要同步更新，需求變更很少會同步提醒的；人工判斷太過於麻煩，我又是一個懶人；只好寫個指令碼自動進行識別並加以執行了。

這只是一個簡單的版本比較工具，事實上變更是很難判斷的，例如表中新增了一個欄位，導致順序發生變更，這個時候你很難判斷是新增還是修改的；諸如欄位型別的修改.因此只能去判斷表是否存在，欄位是否存在，進而執行判斷指令碼，產生相應的表指令碼和欄位教本。

事實上Oracle有個DBMS_METADATA資料包提供了DLL指令碼，不過包含了很多儲存引數，對版本比較和物件生成沒有什麼意義。

--CREATE THE CREATE_TABLE SCRIPT, THOUGH WE CAN USE

--LIKE select dbms_metadata.get_ddl('TABLE','TABLENAME','USERNAME') from dual; GET THE SQL SCRIPT

--BUT IT IS NOT HELPFUL TO COMPARE THE DIFFERENT VERSION

SELECT SQLTEXT FROM
(
SELECT 'CREATE TABLE '||TABLE_NAME AS SQLTEXT,-1 AS COLUMN_ID,TABLE_NAME FROM
UNION
SELECT '(' AS SQLTEXT,0 AS COLUMN_ID,TABLE_NAME FROM
UNION
SELECT ');' AS SQLTEXT,100 AS COLUMN_ID,TABLE_NAME FROM
UNION
SELECT
CASE WHEN DATA_TYPE='NUMBER'
THEN
CASE WHEN DATA_PRECISION IS NULL
THEN COLUMN_NAME||' INTEGER,'
ELSE COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'||DECODE(COLUMN_ID,(SELECT MAX

(B.COLUMN_ID) FROM B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
END
WHEN DATA_TYPE='NVARCHAR2'
THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH/2)||')'||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM

B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
WHEN DATA_TYPE IN ('CHAR','VARCHAR2')
THEN COLUMN_NAME||' '||DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')'||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM

B WHERE A.TABLE_NAME=B.TABLE_NAME),' ',',')
WHEN DATA_TYPE IN ('DATE','BLOB','CLOB','LONG','NCLOB')
THEN COLUMN_NAME||' '||DATA_TYPE||DECODE(COLUMN_ID,(SELECT MAX(B.COLUMN_ID) FROM B WHERE

A.TABLE_NAME=B.TABLE_NAME),' ',',')
END AS SQLTEXT,
COLUMN_ID,
A.TABLE_NAME
FROM A
Where A.table_name Like 'T%'
) d
Where d.table_name Not In
(
Select c.table_name From USER_TABLES c
Where c.table_name Like 'T%'
)
ORDER BY TABLE_NAME,COLUMN_ID,SQLTEXT

--ADD NEW COLUMN ACCORDING THE LATEST TABLE NAME

SELECT 'ALTER TABLE '||TABLE_NAME||' ADD "'||COLUMN_NAME||'" '||
(CASE WHEN DATA_TYPE='NUMBER'
THEN
CASE WHEN DATA_PRECISION IS NULL
THEN 'INTEGER'
ELSE DATA_TYPE||'('||TO_CHAR(DATA_PRECISION)||','||TO_CHAR(DATA_SCALE)||')'
END
WHEN DATA_TYPE='NVARCHAR2'
THEN DATA_TYPE||'('||TO_CHAR(DATA_LENGTH/2)||')'
WHEN DATA_TYPE IN ('CHAR','VARCHAR2')
THEN DATA_TYPE||'('||TO_CHAR(DATA_LENGTH)||')'
WHEN DATA_TYPE IN ('DATE','BLOB','CLOB','LONG','NCLOB')
THEN DATA_TYPE
END)||' ;' SQLTEXT
FROM A
Where (a.table_name,a.column_name) Not In
(
Select b.table_name,b.column_name From USER_TAB_COLS b
Where b.table_name Like 'T%'
)
And A.table_name Like 'T%'
ORDER BY COLUMN_ID

--BASED THE COLUMN_NAME AND COLUMN_ID,NEED TO MODIFY THE COLUMN NAME

--BUT IN FACT MOST SITUATION IT RESULT IN NEW COLUMN,SO IT IS NO USEFUL

....

關於資料庫物件版本比較的指令碼

相關文章