VBAでテーブル仕様書からDLL文を自動生成する

概要

このページでは

Excelのテーブル仕様書から、DLL文をVBAで自動生成するツールを作成します。

テーブル仕様書とは

ウォーターフォール型で進めるプロジェクト設計では、データベース設計を行う際に、テーブル仕様書なるものを記述します。(アジャイル開発などでも必要に応じて設計書に起こしたりします。)
テーブル仕様書には、テーブルの目的や要件と、構成する列の属性などを定義しておき、実際のテーブルを作成する際のインプット資料とします。

開発工程では、テーブル仕様書を作成した後、その内容からDLL文(Create Table文)を起こして実際のテーブルを作成しますが、今回はこれをVBAで自動生成するツールを作ります。簡単なサンプルになりますが、拡張すれば実業務でも使えるものです。

今回の記事における限定・制約

本来のテーブル仕様書には、プロジェクト名やら作成・確認者や作成・更新履歴などの書き込み欄が存在しますが、今回は紛らわしくなるので、最低限の項目だけで話をすすめます。また、内部データ型も今回は限定して作成します。 
いずれも仕組みが理解出来れば簡単に拡張出来ると思います。
なお、自動生成するDLL文はOracleの形式とします。

詳細

テーブル仕様の概要

イメージ

Excel文書ファイルでテーブル仕様書を作成します。
すべて同じレイアウトにすることで様々な自動生成処理を実現することが出来ます。

今回はこのExcel文書ファイルからDDL文を自動生成するツールを作成します。



テーブルの情報

・テーブル名
・テーブルID
・要件

構成する列の情報

・No
・日本語名
・カラム名(列ID)
・データ型(内部データ型)
・長さ  (桁数)
・PK   (主キーならY)
・初期値
・NULL許容(NOT NULLならN)
・備考

取扱う内部データ項目

内部データ項目の形式は、DDL文の自動生成についてわかりやすくシンプルな説明とするため以下の3つだけにします。(これを理解出来れば他の内部データ項目の拡張も容易です。)

・DATE   固定長日付/時刻値
・NUMBER  固定数または浮動小数点数
・VARCHAR2 可変長文字列(2000バイト以下)

作成するDDL文

上記のテーブル仕様書からDDL文(Create Table)を自動生成します。
ブック内に複数のテーブル仕様書シートがあればすべて対象とします。

・テーブル仕様書には日本語名や備考が記述されているので、それらをコメントとしてDDL文に添えるのがよいが今回は省略します。(続編で実現予定)

・今回はサンプルなので、以下の考慮をしていません。

 テーブル仕様書以外のシートを含んだ場合の考慮をしません。
 一般的には何らかのルールを設けて、自動生成対象のシートかどうか判断可能なレイアウトにします。例えば今回の例ですとシート名とテーブルIDが等しければ自動生成の対象とする、などとしてもいいかもしれません。(厳格なことを言えばそれではNGだと思いますが)

 セルの値の入力チェックをしていません。
 日本語名の必須チェックや長さの数値チェックなど沢山のチェックが考えられますが、今回は実装の対象外としています。いざチェックを入れようとした場合、事前チェック用のツールを作った方がいい結果が得られるかもしれません。(細かなチェックと生成が絡み合ってわかりにくくなるので)

DDLサンプル

実際に今回のツールで生成したものです。

CREATE TABLE customer (
    cid varchar2(8) NOT NULL, 
    cname varchar2(300) NOT NULL, 
    founded date , 
    capital number(10) DEFAULT 0, 
    address varchar2(500), 
    telno varchar2(100), 
    note varchar2(1000), 
 PRIMARY KEY (cid)
)

実現方法・説明

VBAソースコード

メイン(main)
'
' テーブル仕様書Excel文書ファイルからDDL文を自動生成
'
Sub main()
  
    Dim bookname As String
    bookname = "makedll01.xlsm"
  
    Dim wb As Workbook
    Dim ws As Worksheet

    'Excel文書ファイルをブックオブジェクトとして設定
    Set wb = Workbooks(bookname)
    For Each ws In wb.Worksheets
        'テーブル毎の処理(DLL文の生成)
        sheet_proc ws
    Next
    
    MsgBox "おわり"

End Sub
メインの説明

Excel文書ファイル(ブック)に含まれるテーブル仕様書(シート)毎に、DDL文を生成する処理(処理名:sheet_proc)を呼び出すロジックになっています。
この処理により1ブックに作成したテーブル仕様書分のDLL文が自動生成されることになります。

なお、ブックに含まれる複数シートの処理については、
Excelブックに含まれる複数シートの一括処理
に説明したページがありますのでよろしければご覧ください。

シート毎の処理(sheet_proc)

