| Oracle® Database 2日で開発者ガイド 12c リリース1 (12.1) B71350-02 |
|
 前 |
 次 |
この章の内容は、次のとおりです。
サブプログラムは、特定の問題を解決したり、関連する一連のタスクを実行するSQL文およびPL/SQL文で構成されているPL/SQLユニットです。サブプログラムはパラメータを持つことができ、値は起動元から提供されます。サブプログラムは、プロシージャまたはファンクションです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
ストアド・サブプログラムは、データベースに格納されたサブプログラムです。ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。別のサブプログラムまたは無名ブロック内で宣言されたサブプログラムは、ネストされたサブプログラムまたはローカル・サブプログラムと呼ばれます。宣言されたサブプログラムまたはブロックの外部から呼び出すことはできません。無名ブロックとは、データベースに格納されていないブロックです。
ストアド・サブプログラムは2種類あります。
スタンドアロンのサブプログラムは、一部のプログラム・ロジックのテストに有用ですが、確実にこれらを意図したとおりに作動させる場合、これらをパッケージ内に配置することをお薦めします。
|
参照:
|
パッケージとは、関連するサブプログラムとそれらで使用する宣言カーソルと変数からなるPL/SQLユニットです。
サブプログラムは、パッケージに含めることをお薦めします。理由は次のとおりです。
パッケージでは、クライアント・プログラムから実装の詳細を隠すことができます。
クライアント・プログラムから実装の詳細を隠すことは、広く指示されるベスト・プラクティスです。Oracleのカスタマの多くは、このプラクティスに厳密に従っていて、クライアント・プログラムでは、PL/SQLサブプログラムを起動したときにのみデータベースにアクセスできます。一部のカスタマは、クライアント・プログラムでSELECT文を使用し、データベース表から情報を取得することを可能にしていますが、この文は、データベースを変更するすべてのビジネス機能に対するPL/SQLサブプログラムを起動する必要があります。
パッケージ済サブプログラムは、パッケージ外部からの起動時にパッケージ名による修飾が必要であり、これによりこれらのパッケージ名はパッケージ外部からの起動時に常に確実に機能します。
たとえば、Oracle Database 11g以前のバージョンでCONTINUEというスキーマ・レベル・プロシージャを作成したとします。Oracle Database 11gはCONTINUE文を導入しました。したがって、コードをOracle Database 11gに移植しても、コンパイルされません。ただし、パッケージ内にプロシージャを作成した場合、コードはプロシージャをpackage_name.CONTINUEとして参照するため、コンパイルできます。
|
注意: Oracle Databaseは、多くのPL/SQLパッケージを提供してデータベース機能を拡張し、SQL機能へのPL/SQLによるアクセスを可能にしています。提供されたパッケージは、アプリケーションの作成や、独自のストアド・プロシージャを作成するアイデアのために使用できます。パッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 |
|
参照:
|
PL/SQL、サブプログラム、パッケージ、パラメータ、変数、定数、例外および宣言カーソルには、それぞれ名前があり、それぞれの名前がPL/SQL識別子です。
識別子は最短で1文字、最長で30文字です。最初は文字である必要がありますが、以降は文字、数字、ドル記号($)、アンダースコア(_)またはシャープ記号(#)を使用できます。たとえば、次に示すのが許容可能な識別子です。
X t2 phone# credit_limit LastName oracle$number money$$$tree SN## try_again_
PL/SQLでは、識別子に関して大/小文字の区別がありません。たとえば、PL/SQLは次を同一とみなします。
lastname LastName LASTNAME
PL/SQLの予約語は、識別子として使用できません。PL/SQLキーワードは識別子として使用できますが、推奨されていません。PL/SQLの予約語およびキーワードのリストは、『Oracle Database PL/SQL言語リファレンス』を参照してください。
|
参照:
|
すべてのPL/SQL定数、変数、サブプログラム・パラメータおよびファンクション戻り値は、記憶形式、制約、値の有効範囲および実行できる演算を決定するデータ型を持っています。
PL/SQLデータ型は、SQLデータ型(VARCHAR2、NUMBER、DATEなど)またはPL/SQLのみのデータ型です。後者には、BOOLEAN、RECORD、REF CURSORに加え、多くの事前定義サブタイプが含まれます。また、PL/SQLを使用して、独自のサブタイプを定義することもできます。
サブタイプは、他のデータ型のサブセットで、ベース型と呼ばれます。サブタイプには、そのベース型として同じ有効な操作がありますが、その有効な値のサブセットのみです。サブタイプでは、定数と変数の用途を示すことにより、信頼性の向上、ANSI/ISO型との互換性の提供、および見やすさの改善が可能です。
事前定義された数値のサブタイプPLS_INTEGERは特に便利です。演算に、ベース型が使用するライブラリ算術計算のかわりにハードウェア算術計算が使用されるためです。
スキーマ・レベル(つまり、表内またはスタンドアロン・サブプログラム内)ではPL/SQL-onlyデータ型は使用できません。したがって、ストアド・サブプログラムでこれらのデータ型を使用するには、パッケージに配置する必要があります。
|
参照:
|
この項の内容は次のとおりです。
|
注意: このマニュアルのチュートリアルを行うには、ユーザーHRとして、SQL DeveloperからOracle Databaseに接続している必要があります。 |
サブプログラムは、PL/SQLブロック構造に従っています。つまり、次を含みます。
宣言部には、型、定数、変数、例外、宣言カーソル、およびネストされたサブプログラムの宣言が含まれます。これらのアイテムは、サブプログラムに対してローカルであり、サブプログラムの実行が完了すると存在しなくなります。
実行可能部分には、値を割り当て、実行を制御し、データを操作する文が含まれます。
例外処理部には、例外(実行時エラー)を処理するコードがあります。
コメントは、PL/SQLコードの任意の場所に表示可能です。PL/SQLコンパイラには無視されます。プログラムにコメントを追加することで、可読性が向上し、理解を助けます。単一行コメントは二重ハイフン(--)で始まり、行の末尾まで拡張されます。 複数行にまたがるコメントはスラッシュとアスタリスク(/*)で始まり、アスタリスクとスラッシュ(*/)で終わります。
PROCEDURE name [ ( parameter_list ) ] { IS | AS } [ declarative_part ] BEGIN -- executable part begins statement; [ statement; ]... [ EXCEPTION -- executable part ends, exception-handling part begins] exception_handler; [ exception_handler; ]... ] END; /* exception-handling part ends if it exists; otherwise, executable part ends */
ファンクションの構造はプロシージャの構造に似ていますが、RETURN句および少なくとも1つのRETURN文(およびこのマニュアルの範囲外のオプション句)が含まれる点が異なります。
FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
{ IS | AS }
[ declarative_part ]
BEGIN -- executable part begins
-- at least one statement must be a RETURN statement
statement; [ statement; ]...
[ EXCEPTION -- executable part ends, exception-handling part begins]
exception_handler; [ exception_handler; ]... ]
END; /* exception-handling part ends if it exists;
otherwise, executable part ends */
PROCEDUREまたはFUNCTIONで始まりISまたはASの前で終わるコードは、サブプログラムの署名です。宣言部分、実行可能部分および例外処理部分は、サブプログラムの本体を構成します。例外ハンドラの構文は、「例外および例外ハンドラについて」を参照してください。
|
参照: サブプログラムの部分の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
スタンドアロン・プロシージャを作成するには、SQL Developerツールのプロシージャの作成またはDDL文CREATE PROCEDUREのいずれかを使用します。
このチュートリアルでは、プロシージャの作成ツールを使用して、例4-1で作成されたEVALUATIONS表に行を追加するADD_EVALUATIONというスタンドアロン・プロシージャを作成する方法を示します。
プロシージャの作成ツールを使用してスタンドアロン・プロシージャを作成するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「プロシージャ」を右クリックします。
選択肢のリストで、「新規プロシージャ」をクリックします。
「プロシージャの作成」ウィンドウが開きます。
「スキーマ」では、デフォルト値のHRを受け入れます。
「名前」では、PROCEDURE1をADD_EVALUATIONに変更します。
「パラメータの追加」アイコンをクリックします。
列のヘッダーの下に1行表示されます。そのフィールドには、デフォルト値(名前: PARAM1、モード: IN、コピーなし: 選択解除、データ型: VARCHAR2、デフォルト値: 空)が設定されています。
「名前」では、PARAM1をEVALUATION_IDに変更します。
「モード」では、デフォルト値のINを受け入れます。
「データ型」では、メニューからNUMBERを選択します。
「デフォルト値」は空白のままにします。
「名前」にEMPLOYEE_ID、「データ型」にNUMBERを使用して、手順 6から10を繰り返すことにより、2番目のパラメータを追加します。
「名前」にEVALUATION_DATE、「データ型」にDATEを使用して、手順 6から10を繰り返すことにより、3番目のパラメータを追加します。
「名前」にJOB_ID、「データ型」にVARCHAR2を使用して、手順 6から10を繰り返すことにより、4番目のパラメータを追加します。
「名前」にMANAGER_ID、「データ型」にNUMBERを使用して、手順 6から10を繰り返すことにより、5番目のパラメータを追加します。
「名前」にDEPARTMENT_ID、「データ型」にNUMBERを使用して、手順 6から10を繰り返すことにより、6番目のパラメータを追加します。
「名前」にTOTAL_SCORE、「データ型」にNUMBERを使用して、手順 6から10を繰り返すことにより、7番目のパラメータを追加します。
「OK」をクリックします。
ADD_EVALUATIONペインが開き、プロシージャを作成したCREATE PROCEDURE文が表示されます。
CREATE OR REPLACE PROCEDURE ADD_EVALUATION ( EVALUATION_ID IN NUMBER , EMPLOYEE_ID IN NUMBER , EVALUATION_DATE IN DATE , JOB_ID IN VARCHAR2 , MANAGER_ID IN NUMBER , DEPARTMENT_ID IN NUMBER , TOTAL_SCORE IN NUMBER ) AS BEGIN NULL; END ADD_EVALUATION;
ADD_EVALUATIONペインのタイトルがイタリック・フォントになっています。プロシージャがデータベースに保存されていないことを示しています。
プロシージャの実行部分にある唯一の文がNULLであるため、プロシージャは何も行いません。
NULLを次の文に置換します。
INSERT INTO EVALUATIONS ( evaluation_id, employee_id, evaluation_date, job_id, manager_id, department_id, total_score ) VALUES ( ADD_EVALUATION.evaluation_id, ADD_EVALUATION.employee_id, ADD_EVALUATION.evaluation_date, ADD_EVALUATION.job_id, ADD_EVALUATION.manager_id, ADD_EVALUATION.department_id, ADD_EVALUATION.total_score );
(パラメータ名をプロシージャ名で修飾すると、パラメータは同じ名前の列と混同されなくなります。)
「ファイル」メニューから、「保存」を選択します。
Oracle Databaseは、プロシージャをコンパイルして保存します。ADD_EVALUATIONペインのタイトルがイタリック・フォントではなくなります。メッセージ - ログには「コンパイル済」というメッセージが表示されます。
|
参照:
|
スタンドアロン・ファンクションを作成するには、SQL Developerツールのファンクションの作成またはDDL文CREATE FUNCTIONのいずれかを使用します。
このチュートリアルでは、ファンクションの作成ツールを使用して、3つのパラメータがあり、NUMBER型の値を戻す、CALCULATE_SCOREという名前のスタンドアロン・ファンクションを作成する方法を表示します。
ファンクションの作成ツールを使用してスタンドアロン・ファンクションを作成するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「ファンクション」を右クリックします。
選択肢のリストで、新規ファンクションをクリックします。
「ファンクションの作成」ウィンドウが開きます。
「スキーマ」では、デフォルト値のHRを受け入れます。
「名前」で、FUNCTION1をCALCULATE_SCOREに変更します。
「戻り値の型」では、メニューからNUMBERを選択します。
「パラメータの追加」アイコンをクリックします。
列のヘッダーの下に1行表示されます。そのフィールドには、デフォルト値(名前: PARAM1、モード: IN、コピーなし: 選択解除、データ型: VARCHAR2、デフォルト値: 空)が設定されています。
「名前」では、PARAM1をcatに変更します。
「モード」では、デフォルト値のINを受け入れます。
「データ型」では、デフォルトのVARCHAR2を受け入れます。
「デフォルト値」は空白のままにします。
「名前」にscore、「型」にNUMBERを使用して、手順7から11を繰り返すことにより、2番目のパラメータを追加します。
「名前」にweight、「型」にNUMBERを使用して、手順7から11を繰り返すことにより、3番目のパラメータを追加します。
「OK」をクリックします。
CALCULATE_SCOREペインが開き、ファンクションを作成したCREATE FUNCTION文が表示されます。
CREATE OR REPLACE FUNCTION CALCULATE_SCORE ( CAT IN VARCHAR2 , SCORE IN NUMBER , WEIGHT IN NUMBER ) RETURN NUMBER AS BEGIN RETURN NULL; END CALCULATE_SCORE;
CALCULATE_SCOREペインのタイトルがイタリック・フォントになっています。ファンクションがデータベースに保存されていないことを示しています。
ファンクションの実行部分にある唯一の文がRETURN NULLであるため、ファンクションは何も行いません。
NULLをscore * weightに置換します。
「ファイル」メニューから、「保存」を選択します。
Oracle Databaseは、ファンクションをコンパイルして保存します。CALCULATE_SCOREペインのタイトルがイタリック・フォントではなくなります。メッセージ - ログには「コンパイル済」というメッセージが表示されます。
|
参照:
|
スタンドアロン・サブプログラムを変更するには、SQL Developerツールの編集またはDDL文ALTER PROCEDUREまたはALTER FUNCTIONのいずれかを使用します。
編集ツールを使用してスタンドアロンのサブプログラムを変更するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「ファンクション」または「プロシージャ」を展開します。
ファンクションまたはプロシージャのリストが表示されます。
変更するファンクションまたはプロシージャをクリックします。
「接続」フレームの右側に、フレームが表示されます。上部のタブには変更するサブプログラムの名前が表示されます。「コード」ペインにサブプログラムを作成したコードが表示されます。
「コード」ペインが書込みモードになっています。(鉛筆アイコンをクリックすると、モードが書込みモードから読取り専用モードに、またはその逆に切り替わります。)
「コード」ペインで、コードを変更します。
ペインのタイトルはイタリック・フォントになっており、変更がデータベースに保存されていないことを示しています。
「ファイル」メニューから、「保存」を選択します。
Oracle Databaseは、サブプログラムをコンパイルして保存します。ペインのタイトルがイタリック・フォントではなくなります。メッセージ - ログには「コンパイル済」というメッセージが表示されます。
|
参照:
|
このチュートリアルでは、SQL Developer実行ツールを使用して、スタンドアロンのファンクションCALCULATE_SCOREをテストする方法について説明します。
実行ツールを使用してCALCULATE_SCOREファンクションをテストするには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「ファンクション」を展開します。
ファンクションのリストで、CALCULATE_SCOREを右クリックします。
選択肢のリストで、「実行」をクリックします。
「PL/SQLの実行」ウィンドウが開きます。「PL/SQLブロック」フレームには、次のコードが含まれます。
v_Return := CALCULATE_SCORE (
CAT => CAT,
SCORE => SCORE,
WEIGHT => WEIGHT
);
SCOREおよびWEIGHTの値を、それぞれ8および0.2に変更します。
v_Return := CALCULATE_SCORE (
CAT => CAT,
SCORE => 8,
WEIGHT => 0.2
);
「OK」をクリックします。
「コード」ペインの下に「実行中」ウィンドウが開き、次の結果が表示されます。
Connecting to the database hr_conn. Process exited. Disconnecting from the database hr_conn.
「実行中」タブの右に「出力変数」タブが表示されます。
「出力変数」タブをクリックします。
「変数」および「値」という2つのフレームが表示され、それぞれ<Return Value>および1.6と表示されます。
|
参照: SQL Developerを使用したプロシージャおよびファンクションの実行とデバッグについては、『Oracle SQL Developerユーザーズ・ガイド』を参照してください。 |
スタンドアロン・サブプログラムを削除するには、SQL Developerの「接続」フレームと削除ツール、またはDDL文DROP PROCEDUREまたはDROP FUNCTIONのいずれかを使用します。
|
注意: プロシージャADD_EVALUATIONまたはファンクションCALCULATE_SCOREは今後のチュートリアルで必要なため、削除しないでください。サブプログラムの削除の実習を行う場合は、簡単なサブプログラムを作成してから削除してください。 |
Dropツールを使用してスタンドアロンのサブプログラムを削除するには、次の操作を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「ファンクション」または「プロシージャ」を展開します。
ファンクションまたはプロシージャのリストで、削除するファンクションまたはプロシージャの名前を右クリックします。
選択肢のリストで、「削除」をクリックします。
削除ウィンドウで、「適用」をクリックします。
「確認」ウィンドウで「OK」をクリックします。
|
参照:
|
この項の内容は次のとおりです。
パッケージには必ず仕様部があり、通常、さらに本体があります。
パッケージ仕様は、パッケージを定義し、型、変数、定数、例外、宣言カーソル、およびパッケージ外部から参照される可能性のあるサブプログラムを宣言します。パッケージ仕様はApplication Program Interface (API)です。クライアント・プログラムからサブプログラムを起動するために必要な情報はすべて含まれていますが、それらの実装に関する情報は含まれません。
パッケージ本体には、パッケージ仕様で宣言されている宣言カーソルやサブプログラムについて、対応する問合せやコードを定義します(そのため、宣言カーソルもサブプログラムもないパッケージについては本体は必要ありません)。また、パッケージ本体は、仕様部で宣言されずパッケージの他のサブプログラムでのみ起動できるローカル・サブプログラムも定義できます。パッケージ本体の内容は、クライアント・プログラムに対して非表示です。パッケージ本体は、パッケージをコールするアプリケーションを無効にすることなく変更できます。
|
参照:
|
パッケージ仕様部を作成するには、SQL Developerのパッケージの作成ツールまたはDDL文のCREATE PACKAGEを使用します。
このチュートリアルでは、パッケージの作成ツールを使用して、このドキュメントの多くのチュートリアルおよび例に示されているEMP_EVALというパッケージの仕様を作成する方法について説明します。
パッケージの作成ツールを使用してパッケージ仕様部を作成するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を右クリックします。
選択肢のリストで、「新規パッケージ」をクリックします。
「パッケージの作成」ウィンドウが開きます。「スキーマ」フィールドには値HRが、「名前」フィールドにはデフォルト値PACKAGE1が入り、「新規ソースを小文字で追加」チェック・ボックスは選択解除されています。
「スキーマ」では、デフォルト値のHRを受け入れます。
「名前」では、PACKAGE1をEMP_EVALに変更します。
「OK」をクリックします。
EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。
CREATE OR REPLACE PACKAGE emp_eval AS /* TODO enter package declarations (types, exceptions, methods etc) here */ END emp_eval;
ペインのタイトルがイタリック・フォントになっています。これは、パッケージがデータベースに保存されていないことを示しています。
(オプション)CREATE PACKAGE文で、コメントを宣言に置換します。
「パッケージ仕様の変更」に示すとおり、今この手順を行わない場合、後で行うこともできます。
「ファイル」メニューから、「保存」を選択します。
Oracle Databaseは、パッケージをコンパイルして保存します。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。
|
参照: CREATE PACKAGE文(パッケージ仕様部)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ仕様部を変更するには、SQL Developerの編集ツール、またはOR REPLACE句を持つCREATE PACKAGEDDL文を使用します。
このチュートリアルでは、編集ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの仕様を変更する方法について説明します。具体的には、このチュートリアルでは、プロシージャ、EVAL_DEPARTMENTおよび関数CALCULATE_SCOREの宣言を追加する方法を表示されます。
編集ツールを使用してEMP_EVALパッケージ仕様部を変更するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストで、EMP_EVALを右クリックします。
選択肢のリストで、「編集」をクリックします。
EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。
CREATE OR REPLACE PACKAGE emp_eval AS /* TODO enter package declarations (types, exceptions, methods etc) here */ END emp_eval;
ペインのタイトルがイタリック・フォントになっていません。これは、パッケージがデータベースに保存されていることを示しています。
EMP_EVALペインで、コメントを次のコードに置換します。
PROCEDURE eval_department ( dept_id IN NUMBER );
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER;
EMP_EVALペインのタイトルがイタリック・フォントに変更されます。その変更がまだデータベースに保存されていないことを示しています。
「コンパイル」アイコンをクリックします。
変更されたパッケージ仕様部は、コンパイルされデータベースに保存されます。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。
|
参照: OR REPLACE句が指定されたCREATE PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ本体を作成するには、SQL Developerの本体の作成ツールまたはDDL文のCREATE PACKAGE BODYを使用します。
このチュートリアルでは、本体の作成ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの本体を作成する方法について説明します。
本体の作成ツールを使用してEMP_EVALパッケージの本体を作成するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストで、EMP_EVALを右クリックします。
選択肢のリストで、「本体の作成」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体の自動生成されたコードが表示されます。
CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS
PROCEDURE eval_department(dept_id IN NUMBER) AS
BEGIN
-- TODO implementation required for PROCEDURE EMP_EVAL.eval_department
NULL;
END eval_department;
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
BEGIN
-- TODO implementation required for FUNCTION EMP_EVAL.calculate_score
RETURN NULL;
END calculate_score;
END EMP_EVAL;
ペインのタイトルがイタリック・フォントになっています。コードがデータベースに保存されていないことを示しています。
(オプション)CREATE PACKAGE BODY文で、次の手順を実行します。
コメントを実行可能文に置換します。
(オプション)プロシージャの実行可能部分で、NULLを削除するか、または実行可能文に置換します。
(オプション)ファンクションの実行可能部分で、NULLを別の式に置換します。
「チュートリアル: サブプログラムの変数および定数の宣言」に示すとおり、今この手順を行わない場合、後で行うこともできます。
「コンパイル」アイコンをクリックします。
変更されたパッケージ本体は、コンパイルされデータベースに保存されます。 EMP_EVAL本体ペインのタイトルがイタリック・フォントではなくなります。
|
参照: CREATE PACKAGE BODY文(パッケージ本体)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ(仕様および本体)を削除するには、SQL Developerの「接続」フレームと削除ツールまたはDDL文DROP PACKAGEのいずれかを使用します。
|
注意: パッケージEMP_EVALは今後のチュートリアルで必要なため、削除しないでください。パッケージの削除の実習を行う場合は、簡単なパッケージを作成してから削除してください。 |
削除ツールを使用してパッケージを削除するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストが表示されます。
パッケージのリストで、削除するパッケージの名前を右クリックします。
選択肢のリストで、「パッケージの削除」をクリックします。
削除ウィンドウで、「適用」をクリックします。
「確認」ウィンドウで「OK」をクリックします。
|
参照: DROP PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
PL/SQLがSQLより優れている点の1つは、PL/SQLでは変数および定数を宣言して使用できることです。
パッケージ仕様で宣言された変数または定数は、このパッケージにアクセスしている任意のプログラムで使用可能です。パッケージ本体またはサブプログラムで宣言された変数または定数は、そのパッケージまたはサブプログラムに対してローカルです。
変数には特定のデータ型の値が格納されます。ご使用のプログラムで、実行時に値を変更できます。 定数には、変更できない値が格納されます。
変数または定数には、任意のPL/SQLデータ型を指定できます。変数を宣言する際に初期値を割り当てることができ、割り当てない場合はこの値がNULLになります。定数を宣言するときには初期値を割り当てる必要があります。変数または定数に初期値を割り当てるには、代入演算子(:=)を使用します。
|
ヒント: 変わらないすべての値を定数として宣言します。これによってコンパイル・コードが最適化され、ソース・コードがメンテナンスしやすくなります。 |
この項の内容は次のとおりです。
|
参照: 変数および定数の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
このチュートリアルでは、SQL Developerツールの編集を使用して、(「チュートリアル: パッケージ仕様の作成」で指定した)EMP_EVAL.CALCULATE_SCOREファンクションの変数および定数を宣言する方法を表示します。(また、このチュートリアルはパッケージ本体の変更の例も示します。)
CALCULATE_SCORE関数の変数および定数を宣言するには、次のようにします。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストで、EMP_EVALを展開します。
選択肢のリストで、EMP_EVALの本体を右クリックします。
選択肢のリストが表示されます。
選択肢のリストで、「編集」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。
CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS
PROCEDURE eval_department ( dept_id IN NUMBER ) AS
BEGIN
-- TODO implementation required for PROCEDURE EMP_EVAL.eval_department
NULL;
END eval_department;
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
BEGIN
-- TODO implementation required for FUNCTION EMP_EVAL.calculate_score
RETURN NULL;
END calculate_score;
END EMP_EVAL;
RETURN NUMBER ASとBEGINの間に、次の変数および定数の宣言を追加します。
n_score NUMBER(1,0); -- variable n_weight NUMBER; -- variable max_score CONSTANT NUMBER(1,0) := 9; -- constant, initial value 9 max_weight CONSTANT NUMBER(8,8) := 1; -- constant, initial value 1
EMP_EVAL本体ペインのタイトルがイタリック・フォントに変更されます。コードがまだデータベースに保存されていないことを示しています。
「ファイル」メニューから、「保存」を選択します。
Oracle Databaseは、変更されたパッケージ本体をコンパイルおよび保存します。 EMP_EVAL本体ペインのタイトルがイタリック・フォントではなくなります。
「チュートリアル: サブプログラム内の変数および定数の宣言」の後のEMP_EVAL.CALCULATE_SCOREファンクションのコードは、次のとおりです。
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER )
RETURN NUMBER AS
n_score NUMBER(1,0); -- variable
n_weight NUMBER; -- variable
max_score CONSTANT NUMBER(1,0) := 9; -- constant, initial value 9
max_weight CONSTANT NUMBER(8,8) := 1; -- constant, initial value 1
BEGIN
-- TODO implementation required for FUNCTION EMP_EVAL.calculate_score
RETURN NULL;
END calculate_score;
関数の変数、定数およびパラメータは、「表の作成」で作成した表SCORESおよびPERFORMANCE_PARTSの値を表します。
変数n_scoreは、SCORE.SCORES列の値を保持し、定数max_scoreは、その値と比較されます。
変数n_weightは、PERFORMANCE_PARTS.WEIGHT列の値を保持し、定数max_weightは、その値と比較されます。
パラメータevaluation_idは、SCORE.EVALUATION_ID列の値を保持します。
パラメータperformance_idは、SCORE.PERFORMANCE_ID列の値を保持します。
このため、各変数、定数およびパラメータのデータ型は、対応する列と同じです。
列のデータ型が変更されたら、変数、定数およびパラメータのデータ型も同じデータ型に変わる必要があります。そうでないと、CALCULATE_SCOREファンクションが無効になります。
変数、定数およびパラメータのデータ型が常に列のデータ型と一致することを確認するには、これらを%TYPE属性で宣言します。 %TYPE属性は、表の列または別の変数のデータ型を提供し、正しいデータ型の割当てを保証します。
|
参照:
|
このチュートリアルでは、SQL Developerツールの「編集」を使用して、(「チュートリアル: サブプログラムの変数および定数の宣言」で示した)EMP_EVAL.CALCULATE_SCOREファンクションの変数、定数および仮パラメータの宣言を変更して%TYPE属性を使用する方法を示します。
CALCULATE_SCOREの宣言を変更して%TYPEを使用するには、次のようにします。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストで、EMP_EVALを展開します。
選択肢のリストで、EMP_EVALの本体を右クリックします。
選択肢のリストで、「編集」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。
CREATE OR REPLACE
PACKAGE BODY emp_eval AS
PROCEDURE eval_department ( dept_id IN NUMBER ) AS
BEGIN
-- TODO implementation required for PROCEDURE EMP_EVAL.eval_department
NULL;
END eval_department;
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER )
RETURN NUMBER AS
n_score NUMBER(1,0); -- variable
n_weight NUMBER; -- variable
max_score CONSTANT NUMBER(1,0) := 9; -- constant, initial value 9
max_weight CONSTANT NUMBER(8,8) := 1; -- constant, initial value 1
BEGIN
-- TODO implementation required for FUNCTION EMP_EVAL.calculate_score
RETURN NULL;
END calculate_score;
END emp_eval;
ファンクションのコードに、太字で示された変更を加えます。
FUNCTION calculate_score ( evaluation_id IN SCORES.EVALUATION_ID%TYPE , performance_id IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER AS n_score SCORES.SCORE%TYPE; n_weight PERFORMANCE_PARTS.WEIGHT%TYPE; max_score CONSTANT SCORES.SCORE%TYPE := 9; max_weight CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
EMP_EVALを右クリックします。
選択肢のリストで、「編集」をクリックします。
EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。
CREATE OR REPLACE PACKAGE EMP_EVAL AS
PROCEDURE eval_department(dept_id IN NUMBER);
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER;
END EMP_EVAL;
ファンクションのコードに、太字で示された変更を加えます。
FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE , performance_id IN scores.performance_id%TYPE)
EMP_EVALを右クリックします。
選択肢のリストで、「コンパイル」をクリックします。
EMP_EVAL Bodyを右クリックします。
選択肢のリストで、「コンパイル」をクリックします。
次の方法で変数に値を割り当てることができます。
代入演算子を使用して、式の値を割り当てます。
SELECT INTO文またはFETCH文を使用して、表の値を割り当てます。
OUTパラメータまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で値を代入する方法。
変数を値にバインドします。
この項の内容は次のとおりです。
|
参照:
|
代入演算子(:=)を使用して、サブプログラムの宣言部分または実行可能部分の変数に式の値を割り当てることができます。
サブプログラムの宣言部分では、宣言時に、変数に初期値を割り当てることができます。次に構文を示します。
variable_name data_type := expression;
サブプログラムの実行可能部分では、代入文によって変数に値を割り当てることができます。次に構文を示します。
variable_name := expression;
例5-1では、EMP_EVAL.CALCULATE_SCOREファンクションに対して行う変更が太字で表示されて、変数running_totalが追加され、この新しい変数がファンクションの戻り値として使用されます。代入演算子は、ファンクションの宣言部と実行可能部の両方に表示されます。(running_totalのデータ型は、異なる精度およびスケールを持つ2つのNUMBER値の積を保持するため、SCORES.SCORE%TYPEまたはPERFORMANCE_PARTS.WEIGHT%TYPEではなく、NUMBERである必要があります。)
例5-1 代入演算子を使用した変数への値の割当て
FUNCTION calculate_score(evaluation_id IN SCORES.EVALUATION_ID%TYPE
, performance_id IN SCORES.PERFORMANCE_ID%TYPE)
RETURN NUMBER AS
n_score SCORES.SCORE%TYPE;
n_weight PERFORMANCE_PARTS.WEIGHT%TYPE;
running_total NUMBER := 0;
max_score CONSTANT SCORES.SCORE%TYPE := 9;
max_weight CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE:= 1;
BEGIN
running_total := max_score * max_weight;
RETURN running_total;
END calculate_score;
|
参照:
|
サブプログラムまたはパッケージの表の値を使用するには、SELECT INTO文によって変数に値を割り当てる必要があります。
例5-2では、表の値からrunning_totalを計算させるためにEMP_EVAL.CALCULATE_SCOREファンクションに対して加える変更を太字で示しています。
例5-2 SELECT INTOを使用した変数への表の値の割当て
FUNCTION calculate_score ( evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE )
RETURN NUMBER AS
n_score scores.score%TYPE;
n_weight performance_parts.weight%TYPE;
running_total NUMBER := 0;
max_score CONSTANT scores.score%TYPE := 9;
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
BEGIN
SELECT s.score INTO n_score
FROM SCORES s
WHERE evaluation_id = s.evaluation_id
AND performance_id = s.performance_id;
SELECT p.weight INTO n_weight
FROM PERFORMANCE_PARTS p
WHERE performance_id = p.performance_id;
running_total := n_score * n_weight;
RETURN running_total;
END calculate_score;
例5-3に示すADD_EVALプロシージャは、EVALUATIONS表への行挿入に、EMPLOYEES表の対応する行の値を使用する場合の例です。ADD_EVALプロシージャは、EMP_EVALパッケージの本体にのみ追加し、仕様には追加しません。ADD_EVALは仕様内には定義しないので、そのパッケージのローカル・プロシージャになり、パッケージ内の他のサブプログラムからのみ起動でき、パッケージ外部からは起動できません。
例5-3 他の表からの値を使用した表の行の挿入
PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
, today IN DATE )
AS
job_id EMPLOYEES.JOB_ID%TYPE;
manager_id EMPLOYEES.MANAGER_ID%TYPE;
department_id EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
INSERT INTO EVALUATIONS (
evaluation_id,
employee_id,
evaluation_date,
job_id,
manager_id,
department_id,
total_score
)
SELECT
evaluations_sequence.NEXTVAL, -- evaluation_id
add_eval.employee_id, -- employee_id
add_eval.today, -- evaluation_date
e.job_id, -- job_id
e.manager_id, -- manager_id
e.department_id, -- department_id
0 -- total_score
FROM employees e;
IF SQL%ROWCOUNT = 0 THEN
RAISE NO_DATA_FOUND;
END IF;
END add_eval;
|
参照: SELECT INTO文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
入力の順序に従って文を実行するSQLとは異なり、PL/SQLには、プログラム・フローを制御できる制御文があります。
この項の内容は次のとおりです。
PL/SQLには、次の3つのカテゴリの制御文があります。
条件付き選択文は、異なるデータ値に対して異なる文を実行します。
条件選択文は、IFおよびCASEです。
繰り返し文は、FOR LOOP、WHILE LOOPおよび基本のLOOPです。
EXIT文は、制御をループの終わりに転送します。 CONTINUE文は、現在のループの反復を終了し、制御を次の反復に転送します。 EXITおよびCONTINUEには、オプションのWHEN句があり、条件を指定できます。
順次制御文は、指定されたラベル付き文に移動するか、または何も処理をしません。
順次制御文は、GOTOおよびNULLです。
|
参照: PL/SQL制御文の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
IF文は、ブール式の値に応じて一連の文を実行またはスキップします。
IF文の構文は、次のとおりです。
IF boolean_expression THEN statement [, statement ] [ ELSIF boolean_expression THEN statement [, statement ] ]... [ ELSE statement [, statement ] ] END IF;
企業が雇用の最初の10年は1年に2回、その後は1年に1回のみ、従業員を評価するとします。これには従業員の評価頻度を戻すファンクションが必要です。この場合、例5-4のように、IF文を使用してファンクションの戻り値を判断できます。
EVAL_FREQUENCY関数をEMP_EVALパッケージの本体に追加しますが、仕様には追加しません。EVAL_FREQUENCYは、仕様にはないため、パッケージに対してローカルであり、パッケージ内の他のサブプログラムでのみ起動でき、パッケージ外からは起動できません。
例5-4 ファンクションの戻り値を判断するIF文
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER AS h_date EMPLOYEES.HIRE_DATE%TYPE; today EMPLOYEES.HIRE_DATE%TYPE; eval_freq PLS_INTEGER; BEGIN SELECT SYSDATE INTO today FROM DUAL; SELECT HIRE_DATE INTO h_date FROM EMPLOYEES WHERE EMPLOYEE_ID = eval_frequency.emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; ELSE eval_freq := 2; END IF; RETURN eval_freq; END eval_frequency;
|
参照:
|
CASE文は、一連の条件から選択し、対応する文を実行します。
単純なCASE文は、1つの式を評価し、それをいくつかの潜在的な値と比較します。構文は次のとおりです。
CASE expression WHEN value THEN statement [ WHEN value THEN statement ]... [ ELSE statement [, statement ]... ] END CASE;
検索CASE文は、複数のブール式を評価し、値がTRUEとなった最初の式を選択します。検索CASE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
|
ヒント: CASE文またはネストされたIF文のいずれも使用できる場合、CASE文を使用すると、より読みやすく効率的です。 |
従業員が1年に1回のみ評価され、JOB_IDに応じて昇給を提案するEVAL_FREQUENCY関数が必要だと仮定します。
例5-5に太字で示されているように、EVAL_FREQUENCYファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。)
例5-5 出力する文字列を判断するCASE文
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER AS h_date EMPLOYEES.HIRE_DATE%TYPE; today EMPLOYEES.HIRE_DATE%TYPE; eval_freq PLS_INTEGER; j_id EMPLOYEES.JOB_ID%TYPE; BEGIN SELECT SYSDATE INTO today FROM DUAL; SELECT HIRE_DATE, JOB_ID INTO h_date, j_id FROM EMPLOYEES WHERE EMPLOYEE_ID = eval_frequency.emp_id; IF ((h_date + (INTERVAL '12' MONTH)) < today) THEN eval_freq := 1; CASE j_id WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 8% salary increase for employee # ' || emp_id); WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 7% salary increase for employee # ' || emp_id); WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 6% salary increase for employee # ' || emp_id); WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 5% salary increase for employee # ' || emp_id); WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 5% salary increase for employee # ' || emp_id); WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE( 'Consider 4% salary increase for employee # ' || emp_id); ELSE DBMS_OUTPUT.PUT_LINE( 'Nothing to do for employee #' || emp_id); END CASE; ELSE eval_freq := 2; END IF; RETURN eval_freq; END eval_frequency;
|
参照:
|
FOR LOOP文は、lower_boundからupper_boundまでの範囲の各整数に対して1回ずつ、一連の文を繰り返します。次に構文を示します。
FOR counter IN lower_bound..upper_bound LOOP statement [, statement ]... END LOOP;
LOOPとEND LOOP間の文では、counterを使用できますが、値は変更できません。
給与の値上げを想定するだけでなく、EVAL_FREQUENCYファンクションを使用して、5年間で毎年推定額が増加した場合に給与がどう変わるかをレポートするとします。
例5-6に太字で示されているように、EVAL_FREQUENCYファンクションを変更します。(文字列DBMS_OUTPUT.PUT_LINEを出力するプロシージャの詳細は、Oracle Database PL/SQLパッケージおよびタイプ・リファレンスを参照してください。)
例5-6 5年後の給与を計算するFOR LOOP文
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER AS h_date EMPLOYEES.HIRE_DATE%TYPE; today EMPLOYEES.HIRE_DATE%TYPE; eval_freq PLS_INTEGER; j_id EMPLOYEES.JOB_ID%TYPE; sal EMPLOYEES.SALARY%TYPE; sal_raise NUMBER(3,3) := 0; BEGIN SELECT SYSDATE INTO today FROM DUAL; SELECT HIRE_DATE, JOB_ID, SALARY INTO h_date, j_id, sal FROM EMPLOYEES WHERE EMPLOYEE_ID = eval_frequency.emp_id; IF ((h_date + (INTERVAL '12' MONTH)) < today) THEN eval_freq := 1; CASE j_id WHEN 'PU_CLERK' THEN sal_raise := 0.08; WHEN 'SH_CLERK' THEN sal_raise := 0.07; WHEN 'ST_CLERK' THEN sal_raise := 0.06; WHEN 'HR_REP' THEN sal_raise := 0.05; WHEN 'PR_REP' THEN sal_raise := 0.05; WHEN 'MK_REP' THEN sal_raise := 0.04; ELSE NULL; END CASE; IF (sal_raise != 0) THEN BEGIN DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' || ROUND((sal_raise * 100),0) || '% each year for 5 years, it will be:'); FOR i IN 1..5 LOOP sal := sal * (1 + sal_raise); DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2) || ' after ' || i || ' year(s)'); END LOOP; END; END IF; ELSE eval_freq := 2; END IF; RETURN eval_freq; END eval_frequency;
|
参照:
|
WHILE LOOP文は、条件がTRUEであるかぎり一連の文を繰り返します。次に構文を示します。
WHILE condition LOOP statement [, statement ]... END LOOP;
|
注意: LOOPとEND LOOPの間の文によってconditionがFALSEにならない場合、WHILE LOOP文は無限に実行され続けます。 |
EVAL_FREQUENCYファンクションでFOR LOOP文ではなくWHILE LOOP文を使用して、推奨した給与がJOB_IDの最大給与を超過したときに停止するようにするとします。
例5-7に太字で示されているように、EVAL_FREQUENCYファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。)
例5-7 最大値まで給与を計算するWHILE LOOP文
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER AS h_date EMPLOYEES.HIRE_DATE%TYPE; today EMPLOYEES.HIRE_DATE%TYPE; eval_freq PLS_INTEGER; j_id EMPLOYEES.JOB_ID%TYPE; sal EMPLOYEES.SALARY%TYPE; sal_raise NUMBER(3,3) := 0; sal_max JOBS.MAX_SALARY%TYPE; BEGIN SELECT SYSDATE INTO today FROM DUAL; SELECT HIRE_DATE, j.JOB_ID, SALARY, MAX_SALARY INTO h_date, j_id, sal, sal_max FROM EMPLOYEES e, JOBS j WHERE EMPLOYEE_ID = eval_frequency.emp_id AND JOB_ID = eval_frequency.j_id; IF ((h_date + (INTERVAL '12' MONTH)) < today) THEN eval_freq := 1; CASE j_id WHEN 'PU_CLERK' THEN sal_raise := 0.08; WHEN 'SH_CLERK' THEN sal_raise := 0.07; WHEN 'ST_CLERK' THEN sal_raise := 0.06; WHEN 'HR_REP' THEN sal_raise := 0.05; WHEN 'PR_REP' THEN sal_raise := 0.05; WHEN 'MK_REP' THEN sal_raise := 0.04; ELSE NULL; END CASE; IF (sal_raise != 0) THEN BEGIN DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' || ROUND((sal_raise * 100),0) || '% each year, it will be:'); WHILE sal <= sal_max LOOP sal := sal * (1 + sal_raise); DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2)); END LOOP; DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max); END; END IF; ELSE eval_freq := 2; END IF; RETURN eval_freq; END eval_frequency;
|
参照:
|
基本のLOOP文は、一連の文を繰り返します。次に構文を示します。
LOOP statement [, statement ]... END LOOP;
少なくとも1つの文は、EXIT文である必要があります。そうでない場合、LOOP文は無限に実行され続けます。
EXIT WHEN文(オプションのWHEN句を持つEXIT文)は、条件がTRUEのときにループを終了し、制御をループの終わりに転送します。
EVAL_FREQUENCYファンクションでは、WHILE LOOP文の最後の反復で、通常、最後に計算された値が最大給与を超過します。
例5-8に示すように、WHILE LOOP文を、EXIT WHEN文を含む基本のLOOP文に変更します。
例5-8 EXIT WHEN文の使用
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN PLS_INTEGER
AS
h_date EMPLOYEES.HIRE_DATE%TYPE;
today EMPLOYEES.HIRE_DATE%TYPE;
eval_freq PLS_INTEGER;
j_id EMPLOYEES.JOB_ID%TYPE;
sal EMPLOYEES.SALARY%TYPE;
sal_raise NUMBER(3,3) := 0;
sal_max JOBS.MAX_SALARY%TYPE;
BEGIN
SELECT SYSDATE INTO today FROM DUAL;
SELECT HIRE_DATE, j.JOB_ID, SALARY, MAX_SALARY INTO h_date, j_id, sal, sal_max
FROM EMPLOYEES e, JOBS j
WHERE EMPLOYEE_ID = eval_frequency.emp_id AND JOB_ID = eval_frequency.j_id;
IF ((h_date + (INTERVAL '12' MONTH)) < today) THEN
eval_freq := 1;
CASE j_id
WHEN 'PU_CLERK' THEN sal_raise := 0.08;
WHEN 'SH_CLERK' THEN sal_raise := 0.07;
WHEN 'ST_CLERK' THEN sal_raise := 0.06;
WHEN 'HR_REP' THEN sal_raise := 0.05;
WHEN 'PR_REP' THEN sal_raise := 0.05;
WHEN 'MK_REP' THEN sal_raise := 0.04;
ELSE NULL;
END CASE;
IF (sal_raise != 0) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
ROUND((sal_raise * 100),0) ||
'% each year, it will be:');
LOOP
sal := sal * (1 + sal_raise);
EXIT WHEN sal > sal_max;
DBMS_OUTPUT.PUT_LINE(ROUND(sal,2));
END LOOP;
DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
END;
END IF;
ELSE
eval_freq := 2;
END IF;
RETURN eval_freq;
END eval_frequency;
|
参照:
|
この項の内容は次のとおりです。
|
参照: レコードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
レコードは、C、C++、Javaなどのstruct型に似た、異なる型のデータ値を格納できるPL/SQLの複合変数です。レコードの内部コンポーネントは、フィールドと呼ばれます。レコード・フィールドにアクセスするには、ドット表記法record_name.field_nameを使用します。
レコード・フィールドは、スカラー変数のように扱うことができます。サブプログラム・パラメータとしてレコード全体を渡すこともできます。
レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。各レコード・フィールドは表の列に対応しています。
レコード型を宣言し、その型の変数を宣言する。
次に構文を示します。
TYPE record_name IS RECORD ( field_name data_type [:= initial_value] [, field_name data_type [:= initial_value ] ]... ); variable_name record_name;
レコードのフィールドの名前およびデータ型は、表の列と同じです。
cursor_name%ROWTYPE型の変数を宣言します。
レコードのフィールドの名前およびデータ型は、カーソルSELECT文のFROM句の表の列と同じです。
|
参照:
|
このチュートリアルでは、SQL Developerの編集ツールを使用してsal_infoというRECORD型を宣言する方法について説明します。この型には、各従業員の給与情報(ジョブID、最小および最大給与、現在の給与、推奨される増加額など)を保持するためのフィールドを指定します。
RECORD型sal_infoを宣言するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「パッケージ」を展開します。
パッケージのリストが表示されます。
EMP_EVALを右クリックします。
選択肢のリストが表示されます。
「編集」をクリックします。
EMP_EVALペインが開き、パッケージを作成したCREATE PACKAGE文が表示されます。
CREATE OR REPLACE PACKAGE EMP_EVAL AS
PROCEDURE eval_department(dept_id IN NUMBER);
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER;
END EMP_EVAL;
EMP_EVALペインで、END EMP_EVALの直前に次のコードを追加します。
TYPE sal_info IS RECORD ( j_id jobs.job_id%type , sal_min jobs.min_salary%type , sal_max jobs.max_salary%type , sal employees.salary%type , sal_raise NUMBER(3,3) );
ペインのタイトルがイタリック・フォントになっています。これは、変更がデータベースに保存されていないことを示しています。
「コンパイル」アイコンをクリックします。
変更されたパッケージ仕様部は、コンパイルされデータベースに保存されます。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。
これで、「チュートリアル: レコード・パラメータによるサブプログラムの作成および起動」に示すようにsal_info型のレコードを宣言できます。
このチュートリアルでは、RECORD型sal_infoを「チュートリアル: RECORD型の宣言」で宣言した場合に、SQL Developerの編集ツールを使用して、次の手順を実行する方法を示します。
型sal_infoのパラメータを持つプロシージャSALARY_SCHEDULEを作成します。
EVAL_FREQUENCYファンクションを変更して、レコードemp_salとその型sal_infoを宣言し、このフィールドを移入して、SALARY_SCHEDULEプロシージャに渡します。
SALARY_SCHEDULEはEVAL_FREQUENCYによって起動されるため、SALARY_SCHEDULEの宣言は、EVAL_FREQUENCYの宣言の前に行う必要があります(そうでない場合、パッケージはコンパイルしません)。ただし、SALARY_SCHEDULEの宣言は、パッケージ本体のどこで実行しても構いません。
SALARY_SCHEDULEを作成して、EVAL_FREQUENCYを変更するには、次の手順を実行します。
「接続」フレームで、hr_connを展開します。
スキーマ・オブジェクト・タイプのリストで、「パッケージ」を展開します。
パッケージのリストで、EMP_EVALを展開します。
選択肢のリストで、EMP_EVALの本体を右クリックします。
選択肢のリストで、「編集」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。
EMP_EVAL本体ペインで、END EMP_EVALの直前にSALARY_SCHEDULEプロシージャのこの定義を追加します。
PROCEDURE salary_schedule (emp IN sal_info) AS
accumulating_sal NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('If salary ' || emp.sal ||
' increases by ' || ROUND((emp.sal_raise * 100),0) ||
'% each year, it will be:');
accumulating_sal := emp.sal;
WHILE accumulating_sal <= emp.sal_max LOOP
accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
DBMS_OUTPUT.PUT_LINE(ROUND(accumulating_sal,2) ||', ');
END LOOP;
END salary_schedule;
ペインのタイトルがイタリック・フォントになっています。これは、変更がデータベースに保存されていないことを示しています。
EMP_EVAL本体ペインで、太字で示されたコードを次の位置に入力します。
CREATE OR REPLACE PACKAGE BODY EMP_EVAL AS FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER; PROCEDURE salary_schedule(emp IN sal_info); PROCEDURE add_eval(employee_id IN employees.employee_id%type, today IN DATE); PROCEDURE eval_department (dept_id IN NUMBER) AS
EVAL_FREQUENCYファンクションを編集し、太字フォントで示されている箇所を変更します。
FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE) RETURN PLS_INTEGER AS h_date EMPLOYEES.HIRE_DATE%TYPE; today EMPLOYEES.HIRE_DATE%TYPE; eval_freq PLS_INTEGER; emp_sal SAL_INFO; -- replaces sal, sal_raise, and sal_max BEGIN SELECT SYSDATE INTO today FROM DUAL; SELECT HIRE_DATE INTO h_date FROM EMPLOYEES WHERE EMPLOYEE_ID = eval_frequency.emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; /* populate emp_sal */ SELECT j.JOB_ID, j.MIN_SALARY, j.MAX_SALARY, e.SALARY INTO emp_sal.j_id, emp_sal.sal_min, emp_sal.sal_max, emp_sal.sal FROM EMPLOYEES e, JOBS j WHERE e.EMPLOYEE_ID = eval_frequency.emp_id AND j.JOB_ID = eval_frequency.emp_id; emp_sal.sal_raise := 0; -- default CASE emp_sal.j_id WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08; WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07; WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06; WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05; WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05; WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04; ELSE NULL; END CASE; IF (emp_sal.sal_raise != 0) THEN salary_schedule(emp_sal); END IF; ELSE eval_freq := 2; END IF; RETURN eval_freq; END eval_frequency;
「コンパイル」をクリックします。
Oracle Databaseは、SQL文の実行時に、結果セットおよび処理情報を無名のプライベートSQL領域に保存します。この名前のない領域へのポインタは、カーソルと呼ばれ、これを使用して結果セットを1行ずつ取得することができます。 カーソル属性は、カーソルの状態に関する情報を戻します。
SQL DML文またはPL/SQL SELECT INTO文を実行するたびに、PL/SQLは暗黙カーソルをオープンします。このカーソルに関する情報は属性から得られますが、制御はできません。文の実行後、データベースはカーソルをクローズしますが、属性の値は別のDMLまたはSELECT INTO文が実行されるまで使用可能です。
PL/SQLを使用すれば、カーソルも宣言できます。宣言カーソルには名前があり、通常、複数行が返される問合せ(SQL SELECT文)に関連付けられています。カーソルを宣言した後、暗黙的または明示的に処理する必要があります。カーソルを暗黙的に処理するには、カーソルFOR LOOPを使用します。次に構文を示します。
FOR record_name IN cursor_name LOOP statement [ statement ]... END LOOP;
カーソルを明示的に処理するには、カーソルを開き(OPEN文)、結果セットから1行ずつまたは一括して行をフェッチし(FETCH文)、カーソルを閉じます(CLOSE文)。カーソルのクローズ後は、結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。
暗黙カーソル属性の値の構文は、SQL属性(SQL%FOUNDなど)です。 SQL属性は常に、最後に実行されたDMLまたはSELECT INTO文を参照します。
宣言カーソルの属性値の構文は、属性の直前にcursor_nameが来ます(たとえば、c1%FOUND)。
表5-1に、カーソル属性および戻すことのできる値のリストを示します。(暗黙カーソルには、このマニュアルの範囲外の追加属性があります。)
表5-1 カーソル属性の値
| 属性 | 宣言カーソルの値 | 暗黙カーソルの値 |
|---|---|---|
|
カーソルは開くが脚注1フェッチが試行されない場合は、 最後のフェッチが行を戻した場合、 最後のフェッチが行を戻さなかった場合、 |
DMLまたは 最後のDMLまたは 最後のDMLまたは |
|
|
カーソルは開くが脚注1フェッチが試行されない場合は、 最後のフェッチが行を戻した場合、 最後のフェッチが行を戻さなかった場合、 |
DMLまたは 最後のDMLまたは 最後のDMLまたは |
|
|
カーソルが開く場合脚注1、0以上の数字です。 |
DMLまたは |
|
|
カーソルがオープンされている場合、 |
常に |
脚注1カーソルが開いていない場合、属性は事前定義済の例外INVALID_CURSORを発生します。
|
参照:
|
次の手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。
宣言カーソルを使用して、結果セットの行を1行ずつ取得するには、次の手順を実行します。
宣言部分で、次の手順を実行します。
カーソルを宣言します。
CURSOR cursor_name IS query;
宣言カーソルの宣言構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソルによって戻された行を格納するレコードを宣言します。
record_name cursor_name%ROWTYPE;
%ROWTYPE構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
実行可能部分で、次の手順を実行します。
カーソルをオープンします。
OPEN cursor_name;
OPEN文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
次に、似た構文を持つLOOP文を使用して、カーソルから行(結果セットからの行)を1つずつフェッチします。
LOOP FETCH cursor_name INTO record_name; EXIT WHEN cursor_name%NOTFOUND; -- Process row that is in record_name: statement; [ statement; ]... END LOOP;
FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソルをクローズします。
CLOSE cursor_name;
CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
このチュートリアルでは、宣言カーソルemp_cursorを使用するプロシージャEMP_EVAL.EVAL_DEPARTMENTの実装方法を示します。
EMP_EVAL.EVAL_DEPARTMENTプロシージャを実装するには、次の手順を実行します。
EMP_EVALパッケージ仕様部で、太字で示されているように、EVAL_DEPARTMENTプロシージャの宣言を変更します。
PROCEDURE eval_department(dept_id IN employees.department_id%TYPE);
EMP_EVALパッケージ本体で、太字で示されているように、EVAL_DEPARTMENTプロシージャの定義を変更します。
PROCEDURE eval_department (dept_id IN employees.department_id%TYPE) AS CURSOR emp_cursor IS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = eval_department.dept_id; emp_record EMPLOYEES%ROWTYPE; -- for row returned by cursor all_evals BOOLEAN; -- true if all employees in dept need evaluations today DATE; BEGIN today := SYSDATE; IF (EXTRACT(MONTH FROM today) < 6) THEN all_evals := FALSE; -- only new employees need evaluations ELSE all_evals := TRUE; -- all employees need evaluations END IF; OPEN emp_cursor; DBMS_OUTPUT.PUT_LINE ( 'Determining evaluations necessary in department # ' || dept_id ); LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; IF all_evals THEN add_eval(emp_record.employee_id, today); ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN add_eval(emp_record.employee_id, today); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.'); CLOSE emp_cursor; END eval_department;
(パッケージ本体を変更する手順の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
EMP_EVALパッケージ仕様部をコンパイルします。
EMP_EVALパッケージ本体をコンパイルします。
カーソル変数はカーソルに似ていますが(「カーソルについて」を参照)、1つの問合せに限定されない点が異なります。カーソル変数を問合せに対してオープンし、結果セットを処理した後、カーソル変数を別の問合せのために使用できます。カーソル変数は、問合せ結果をサブプログラム間で受け渡すのに便利です。
カーソル変数を宣言するには、REF CURSOR型を宣言し、その型の変数を宣言します(このため、カーソル変数はREF CURSORと呼ばれることもあります)。REF CURSOR型には強弱があります。
強い REF CURSOR 型は、カーソル変数のRECORD型である戻り型を指定します。PL/SQLコンパイラでは、これらの強い型指定のカーソル変数を、戻り型と異なる行を戻す問合せに使用できません。強いREF CURSOR型は、弱い型よりもエラー発生の可能性が少なく、弱い型はより柔軟です。
弱い REF CURSOR 型は、戻り型を指定しません。PL/SQLコンパイラでは、弱い型指定のカーソル変数をすべての問合せに使用できます。弱いREF CURSOR型は置換可能なため、弱いREF CURSOR型を作成するかわりに、事前定義型の、弱いカーソル型SYS_REFCURSORを使用できます。
カーソル変数を宣言した後、(OPEN FOR文を使用して)特定の問合せに対して変数をオープンし、(FETCH文を使用して)結果セットから行を1つずつフェッチしてから、(CLOSE文を使用して)カーソルをクローズするか、または(OPEN FOR文を使用して)別の特定の問合せに対してオープンする必要があります。カーソル変数を別の問合せに対してオープンすると、前の問合せに対してはクローズされます。特定の問合せに対してカーソル変数をクローズした後は、その問合せの結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。
|
参照:
|
次の手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。
カーソル変数を使用して結果セット行を1つずつ取得するには、次の手順を実行します。
宣言部分で、次の手順を実行します。
REF CURSOR型を宣言します。
TYPE cursor_type IS REF CURSOR [ RETURN return_type ];
REF CURSOR型宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
その型のカーソル変数を宣言します。
cursor_variable cursor_type;
カーソル変数宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソルによって戻された行を格納するレコードを宣言します。
record_name return_type;
レコード宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
実行可能部分で、次の手順を実行します。
カーソル変数を特定の問合せに対してオープンします。
OPEN cursor_variable FOR query;
OPEN FOR文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
次に、似た構文を持つLOOP文を使用して、カーソル変数から行(結果セットからの行)を1つずつフェッチします。
LOOP FETCH cursor_variable INTO record_name; EXIT WHEN cursor_variable%NOTFOUND; -- Process row that is in record_name: statement; [ statement; ]... END LOOP;
FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソル変数をクローズします。
CLOSE cursor_variable;
または、カーソル変数を別の問合せに対してオープンすることで、現在の問合せに対してクローズすることもできます。
CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
このチュートリアルでは、EMP_EVAL.EVAL_DEPARTMENTプロシージャを変更して、宣言カーソルのかわりにカーソル変数を使用して複数の部門を処理する方法を示します。変更には、カーソル変数を使用するプロシージャの追加が含まれます。
また、このチュートリアルでは、EMP_EVAL.EVAL_DEPARTMENTおよびEMP_EVAL.ADD_EVALのより効率的な使用方法も示します。レコードの1つのフィールドをADD_EVALに渡して、ADD_EVALで同じレコード内の他の3つのフィールドを抽出する3つの問合せを使用するかわりに、EVAL_DEPARTMENTはレコード全体をADD_EVALに渡し、ADD_EVALは、ドット表記法を使用して他の3つのフィールドの値にアクセスします。
EMP_EVAL.EVAL_DEPARTMENTプロシージャを変更して、カーソル変数を使用するには、次の手順を実行します。
EMP_EVALパッケージ仕様部で、太字で示されているように、プロシージャの宣言およびREF CURSOR型の定義を追加します。
CREATE OR REPLACE PACKAGE emp_eval AS PROCEDURE eval_department (dept_id IN employees.department_id%TYPE); PROCEDURE eval_everyone; FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE , perf_id IN scores.performance_id%TYPE) RETURN NUMBER; TYPE SAL_INFO IS RECORD ( j_id jobs.job_id%type , sal_min jobs.min_salary%type , sal_max jobs.max_salary%type , salary employees.salary%type , sal_raise NUMBER(3,3)); TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE; END emp_eval;
EMP_EVALパッケージ本体で、次に太字フォントで示されているように、プロシージャEVAL_LOOP_CONTROLの前の宣言を追加し、プロシージャADD_EVALの宣言を編集します。
CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS
FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN PLS_INTEGER;
PROCEDURE salary_schedule(emp IN sal_info);
PROCEDURE add_eval(emp_record IN EMPLOYEES%ROWTYPE, today IN DATE);
PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
...
(パッケージ本体を変更する手順の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
次に太字フォントで示されているように、部門ごとの3つの結果セットを取得し、EVAL_LOOP_CONTROLプロシージャを起動するよう、EVAL_DEPARTMENTプロシージャを変更します。
PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS emp_cursor emp_refcursor_type; current_dept departments.department_id%TYPE; BEGIN current_dept := dept_id; FOR loop_c IN 1..3 LOOP OPEN emp_cursor FOR SELECT * FROM employees WHERE current_dept = eval_department.dept_id; DBMS_OUTPUT.PUT_LINE ('Determining necessary evaluations in department #' || current_dept); eval_loop_control(emp_cursor); DBMS_OUTPUT.PUT_LINE ('Processed ' || emp_cursor%ROWCOUNT || ' records.'); CLOSE emp_cursor; current_dept := current_dept + 10; END LOOP; END eval_department;
次に太字フォントで示されているように、ADD_EVALを変更します。
PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE) AS -- (Delete local variables) BEGIN INSERT INTO EVALUATIONS ( evaluation_id, employee_id, evaluation_date, job_id, manager_id, department_id, total_score ) VALUES ( evaluations_sequence.NEXTVAL, -- evaluation_id emp_record.employee_id, -- employee_id today, -- evaluation_date emp_record.job_id, -- job_id emp_record.manager_id, -- manager_id emp_record.department_id, -- department_id 0 -- total_score ); END add_eval;
END EMP_EVALの前に、結果セットから個々のレコードをフェッチして処理する、次のプロシージャを追加します。
PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
emp_record EMPLOYEES%ROWTYPE;
all_evals BOOLEAN;
today DATE;
BEGIN
today := SYSDATE;
IF (EXTRACT(MONTH FROM today) < 6) THEN
all_evals := FALSE;
ELSE
all_evals := TRUE;
END IF;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
IF all_evals THEN
add_eval(emp_record, today);
ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
add_eval(emp_record, today);
END IF;
END LOOP;
END eval_loop_control;
END EMP_EVALの前に、企業のすべての従業員を含む結果セットを取得する次のプロシージャを追加します。
PROCEDURE eval_everyone AS
emp_cursor emp_refcursor_type;
BEGIN
OPEN emp_cursor FOR SELECT * FROM employees;
DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
eval_loop_control(emp_cursor);
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
END eval_everyone;
EMP_EVALパッケージ仕様部をコンパイルします。
EMP_EVALパッケージ本体をコンパイルします。
連想配列は、コレクションの型です。
この項の内容は次のとおりです。
|
参照: コレクションの詳細は、次を参照してください。
|
コレクションは、1次元配列に似ていて、同じ型の要素を指定された順序で格納するPL/SQLの複合変数です。コレクションの内部コンポーネントは、要素と呼ばれます。各要素には、コレクション内での位置を識別する一意のサブスクリプトがあります。コレクション要素にアクセスするには、サブスクリプト表記法collection_name(element_subscript)を使用します。
コレクション要素は、スカラー変数のように扱うことができます。また、コレクション全体をサブプログラムのパラメータとして渡すこともできます(送信または受信サブプログラムのどちらもスタンドアロンのサブプログラムでない場合)。
コレクション・メソッドは、コレクションに関する情報を戻す、またはコレクション上で動作する埋込みPL/SQLサブプログラムです。コレクション・メソッドを起動するには、ドット表記法collection_name.method_nameを使用します。たとえば、collection_name.COUNTはコレクションの要素の数を戻します。
PL/SQLには、次の3つの型のコレクションがあります。
このマニュアルでは、連想配列のみを説明します。
|
参照:
|
連想配列は、制限のない一連のキーと値のペアです。各キーは一意であり、対応する値を保持する要素のサブスクリプトとして機能します。そのため、配列内の位置がわからなくても、また配列を横断しなくても要素にアクセスできます。
キーのデータ型は、PLS_INTEGERまたはVARCHAR2(length)です。
キーのデータ型がPLS_INTEGERで、連想配列が整数で索引付けされ、稠密(つまり、要素間に差異がない)である場合、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値はNULLの場合もあります)。
キー・タイプがVARCHAR2 (length)の場合、連想配列は文字列(length文字)で索引付けされ、スパースです(つまり、要素間に差異がある可能性があります)。
稠密な連想配列をトラバースする場合、要素間の差異を考慮する必要はありません。スパースな連想配列をトラバースする場合は、要素間の差異を考慮する必要があります。
連想配列の要素に値を割り当てるには、代入演算子を使用できます。
array_name(key) := value
キーが配列にない場合、代入文によって配列にキー-値のペアが追加されます。そうでない場合、文がarray_name(key)の値をvalueに変更します。
連想配列は、データの一時的な格納に便利です。連想配列では、表が必要とするディスク領域やネットワーク操作を使用しません。ただし、連想配列は、データを一時的に格納する用途のため、DML文で操作できません。
パッケージ内で連想配列を宣言し、パッケージ本体の変数に値を割り当てると、連想配列はデータベース・セッションの存続期間中に存在します。そうでない場合は、宣言をしたサブプログラムが存続するかぎり存在します。
|
参照: 連想配列の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
連想配列を宣言するには、連想配列の型を宣言し、その型の変数を宣言します。次に、最も単純な構文を示します。
TYPE array_type IS TABLE OF element_type INDEX BY key_type; array_name array_type;
効率的に連想配列を宣言するには、次の手順でカーソルを使用します。この手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。
カーソルを使用して連想配列を宣言するには、次の手順を実行します。
宣言部分で、次の手順を実行します。
カーソルを宣言します。
CURSOR cursor_name IS query;
宣言カーソルの宣言構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
連想配列型を宣言します。
TYPE array_type IS TABLE OF cursor_name%ROWTYPE
INDEX BY { PLS_INTEGER | VARCHAR2 length }
連想配列型宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
その型の連想配列変数を宣言します。
array_name array_type;
変数宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例5-9では、前述の手順を使用して2つの連想配列employees_jobsおよびjobs_を宣言し、カーソルを使用せずに3つ目の連想配列job_titlesを宣言します。初めの2つの配列は整数によって索引付けされ、3つ目の配列は文字列によって索引付けされます。
|
注意: employees_jobs_cursorの宣言のORDER BY句が、連想配列employee_jobsの要素の格納順序を決定します。 |
例5-9 連想配列の宣言
DECLARE
-- Declare cursor:
CURSOR employees_jobs_cursor IS
SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;
-- Declare associative array type:
TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
INDEX BY PLS_INTEGER;
-- Declare associative array:
employees_jobs employees_jobs_type;
-- Use same procedure to declare another associative array:
CURSOR jobs_cursor IS
SELECT JOB_ID, JOB_TITLE
FROM JOBS;
TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
INDEX BY PLS_INTEGER;
jobs_ jobs_type;
-- Declare associative array without using cursor:
TYPE job_titles_type IS TABLE OF JOBS.JOB_TITLE%TYPE
INDEX BY JOBS.JOB_ID%TYPE; -- jobs.job_id%type is varchar2(10)
job_titles job_titles_type;
BEGIN
NULL;
END;
/
通常、稠密な連想配列を移入する最も効率的な方法は、BULK COLLECT INTO句を含むSELECT文を使用することです。
SELECT文を使用して、スパースな連想配列(「連想配列の宣言」のjob_titlesなど)を移入できません。かわりに、繰り返し文の中の代入文を使用する必要があります。繰り返し文の詳細は、「プログラム・フローの制御」を参照してください。
例5-10では、SELECT文を使用して、整数で索引付けされるemployees_jobsおよびjobs_の連想配列を移入します。次に、FOR LOOP文内部の代入文を使用して、文字列で索引付けされる連想配列job_titlesを移入します。
例5-10 連想配列の移入
-- Declarative part from Example 5-9 goes here. BEGIN -- Populate associative arrays indexed by integer: SELECT FIRST_NAME, LAST_NAME, JOB_ID BULK COLLECT INTO employees_jobs FROM EMPLOYEES ORDER BY JOB_ID, LAST_NAME, FIRST_NAME; SELECT JOB_ID, JOB_TITLE BULK COLLECT INTO jobs_ FROM JOBS; -- Populate associative array indexed by string: FOR i IN 1..jobs_.COUNT() LOOP job_titles(jobs_(i).job_id) := jobs_(i).job_title; END LOOP; END; /
稠密な連想配列(整数による索引付け)には、要素間の差異がなく、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値はNULLの場合もあります)。例5-11のように、FOR LOOP文を使用して稠密な配列を横断できます。
例5-11のFOR LOOP文は、例5-10の実行可能部分に挿入すると、employees_jobs配列を移入するコードの後に、employees_jobs配列の要素を、格納された順序で出力します。格納順序は、employees_jobsを宣言するために使用されたemployees_jobs_cursor宣言のORDER BY句によって決定されます(例5-9を参照)。
FOR LOOP文の上限employees_jobs。COUNTにより、配列内の要素の数を戻すコレクション・メソッドが起動されます。COUNTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例5-11 稠密連想配列の横断
-- Code that populates employees_jobs must precede this code:
FOR i IN 1..employees_jobs.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(employees_jobs(i).first_name, 23) ||
RPAD(employees_jobs(i).last_name, 28) || employees_jobs(i).job_id);
END LOOP;
結果:
William Gietz AC_ACCOUNT Shelley Higgins AC_MGR Jennifer Whalen AD_ASST Steven King AD_PRES Lex De Haan AD_VP Neena Kochhar AD_VP John Chen FI_ACCOUNT ... Jose Manuel Urman FI_ACCOUNT Nancy Greenberg FI_MGR Susan Mavris HR_REP David Austin IT_PROG ... Valli Pataballa IT_PROG Michael Hartstein MK_MAN Pat Fay MK_REP Hermann Baer PR_REP Shelli Baida PU_CLERK ... Sigal Tobias PU_CLERK Den Raphaely PU_MAN Gerald Cambrault SA_MAN ... Eleni Zlotkey SA_MAN Ellen Abel SA_REP ... Clara Vishney SA_REP Sarah Bell SH_CLERK ... Peter Vargas ST_CLERK Adam Fripp ST_MAN ... Matthew Weiss ST_MAN
スパース連想配列(文字列によって索引付けされたもの)は、要素間に差分がある場合があります。例5-12のように、WHILE LOOP文を使用して横断できます。
例5-12でコードを実行し、job_titles配列の要素を出力するには、次の手順を実行します。
例5-9の宣言部分の終わりに、次の変数宣言を挿入します。
i jobs.job_id%TYPE;
例5-12には、job_titles.FIRSTおよびjob_titles.NEXT(i)という2つのコレクション・メソッドの起動が含まれます。job_titles.FIRSTでは、job_titlesの最初の要素が返され、job_titles.NEXT(i)では、iに続くサブスクリプトが返されます。FIRSTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。NEXTの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例5-12 スパース連想配列の横断
/* Declare this variable in declarative part: i jobs.job_id%TYPE; Add this code to the executable part, after code that populates job_titles: */ i := job_titles.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(RPAD(i, 12) || job_titles(i)); i := job_titles.NEXT(i); END LOOP;
結果:
AC_ACCOUNT Public Accountant AC_MGR Accounting Manager AD_ASST Administration Assistant AD_PRES President AD_VP Administration Vice President FI_ACCOUNT Accountant FI_MGR Finance Manager HR_REP Human Resources Representative IT_PROG Programmer MK_MAN Marketing Manager MK_REP Marketing Representative PR_REP Public Relations Representative PU_CLERK Purchasing Clerk PU_MAN Purchasing Manager SA_MAN Sales Manager SA_REP Sales Representative SH_CLERK Shipping Clerk ST_CLERK Stock Clerk ST_MAN Stock Manager
この項の内容は次のとおりです。
|
参照: PL/SQLエラーの処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
PL/SQLコードで実行時エラーが発生すると、例外が発生します。例外が発生するサブプログラム(またはブロック)に例外処理部がある場合、制御が転送されます。そうでない場合は、実行が停止します。(「サブプログラム構造について」に例外処理部の配置の説明があります。)
実行時エラーは、設計障害、コードの誤り、ハードウェア障害およびその他の多くの原因によって発生する可能性があります。
Oracle Databaseには多くの事前定義済の例外があり、プログラムがデータベース・ルールに違反したり、システム依存の限度を超えた場合に自動的に発生します。たとえば、SELECT INTO文で行が返されない場合、事前定義済の例外NO_DATA_FOUNDが発生します。PL/SQLの事前定義済の例外の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
PL/SQLでは、独自の例外を定義(宣言)できます。例外宣言の構文は、次のとおりです。
exception_name EXCEPTION;
事前定義済の例外と異なり、ユーザー定義の例外はRAISE文またはDBMS_STANDARD.RAISE_APPLICATION_ERRORプロシージャを使用して、明示的に発生させる必要があります。次に例を示します。
IF condition THEN RAISE exception_name;
DBMS_STANDARD.RAISE_APPLICATION_ERRORプロシージャの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
サブプログラムの例外処理部分には、1つ以上の例外ハンドラが含まれています。 例外ハンドラの構文は、次のとおりです。
WHEN { exception_name [ OR exception_name ]... | OTHERS } THEN
statement; [ statement; ]...
WHEN OTHERS例外ハンドラは、予期しないランタイム・エラーを処理します。これは、最後に使用する必要があります。次に例を示します。
EXCEPTION WHEN exception_1 THEN statement; [ statement; ]... WHEN exception_2 OR exception_3 THEN statement; [ statement; ]... WHEN OTHERS THEN statement; [ statement; ]... RAISE; -- Reraise the exception (very important). END;
WHEN OTHERS例外ハンドラのかわりに、EXCEPTION_INITプラグマを使用することもできます。このプラグマによって、ユーザー定義の例外の名前がOracle Databaseのエラー番号に関連付けられます。
|
参照:
|
次の状況のみ、例外ハンドラを使用することをお薦めします。
例外を予期して処理します。
たとえば、SELECT INTO文で行が返されず、Oracle Databaseで事前定義済の例外NO_DATA_FOUNDが発生するとします。例5-13のとおり、サブプログラムまたはブロックでその例外(エラーではない)を処理して続行します。
リソースを放棄するか、閉じる必要があります。
次に例を示します。
... file := UTL_FILE.OPEN ... BEGIN statement statement]... -- If this code fails for any reason, EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(file); -- then you want to close the file. RAISE; -- Reraise the exception (very important). END; UTL_FILE.FCLOSE(file); ...
コードの最上位レベルで、エラーを記録します。
たとえば、クライアント・プロセスがこのブロックを発行する場合があります。
BEGIN
proc(...);
EXCEPTION
WHEN OTHERS THEN
log_error_using_autonomous_transaction(...);
RAISE; -- Reraise the exception (very important).
END;
/
または、クライアントが起動するスタンドアロンのサブプログラムは、同じ例外処理ロジックを含むことができますが、最上位レベルのみです。
例5-13では、EMP_EVAL.EVAL_DEPARTMENTプロシージャを変更して事前定義済の例外NO_DATA_FOUNDを処理する方法を太字で示しています。この変更を行い、変更したプロシージャをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
例5-13 事前定義済の例外NO_DATA_FOUNDの処理
PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
emp_cursor emp_refcursor_type;
current_dept departments.department_id%TYPE;
BEGIN
current_dept := dept_id;
FOR loop_c IN 1..3 LOOP
OPEN emp_cursor FOR
SELECT *
FROM employees
WHERE current_dept = eval_department.dept_id;
DBMS_OUTPUT.PUT_LINE
('Determining necessary evaluations in department #' ||
current_dept);
eval_loop_control(emp_cursor);
DBMS_OUTPUT.PUT_LINE
('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
current_dept := current_dept + 10;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
END eval_department;
|
参照: 事前定義済の例外の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
例5-14では、EMP_EVAL.CALCULATE_SCOREファンクションを変更して2つのユーザー定義の例外wrong_weightおよびwrong_scoreを宣言および処理する方法を太字で示しています。この変更を行い、変更したファンクションをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
例5-14 ユーザー定義の例外の処理
FUNCTION calculate_score ( evaluation_id IN scores.evaluation_id%TYPE
, performance_id IN scores.performance_id%TYPE )
RETURN NUMBER AS
weight_wrong EXCEPTION;
score_wrong EXCEPTION;
n_score scores.score%TYPE;
n_weight performance_parts.weight%TYPE;
running_total NUMBER := 0;
max_score CONSTANT scores.score%TYPE := 9;
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
BEGIN
SELECT s.score INTO n_score
FROM SCORES s
WHERE evaluation_id = s.evaluation_id
AND performance_id = s.performance_id;
SELECT p.weight INTO n_weight
FROM PERFORMANCE_PARTS p
WHERE performance_id = p.performance_id;
BEGIN
IF (n_weight > max_weight) OR (n_weight < 0) THEN
RAISE weight_wrong;
END IF;
END;
BEGIN
IF (n_score > max_score) OR (n_score < 0) THEN
RAISE score_wrong;
END IF;
END;
running_total := n_score * n_weight;
RETURN running_total;
EXCEPTION
WHEN weight_wrong THEN
DBMS_OUTPUT.PUT_LINE(
'The weight of a score must be between 0 and ' || max_weight);
RETURN -1;
WHEN score_wrong THEN
DBMS_OUTPUT.PUT_LINE(
'The score must be between 0 and ' || max_score);
RETURN -1;
END calculate_score;
|
参照: ユーザー定義の例外の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
