VBAでRefCursorを用いたプロシージャを実行する(ADO版)

今時Excel2000のVBAを使ってOracleDBのデータ操作を行ったりするが、集計用に作ったSelect文が大きすぎてマクロ内に埋め込むのが面倒臭くなったので、Select文だけストアドで保持できないかと調べてみた。

どうやらRefCursorを使ったプロシージャで可能のよう。

ちなみに現在の環境
> クライアント側：
・OracleVirtualBoxのバージョン4.1.22のWindowsXPモード
・MicrosoftOffice2000Premium - Excel2000
・Oracle11gClient
> サーバー側：
・Windows7ultimate
・Oracle11gXE


■１．ＤＢ側の準備

とりあえずadminというスキーマにsampleというテーブルを作成し、sampleテーブルをSelectするプロシージャを作成。

(1) sampleテーブルの作成

SQL>create table sample (user_id number primary key, user_name varchar2(40), message varchar2(200));


(2) sampleテーブルにサンプルデータ投入

SQL>insert into sample values (1, 'hoge-a', 'good morning.');
SQL>insert into sample values (2, 'hoge-b', 'good evening.');
SQL>commit;

(3) プロシージャの作成

sample_toolというパッケージを作成し、その中にstored_sampleというプロシージャを作成。

・パッケージ仕様部
SQL>create or replace package sample_tool
 as
  type type_cur is ref cursor;
  procedure stored_sample(set_user_id in number, io_cur in out type_cur);
 end;
 /


・パッケージ本体部
SQL>create or replace package body sample_tool
 as
  procedure stored_sample(set_user_id in number, io_cur in out type_cur)
  is
   cur type_cur;
  begin
   open cur for 
    -- データ操作したいSelect文
    select * from sample where user_id = set_user_id; 
   io_cur := cur;
  end;
 end;
 /


オレンジの部分が本来の目的の「ストアドで保持したいSelect文」となる。
サンプルでは指定のuser_idのデータをSelectするような形にしてみる。カーソルはオープンしておき、そのカーソルを出力用カーソル引数に渡す。


■２．ＶＢＡ側の準備

適当に新規ブック(Book1.xls)を開き、ワークシートにコマンドボタンを追加し、ボタン押下イベント内にデータ操作を記述。
ちなみにDB操作のための「Microsoft ActiveX Data Objects 2.8 Library」は参照設定済みとする。

Private Sub CommandButton1_Click()

    Dim oraconn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim param As ADODB.Parameter
    Dim val As Variant
    Dim int_count As Integer
    
    Set oraconn = New ADODB.Connection
    oraconn.Errors.Clear
    oraconn.ConnectionString = "Provider=MSDAORA;Data Source=*****;User ID=admin;Password=*****;" '①ProviderをMSDAORAにする。*****は任意 
    oraconn.Open
    
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    
    With cmd
        Set .ActiveConnection = oraconn
        .CommandType = adCmdStoredProc '②adCmdStoredProcにする 
        .CommandText = "sample_tool.stored_sample" '③ストアドを指定 
        Set param = New ADODB.Parameter
        Set param = .CreateParameter("set_user_id", adInteger, adParamInput, , 1) '④set_user_idに1をセットし、1のデータSelectを行う 
        .Parameters.Append param
    End With
    
    Set rs = cmd.Execute '⑤cmdを実行し、rsにレコード情報を渡してやる 
     
    ReDim val(1, rs.Fields.Count - 1)
    
    'カラム取得
    For int_count = 0 To rs.Fields.Count - 1
        val(0, int_count) = rs.Fields(int_count).Name
    Next int_count
    
    'データ取得
    Do Until rs.EOF
        For int_count = 0 To rs.Fields.Count - 1
            val(1, int_count) = rs.Fields(int_count).Value
        Next int_count
        rs.MoveNext
    Loop
    
    '表示
    With ActiveSheet
        .Cells.Clear
        With .Range(.Cells(5, 1), .Cells(5 + UBound(val), 1 + UBound(val, 2)))
            .Borders.Weight = xlThin
            .Value = val
        End With
        .Columns.AutoFit
    End With
    
    rs.Close
    Set param = Nothing
    Set cmd = Nothing
    Set rs = Nothing
    
    oraconn.Close
    Set oraconn = Nothing
   
End Sub


これで完了。CommandButton1を押下すると、
 
user_id=1のデータが読込まれ表示される。

注意点としては…

(1) ①でMSDAORAにすること。通常OraOLEDB.Oracleにした方が圧倒的に読み込みが早いが、RefCursorを用いたストアドには対応してない(という罠にはまった)。
(2) ④でプロシージャがnumber型の引数にはadIntegerやadDoubleで渡す。(adNumericだと型が違うと指摘されるが、複数引数があった場合に最後の引数の型さえ合ってれば使えたりする(?))

だろうか？データ操作したいSelect文は、通常のカーソル文でエラーにならない文であればwith句が伴ってても特に問題なかった。

ちなみにRefCursorを用いたRecordsetは新規挿入・更新はできなかった。