oo4o - ストアドプログラムの利用

Oracleではストアドプログラムというメカニズムが存在する。プロシージャ・ファンクションをあらかじめOracleサーバに格納・コンパイルしておくことでデータ送信、プログラム解析などのオーバーヘッドが軽減する。

以下のストアドプロシージャをOracleサーバに定義する。

create or replace procedure oo4otest is
begin
    insert into sample_table values('test', 1000);
end;

OO4Oでストアドプロシージャの呼び出して実行するにはOraDatabaseオブジェクトのExecuteSQLメソッドを利用する。

Private Sub CommandButton1_Click()
    Dim OraSession As Object
    Dim OraDatabase As Object

    On Error Resume Next
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = _
        OraSession.OpenDatabase("sampleDB", "scott/tiger", 0)
    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbCritical, "接続エラー"
        Exit Sub
    End If

    OraDatabase.ExecuteSQL ("begin oo4otest; end;")
    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbExclamation, "エラー"
        Exit Sub
    Else
        MsgBox "正常終了", vbOKOnly + vbInformation, "正常終了"
    End If
    Set OraDynaset = Nothing
    Set OraSession = Nothing
    Set OraDatabase = Nothing
End Sub

パラメータを持っているストアドプロシージャも実行することができる。

create or replace procedure oo4otest is(
    par_in in char,
    par_out out char
) is
begin
  insert into debug values(par_in);
  par_out := 'OK'
  commit;

exception
    when others then
        par_out := 'NG';
end;

このストアドプロシージャを呼び出し、パラメータを設定・参照するプログラムは以下のようになる。パラメータはバインド変数として設定する。

Public Const ORAPARM_INPUT = 1
Public Const ORAPARM_OUTPUT = 2
Public Const ORAPARM_BOTH = 3
Public Const ORATYPE_VARCHAR2 = 1
Public Const ORATYPE_NUMBER = 2
Public Const ORATYPE_SINT = 3
Public Const ORATYPE_FLOAT = 4
Public Const ORATYPE_STRING = 5
Public Const ORATYPE_VARCHAR = 9
Public Const ORATYPE_DATE = 12
Public Const ORATYPE_UINT = 68
Public Const ORATYPE_CHAR = 96
Public Const ORATYPE_CHARZ = 97
Public Const ORATYPE_CURSOR = 102

Private Sub CommandButton1_Click()

    Dim OraSession As Object
    Dim OraDatabase As Object

    On Error Resume Next
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = _
        OraSession.OpenDatabase("sampleDB", "scott/tiger", 0)
    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbCritical, "接続エラー"
        Exit Sub
    End If

    ' IN パラメータの設定
    OraDatabase.Parameters.Add("par_in", "200404", ORAPARM_INPUT)
    OraDatabase.Parameters("par_in").ServerType = ORATYPE_CHAR

    ' OUTパラメータの設定
    OraDatabase.Parameters.Add("par_out", "", ORAPARM_OUTPUT)
    OraDatabase.Parameters("par_out").ServerType = ORATYPE_CHAR

    ' 実行
    OraDatabase.ExecuteSQL("begin oo4otest(:par_in, :par_out); end;")

    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbExclamation, "エラー"
        Exit Sub
    Else
        MsgBox(OraDatabase.Parameters("par_out").value, MsgBoxStyle.OKOnly, "結果")
    End If

    Set OraDynaset = Nothing
    Set OraSession = Nothing
    Set OraDatabase = Nothing
End Sub

以下のストアドファンクションをOracleサーバに定義する。

create or replace function oo4otest return number is
  result number;
begin
  result := 10000;
  return(result);
end oo4otest;

OO4Oでストアドファンクションを呼び出すにはOraDynasetにselect ストアドプロシージャ名 from dualという少し変わったSQL文を設定し、実行する。

Private Sub CommandButton1_Click()

    Dim OraSession As Object
    Dim OraDatabase As Object
    Dim OraDynaset As Object

    On Error Resume Next
    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("sampleDB", "scott/tigert", 0)
    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbCritical, "接続エラー"
        Exit Sub
    End If

    Set OraDynaset = _
        OraDatabase.CreateDynaset("SELECT oo4otest FROM dual", &H0&)

    Dim rownumber As Integer
    rownumber = OraDynaset.Fields(0).Value

    If Err.Description <> "" Then
        MsgBox Err.Description, vbOKOnly + vbExclamation, "エラー"
        Exit Sub
    Else
        MsgBox "戻り値：" & rownumber
    End If

    Set OraDynaset = Nothing
    Set OraSession = Nothing
    Set OraDatabase = Nothing
End Sub

これでストアドプロシージャoo4otestを実行することができ、戻り値も受け取ることができる。

ストアドパッケージを利用する方法はストアドプロシージャ、ストアドファンクションと同じです。その際

パッケージ名.ストアドプログラム名

と表記して呼び出します。