' テーブルレイアウト生成(シート毎の処理)
'
'   引数はテーブル仕様書のシートオブジェクト
'
Sub sheet_proc(ws As Worksheet)

    'DLLファイル生成用
    Dim f As Integer            ' ファイル番号
    Dim fname As String         ' ファイル名
    
    'Excelシート読み込み(テーブル情報)
    Dim tname As String         ' テーブル名
    Dim tid As String           ' テーブルID
    Dim tnote As String         ' 要件
        
    'Excelシート読み込み(カラム情報)
    Dim itemno As String        ' No
    Dim itemname As String      '日本語名
    Dim itemid As String        'カラム名
    Dim itempk As String        'プライマリーキー
    Dim itemtype As String      'データ型
    Dim itemleng As String      '長さ
    Dim itemval As String       '初期値
    Dim itemnull As String      'NULL
    Dim itemnote As String      '備考
    
    Dim pktbl(100) As String    'プライマリーキー保存エリア
    Dim pkcount As Integer      'プライマリーキー保存数
    
    Dim linectr As Integer
    Dim workstr As String       'SQL編集用領域
    
    pkcount = 0           ' プライマリーキー保存数のクリア
    linectr = 6             ' カラム開始行のセット(この行から処理をする)
            
    '処理中のシートからテーブル情報を読み取る
    tname = ws.Cells(1, 2)
    tid = ws.Cells(2, 2)
    tnote = ws.Cells(3, 2)
    
    'ファイル名の設定(テーブル名付与)
    fname = "CreateTable_" & tid & ".sql"
    
    'ファイルオープン
    '(開いているシートのテーブルDDL文創成)
    f = FreeFile
    Open fname For Output As #f
    
    'Create Table文の自動生成
    Print #f, "CREATE TABLE " & tid & " ("
    
    'Noの列に値がある間繰り返す
    While ws.Cells(linectr, 1) <> ""
        
        DoEvents
        
        'カラムの情報を読み取る
        itemno = Trim(ws.Cells(linectr, 1))
        itemname = Trim(ws.Cells(linectr, 2))
        itemid = Trim(ws.Cells(linectr, 3))
        itempk = Trim(ws.Cells(linectr, 4))
        itemtype = Trim(ws.Cells(linectr, 5))
        itemleng = Trim(ws.Cells(linectr, 6))
        itemval = Trim(ws.Cells(linectr, 7))
        itemnull = Trim(ws.Cells(linectr, 8))
        itemnote = Trim(ws.Cells(linectr, 9))
        
        'Create Table文の自動生成
        workstr = ""
        'カラム名編集
        workstr = workstr & itemid & " "
            
        'プライマリーキー保存number
        If itempk = "Y" Then
            pkcount = pkcount + 1
            pktbl(pkcount) = itemid
        End If
        
        'データ型編集
        Select Case itemtype
            Case "文字"
                workstr = workstr & "varchar2("
                workstr = workstr & itemleng & ")"
            Case "数値"
                workstr = workstr & "number("
                workstr = workstr & itemleng & ")"
            Case "日付"
                workstr = workstr & "date "
        End Select
                
        '初期値編集(記入時のみ)
        If Len(itemval) > 0 Then
            workstr = workstr & " DEFAULT " & itemval
        End If
        
        'NULL編集(記入時のみ)
        If Len(itemnull) > 0 Then
            If itemnull = "N" Then
                workstr = workstr & " NOT NULL"
            End If
        End If
        
        workstr = workstr & ", "
        Print #f, Space(4); workstr
            
        linectr = linectr + 1
    
    Wend
    
    'プライマリーキー情報の編集
    Dim i As Integer
    If pkcount > 0 Then
        Print #f, " PRIMARY KEY (";
        For i = 1 To pkcount
            Print #f, pktbl(i);
            If i < pkcount Then
                Print #f, ",";
            Else
                Print #f, ")"
            End If
        Next
    End If
    
    Print #f, ")"
    Close #f

End Sub

シート毎の処理の説明

インプットパラメータ

(5行め)
この処理はテーブル仕様書のシートオブジェクトをインプットにします。
従って、含まれるテーブル仕様書のシート数分のテーブルのDDL文が自動生成されます。

生成するDLL文

DLL文はテーブルごとにテキストファイルとして作成します。
(41行め)
ファイル名は、CreateTable_[テーブルID].sqlとします。

DLL文の生成

(46~47行め)
出力モードでオープンします。

(50行め)
Create Table文の書き出し

(53行め)
シートの6行めから最終行まで行単位に繰り返します。

(57~66行め)
1行分の各項目の値をセルから取得します。
(今後の拡張性も考えて全項目を取得)

(68~103行め)
1行分のDLL文を編集します。

(104行め)
1行分のDLL文の書き出し

(73~77行め)
プライマリーキー指定の項目を保持しておきます。

(110~122行め)
保持したプライマリーキー指定の項目の書き出し

(124~125行め)
DLLの書き出しの終わりとファイルクローズ

検証

今回はOracleデータベースを使って検証します。
OracleデータベースやSQL Developerの導入についてはこちらもご覧ください。
Oracle Database 18c Express Edition(XE)のインストール
SQL Developerの導入

ツールが生成したDDL文(ファイル)

本体のExcelファイルと同じフォルダに生成されます。
他のフォルダに出力したい場合は、sheet_procの47行めのファイル名に任意のパスを付けて指定してください。


テーブル生成の実施

今回のツールで自動生成したDDL文を、SQL DeveloperのSQLワークシートで実行しました。
正常に作成されたことが表示されています。



生成したテーブルの内容

作成したテーブルの定義内容を表示しました。
テーブル仕様書通りに作成されています。



テーブルにデータを追加

作成したテーブルにデータを追加出来ることを確認出来ました。



補足

今後の展開

これでテーブル仕様書があればテーブルを自動生成出来るようになりました。
今後、このテーブルのテストデータの自動生成ツールも作成する予定で考えています。

業務支援について

弊社では、テーブル設計やツール作成など支援しています。

今回のツールのようなものは、ある程度の経験がある方であれば、簡単に作成出来ますが、実際に業務で使用するとなると様々な考慮も必要になります。豊富な知識と経験でサポートいたしますので、どうぞお気軽にご相談ください。

タイトルとURLをコピーしました