DB2 10.5 for Linux, UNIX, and Windows

Visual Basic .NET CLR プロシージャーの例

プロシージャー (ストアード・プロシージャーともいう) の基礎と .NET 共通言語ランタイム・ルーチンの基本を理解できたら、 アプリケーションで CLR プロシージャーをさっそく活用できます。 このトピックでは、 Visual Basic でインプリメントした CLR プロシージャーの例をいくつか紹介します。 それぞれの例は、 サポートされているパラメーター・スタイル、 パラメーター (dbinfo 構造を含む) の受け渡し、 結果セットの戻し方などを示しています。

始める前に

CLR プロシージャーの例を使用した作業を開始する前に、 概念について説明している以下のトピックを参照することもできます。

このタスクについて

Visual Basic の CLR UDF の例については、以下を参照してください。

次の例では、SAMPLE データベースに含まれる EMPLOYEE という名前の表を使用しています。

手順

独自の Visual Basic CLR プロシージャーを作成するときには、以下の例を参考にしてください。

例

Visual Basic 外部コード・ファイル

以下の例では、Visual Basic プロシージャーのさまざまなインプリメンテーションを示しています。 それぞれの例は、CREATE PROCEDURE ステートメントと、 関連アセンブリーのビルド元プロシージャーの外部 Visual Basic コード・インプリメンテーションという 2 つの部分から成っています。

以下の例のプロシージャー・インプリメンテーションに含まれる Visual Basic ソース・ファイルは、 gwenVbProc.vb という名前であり、 以下の形式になっています。
  using System;
  using System.IO;
  using IBM.Data.DB2;
 
  Namespace bizLogic
  
     Class empOps
                ... 
       ' Visual Basic procedures 
                ...
     End Class
  End Namespace

ファイルの先頭には、このファイルに組み込むものを示します。 ファイル内のプロシージャーのいずれかに SQL が含まれる場合は、 IBM.Data.DB2 を含める必要があります。 このファイルには、ネーム・スペース宣言を組み込み、 プロシージャーを内容とするクラス empOps を組み込みます。 ネーム・スペースの使用はオプションです。 ネーム・スペースを使用する場合は、 CREATE PROCEDURE ステートメントの EXTERNAL 節に指定するアセンブリー・パス名の中にネーム・スペースを入れなければなりません。

ファイルの名前、ネームスペース、 特定のプロシージャー・インプリメンテーションを含むクラスの名前をメモしておくことは重要です。 各プロシージャーの CREATE PROCEDURE ステートメントの EXTERNAL 節でその情報を指定して、データベース・マネージャーがアセンブリーと CLR プロシージャーのクラスを見つけられるようにする必要があるからです。

例 1: Visual Basic のパラメーター・スタイル GENERAL のプロシージャー
この例では、以下について説明します。
  • パラメーター・スタイル GENERAL のプロシージャーの CREATE PROCEDURE ステートメント
  • パラメーター・スタイル GENERAL のプロシージャーの Visual Basic コード
