6 PL/SQLストアド・プロシージャおよびREF CURSORの使用

この章の内容は次のとおりです。

PL/SQLストアド・プロシージャの概要

ストアド・プロシージャとは、ある操作を実行するために設計された一連のPL/SQL文を1つにまとめて名前を付けたものです。ストアド・プロシージャはデータベース内に格納されます。これは、クライアント・アプリケーションがデータベース・オブジェクトと直接対話できるようにするものではなく、データベースのプログラミング・インタフェースを定義するものです。一般的にストアド・プロシージャを使用するのは、データを検証する場合や、複数のSQL問合せを組み合せた大規模で複雑な処理の指示をカプセル化する場合です。

ストアド・ファンクションは、戻り値パラメータを1つとります。ファンクションとは異なり、プロシージャは値を戻す場合と戻さない場合があります。

PL/SQLパッケージとパッケージ本体の概要

PL/SQLパッケージには、関連する項目が単一の論理エンティティとして格納されます。パッケージは、次の2つの部分で構成されます。

パッケージ仕様部では、パッケージに含まれるものを定義します。これは、C++などの言語のヘッダー・ファイルに似ています。仕様部では、すべてのパブリック項目を定義します。仕様部はパッケージの公開インタフェースです。
パッケージ本体には、仕様部で定義したプロシージャおよびファンクションのコードと、仕様部で宣言していないプライベート・プロシージャおよびファンクションのコードが含まれます。プライベート・コードはパッケージ本体内でのみ確認できます。

パッケージ仕様部とパッケージ本体は、データ・ディクショナリに別々のオブジェクトとして格納され、user_sourceビューで確認できます。仕様部はPACKAGE型として格納され、本体はPACKAGE BODY型として格納されます。

一連のパブリック定数を宣言する場合と同様、本体のない仕様部を保持することはできますが、仕様部のない本体を保持することはできません。

REF CURSORの概要

REF CURSORを使用することは、Oracle Databaseからの問合せ結果をクライアント・アプリケーションに戻す最も強力かつ柔軟で、拡張性のある方法の1つです。

REF CURSORはPL/SQLデータ型であり、この値はデータベース上の問合せ作業領域のメモリー・アドレスです。つまり、REF CURSORは、データベース上にある結果セットへのポインタまたはハンドルとなります。REF CURSORは、OracleRefCursor ODP.NETクラスを使用して表現します。

REF CURSORには次の特性があります。

REF CURSORは、データベース上のメモリー・アドレスを参照します。そのため、REF CURSORにアクセスするには、REF CURSORの存続期間中、クライアントがデータベースに接続されている必要があります。
REF CURSORにより、追加のデータベース・ラウンドトリップが発生します。REF CURSORがクライアントに戻されても、クライアントがREF CURSORをオープンしてデータをリクエストするまで、実際のデータは戻されません。ユーザーがREF CURSORの読取りを試行するまで、データは取得されないことに注意してください。
REF CURSORは更新できません。REF CURSORで表される結果セットは読取り専用です。REF CURSORを使用してデータベースを更新することはできません。
REF CURSORは後方にスクロールできません。REF CURSORで表される結果セットは、前進専用で順次アクセスされます。結果セット内のレコードをランダムにポイントするためにREF CURSOR内にレコード・ポインタを配置することはできません。
REF CURSORはPL/SQLデータ型です。PL/SQLコード・ブロック内でREF CURSORを作成して戻すことができます。

REF CURSORを使用するPL/SQLストアド・プロシージャの作成

この項では、PL/SQLストアド・プロシージャの作成方法を説明します。

ストアド・プロシージャを作成するには、次の手順を実行します。

Server Explorerを開いてHRをダブルクリックし、「Oracle Databaseへの接続」で作成したHRスキーマへの接続をオープンします。

画像の説明

以前にパスワードを保存しなかった場合は、「Oracle Server Login」が開き、パスワードを入力できます。パスワードを以前に保存している場合は、すぐに接続がオープンします。
Server Explorerで、「Packages」を右クリックして「New Package」を選択します。

