Oracleのストアド・プロシージャをVB／C#で記述する方法

Oracle Database Extensions for .NET（ODE.NET）の利用

.NETストアドプロシージャの開発とデプロイ

　ODE.NETを使用するための準備が終わったら、Visual Studioで.NETストアドプロシージャを作成しましょう。今回はデータベースへのアクセスのない、簡単な.NETストアドプロシージャを作成することで、開発からデータベースへのデプロイまでの手順を説明します。ODTがインストールされた環境で、Visual Studioを起動すると、新しいプロジェクトとして、［Oracle Project］を選択することができます。

 

　［Oracle Project］を選択して.NETストアドプロシージャの作成を開始します。次のような.NETストアドプロシージャを記述するためのコードエディタが表示されます。

 

　では、実際に.NETストアドプロシージャを記述してみましょう。.NETストアドプロシージャとしてデプロイするプロシージャやファンクションは、VB.NETの場合は「Public Shared」、C#の場合は「public static」で宣言されている必要があります。この点に注意して、「SCOTT」ユーザーの「EMP」表にアクセスする簡単なコードを記述してみます。

「SCOTT」ユーザーの「EMP」表にアクセスするサンプル

Public Shared Function GetEmpName(ByVal empno As Integer) As String
  'コネクションの確立
  Dim conn As New OracleConnection("context connection=true")
  conn.Open()

  ''SQLの発行と値の取得
  Dim cmd As OracleCommand = conn.CreateCommand
  cmd.CommandText = "SELECT ename FROM emp WHERE empno=:1"
  cmd.Parameters.Add(":1", OracleDbType.Int32, empno, _
    ParameterDirection.Input)
  Dim rdr As OracleDataReader = cmd.ExecuteReader

  Dim strRet As String
  If rdr.Read Then
    strRet = rdr.Item("ename")
  Else
    strRet = ""
  End If
  rdr.Close()
  conn.Close()

  Return strRet
End Function

「SCOTT」ユーザーの「EMP」表にアクセスするサンプル

public static string GetEmpName(int empno)
  {
    // コネクションの確立
    OracleConnection conn =
      new OracleConnection("context connection=true");
    conn.Open();

    // SQLの発行と値の取得
    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT ename FROM emp WHERE empno=:1";
    cmd.Parameters.Add(":1", OracleDbType.Int32,empno,
      ParameterDirection.Input);
    OracleDataReader rdr = cmd.ExecuteReader();

    String strRet = "";
    if(rdr.Read())
    {
      strRet = rdr.GetString(0);
    }
    else
    {
      strRet = "";
    }
    rdr.Close();
    conn.Close();

    return strRet;
  }
}

　上記コードのように、通常のデータアクセスのコードと大差なく.NETストアドプロシージャを記述できるのが理解できるかと思います。一つ相違点として挙げられるのがコネクション確立時の記述方法が違う点です。通常ODP.NETからOracleデータベースへ接続する際の接続文字列は、

通常のODP.NETからOracleデータベースへ接続する際の接続文字列

"User Id=scott;Password=tiger;Data Source=orcl"

　上記のようになりますが、.NETストアドファンクションでは次のような記述になります。

通常のODP.NETからOracleデータベースへ接続する際の接続文字列

"context connection=true"

　.NETストアドプロシージャでは呼び出し元のコネクションを引き継ぐため、呼び出し元のユーザーIDやパスワード情報もそのまま引き継がれます。そのため、接続文字列中にDBユーザーの情報を記述する必要がありません。また、コネクション情報と共にトランザクションも引き継がれますので、.NETストアドプロシージャ内で新規にトランザクションを開始する必要はありません。コードの記述が終わったら、次にOracleデータベース側に、作成された.NETストアドプロシージャをデプロイします。Visual Studioのメニューバーより［ビルド］-［ソリューションの配置］をクリックします。

 

　これにより.NETアセンブリのビルドが行われ、データベースへのデプロイを行うためのOracle Deployment Wizard for .NETが起動します。このウィザードを利用することで、次のような作業をウィザード形式で行うことが可能です。

1. .NETアセンブリの作成
2. .NETアセンブリのデプロイ
3. .NETアセンブリを呼び出すためのPL/SQLラッパーの作成

 

　デプロイを行う際に.NETストアドプロシージャのセキュリティレベルの設定を行います。セキュリティレベルについては以下の表を参照してください。

表1　.NETストアドプロシージャ/ファンクションのセキュリティレベル

セキュリティレベル	説明
Safe（デフォルト）	データベース・リソースへのアクセスのみが許可される。ローカル・ファイルやネットワークなどの外部リソースへのアクセスは許可されない。
External	ローカル・ファイルの読取りまたは書込みと、ソケットやインターネット・ノードなどのネットワーク・リソースへのアクセスも許可される。
Unsafe	管理されていないコードの実行を含め、制限なしの実行が許可される。他のすべてのセキュリティ・レベルのスーパーセット。

.NETストアドプロシージャの実行

　デプロイが完了したらOracleエクスプローラより、.NETストアドプロシージャを実行してみましょう。今回はストアド・ファンクションになるので、.NETストアド・ファンクションを呼び出すための、PL/SQLファンクションが同じ名前で作成されています。右クリックして［実行］をクリックしてみます。

 

　ファンクションを実行した後に、引数を入力するダイアログが表示されますので、引数を入力して［OK］ボタンをクリックすると結果が表示されます。以上のように［Oracleエクスプローラ］から呼び出すこともできますし、通常のSQL文から.NETストアドプロシージャを呼び出すこともできます。ストアドファンクションの場合は、次のようにSQLにPL/SQLラッパーを直接記述します。ストアドプロシージャの場合は、「EXECUTE（EXEC）」文を発行します。

.NETストアドプロシージャの呼び出し例

C:\>SQLPLUS scott/tiger

SQL> SELECT getempname(7369) FROM dual;

GETEMPNAME(7369)
------------------------------------------

SMITH

　通常のPL/SQLで記述したストアドプロシージャと同様に.NETストアドプロシージャ／ファンクションを呼び出せることが確認できたと思います。

おわりに

　今回は、.NETストアドプロシージャを実現するODE.NETの概要と、ODE.NETのインストール、および設定方法、.NETストアドプロシージャの開発手順を簡単なサンプルを使って解説しました。次回は、ODE.NETで作成された.NETストアドプロシージャをデバッグする方法と、PL/SQLではできない処理をODE.NETで実現するための記事を投稿していきます。ご期待ください！

参考資料

1. Oracle Database Extensions for .NET開発者ガイド