HIRO's.NET Blog

VB.NETからOracleストアドファンクションを呼び出す

前回までは、OracleのストアドプロシージャをVB.NETから使用する方法を紹介しました。

今回は、OracleのストアドファンクションをVB.NETから呼び出す方法を紹介します。

開発言語：VB.NET 2005

使用するテーブルは、Oracleを使用している人であればおなじみの、scott/tigerのEMP表です。

ストアドファンクションは下記の通りです。呼び出すと777と言う数値を返すだけのものです。

（例によって深い意味はありません。説明用です。）

CREATE OR REPLACE FUNCTION SCOTT.FUNCTION1
RETURN NUMBER -- 戻り値のデータ型
IS
BEGIN
    return (777);
END;
/
/

　以下、ODP.NET, MicorosoftのOracleClient, OleDB の３種類を使用してFUNCTION1を呼び出す例です。

　今回は、ストアドファンクションの戻り値をVB.NETで受け取る方法について説明します。

　まず、OracleParameterのインスタンスを作成します。

　OracleParameterのインスタンス作成時の第１引数には、適当に名前を付けた値を指定します。（今回は戻り値を受け取るという意味合いをこめて"retVal"としています）。第２引数にはストアドファンクションの戻り値であるNumber型に対応する型を指定します。（ODP.NETの場合はOracleDbType.Decimal、OracleClientの場合はOracleType.Number、OleDbの場合はOleDbType.Decimal）

　次に、ExecuteNonQueryを実行し、作成しておいたOracleParameterのインスタンス（retVal）のValueプロパティを参照することで、ストアドファンクションの戻り値を取得することができます。

Private Sub ODP_FUNCTION1()
    Try
        Using OraConn As New OracleConnection("user id=scott;password=tiger;data source=YourServer")
            Using Cmd As New OracleCommand
                'データベース接続を開く
                OraConn.Open()
                Cmd.Connection = OraConn
                'コマンドタイプをストアドプロシージャにする
                Cmd.CommandType = CommandType.StoredProcedure
                '実行するストアドプロシージャを指定
                Cmd.CommandText = "FUNCTION1"
                'ストアドファンクションの戻り値パラメータ作成
                Dim retVal As New OracleParameter("retVal", OracleDbType.Decimal)
                retVal.Direction = ParameterDirection.ReturnValue
                Cmd.Parameters.Add(retVal)

                'ストアドファンクションの実行
                Cmd.ExecuteNonQuery()

                MessageBox.Show("FUNCTION1 正常終了 戻り値=" & retVal.Value.ToString(), _
                    "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Using
        End Using

    Catch OraEx As OracleException
        'Oracle例外発生時
        MessageBox.Show(OraEx.Message, "ORACLE ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)

    Catch ex As Exception
        '一般例外発生時
        MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)
    End Try
End Sub

Private Sub OracleClient_FUNCTION1()
    Try
        Using OraConn As New OracleConnection("user id=scott;password=tiger;data source=YourServer")
            Using Cmd As New OracleCommand
                'データベース接続を開く
                OraConn.Open()
                Cmd.Connection = OraConn
                'コマンドタイプをストアドプロシージャにする
                Cmd.CommandType = CommandType.StoredProcedure
                '実行するストアドプロシージャを指定
                Cmd.CommandText = "FUNCTION1"
                'ストアドファンクションの戻り値パラメータ作成
                Dim retVal As New OracleParameter("retVal", OracleType.Number)
                retVal.Direction = ParameterDirection.ReturnValue
                Cmd.Parameters.Add(retVal)

                'ストアドファンクションの実行
                Cmd.ExecuteNonQuery()

                MessageBox.Show("FUNCTION1 正常終了 戻り値=" & retVal.Value.ToString(), _
                    "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Using
        End Using

    Catch OraEx As OracleException
        'Oracle例外発生時
        MessageBox.Show(OraEx.Message, "ORACLE ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)

    Catch ex As Exception
        '一般例外発生時
        MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)
    End Try
End Sub

Private Sub OleDB_FUNCTION1()
    Try
        Using OraConn As New OleDbConnection("Provider=MSDAORA.1.Oracle;user id=scott;" & _
            "password=tiger;data source=YourServer;PLSQLRset=True")
            Using Cmd As New OleDbCommand
                'データベース接続を開く
                OraConn.Open()
                Cmd.Connection = OraConn
                'コマンドタイプをストアドプロシージャにする
                Cmd.CommandType = CommandType.StoredProcedure
                '実行するストアドプロシージャを指定
                Cmd.CommandText = "FUNCTION1"
                'ストアドファンクションの戻り値パラメータ作成
                Dim retVal As New OleDbParameter("retVal", OleDbType.Decimal)
                retVal.Direction = ParameterDirection.ReturnValue
                Cmd.Parameters.Add(retVal)

                'ストアドファンクションの実行
                Cmd.ExecuteNonQuery()

                MessageBox.Show("FUNCTION1 正常終了 戻り値=" & retVal.Value.ToString(), _
                    "SUCCESS", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End Using
        End Using

    Catch OleEx As OleDbException
        'Oracle例外発生時
        MessageBox.Show(OleEx.Message, "OleDB ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)

    Catch ex As Exception
        '一般例外発生時
        MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Stop)
    End Try
End Sub

投稿日時 : 2007年4月22日 17:39