コメント |
@C:RED(VB.NET + Framework)
http://lightbox.cocolog-nifty.com/photos/app/plsql_param_test_01.jpg
[[引数テスト_IN]]
@DIV
PROCEDURE 引数テスト_IN
(
PM_STRING IN VARCHAR2
,PM_NUMBER IN NUMBER
)
@C:green(/**********************************************************/
/* 変数の定義 */
/**********************************************************/)
AS
@C:green(/**********************************************************/
/* 処理開始 */
/**********************************************************/)
BEGIN
DBMS_OUTPUT.PUT_LINE('デバッグ:開始');
DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_STRING);
DBMS_OUTPUT.PUT_LINE('デバッグ:引数:'||PM_NUMBER);
@C:green(/**********************************************************/
/* 一番外側のブロックの例外処理 */
/**********************************************************/)
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('例外発生:'||SQLCODE||':'||SQLERRM);
END;
@END
[[PLSQL.vb]]
@DIV
Imports System.Data.OracleClient
Imports System.Windows.Forms
Public Class PLSQL
Public myCon As New OracleConnection()
Public myCommand As New OracleCommand()
Dim Query As String = Nothing
' ******************************************************
' IN のみの引数の実行
' ******************************************************
Private Sub 引数テスト_IN_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles 引数テスト_IN.Click
myCon.ConnectionString = _
"Server=night/xe;" + _
"User ID=ora01;" + _
"Password=ora01;"
@C:green(' *******************************************
' 接続
' *******************************************)
Try
myCon.Open()
myCommand.Connection = myCon
@C:green(' コマンドをSQL用に変更)
myCommand.CommandType = CommandType.Text
Catch ex As Exception
MessageBox.Show(ex.Message)
Return
End Try
@C:green(' *******************************************
' DBMS_OUTPUT の結果を取得する為に 使用可能にする
' *******************************************)
Query = "BEGIN DBMS_OUTPUT.ENABLE(); END;"
myCommand.CommandText = Query
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception
myCon.Close()
MessageBox.Show(ex.Message)
Return
End Try
@C:green(' コマンドをプロシージャ用に変更)
myCommand.CommandType = CommandType.StoredProcedure
@C:green(' *******************************************
' プロシージャの実行
' *******************************************)
myCommand.CommandText = "引数テスト_IN"
myCommand.Parameters.Add("PM_STRING", OracleType.VarChar).Value = "0001"
myCommand.Parameters.Add("PM_NUMBER", OracleType.Number).Value = 1234
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception
myCon.Close()
MessageBox.Show(ex.Message)
Return
End Try
@C:green(' *******************************************
' DBMS_OUTPUT の結果を取得する
' *******************************************)
myCommand.Parameters.Clear()
myCommand.CommandText = "DBMS_OUTPUT.GET_LINE"
Dim PARAM As OracleParameter = _
New OracleParameter("line", OracleType.VarChar)
PARAM.Direction = ParameterDirection.Output
PARAM.Size = 200
myCommand.Parameters.Add(PARAM)
Dim STATUS As OracleParameter = _
New OracleParameter("status", OracleType.Number)
STATUS.Direction = ParameterDirection.Output
myCommand.Parameters.Add(STATUS)
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception
myCon.Close()
MessageBox.Show(ex.Message)
Return
End Try
' グリッドの列を作成
Me.LboxGrid1.Reset()
Me.LboxGrid1.AddColumn("LINE", "DBMS_OUTPUT.GET_LINEからのデバッグデータ")
Do While STATUS.Value = 0
Me.LboxGrid1.AddRow()
Me.LboxGrid1.SetColumnText("LINE", PARAM.Value.ToString())
Try
myCommand.ExecuteNonQuery()
Catch ex As Exception
Exit Do
End Try
Loop
myCommand.Parameters.Clear()
myCon.Close()
End Sub
End Class
@END
|