読者です 読者をやめる 読者になる 読者になる
スポンサーリンク

実行環境付きのSQL例文集 (ExcelのドキュメントからDBにアクセス)

DB VBA SQL ドキュメント 実行可能ドキュメント


あるプロジェクトのDB構造を知りたい場合,

  • テーブル定義書を見る。
  • ER図を見る。

これらはごく一般的なことだ。


だが,膨大なテーブル群の中で業務上重要なカラムは少ないかもしれない。

また,「ビューにするほどではないけど取っておきたいSQL」が生じてくるかもしれない。


そんな時のために,プロジェクト用に「SQL例文集」を用意しておくのはどうか。


こちらからダウンロードできます。

実行環境付きのSQL例文集
http://www.name-of-this-site.org/coding/vba/SQL_samples.xls


使い方:

まず,表紙のシートにODBC接続情報を入力する。

次いで原紙をコピーし,各シートのB5セルにSQL文を入力し,「実行」ボタンを押すと,DBに対してSQLが実行される。

実行結果は,そのシートに表示される。

  • DBの仕様を理解するために役立つSQL
  • ちょっと取っておきたいSQL
  • たまに動作確認したいSQL

などを,即動く形態で記録・保持しておく事ができる。



あるいはテストファーストなDB設計を行なうのにも役立つ。

(→ この点はDBUnitも参照:http://blogs.itmedia.co.jp/morisaki/2009/01/sql-c44f.html




ExcelからDBを操作するためのVBAのコードは下記のとおり。

Sub ボタン1_Click()

    ' SQL文の入力チェック
    
    strSQL = ActiveSheet.Cells(5, 2)
    ' 空か
    If Len(strSQL) = 0 Then
        MsgBox "SQLが入力されていません。"
        Exit Sub
    End If
    ' SELECT以外を実行しようとしているか
    If InStr(strSQL, "SELECT") <> 1 Then
       MsgBox "SQL文を「SELECT」以外で始めてはいけません。ブックを強制終了します。"
       ActiveWorkbook.Saved = True
       ActiveWorkbook.Close
    End If



    ' DBに接続
    
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.RecordSet")
    's = Timer
    infoSheet = "表紙と設定"
    strDsn = Worksheets(infoSheet).Cells(7, 2)
    strUser = Worksheets(infoSheet).Cells(8, 2)
    strPW = Worksheets(infoSheet).Cells(9, 2)
    cn.Open "DSN=" & strDsn & ";UID=" & strUser & ";PWD=" & strPW
    
    
    
    ' SQL実行
        
    rs.Open strSQL, cn
    
    
    
    ' 結果表示
    
    ofsy = 8
    ofsx = 2
    ' カラム名一覧を表示
    For i = 1 To rs.Fields.Count
        ActiveSheet.Cells(ofsy + 1, ofsx + i).Value = rs.Fields(i - 1).Name
    Next i
    ' 内容を表示
    rn = 2
    If rs.EOF Then
        ' テーブルが空
    Else
        rs.MoveFirst
        ' 各レコード
        While Not rs.EOF
            For i = 0 To rs.Fields.Count - 1
                ActiveSheet.Cells(ofsy + rn, ofsx + i + 1).Value = rs.Fields(i).Value
            Next i
            rs.MoveNext
            rn = rn + 1
        Wend
    End If
    ' レコード数 = rn - 2
    
    
    ' 終了
    
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Rows(ofsy + rn & ":" & ofsy + rn + 50).Delete Shift:=xlUp
    
    'Debug.Print cn.Version
    'Debug.Print Timer - s
    
    
End Sub

利用例・・・


金融系の現場にいた時,現在の取扱金額に求められる精度を知る必要があった。

商品は銘柄ごとに価格変動の最小幅と販売単元個数が設定されており,この積が最小になるような場合が「最小の金額精度」ということになった。

それで,下記のようなSQLを書くことになる。(実際には日本語は使わないが)

SELECT TOP 20
    pr.銘柄コード,
    pr.単元個数,
    pr.値の刻み幅,
    pr.単元個数 * pr.値の刻み幅 as MIN_STEP
FROM
    価格ボード pr INNER JOIN 銘柄マスタ br ON pr.銘柄コード = br.銘柄コード
WHERE
    br.削除フラグ = 0
ORDER BY
    MIN_STEP asc ;


これはシステム中で実行されるコードではない。

この結果を運用上,常にだれかが閲覧するわけでもない。


だが,もしこれを取っておけば,今度またすぐに実行+確認できるし,何より引き継ぎが楽だ。

銘柄マスタや価格ボードなどの各テーブルの実際的な使い方がわかるし,どのカラムに何が入っていなければならないか,重要な所に注意がいく。

キャッチアップのために漫然とテーブル定義書をめくるような事は減らせる。

そう思い,こういうのを例文集としていくつか保管しておいたのだった。


補足

データ件数が大きくなる場合は,このシートからコピペしてCSEで実行するのがいいだろう。

CSEのダウンロード
http://www.hi-ho.ne.jp/tsumiki/

画面イメージ
http://www.hi-ho.ne.jp/tsumiki/image_odbc.html