実行環境付きのSQL例文集 (ExcelのドキュメントからDBにアクセス)
あるプロジェクトの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 ;
これはシステム中で実行されるコードではない。
この結果を運用上,常にだれかが閲覧するわけでもない。
だが,もしこれを取っておけば,今度またすぐに実行+確認できるし,何より引き継ぎが楽だ。
銘柄マスタや価格ボードなどの各テーブルの実際的な使い方がわかるし,どのカラムに何が入っていなければならないか,重要な所に注意がいく。
キャッチアップのために漫然とテーブル定義書をめくるような事は減らせる。
そう思い,こういうのを例文集としていくつか保管しておいたのだった。