このプロシージャーは、従業員 ID と現在のボーナスの額を入力値として取ります。 そして、従業員の名前と給与を検索します。 現在のボーナスの額がゼロの場合は、 従業員の給与に基づいて新しいボーナスを計算し、従業員の氏名と一緒に戻します。 従業員が見つからない場合は、空ストリングを戻します。
  CREATE PROCEDURE SetEmpBonusGEN(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC setEmpBonusGEN
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusGEN'
    Public Shared Sub SetEmpBonusGEN(ByVal empId As String, _
                                     ByRef bonus As Decimal, _
                                     ByRef empName As String)

       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       myCommand = DB2Context.GetCommand()
       myCommand.CommandText = _
                  "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                + "FROM EMPLOYEE " _
                + "WHERE EMPNO = '" + empId + "'"
       myReader = myCommand.ExecuteReader()

       If myReader.Read()  ' If employee record is found
          ' Get the employee's full name and salary
          empName = myReader.GetString(0) + " " _
                  + myReader.GetString(1) + ". " _
                  + myReader.GetString(2)

          salary = myReader.GetDecimal(3)

          If bonus = 0
             If salary > 75000
                bonus = salary * 0.025
             Else
                bonus = salary * 0.05
             End If
          End If
       Else  ' Employee not found
          empName = ""  ' Set output parameter
       End If

       myReader.Close()

    End Sub
例 2: Visual Basic のパラメーター・スタイル GENERAL WITH NULLS のプロシージャー
この例では、以下について説明します。
  • パラメーター・スタイル GENERAL WITH NULLS のプロシージャーの CREATE PROCEDURE ステートメント
  • パラメーター・スタイル GENERAL WITH NULLS のプロシージャーの Visual Basic コード
このプロシージャーは、従業員 ID と現在のボーナスの額を入力値として取ります。 入力パラメーターが NULL 以外の場合は、従業員の名前と給与を検索します。 現在のボーナスの額がゼロの場合は、 給与に基づいて新しいボーナスを計算し、従業員の氏名と一緒に戻します。 従業員データが見つからない場合は、NULL ストリングと整数を戻します。
  CREATE PROCEDURE SetEmpBonusGENNULL(IN empId CHAR(6),
                                      INOUT bonus Decimal(9,2),
                                      OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusGENNULL
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusGENNULL'
    Public Shared Sub SetEmpBonusGENNULL(ByVal empId As String, _
                                         ByRef bonus As Decimal, _
                                         ByRef empName As String, _
                                         byVal nullInds As Int16())

       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       If nullInds(0) = -1   ' Check if the input is null
          nullInds(1) = -1   ' Return a NULL bonus value
          empName = ""       ' Set output parameter
          nullInds(2) = -1   ' Return a NULL empName value
          Return
       Else
          myCommand = DB2Context.GetCommand()
          myCommand.CommandText = _
                     "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                   + "FROM EMPLOYEE " _
                   + "WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()

          If myReader.Read()  ' If employee record is found
             ' Get the employee's full name and salary
             empName = myReader.GetString(0) + " "  _
                     + myReader.GetString(1) + ". " _
                     + myReader.GetString(2)

             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   bonus = Salary * 0.025
                   nullInds(1) = 0 'Return a non-NULL value
                Else
                   bonus = salary * 0.05
                   nullInds(1) = 0 ' Return a non-NULL value
                End If
             Else  'Employee not found
                empName = ""       ' Set output parameter
                nullInds(2) = -1   ' Return a NULL value
             End If
          End If

          myReader.Close()

       End If

    End Sub
例 3: Visual Basic のパラメーター・スタイル SQL のプロシージャー
この例では、以下について説明します。
  • パラメーター・スタイル SQL のプロシージャーの CREATE PROCEDURE ステートメント
  • パラメーター・スタイル SQL のプロシージャーの Visual Basic コード
このプロシージャーは、従業員 ID と現在のボーナスの額を入力値として取ります。 そして、従業員の名前と給与を検索します。 現在のボーナスの額がゼロの場合は、 給与に基づいて新しいボーナスを計算し、従業員の氏名と一緒に戻します。 従業員が見つからない場合は、空ストリングを戻します。
  CREATE PROCEDURE SetEmpBonusSQL(IN empId CHAR(6),
                                  INOUT bonus Decimal(9,2),
                                  OUT empName VARCHAR(60))
  SPECIFIC SetEmpBonusSQL
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DYNAMIC RESULT SETS 0
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!SetEmpBonusSQL'
    Public Shared Sub SetEmpBonusSQL(byVal empId As String, _
                                     byRef bonus As Decimal, _
                                     byRef empName As String, _
                                     byVal empIdNullInd As Int16, _
                                     byRef bonusNullInd As Int16, _
                                     byRef empNameNullInd As Int16, _
                                     byRef sqlState As String, _
                                     byVal funcName As String, _
                                     byVal specName As String, _
                                     byRef sqlMessageText As String)

       ' Declare local host variables
       Dim salary As Decimal
       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader

       salary = 0

       If empIdNullInd = -1   ' Check if the input is null
          bonusNullInd = -1   ' Return a NULL Bonus value
          empName = ""
          empNameNullInd = -1 ' Return a NULL empName value
       Else
         myCommand = DB2Context.GetCommand()
         myCommand.CommandText = _
                    "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                  + "FROM EMPLOYEE " _
                  + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()

          If myReader.Read()  ' If employee record is found
             '  Get the employee's full name and salary
             empName = myReader.GetString(0) + " " 
                     + myReader.GetString(1) _
                     + ". " +  myReader.GetString(2)
             empNameNullInd = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   bonus = salary * 0.025
                   bonusNullInd = 0  ' Return a non-NULL value
                Else
                   bonus = salary * 0.05
                   bonusNullInd = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             empName = ""            ' Set output parameter
             empNameNullInd = -1     ' Return a NULL value
          End If
          
          myReader.Close()
       End If
       
    End Sub
例 4: 結果セットを戻す Visual Basic のパラメーター・スタイル GENERAL のプロシージャー
この例では、以下について説明します。
  • 結果セットを戻す外部 Visual Basic プロシージャーの CREATE PROCEDURE ステートメント
  • 結果セットを戻すパラメーター・スタイル GENERAL のプロシージャーの Visual Basic コード
このプロシージャーは、パラメーターとして表の名前を受け入れます。 そして、入力パラメーターによって指定されている表の行すべてを含む結果セットを戻します。 この処理のために、プロシージャーの戻り時に特定の照会結果セットの DB2DataReader をオープンにしておきます。 具体的には、reader.Close() が実行されなければ、結果セットが戻されるということです。 
  CREATE PROCEDURE ReturnResultSet(IN tableName VARCHAR(20))
  SPECIFIC ReturnResultSet
  DYNAMIC RESULT SETS 1
  LANGUAGE CLR
  PARAMETER STYLE GENERAL
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!ReturnResultSet'
Public Shared Sub ReturnResultSet(byVal tableName As String)

      Dim myCommand As DB2Command
      Dim myReader As DB2DataReader

      myCommand = DB2Context.GetCommand()

      ' Set the SQL statement to be executed and execute it.
      myCommand.CommandText = "SELECT * FROM " + tableName
      myReader = myCommand.ExecuteReader()

      ' The DB2DataReader contains the result of the query.
      ' This result set can be returned with the procedure,
      ' by simply NOT closing the DB2DataReader.
      ' Specifically, do NOT execute reader.Close()

    End Sub
例 5: dbinfo 構造にアクセスする Visual Basic のパラメーター・スタイル SQL のプロシージャー
この例では、以下について説明します。
  • dbinfo 構造にアクセスするプロシージャーの CREATE PROCEDURE ステートメント
  • dbinfo 構造にアクセスするパラメーター・スタイル SQL のプロシージャーの Visual Basic コード
dbinfo 構造にアクセスするには、 CREATE PROCEDURE ステートメントに DBINFO 節を指定する必要があります。 CREATE PROCEDURE ステートメントの dbinfo 構造にパラメーターは必要ありませんが、 外部ルーチン・コードでそのためのパラメーターを作成する必要があります。 このプロシージャーは、 dbinfo 構造の dbname フィールドからの現行データベース名の値だけを戻します。
  CREATE PROCEDURE ReturnDbName(OUT dbName VARCHAR(20))
  SPECIFIC ReturnDbName
  LANGUAGE CLR
  PARAMETER STYLE SQL
  DBINFO
  FENCED
  PROGRAM TYPE SUB
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!ReturnDbName'
    Public Shared Sub ReturnDbName(byRef dbName As String, _
                                   byRef dbNameNullInd As Int16, _
                                   byRef sqlState As String, _
                                   byVal funcName As String, _
                                   byVal specName As String, _
                                   byRef sqlMessageText As String, _
                                   byVal dbinfo As sqludf_dbinfo)

      ' Retrieve the current database name from the
      ' dbinfo structure and return it.
      dbName = dbinfo.dbname
      dbNameNullInd = 0  ' Return a non-null value

      ' If you want to return a user-defined error in
      ' the SQLCA you can specify a 5 digit user-defined
      ' SQLSTATE and an error message string text.
      ' For example:
      '
      ' sqlState = "ABCDE"
      ' msg_token = "A user-defined error has occurred"
      '
      ' These will be returned by database managher in the SQLCA.
			' It will appear in the format of a regular sqlState
      ' error.
    End Sub
例 6: PROGRAM TYPE MAIN スタイルの Visual Basic プロシージャー
この例では、以下について説明します。
  • メインプログラム・スタイルを使用したプロシージャーの CREATE PROCEDURE ステートメント
  • メインプログラム・スタイルを使用した Visual Basic のパラメーター・スタイル GENERAL WITH NULLS のコード
メインプログラム・スタイルでルーチンをインプリメントするには、 CREATE PROCEDURE ステートメントの PROGRAM TYPE 節に MAIN という値を指定する必要があります。 CREATE PROCEDURE ステートメントにもパラメーターを指定しますが、 コードのインプリメンテーションでは、 ルーチンの argc 整数パラメーターと argv パラメーター配列にパラメーターを渡します。
  CREATE PROCEDURE MainStyle(IN empId CHAR(6),
                             INOUT bonus Decimal(9,2),
                             OUT empName VARCHAR(60))
  SPECIFIC mainStyle
  DYNAMIC RESULT SETS 0
  LANGUAGE CLR
  PARAMETER STYLE GENERAL WITH NULLS
  FENCED
  PROGRAM TYPE MAIN
  EXTERNAL NAME 'gwenVbProc.dll:bizLogic.empOps!Main' 
    Public Shared Sub Main( byVal argc As Int32, _
                            byVal argv As Object())

       Dim myCommand As DB2Command
       Dim myReader As DB2DataReader
       Dim empId As String
       Dim bonus As Decimal
       Dim salary As Decimal
       Dim nullInds As Int16()

       empId = argv(0)  ' argv[0] (IN)    nullInd = argv[3]
       bonus = argv(1)  ' argv[1] (INOUT) nullInd = argv[4]
                        ' argv[2] (OUT)   nullInd = argv[5]
       salary = 0
       nullInds = argv(3)

       If nullInds(0) = -1     ' Check if the empId input is null
          nullInds(1) = -1     ' Return a NULL Bonus value
          argv(1) = ""         ' Set output parameter empName
          nullInds(2) = -1     ' Return a NULL empName value
          Return
       Else
          ' If the employee exists and the current bonus is 0,
          ' calculate a new employee bonus based on the employee's
          ' salary.  Return the employee name and the new bonus
          myCommand = DB2Context.GetCommand()
          myCommand.CommandText = _
                     "SELECT FIRSTNME, MIDINIT, LASTNAME, SALARY " _
                   + " FROM EMPLOYEE " _
                   + " WHERE EMPNO = '" + empId + "'"

          myReader = myCommand.ExecuteReader()


          If myReader.Read()  ' If employee record is found
             ' Get the employee's full name and salary
             argv(2) = myReader.GetString(0) + " "  _
                     + myReader.GetString(1) + ". " _
                     + myReader.GetString(2)
             nullInds(2) = 0
             salary = myReader.GetDecimal(3)

             If bonus = 0
                If salary > 75000
                   argv(1) = salary * 0.025
                   nullInds(1) = 0  ' Return a non-NULL value
                Else
                   argv(1) = Salary * 0.05
                   nullInds(1) = 0  ' Return a non-NULL value
                End If
             End If
          Else  ' Employee not found
             argv(2) = ""      ' Set output parameter
             nullInds(2) = -1  ' Return a NULL value
          End If

          myReader.Close()
       End If

    End Sub