前回(その3)は引数ありのストアドプロシージャ
のOUTパラメータを受け取る方法を紹介しました。
今回は、REF CURSORをOUT引数として持つストアドプロシージャをVB.NETでDataReaderに受け取る方法を紹介します。
開発言語:VB.NET 2005
使用するテーブルは、Oracleを使用している人であればおなじみの、scott/tigerのEMP表です。
ストアドプロシージャは下記の通りです。
(例によって深い意味はありません。説明用です。)
ストアドプロシージャ4
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);
varSQL := 'SELECT * FROM EMP WHERE DEPTNO=' || in_DEPTNO;
OPEN out_CURSOR FOR varSQL;
END PROCEDURE4;
END PKG_TEST1;
/
以下、ODP.NET, MicorosoftのOracleClient, OleDB の3種類を使用してPROCEDURE4を呼び出す例です。
ストアドプロシージャのINパラメータをVB.NETから指定する方法はその2で説明した通りです。
今回は、ストアドプロシージャのOUTパラメータをVB.NETのDataReaderに受け取る方法について説明します。
まず、OracleParameterのインスタンスを作成します。(OleDBの場合は作成しません。)
OracleParameterのインスタンス作成時の第1引数には、ストアドプロシージャのREF CUROSOR(OUT引数)名(この場合は"out_cursor")、第2引数にはストアドプロシージャOUT引数の型に合わせたものを指定します。(今回の場合、OracleのREF
CURSOR型に対して、ODP.NETの場合はOracleDbType.RefCursor、OracleClientの場合はOracleType.Cursor)
次に、PrameterDirectionにOutputを指定します。(これはストアドプロシージャout_cursorがOUT引数であるため)
あとはDataReaderを作成し、Readメソッドを使用することで、1レコードずつ処理をすることができます。
■ODP.NETサンプル
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
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
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
■OracleClientサンプル
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
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
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
■OleDBサンプル
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
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