社内SEの徒然なる日記

oo4oでExcelから接続してみた(抽出)

データ抽出

前回(oo4oでExcelから接続してみた(接続・切断))は、oo4oを使用してExcelからOracleへの接続まで出来るようになりました。

今回は、データを抽出してみることにします。
要するに、SELECT文の実行ですね。

共通部品としての機能

とりあえず、SELECT文を実行して、結果を取得するってだけの機能を持った関数を作ろうと思います。
この関数を実行する側では接続先を意識しないようにしたいので、取得結果をVariant型の二次元配列として返却するようにします。

前回の共通変数や関数も使用してるので、このプロシージャだけだとコンパイルエラーになるんで注意して下さい。


' 機能 : SQL(SELECT文のみ)を実行する
'
' 引数 : pSql - 実行するSQL
' : pTitle - タイトル追加フラグ(true:有り、false:無し)
' :  ※ 省略可で省略時はfalseになる。
'
' 戻値 : 実行結果を格納したVariant型配列
'
Public Function oracleSqlSelect(ByRef pSql As String, Optional ByRef pTitle As Boolean)
On Error GoTo errEnd:

'-------------
' 変数宣言
'-------------

Dim myDynaset As Object 'Dynasetオブジェクト
Dim myData As Object 'Fieldsオブジェクト
Dim myOraDynCount As Object 'カウント用
Dim mySnapshotID As Object 'スナップショット

'結果配列
Dim myWriteInd As Long 'インデックス
ReDim myWkTbl(0, 0) '結果配列

'ループカウンタ
Dim i As Long

'---------------
' エラーチェック
'---------------

'Oracle接続確認
If Not oracleOpenChk Then
MsgBox "Oracle接続エラー"
GoTo errEnd
End If

'-------------
' SQL実行
'-------------

'OraDynasetオブジェクトの作成
Set myDynaset = OraDatabase.CreateDynaset(pSql, ORADYN_READONLY)

'スナップショットIDを取得
Set mySnapshotID = myDynaset.Snapshot

'検索時のスナップショットIDから、カウントを実行
Set myOraDynCount = OraDatabase.CreateDynaset("select count(*) NUMROWS FROM (" & pSql & ")", 0&, mySnapshotID)

'レコード件数を共通変数に格納します。
OraRecodeCount = myOraDynCount.Fields("NUMROWS").Value

'対象件数が0件の場合、処理を終了します。
If OraRecodeCount = 0 Then
GoTo errEnd
End If

'Fieldsオブジェクトを取得します。
Set myData = myDynaset.Fields

'--------------
' タイトル出力
'--------------

'結果配列の出力インデックスを初期化
myWriteInd = 0

'タイトル出力モードの場合、結果配列の先頭行にタイトルを追加します。
If pTitle = True Then

'結果配列を初期化
ReDim myWkTbl(OraRecodeCount, myData.Count - 1)

'タイトルを設定
For i = 0 To myData.Count - 1
myWkTbl(myWriteInd, i) = myData(i).Name
Next

'結果配列インデックスを増加
myWriteInd = 1

'タイトル無しの場合、配列の初期化のみ行います。
Else
'結果配列を初期化
ReDim myWkTbl(OraRecodeCount - 1, myData.Count - 1)
End If

'--------------
' データ出力
'--------------

'結果セットの配列転記
Do Until myDynaset.EOF

'結果配列に結果セットを転記します。
For i = 0 To myData.Count - 1
myWkTbl(myWriteInd, i) = myDynaset.Fields(i).Value
Next

'カーソル位置を前に進めます。
myDynaset.MoveNext

'結果配列の出力行を前に進めます。
myWriteInd = myWriteInd + 1
Loop

'オブジェクトの開放
Set myDynaset = Nothing
Set myData = Nothing
Set myOraDynCount = Nothing
Set mySnapshotID = Nothing

'--------------
' 終了処理
'--------------
normalEnd:
oracleSqlSelect = myWkTbl
Exit Function
errEnd:
'エラーメッセージの表示、戻値の初期化、オブジェト解放、エラークリア
If Not Err.Number = 0 Then MsgBox (Err.Number & ", " & Err.Description)
OraRecodeCount = 0
ReDim myWkTbl(0, 0)
Set myDynaset = Nothing
Set myOraDynCount = Nothing
Set mySnapshotID = Nothing
oracleSqlSelect = myWkTbl
Err.Clear
End Function


機能説明

1.OraDynasetオブジェクトの作成

事前に、Oracleに接続していることが前提の関数です。
OraDatabase.CreateDynasetメソッドでOraDynasetオブジェクトを作成します。
第二引数のOptionsには、ORADYN_READONLY(値:&H4&)を設定します。
これによって、ダイナセットを読取り専用にしています。

2.カウント

同じSQLにカウントをかぶせて再実行して対象件数を取得します。
最初は、RecordCountプロパティってので件数を取得するつもりだったのですが、これを使うと全ての行がフェッチされるというので、OraDynasetオブジェクトのSnapshotIDを使用してカウントを実行するようにしています。

取得結果が少ない件数になることが分っている時は、RecordCountプロパティの方が良い気もしますけどね。
まぁ、このあたりはどっかで検証してみますかね。

3.戻値の作成(タイトル)

関数の第二引数にtrueが設定されていた場合、取得結果の項目名を戻値の二次元配列の先頭に追加出力します。

4.戻値の作成(データ)

今度は、取得したデータを戻値の二次元配列の出力します。

5.後処理

使用したオブジェクトを解放して、戻値の配列を返却します。

使ってみた

とりあえず、テスト用に簡単な処理を作って動かして見ました。

Sub selectTest()

'変数宣言
Dim mySql As String 'SQL文
Dim myRs '戻値
Dim i As Integer 'ループカウンタ

'DBに接続
Call db接続

'SELECT文作成
mySql = "SELECT AMLWLMAM, AMUPLMAM FROM JSTM "
mySql = mySql & "WHERE AMLWLMAM > 300000000"

'SQL実行
myRs = oracleSqlSelect(mySql, True)

'結果件数の確認
If OraRecodeCount = 0 Then
MsgBox "該当するデータが存在しません。"
Exit Sub
End If

'実行結果をデバッグログに出力する
For i = LBound(myRs) To UBound(myRs)
Debug.Print myRs(i, 0) & " / "; myRs(i, 1)
Next

End Sub


イミディエイトウィンドウの出力結果は、こんな感じになりました。
WS001048.jpg

今度は、第二引数をFalseにして実行してみます。
  myRs = oracleSqlSelect(mySql, False)
今度は、タイトルが表示されなくなりました。
WS001049.jpg

前回:oo4oでExcelから接続してみた(接続・切断)
次回:oo4oによる抽出件数の考察
投稿記事の一覧:目次

--- blog end ---


スポンサードリンク

PageTop

コメント


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