社内SEの徒然なる日記

oo4oでExcelから登録してみた(CreateSQL)

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接続
'-----------------

'更新用の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メソッドを使用する場合、このような処理は最初の1回のみで、Refreshメソッドの実行時には、これらの処理が大幅に省略されます。
このあたりは、マニュアルでは「必要なデータベース操作(SQL解析、バインディングなど)を最低限で済ませる」と記述されています。

実行速度だけで言えば、最近のハード性能を考えると数百件~数千件程度では、対して違いがでないかも知れません。
まぁDBに無用の負荷を掛けないための、一種の作法と考えた方が良いかも知れませんね。


前回:oo4oでExcelから登録してみた(ExecuteSQL)
次回:oo4oでトランザクションを使ってみた
投稿記事の一覧:目次

--- blog end ---



スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する