IISを使用したWebサービスからのPL/SQLデバッグ：.NETユーザー必見！ Oracle 10gのお作法（4）（2/3 ページ）

FUNCTION "SCOTT"."UPDATESALARY" (
  "ID" IN NUMBER, 
  "SALARY" IN NUMBER) RETURN NUMBER IS
 
BEGIN 
    UPDATE EMP
    SET SAL = SALARY
    WHERE EMPNO = ID;
 
    --戻り値は変更行数
    RETURN SQL%ROWCOUNT;
 
 --すべてのエラーをトラップ
  EXCEPTION  
   WHEN OTHERS THEN
        RETURN 0;
END "UPDATESALARY";

Public Function UpdateSalary(ByVal empNo As Integer, _
                             ByVal salary As Integer) As Integer
 
    Dim retVal As Integer
    conn = Me.DbConnect
    cmd = conn.CreateCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "UPDATESALARY"
 
    '戻り値パラメータを指定
    Dim param1 As DbParameter
    param1 = cmd.CreateParameter
    param1.DbType = DbType.Int32
    param1.Direction = ParameterDirection.ReturnValue
    cmd.Parameters.Add(param1)
 
    Dim param2 As DbParameter
    param2 = cmd.CreateParameter
    param2.Value = empNo
    cmd.Parameters.Add(param2)
 
    Dim param3 As DbParameter
    param3 = cmd.CreateParameter
    param3.Value = salary
    cmd.Parameters.Add(param3)
 
    'ストアドファンクション実行
    cmd.ExecuteNonQuery()
    retVal = param1.Value
 
    Return retVal
 
End Function

Public Function GetEmpData() As DataSet
    conn = Me.DbConnect
    cmd = conn.CreateCommand
    cmd.Connection = conn
    'EMP表から全件取得
    cmd.CommandText = "SELECT * FROM EMP"
    ds = New DataSet
    da = factory.CreateDataAdapter()
    da.SelectCommand = cmd
    da.Fill(ds)
    Return ds
End Function

<WebMethod()> _
Public Function UpdateSalary(ByVal empNo As Integer, _
                             ByVal salary As Integer) As Integer
    Dim da As New DataAccess
    Return da.UpdateSalary(empNo, salary)
End Function

<WebMethod()> _
Public Function GetEmpData() As System.Data.DataSet
    Dim da As New DataAccess
    Return da.GetEmpData
End Function