HIRO's.NET Blog

VB.NETからOracleストアドプロシージャを呼び出す　その4

前回（その３）は引数ありのストアドプロシージャ のOUTパラメータを受け取る方法を紹介しました。

今回は、REF CURSORをOUT引数として持つストアドプロシージャをVB.NETでDataReaderに受け取る方法を紹介します。

開発言語：VB.NET 2005

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

ストアドプロシージャは下記の通りです。

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

CREATE OR REPLACE PACKAGE SCOTT.PKG_TEST1
IS
TYPE ref_cursor IS REF CURSOR;
Procedure PROCEDURE4(in_EMPNO IN VARCHAR2, in_ENAME IN VARCHAR2, in_DEPTNO IN NUMBER, out_cursor OUT ref_cursor);
END;
/

CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_TEST1 AS
Procedure PROCEDURE4 (
    in_EMPNO    in varchar2,
    in_ENAME    in varchar2,
    in_DEPTNO   in number,
    out_CURSOR  OUT ref_cursor
)
IS
    varSQL varchar2(2000);
BEGIN
    INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (in_EMPNO, in_ENAME, in_DEPTNO);

    -- open cursor
    varSQL := 'SELECT * FROM EMP WHERE DEPTNO=' || in_DEPTNO;
    OPEN out_CURSOR FOR varSQL;
END PROCEDURE4;
END PKG_TEST1;
/

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

　ストアドプロシージャのINパラメータをVB.NETから指定する方法はその２で説明した通りです。

　今回は、ストアドプロシージャのOUTパラメータをVB.NETのDataReaderに受け取る方法について説明します。

　まず、OracleParameterのインスタンスを作成します。(OleDBの場合は作成しません。）

　OracleParameterのインスタンス作成時の第１引数には、ストアドプロシージャのREF CUROSOR(OUT引数)名（この場合は"out_cursor"）、第２引数にはストアドプロシージャOUT引数の型に合わせたものを指定します。（今回の場合、OracleのREF CURSOR型に対して、ODP.NETの場合はOracleDbType.RefCursor、OracleClientの場合はOracleType.Cursor）

　次に、PrameterDirectionにOutputを指定します。（これはストアドプロシージャout_cursorがOUT引数であるため）

　あとはDataReaderを作成し、Readメソッドを使用することで、１レコードずつ処理をすることができます。

Private Sub ODP_TEST4()
    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 = "PKG_TEST1.PROCEDURE4"
                'ストアドプロシージャの引数に渡す値の設定
                Cmd.Parameters.Add("in_EMPNO", OracleDbType.Varchar2).Value = "7777"
                Cmd.Parameters.Add("in_ENAME", OracleDbType.Varchar2).Value = "HIRO"
                Cmd.Parameters.Add("in_DEPTNO", OracleDbType.Decimal).Value = 20
                'ストアドプロシージャのout cursor用パラメータ作成
                Dim outPara As New OracleParameter("out_CURSOR", OracleDbType.RefCursor)
                outPara.Direction = ParameterDirection.Output
                Cmd.Parameters.Add(outPara)

                Dim OraRead As OracleDataReader
                OraRead = Cmd.ExecuteReader

                ListBox1.Items.Clear()
                'データの取り出し
                While OraRead.Read
                    ListBox1.Items.Add(OraRead.GetString(OraRead.GetOrdinal("ENAME")))
                End While

                MessageBox.Show("PROCEDURE4 正常終了", "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_TEST4()
    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 = "PKG_TEST1.PROCEDURE4"
                'ストアドプロシージャの引数に渡す値の設定
                Cmd.Parameters.Add("in_EMPNO", OracleType.VarChar).Value = "7777"
                Cmd.Parameters.Add("in_ENAME", OracleType.VarChar).Value = "HIRO"
                Cmd.Parameters.Add("in_DEPTNO", OracleType.Number).Value = 20
                'ストアドプロシージャのout cursor用パラメータ作成
                Dim outPara As New OracleParameter("out_CURSOR", OracleType.Cursor)
                outPara.Direction = ParameterDirection.Output
                Cmd.Parameters.Add(outPara)
                Dim OraRead As OracleDataReader
                OraRead = Cmd.ExecuteReader

                ListBox1.Items.Clear()
                'データの取り出し
                While OraRead.Read
                    ListBox1.Items.Add(OraRead.GetString(OraRead.GetOrdinal("ENAME")))
                End While

                MessageBox.Show("PROCEDURE4 正常終了", "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_TEST4()
    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 = "PKG_TEST1.PROCEDURE4"
                'ストアドプロシージャの引数に渡す値の設定
                Cmd.Parameters.Add("in_EMPNO", OleDbType.VarChar).Value = "7777"
                Cmd.Parameters.Add("in_ENAME", OleDbType.VarChar).Value = "HIRO"
                Cmd.Parameters.Add("in_DEPTNO", OleDbType.Decimal).Value = 20

                Dim OraRead As OleDbDataReader
                OraRead = Cmd.ExecuteReader

                ListBox1.Items.Clear()
                'データの取り出し
                While OraRead.Read
                    ListBox1.Items.Add(OraRead.GetString(OraRead.GetOrdinal("ENAME")))
                End While

                MessageBox.Show("PROCEDURE4 正常終了", "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月20日 20:37