画像の説明

「New Package」ウィンドウが表示されます。
「New Package」ウィンドウで、「Package Name」をHR_DATAに変更します。
「Methods」領域の下の「Add」をクリックします。

画像の説明

「Add Method」ウィンドウが表示されます。
「Add Method」ウィンドウで、「Method Name」にGETCURSORSと入力し、「Method Type」をProcedureに変更します。
「Parameters」の下の「Add」をクリックします。

これにより、パラメータを追加するプロセスが開始されます。

右側の「Parameter Details」グループで、次の3つのパラメータを入力します。「Add」をクリックしてから、必要なパラメータを1つずつ追加します。
- 「Name:」にDEP_IDと入力し、「Direction:」は「IN」を選択します。「Data Type:」には「NUMBER」を選択します。
- 「Name:」にEMPLOYEES_Cと入力し、「Direction:」は「OUT」を選択します。「Data Type:」には「SYS_REFCURSOR」を選択します。
- 「Name:」にDEPENDENTS_Cと入力し、「Direction:」は「OUT」を選択します。「Data Type:」には「SYS_REFCURSOR」を選択します。
  
  画像の説明
パラメータの追加が終了したら、「OK」をクリックします。

「New Package」ウィンドウが再度表示されます。

「New Package」ウィンドウで「Preview SQL」をクリックし、作成されたSQLコードを確認します。

次のようなコードを含む「Preview SQL」ウィンドウが表示されます。このコードは、コメントの大部分を削除して短縮したものです。

CREATE PACKAGE "HR"."HR_DATA" IS

  -- Declare types, variables, constants, exceptions, cursors, 
  -- and subprograms that can be referenced from outside the package.

  PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER, 
    "EMPLOYEES_C" OUT SYS_REFCURSOR, 
    "DEPENDENTS_C" OUT SYS_REFCURSOR);

END "HR_DATA";

CREATE PACKAGE BODY "HR"."HR_DATA" IS

  -- Implement subprograms, initialize variables declared in package
  -- specification.

  -- Make private declarations of types and items, that are not accessible 
  -- outside the package
 
  PROCEDURE "GETCURSORS" (
    "DEP_ID" IN NUMBER, 
    "EMPLOYEES_C" OUT SYS_REFCURSOR, 
    "DEPENDENTS_C" OUT SYS_REFCURSOR) IS

  -- Declare constants and variables in this section.

     BEGIN -- executable part starts here

     NULL;

  -- EXCEPTION -- exception-handling part starts here
 
    END "GETCURSORS";

END "HR_DATA";

「OK」をクリックして「Preview SQL」ウィンドウを閉じます。
「New Package」ウィンドウで「OK」をクリックし、新しいパッケージを保存します。

新しいパッケージHR_DATAがServer Explorerに表示されます。
Server Explorerで、パッケージHR_DATAを右クリックして「Edit Package Body」を選択します。

画像の説明

パッケージのコードが表示されます。

GETCURSORSプロシージャの本体までスクロールし、BEGINの後にある行NULL;を次のコードに置き換えます。

OPEN EMPLOYEES_C FOR SELECT * FROM EMPLOYEES 
   WHERE DEP_ID=DEPARTMENT_ID;
OPEN DEPENDENTS_C FOR SELECT * FROM DEPENDENTS;

パッケージへの変更を保存します。
ストアド・プロシージャを実行するには、Server ExplorerでHR_DATAパッケージを開きます。

GETCURSORSメソッドを右クリックして、「Run」を選択します。

画像の説明

「Run Procedure」ウィンドウが表示されます。
「Run Procedur」ウィンドウで、DEP_IDの「Value」に60を入力します。

画像の説明
「OK」をクリックします。

「Output」ウィンドウが表示され、正しく実行されたことが示されます。

