掲載元 2006年1月/2月

カーソル・イン、カーソル・アウト
Mark A. Williams著

ODP.NETとOracle Database 10g Release 2を使用して、参照カーソルをPL/SQLに送信する簡単な方法

参照カーソルは、サーバー・メモリーに常駐する結果セットに対する参照です。 参照カーソルが開いている場合、当初はクライアントにデータは返されません。 代わりに、データが常駐するアドレスがクライアントに渡され、そのクライアントは参照カーソルで表されるデータの処理方法およびタイミングを選択できます。

ODP.NETの以前のリリースでは、参照カーソルからのデータの取得はできましたが、参照カーソルを入力パラメータとしてPL/SQLストアド・プロシージャまたはファンクションに渡すことはできませんでした。 しかし、ODP.NET 10g Release 2では、参照カーソルをOracle Database 10g Release 2の入力パラメータとして簡単に渡すことができます。

このODP.NETのコラムでは、Oracle Database 10g Release 2にアクセスする.NETアプリケーション内のPL/SQLストアド・プロシージャに参照カーソルを送信する方法について説明します。

サンプル・アプリケーションの要件

このコラムに付随するサンプル・アプリケーション・コードは、ユーザーによる employees 表からの従業員のサブセットの選択をシミュレートします。 選択された従業員のサブセットは、参照カーソルによって表され、実際の行はデータベースからクライアント・アプリケーションに渡されることはなく、行のアドレスだけが渡されます。 従業員が選択されると、参照カーソルがストアド・プロシージャに渡されて処理されます。 このケースでは、メッセージを単純に表に挿入するだけです。

このサンプル・アプリケーションは、 HR サンプル・スキーマを使用します。

データベース・オブジェクトの実装

参照カーソルをストアド・プロシージャに渡す.NETコードを作成する前に、このアクティビティをサポートする基盤となるデータベース・オブジェクトを作成します。 .NETコードを最初に作成することもできますが、.NETコードが使用するデータベース・オブジェクトを最初に作成するのがもっとも簡単です。 作成する3つのデータベース・オブジェクトは次のとおりです。

• 表
• PL/SQLパッケージ
• PL/SQLパッケージ本体

表は、PL/SQLパッケージ本体で生じる処理の結果を保持します。 PL/SQLパッケージは参照カーソルを定義し、処理するストアド・プロシージャの定義が含まれます。 ストアド・プロシージャのロジックはPL/SQLパッケージ本体にあり、作業の大半を実行します。このケースでは、参照カーソル内の行を単純にループし、簡単なメッセージを表に挿入します。 oracle.com/technology/oramag/oracle/06-jan/jf06_odpnet.zip からダウンロード可能なサンプル・アプリケーション、database.sqlのコードを使用して、データベース・オブジェクトを作成します。

.NETコードの作成

データベース・オブジェクトを作成したら、新しい.NETコンソール・アプリケーションを作成し、Microsoft Visual Studioのメニュー・バーから「 プロジェクト 」→「 参照の追加 」を選択して、ODP.NETアセンブリの参照を自分のプロジェクトに追加します。次に、参照の追加ダイアログ・ボックスから「 Oracle.DataAccess.dll 」を選択します。

ODP.NETアセンブリに参照を追加することに加え、名前空間の標準セットを各プロジェクトに追加し、クラス・ファイルの先頭に次のコードを含めることで、アプリケーション・コードの量を減らします。

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

Oracle.DataAccess.Types 名前空間には、.NETで利用可能なオラクル固有のデータ型や参照カーソルなどが含まれます。

コード・リスト1： .NET mainメソッド - 参照カーソルの作成および引渡し

static void Main(string[] args)
{
  string constr = "User Id="hr;" Password=hr;
    Data Source="oramag;" Pooling=false";

  OracleConnection con = new OracleConnection(constr);
  con.Open();

  OracleCommand cmd = con.CreateCommand();

  cmd.CommandText = "begin open :1 for
    select * from employees
    where manager_id="101;" end;";

  OracleParameter p_rc = cmd.Parameters.Add(
    "p_rc",
    OracleDbType.RefCursor,
    DBNull.Value,
    ParameterDirection.Output);

  cmd.ExecuteNonQuery();

  cmd.Parameters.Clear();

  cmd.CommandText = "cursor_in_out.process_cursor";
  
  cmd.CommandType = CommandType.StoredProcedure;

  OracleParameter p_input = cmd.Parameters.Add(
    "p_input",
    OracleDbType.RefCursor,
    p_rc.Value,
    ParameterDirection.Input);

  cmd.ExecuteNonQuery();

  p_input.Dispose();
  p_rc.Dispose();
  cmd.Dispose();
  con.Dispose();
}

