從SQL Server資料庫轉到Oracle資料庫的資料指令碼處理

標籤：rtc   針對   方式   sig   cti   通用   欄位   建模   pow   

在我們很多情況下的開發，為了方便或者通用性的考慮，都首先考慮SQL Server資料庫進行開發，但有時候客戶的生產環境是Oracle或者其他資料庫，那麼我們就需要把對應的資料結構和資料指令碼轉換為對應的資料庫，資料結構一般來說，文法都遵循了SQL92的標準，或者我們根據不同的PowerDesigner檔案進行產生對應的結構指令碼即可，但是實際資料的指令碼我們就需要進行一定的處理，以及文本的替換處理了，本文結合Notepad++的文本Regex替換，實現一些如日期較為特殊的資料指令碼調整，把它從SQL Server轉換為Oracle的處理過程，本文就是針對這些整體的資料庫處理進行介紹。

1、資料庫設計檔案及資料庫結構指令碼

我們一般在做資料庫設計的時候，都會使用PowerDesigner這樣的資料庫建模工具進行設計，預設把它設計為SQL Server的資料庫設計模型，如下所示。

當然我們如果需要其他資料庫，那麼把它轉換為對應的資料庫，然後進行一定的資料庫類型調整，以及欄位的大小寫轉換即可。

 

根據這種方式我們調整後的各種資料庫設計檔案如下所示。

不同的資料庫的設計模型有所差異，那麼我們進行一些核對，主要是資料庫類型的核對即可，如備忘欄位的大文本應該設定為CLOB，二進位的應該調整為BLOB等。

例如對於Oracle的資料庫設計（從SQL Server轉換過來的），同時也需要把它的欄位名轉換為大寫才好，在PowerDesigner裡面可以執行自訂函數進行處理。

其中在對話方塊選擇開啟對應的大寫欄位表名的指令碼，如下操作。

為了大家方便使用，我把它貼出來，供使用。

‘檔案：powerdesigner.ucase.VBs‘版本：1.0‘功能：遍曆物理模型中的所有表，將表名、表代碼、欄位名、欄位代碼全部由小寫改成大寫；‘ 並將序列的名和代碼由小寫改成大寫。‘用法：開啟物理模型，運行本指令碼（Ctrl+Shift+X）‘備忘：‘*****************************************************************************dim model ‘current modelset model = ActiveModelIf (model Is Nothing) ThenMsgBox "There is no current Model"ElseIf Not model.IsKindOf(PdPDM.cls_Model) ThenMsgBox "The current model is not an Physical Data model."ElseProcessTables modelProcessSequences modelEnd If‘*****************************************************************************‘函數：ProcessSequences‘功能：遞迴遍曆所有的序列‘*****************************************************************************sub ProcessSequences(folder)‘處理模型中的序列：小寫改大寫dim sequencefor each sequence in folder.sequencessequence.name = UCase(sequence.name)sequence.code = UCase(sequence.code)nextend sub‘*****************************************************************************‘函數：ProcessTables‘功能：遞迴遍曆所有的表‘*****************************************************************************sub ProcessTables(folder)‘處理模型中的表dim tablefor each table in folder.tablesif not table.IsShortCut then ProcessTable tableend ifnext‘對子目錄進行遞迴dim subFolderfor each subFolder in folder.PackagesProcessTables subFoldernext end sub‘*****************************************************************************‘函數：ProcessTable‘功能：遍曆指定table的所有欄位，將欄位名由小寫改成大寫，‘ 欄位代碼由小寫改成大寫‘ 表名由小寫改成大寫 ‘*****************************************************************************sub ProcessTable(table)dim colfor each col in table.Columns‘將欄位名由小寫改成大寫col.code = UCase(col.code)col.name = UCase(col.name)next table.name = UCase(table.name)table.code = UCase(table.code)end sub

這樣處理後，我們在PowerDesigner裡面的表名及欄位就可以正常轉換為大寫了，從而可以獲得對應表的資料結構指令碼，如果需要多個表，那麼可以批量產生資料庫結構指令碼。

 

2、資料庫表資料指令碼的產生

上面的資料庫表結構的指令碼產生，只是我們資料庫遷移指令碼的一部分操作，有時候我們實際的架構或者業務系統裡面，都往往有一些基礎資料需要寫入的，那麼就需要我們構建對應的資料指令碼了。

在資料庫指令碼匯出的，我們可以使用很多工具，如SQL Server本身的工具就可以匯出資料的SQL指令碼，同時我們也可以利用其它資料庫管理工具，如Toad For SQLServer或者Navicat Premium等資料庫管理工具實現資料的匯出指令碼操作。

然後在產生指令碼的過程中，設定輸出的進階選項中的“要編寫指令碼的資料的類型”為【僅限資料】即可，如下所示。

不過預設採用SQLServer產生出來的資料指令碼，對日期類型轉換真不是很好，如下結果所示。

因此我使用更加直觀顯示的Navicat Premium 工具來處理資料庫的資料指令碼。使用Navicat Premium產生的指令碼如下所示（僅僅日期類型有所不同）。

獲得產生的資料指令碼如下所示。

剩下的工作就是我們對這些資料指令碼進行進一步的處理操作了。

 

3、資料表的資料指令碼的替換處理

上面介紹了通過工具來獲得正確的資料指令碼，我們使用了Navicat Premium或者 Toad For SQLServer都能夠獲得類似下面格式的時間指令碼。

 N‘2016-06-22 10:35:36.590‘

這樣我們為了處理為Oracle的日期資料，那麼需要轉換為

to_date(‘2016-06-22 10:35:36‘,‘yyyy-MM-dd HH24:mi:ss‘)

這樣的格式

 那麼我們對上面的指令碼，進行一定規則的處理，如替換:[dbo]. [ ] N‘為‘ 等常規文本處理後，還需要再進行Regex規則的處理才可以，例如我們的日期替換的Regex如下：

‘(\d{4}-\d{2}-\d{2}\s*\d{2}:\d{2}:\d{2})\.\d{3}‘
to_date\(‘\1‘,‘yyyy-MM-dd HH24:mi:ss‘\)

如下所示。

最後使用Regex替換後的資料庫指令碼如下所示。

 

4、資料指令碼在PL-SQL Developer工具上執行操作

上面介紹如何?了表資料的指令碼產生，有了這些指令碼，我們需要使用Oracle的資料庫管理工具 PL-SQL Developer工具進行資料匯入，才能最終完成整個過程。這個操作也是有所講究的。

例如我們建立各類不同的資料庫指令碼，那麼只需要按照順序加入或者選擇加入執行資料庫指令碼即可。

那麼執行這些SQL，該如何操作呢，是不是直接拖動到PL-SQL上就可以了？

當然不是，否則長一點的資料庫指令碼，就可能導致非常遲緩的執行效率。

一般可以通過兩種方式，一種是使用命令列的方式。

這種方式執行速度非常快，比起直接在PL-SQL的SQL視窗上執行更有效率。

另外一種方式，就是可以利用PL-SQL裡面的另外一個地方進行執行資料庫指令碼，如下所示。

在【 工具】【匯入表】的操作裡面，彈出一個對話方塊，也是執行指令碼高效的操作之一。

上面介紹的這些方式，就是在資料庫沒有的情況下，根據資料庫指令碼構建對應的資料對象和資料的。

從SQL Server資料庫轉到Oracle資料庫的資料指令碼處理