プログラミングCreateSQLメソッド
前回（oo4oでExcelから登録してみた（ExecuteSQL））はExecuteSQLメソッドを使用してInsert文を発行しました。
今回は、CreateSQLメソッドを使用して同じことをやってみます。
サンプルソース
手法が違うだけで、やってることは前回とまったく同じです。
' データ登録サンプル（CreateSQL）
'
Sub insertTestCreateSQL()
'--------------
' 変数宣言
'--------------
'SQL文
Dim mySql As String
'登録データ
Dim myTbl(2, 1) As String '登録用データ配列
Dim myInd As Integer 'カウンタ
'oo4o用のオブジェクト
Dim OO4OSession As Object
Dim EmpDb As Object
Dim sqlStatement As Object
'-----------------
' 初期処理
'-----------------
'INSERT文作成
mySql = ""
mySql = mySql & " INSERT INTO"
mySql = mySql & " TESTDB (TESTSTR,TESTNUM)"
mySql = mySql & " VALUES (:ADDSTR, :ADDNUM)"
'登録用のデータ作成
myTbl(0, 0) = "A": myTbl(0, 1) = 1
myTbl(1, 0) = "B": myTbl(1, 1) = 2
myTbl(2, 0) = "C": myTbl(2, 1) = 3
'-----------------
' DB接続
'-----------------
'更新用のＤＢ接続
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set EmpDb = OO4OSession.OpenDatabase(ORA_DBSTRING, ORA_USERNAME & "/" & ORA_PASSWD, 0&)
'バインド変数の設定
EmpDb.Parameters.Add "ADDSTR", CStr(myTbl(0, 0)), 1
EmpDb.Parameters("ADDSTR").ServerType = 1 'ORATYPE_VARCHAR2
EmpDb.Parameters.Add "ADDNUM", CInt(myTbl(0, 1)), 1
EmpDb.Parameters("ADDNUM").ServerType = 2 'ORATYPE_NUMBER
'-----------------
' データ登録
'-----------------
'Insert文の実行（1件目）
Set sqlStatement = EmpDb.CreateSQL(mySql, &H2&)
'Insert文の実行（2件目以降）
For myInd = 1 To UBound(myTbl)
'バインド変数の再設定
EmpDb.Parameters("ADDSTR").Value = CStr(myTbl(myInd, 0))
EmpDb.Parameters("ADDNUM").Value = CInt(myTbl(myInd, 1))
'Insert文を実行
sqlStatement.Refresh
Next
'-----------------
' 終了処理
'-----------------
'終了メッセージの表示
MsgBox "正常終了！" & Chr(10) & Chr(10) & "登録件数：" & sqlStatement.RecordCount
'オブジェクトの開放
Set sqlStatement = Nothing
Set EmpDb = Nothing
Set OO4OSession = Nothing
End Sub
前回までとの変更箇所について
1. CreateSQLメソッド
前回までは、For文の中でExecuteSQLメソッドを連続実行させていましたが、CreateSQLメソッドはFor文の外で1回のみ実行しています。
CreateSQLメソッドは、戻値としてOraSqlStmtオブジェクトを作成します。
このOraSQLStmtオブジェクトは、マニュアルでは「1つのSQL文を表します。」と書いています。
正直よく理解できないのですが、私は「CreateSQLメソッドで実行したSQLに関連する情報を保持するオブジェクト」という風に理解しています。
例えば、DML文の実行件数を取得したい場合、ExecuteSQLメソッドであれば戻値を取得しますが、CreateSQLメソッドの場合は、OraSQLStmtオブジェクトのRecordCountプロパティから取得します。
第二引数に「 &H2& 」 という値を設定すると、SQL実行時にエラーが発生した場合にVBA側でエラーが発生します。
つまり、SQL実行時エラーを「on error goto ... 」のエラートラップで拾えるようになります。
逆に言えば、設定する値を変更すればエラー発生時も処理を続行させれるんですけど、エラー処理ロジックが面倒なので私は使ったことがありません。
2. Refreshメソッド
For文の中では、バインド変数を再設定して、CreateSQLメソッドで作成されたOraSqlStmtオブジェクトのRefreshメソッドを実行します。
RefreshメソッドはOraSqlStmtオブジェクトに設定されているSQL文を再実行します。
この時に、バインド変数の値を変更できるので、同じSQL文でパラメータだけを変更する処理が出来ます。
3. 実行件数の取得（RecordCountプロパティ）
ExecuteSQLメソッドを使用した場合は、ExecuteSQLメソッドの戻値を加算していましたが、CreateSQLメソッドを使用する場合は、OraSqlStmtオブジェクトのRecordCountプロパティから取得できます。
考察
CreateSQLメソッドは、“パラメータのみを変えた同じSQL文を連続実行する”場合の性能面でExecuteSQLメソッドより有利なようです。
OracleはSQLを実行する前に、SQLの文法チェックや使用するオブジェクト（テーブルとか）に対するアクセス権とかの確認などの様々な処理を行っています。
CreateSQLメソッドを使用する場合、このような処理は最初の１回のみで、Refreshメソッドの実行時には、これらの処理が大幅に省略されます。
このあたりは、マニュアルでは「必要なデータベース操作（SQL解析、バインディングなど）を最低限で済ませる」と記述されています。
実行速度だけで言えば、最近のハード性能を考えると数百件～数千件程度では、対して違いがでないかも知れません。
まぁDBに無用の負荷を掛けないための、一種の作法と考えた方が良いかも知れませんね。
前回：oo4oでExcelから登録してみた（ExecuteSQL）
次回：oo4oでトランザクションを使ってみた
投稿記事の一覧：目次
--- blog end ---
スポンサードリンク
0
0