リスト1のコードには、新しいコンソール・アプリケーションを作成するときに、Microsoft Visual Studioが作成するmainメソッドと置き換えるmainメソッドが含まれています。 この新しいコードは、ユーザーによる表からの行のサブセットの選択をシミュレートします。

以下はリスト1のコードの重要な点です。

cmd.CommandText = "begin open :1 for
  select * from employees
  where manager_id="101;" end;";

OracleCommand オブジェクトの CommandText プロパティを、参照カーソルを作成する無名PL/SQLブロックに設定します。 無名ブロックは名前ではなく、 begin キーワードと end キーワードで表され、バインド変数（ :1 ）を参照カーソルのパラメータに使用します。 参照カーソルは open キーワードで作成され、カーソルのコンテンツは、 manager_id が 101 である従業員をすべて取得する単純なSQL文によって決定されます。 このコラムのコードにはリテラル値 101 を使用していますが、ユーザーのアプリケーションにはバインド変数を使用できます。

OracleParameter p_rc = 
  cmd.Parameters.Add(
  "p_rc",
  OracleDbType.RefCursor,
  DBNull.Value,
  ParameterDirection.Output);

.NETコード内で参照カーソルを表す OracleParameter オブジェクト、p_rcを作成します。 パラメータは OracleDbType.RefCursor 型であることを宣言する必要があります（参照カーソルのODP.NETデータ型の詳細については、『 Oracle Data Provider for .NET開発者ガイド 』を参照してください）。 パラメータの初期値はNULL（ DBNull.Value ）に設定され、方向は出力に設定します。

cmd.CommandText = 
"cursor_in_out.process_cursor";
cmd.CommandType = 
CommandType.StoredProcedure;

値を OracleCommand オブジェクトの CommandText プロパティと CommandType プロパティに割り当て、参照カーソルを処理するPL/SQLストアド・プロシージャを起動します。 CommandText プロパティは、ストアド・プロシージャの名前（パッケージ名を接頭辞として含む）に設定され、 CommandType プロパティは、スタンドアロンのSQL文ではなく、ストアド・プロシージャが使用されていることを示すように設定します。 プロシージャではなくストアド・ファンクションを呼び出す場合でも、コマンドが StoredProcedure 型であることを宣言する必要があります。

OracleParameter p_input = 
  cmd.Parameters.Add(
  "p_input",
  OracleDbType.RefCursor,
  p_rc.Value,
  ParameterDirection.Input);

参照カーソルをPL/SQLストアド・プロシージャに渡す OracleParameter オブジェクト、 p_input を作成します。 パラメータは、入力パラメータ（ ParameterDirection.Input ）です。

実行中のコードの確認

コードの結果を確認するには、Microsoft Visual Studio .NETのメイン・メニューから「 ビルド 」→「 ソリューションのビルド 」 を選択し、「 開始 」ボタンをクリックしてプロジェクトを実行します。 アプリケーションは目に見える出力を生成しませんが、Oracle Developer Tools for Visual StudioのSQL Query WindowまたはSQL*Plusなどの問合せツールを使用して、簡単に結果を確認できます。 どのツールを使用しても、次のような結果が表示されます。

SQL> select * from processing_result;
STATUS
----------------------------------------
Processed employee #108: Nancy Greenberg
Processed employee #200: Jennifer Whalen
Processed employee #203: Susan Mavris
Processed employee #204: Hermann Baer
Processed employee #205: Shelley Higgins

5 rows selected.

まとめ

このコラムでは、データベース・オブジェクトと.NETコードの両方を作成して、出力参照カーソルを生成する方法、およびその参照カーソルを入力パラメータとしてPL/SQLストアド・プロシージャに渡す方法について説明しました。 ODP.NETを使用すると入力参照カーソルでの作業を、そのほかのODP.NETやOracle型での作業と同様に簡単に実行できます。 このシンプルな例を展開して、今あるアプリケーションで、ODP.NET、PL/SQLストアド・プロシージャ、参照カーソル、Oracle Database 10g Release 2の機能を活用することが推奨されます。