結果ウィンドウに次のメッセージが表示されます。
```
Procedure <HR.HR_DATA.GETCURSORS@hr.database> was run successfully.
```
このメッセージの下の（DEP_IDとともに表示される）2つの出力パラメータ（EMPLOYEES_CおよびDEPENDENTS_C）を確認します。
EMPLOYEES_Cの「Value」列のエントリを選択します。

「Parameter Details」領域が表示され、部門60の従業員が表示されます。DEP_IDの値は60です。

画像の説明
DEPENDENTS_Cの「Value」列のエントリを選択します。

「Parameter Details」領域が表示され、DEPENDENTS_Cの値が表示されます。

画像の説明

ストアド・プロシージャを実行するためのODP.NETアプリケーションの変更

この項では、Oracle Data Provider for .NETアプリケーションを変更してPL/SQLストアド・プロシージャを実行できるようにする方法を、GETCURSORSストアド・プロシージャを例に説明します。

ストアド・プロシージャを実行できるようにアプリケーションを変更するには、次の手順を実行します。

アプリケーションHR_Connect_CSまたはHR_Connect_VBを開きます。
付録B「フォームのコピー」の手順に従って、第4章の最後で完成させたForm3.xxのコピーを作成し、Form4.xxという名前を付けます。
Form1を選択し、コード・ビューに切り替えます。
connect_Click()メソッドのTryブロックで、コマンドを割り当てる2つの行（cmd = New OracleCommand...で始まる行）を、次に示すコードと置き換えます。

Visual C#:
```
cmd = new OracleCommand("HR_DATA.GETCURSORS", conn);
cmd.CommandType = CommandType.StoredProcedure;
```
Visual Basic:
```
cmd = new OracleCommand("HR_DATA.GETCURSORS", conn)
cmd.CommandType = CommandType.StoredProcedure
```

手順4で追加したコードの下に、GETCURSORSストアド・プロシージャの3つのパラメータの定義とバインドを、それぞれdep_id、employees_cおよびdependents_cという名前のOracleParameterオブジェクトとして追加します。

Visual C#:

OracleParameter dep_id = new OracleParameter();
dep_id.OracleDbType = OracleDbType.Decimal;
dep_id.Direction = ParameterDirection.Input;
dep_id.Value = 60;
cmd.Parameters.Add(dep_id);

OracleParameter employees_c = new OracleParameter();
employees_c.OracleDbType = OracleDbType.RefCursor;
employees_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(employees_c);

OracleParameter dependents_c = new OracleParameter();
dependents_c.OracleDbType = OracleDbType.RefCursor;
dependents_c.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dependents_c);

Visual Basic:

Dim dep_id As OracleParameter = New OracleParameter
dep_id.OracleDbType = OracleDbType.Decimal
dep_id.Direction = ParameterDirection.Input
dep_id.Value = 60
cmd.Parameters.Add(dep_id)

Dim employees_c As OracleParameter = New OracleParameter
employees_c.OracleDbType = OracleDbType.RefCursor
employees_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(employees_c)

Dim dependents_c As OracleParameter = New OracleParameter
dependents_c.OracleDbType = OracleDbType.RefCursor
dependents_c.Direction = ParameterDirection.Output
cmd.Parameters.Add(dependents_c)

アプリケーションをビルドします。

ODP.NETアプリケーションによるPL/SQLストアド・プロシージャの実行

この項では、GETCURSORSストアド・プロシージャなどのPL/SQLストアド・プロシージャを、ODPアプリケーションから実行する方法を説明します。

ストアド・プロシージャを実行するには、次の手順を実行します。

アプリケーションを実行します。

「Form1」ウィンドウが表示されます。
「Form1」ウィンドウで接続情報を入力し、「Connect」をクリックします。
DataGridオブジェクトで、水平方向にスクロールして最後の列DEPARTMENT_IDに含まれる値が60のみであることを確認します。

DataGridには、ストアド・プロシージャの最初の結果セットが含まれており、これはEMPLOYEES表の問合せと一致することに注意してください。

画像の説明
アプリケーションを閉じます。