この章の内容は次のとおりです。
ストアド・プロシージャとは、ある操作を実行するために設計された一連のPL/SQL文を1つにまとめて名前を付けたものです。ストアド・プロシージャはデータベース内に格納されます。これは、クライアント・アプリケーションがデータベース・オブジェクトと直接対話できるようにするものではなく、データベースのプログラミング・インタフェースを定義するものです。一般的にストアド・プロシージャを使用するのは、データを検証する場合や、複数のSQL問合せを組み合せた大規模で複雑な処理の指示をカプセル化する場合です。
ストアド・ファンクションは、戻り値パラメータを1つとります。ファンクションとは異なり、プロシージャは値を戻す場合と戻さない場合があります。
PL/SQLパッケージには、関連する項目が単一の論理エンティティとして格納されます。パッケージは、次の2つの部分で構成されます。
パッケージ仕様部はパッケージの内容を定義し、C++などの言語のヘッダー・ファイルと同様の機能を持ちます。仕様部は、すべてのパブリック・アイテムを定義します。仕様部はパッケージへの公開されたインタフェースです。
パッケージ本体には、仕様部で定義したプロシージャおよびファンクションのコードと、仕様部で宣言していないプライベート・プロシージャおよびファンクションのコードが含まれます。プライベート・コードはパッケージ本体内でのみ確認できます。
パッケージ仕様部とパッケージ本体は、データ・ディクショナリに別々のオブジェクトとして格納され、user_source
ビューで確認できます。仕様部はPACKAGE
型として格納され、本体はPACKAGE BODY
型として格納されます。
一連のパブリック定数を宣言する場合と同様、本体のない仕様部を保持することはできますが、仕様部のない本体を保持することはできません。
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
はPL/SQLデータ型です。PL/SQLコード・ブロック内でREF
CURSOR
を作成して戻すことができます。
この項では、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
の値が表示されます。
この項では、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
手順3で追加したコードの下に、GETCURSORS
ストアド・プロシージャの3つのパラメータに対して定義およびバインディングをOracleParameter
オブジェクトとして追加し、それぞれdep_id
、employees_c
およびdependents_c
と名前を指定します。
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)
アプリケーションをビルドします。
この項では、GETCURSORS
ストアド・プロシージャなどのPL/SQLストアド・プロシージャを、ODPアプリケーションから実行する方法を説明します。
ストアド・プロシージャを実行するには、次の手順を実行します。
アプリケーションを実行します。
「Form1」ウィンドウが表示されます。
「Form1」ウィンドウで接続情報を入力し、「Connect」をクリックします。
DataGrid
オブジェクトで、水平方向にスクロールして最後の列DEPARTMENT_ID
に含まれる値が60
のみであることを確認します。
DataGrid
には、ストアド・プロシージャの最初の結果セットが含まれており、これはEMPLOYEES
表の問合せと一致することに注意してください。
アプリケーションを閉じます。