（元）社内SEの徒然なる日記

oo4oでExcelから接続してみた（抽出）

2012-10-23（Tue）

データ抽出

前回（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)

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

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

    'レコード件数を共通変数に格納します。
    OraRecodeCount = myOraDynCount.Fields("NUMROWS").Value
    
    '対象件数が０件の場合、処理を終了します。
    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