| Oracle® Database Express Edition 2日で開発者ガイド 11g リリース2 (11.2) B66463-01 |
|
 前 |
 次 |
この章の内容は次のとおりです。
サブプログラムは、特定の問題を解決したり、関連する一連のタスクを実行するSQL文およびPL/SQL文で構成されているPL/SQLユニットです。サブプログラムはパラメータを持つことができ、値は起動元から提供されます。サブプログラムは、プロシージャまたはファンクションです。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
ストアド・サブプログラムは、データベースに格納されたサブプログラムです。ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。別のサブプログラムまたは無名ブロック内で宣言されたサブプログラムは、ネストされたサブプログラムまたはローカル・サブプログラムと呼ばれます。宣言されたサブプログラムまたはブロックの外部から呼び出すことはできません。無名ブロックとは、データベースに格納されていないブロックです。
ストアド・サブプログラムは2種類あります。
スタンドアロン・ストアド・サブプログラムはプログラム・ロジックのテストに便利ですが、意図どおりに動作することが確実な場合は、パッケージに含めることをお薦めします。
|
参照:
|
パッケージは、関連するサブプログラムと、サブプログラムで使用される明示カーソルおよび変数からなるPL/SQLユニットです。
サブプログラムは、パッケージに含めることをお薦めします。その理由の一部を次に示します。
パッケージでは、クライアント・プログラムから実装の詳細を隠すことができます。
クライアント・プログラムから実装の詳細を隠すことは、広く指示されるベスト・プラクティスです。Oracleのカスタマの多くは、このプラクティスに厳密に従っていて、クライアント・プログラムでは、PL/SQLサブプログラムを起動したときにのみデータベースにアクセスできます。一部のカスタマは、クライアント・プログラムでSELECT文を使用し、データベース表から情報を取得することを可能にしていますが、この文は、データベースを変更するすべてのビジネス機能に対するPL/SQLサブプログラムを起動する必要があります。
パッケージ・サブプログラムは、起動時にパッケージ名で修飾されていて、名前が常に有効である必要があります。
たとえば、Oracle Database Express Edition (Oracle Database XE) 11gリリース1 (11.1)より前にCONTINUEというスキーマ・レベル・プロシージャを作成したとします。CONTINUE文はリリース11.1で導入されました。したがって、作成したコードを11.1に移植した場合、コードはコンパイルできなくなります。ただし、パッケージ内にプロシージャを作成した場合、コードはプロシージャをpackage_name.CONTINUEとして参照するため、コンパイルできます。
パッケージ・サブプログラムはレコードおよびコレクションを送受信可能です。
スタンドアロン・ストアド・サブプログラムは、VARCHAR2、NUMBER、DATEなどの、内部コンポーネントのない単一の値のスカラー・パラメータのみ送受信可能です。
|
注意: Oracle Database XEは、多くの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のみのデータ型は、スキーマレベル(つまり、表またはスタンドアロン・ストアド・サブプログラム)では使用できません。そのため、PL/SQLのみのデータ型をストアド・サブプログラムで使用するには、サブプログラムをパッケージに含める必要があります。
|
参照:
|
トピック:
|
注意: このマニュアルのチュートリアルを行うには、ユーザーHRとして、SQL DeveloperからOracle Database XEに接続している必要があります。 |
サブプログラムは、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のPL/SQLプロシージャ作成ツールまたはDDL文のCREATE PROCEDUREを使用します。
このチュートリアルでは、PL/SQLプロシージャ作成ツールを使用して、EVALUATIONS表に行を追加するADD_EVALUATIONというスタンドアロン・ストアド・プロシージャを作成する方法を示します。
PL/SQLプロシージャ作成ツールを使用してスタンドアロン・ストアド・プロシージャを作成するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「プロシージャ」を右クリックします。
選択肢のリストが表示されます。
「新規プロシージャ」をクリックします。
「PL/SQLプロシージャ作成」ウィンドウが開きます。
「スキーマ」では、デフォルト値のHRを受け入れます。
「名前」では、PROCEDURE1をADD_EVALUATIONに変更します。
「列の追加」アイコンをクリックします。
列のヘッダーの下に1行表示されます。各フィールドには次のデフォルト値が含まれます。「名前」: PARAM1、「タイプ」: VARCHAR2、「モード」: IN、「デフォルト値」: 空。
「名前」では、param1をevaluation_idに変更します。
「タイプ」で、「NUMBER」をメニューから選択します。
「モード」では、デフォルト値のINを受け入れます。
「デフォルト値」は空白のままにします。
「名前」に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 XEは、プロシージャをコンパイルして保存します。ADD_EVALUATIONペインのタイトルがイタリック・フォントではなくなります。
|
参照:
|
スタンドアロン・ストアド・ファンクションを作成するには、SQL DeveloperのPL/SQLファンクション作成ツールまたはDDL文のCREATE FUNCTIONを使用します。
このチュートリアルでは、PL/SQLファンクション作成ツールを使用して、3つのパラメータを持ちNUMBER型の値を戻す、calculate_scoreというスタンドアロン・ストアド・ファンクションを作成する方法を示します。
PL/SQLファンクション作成ツールを使用してスタンドアロン・ストアド・ファンクションを作成するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「ファンクション」を右クリックします。
選択肢のリストが表示されます。
「新規ファンクション」をクリックします。
PL/SQLファンクションの作成ウィンドウが開きます。このウィンドウはPL/SQLプロシージャの作成ウィンドウ（「チュートリアル: スタンドアロン・ストアド・ファンクションの作成」参照）と似ていますが、パラメータ・ペインに、ファンクションから返された値の入る行があります。その行で、Nameの値は、<Return>であり、型のデフォルト値は、VARCHAR2です。
「スキーマ」では、デフォルト値のHRを受け入れます。
「名前」では、FUNCTION1をcalculate_scoreに変更します。
「パラメータ」ペインでは、唯一の行である「型」フィールドで、メニューからNUMBERを選択します。
「列の追加」アイコンをクリックします。
列のヘッダーの下に1行表示されます。各フィールドには次のデフォルト値が含まれます。「名前」: PARAM1、「タイプ」: VARCHAR2、「モード」: IN、「デフォルト値」: 空。
「名前」では、PARAM1をcatに変更します。
「型」では、デフォルト値のVARCHAR2を受け入れます。
「モード」では、デフォルト値のINを受け入れます。
「デフォルト値」は空白のままにします。
「名前」に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 XEは、ファンクションをコンパイルして保存します。CALCULATE_SCOREペインのタイトルがイタリック・フォントではなくなります。
|
参照:
|
スタンドアロン・ストアド・サブプログラムを変更するには、SQL Developerの編集ツール、DDL文のALTER PROCEDUREまたはALTER FUNCTIONを使用します。
編集ツールを使用してスタンドアロン・ストアド・サブプログラムを変更するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「ファンクション」または「プロシージャ」を展開します。
ファンクションまたはプロシージャのリストが表示されます。
変更するファンクションまたはプロシージャをクリックします。
「接続」ペインの右側に、フレームが表示されます。上部のタブには変更するサブプログラムの名前が表示されます。その下にあるのはサブタブです。
「コード」サブタブをクリックします。
「コード」ペインが表示され、サブプログラムを作成したコードが表示されます。「コード」ペインが書込みモードになっています。(鉛筆アイコンをクリックすると、モードが書込みモードから読取り専用モードに、またはその逆に切り替わります。)
「コード」ペインで、コードを変更します。
ペインのタイトルはイタリック・フォントになっており、変更がデータベースに保存されていないことを示しています。
「ファイル」メニューから、「保存」を選択します。
Oracle Database XEは、サブプログラムをコンパイルして保存します。ペインのタイトルがイタリック・フォントではなくなります。
|
参照:
|
このチュートリアルでは、SQL Developerの実行ツールを使用して、スタンドアロン・ストアド・ファンクションcalculate_scoreをテストする方法を示します。
実行ツールを使用してCALCULATE_SCOREファンクションをテストするには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
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 Database SQL Developerユーザーズ・ガイド』を参照してください。 |
スタンドアロン・ストアド・サブプログラムを削除するには、SQL Developerのナビゲーション・フレームおよび削除ツール、あるいはDDL文のDROP PROCEDUREまたはDROP FUNCTIONを使用します。
削除ツールを使用してスタンドアロン・ストアド・サブプログラムを削除するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「ファンクション」または「プロシージャ」を展開します。
ファンクションまたはプロシージャのリストが表示されます。
削除するファンクションまたはプロシージャの名前を右クリックします。
選択肢のリストが表示されます。
「削除」をクリックします。
「削除」ウィンドウが開きます。
「適用」をクリックします。
「確認」ウィンドウが開きます。
「OK」をクリックします。
|
参照:
|
トピック:
パッケージには必ず仕様部があり、通常、さらに本体があります。
パッケージ仕様には、パッケージの定義が記述されます。パッケージ外部から参照される可能性のある、型、変数、定数、例外、明示カーソル、およびサブプログラムが宣言されます。パッケージ仕様はapplication program interface（API）です。クライアント・プログラムからサブプログラムを起動するために必要な情報はすべて含まれていますが、それらの実装に関する情報は含まれません。
パッケージ本体は、パッケージ仕様部で宣言される明示カーソルの問合せおよびサブプログラムのコードを定義します(したがって、明示カーソルもサブプログラムもないパッケージには本体は必要ありません)。また、パッケージ本体は、仕様部で宣言されずパッケージの他のサブプログラムでのみ起動できるローカル・サブプログラムも定義できます。パッケージ本体の内容は、クライアント・プログラムに対して非表示です。パッケージ本体は、パッケージをコールするアプリケーションを無効にすることなく変更できます。
|
参照:
|
パッケージ仕様部を作成するには、SQL DeveloperのPL/SQLパッケージ作成ツールまたはDDL文のCREATE PACKAGEを使用します。
このチュートリアルでは、PL/SQLパッケージ作成ツールを使用して、EMP_EVALというパッケージの仕様部を作成する方法を示します。
このパッケージ仕様部は、このマニュアルのチュートリアルと例で、開発とデプロイの方法を説明するためのサンプル・アプリケーションのAPIです。
PL/SQLパッケージ作成ツールを使用してパッケージ仕様部を作成するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「パッケージ」を右クリックします。
選択肢のリストが表示されます。
「新規パッケージ」をクリックします。
「PL/SQLパッケージ作成」ウィンドウが開きます。「スキーマ」フィールドには値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 XEは、パッケージをコンパイルして保存します。EMP_EVALペインのタイトルがイタリック・フォントではなくなります。
|
参照: CREATE PACKAGE文(パッケージ仕様部)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
パッケージ仕様部を変更するには、SQL Developerの編集ツール、またはOR REPLACE句を持つCREATE PACKAGEDDL文を使用します。
このチュートリアルでは、編集ツールを使用して、EMP_EVALパッケージの仕様を変更する方法を表示します。具体的には、eval_departmentプロシージャおよびcalculate_scoreファンクションに宣言を追加する方法を示します。
編集ツールを使用してEMP_EVALパッケージ仕様部を変更するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
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を展開します。
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 */
NULL;
END eval_department;
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
BEGIN
/* TODO implementation required */
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を使用します。
削除ツールを使用してパッケージを削除するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
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を展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「パッケージ」を展開します。
パッケージのリストが表示されます。
EMP_EVALを展開します。
リストが表示されます。
EMP_EVAL Bodyを右クリックします。
選択肢のリストが表示されます。
「編集」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。
CREATE OR REPLACE
PACKAGE BODY EMP_EVAL AS
PROCEDURE eval_department ( dept_id IN NUMBER ) AS
BEGIN
/* TODO implementation required */
NULL;
END eval_department;
FUNCTION calculate_score ( evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
BEGIN
/* TODO implementation required */
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 XEは、変更されたパッケージ本体をコンパイルして保存します。 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 */
RETURN NULL;
END calculate_score;
ファンクションの変数、定数およびパラメータは、SCORES表およびPERFORMANCE_PARTS表の値を示しています。
n_score変数には、SCORES表のSCORE列の値が格納され、max_score定数がこれらの値と比較されます。
n_weight変数には、PERFORMANCE_PARTS表のWEIGHT列の値が格納され、max_weight定数がこれらの値と比較されます。
evaluation_idパラメータには、SCORES表のEVALUATION_ID列の値が格納されます。
performance_idパラメータには、SCORES表のPERFORMANCE_ID列の値が格納されます。
このため、各変数、定数およびパラメータのデータ型は、対応する列と同じです。
列のデータ型が変更された場合、変数、定数およびパラメータも同じデータ型に変更される必要があります。変更されない場合、calculate_scoreファンクションが無効化されます。
変数、定数およびパラメータのデータ型を列のデータ型と常に一致させるには、%TYPE属性を付けて宣言します。 %TYPE属性は、表の列または別の変数のデータ型を提供し、正しいデータ型の割当てを保証します。
|
参照:
|
このチュートリアルでは、SQL Developerの編集ツールを使用して、EMP_EVAL.calculate_scoreファンクションの変数、定数および仮パラメータの宣言(「チュートリアル: サブプログラムでの変数および定数の宣言」を参照)を、%TYPE属性を使用した宣言に変更する方法を示します。
%TYPEを使用するようにcalculate_scoreの宣言を変更するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「パッケージ」を展開します。
パッケージのリストが表示されます。
EMP_EVALを展開します。
リストが表示されます。
EMP_EVAL Bodyを右クリックします。
選択肢のリストが表示されます。
「編集」をクリックします。
EMP_EVAL本体ペインが表示され、パッケージ本体のコードが表示されます。
CREATE OR REPLACE
PACKAGE BODY emp_eval AS
PROCEDURE eval_department ( dept_id IN NUMBER ) AS
BEGIN
/* TODO implementation required */
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 */
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;
例7-1では、EMP_EVAL.calculate_score関数に対して行う変更が太字で表示されて、変数running_totalが追加され、この新しい変数が関数の戻り値として使用されます。代入演算子は、ファンクションの宣言部と実行可能部の両方に表示されます。（running_totalのデータ型は、異なる精度およびスケールを持つ2つのNUMBER値の積を保持するため、SCORES.SCORE%TYPEまたはPERFORMANCE_PARTS.WEIGHT%TYPEではなく、NUMBERである必要があります。）
例7-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文によって変数に値を割り当てる必要があります。
例7-2では、表の値からrunning_totalを計算させるためにEMP_EVAL.calculate_scoreファンクションに対して加える変更を太字で示しています。
例7-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;
例7-3に示すadd_evalプロシージャは、EVALUATIONS表への行挿入に、EMPLOYEES表の対応する行の値を使用する場合の例です。add_evalプロシージャは、EMP_EVALパッケージの本体にのみ追加し、仕様には追加しません。add_evalは仕様内には定義しないので、そのパッケージのローカル・プロシージャになり、パッケージ内の他のサブプログラムからのみ起動でき、パッケージ外部からは起動できません。
例7-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
SELECT e.job_id INTO job_id
FROM EMPLOYEES e
WHERE employee_id = e.employee_id;
SELECT e.manager_id INTO manager_id
FROM EMPLOYEES e
WHERE employee_id = e.employee_id;
SELECT e.department_id INTO department_id
FROM EMPLOYEES e
WHERE employee_id = e.employee_id;
INSERT INTO EVALUATIONS (
evaluation_id,
employee_id,
evaluation_date,
job_id,
manager_id,
department_id,
total_score
)
VALUES (
evaluations_seq.NEXTVAL, -- evaluation_id
add_eval.employee_id, -- employee_id
add_eval.today, -- evaluation_date
add_eval.job_id, -- job_id
add_eval.manager_id, -- manager_id
add_eval.department_id, -- department_id
0 -- total_score
);
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です。
|
参照:
|
IF文は、ブール式の値に応じて一連の文を実行またはスキップします。
IF文の構文は、次のとおりです。
IF boolean_expression THEN statement [, statement ] [ ELSIF boolean_expression THEN statement [, statement ] ]... [ ELSE statement [, statement ] ] END IF;
企業が雇用の最初の10年は1年に2回、その後は1年に1回のみ、従業員を評価するとします。これには従業員の評価頻度を戻すファンクションが必要です。この場合、例7-4のように、IF文を使用してファンクションの戻り値を判断できます。
eval_frequency関数をEMP_EVALパッケージの本体に追加しますが、仕様には追加しません。eval_frequencyは、仕様にはないため、パッケージに対してローカルであり、パッケージ内の他のサブプログラムでのみ起動でき、パッケージ外からは起動できません。
例7-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 = 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文には、この構文があります。
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回のみ評価され、eval_frequencyファンクションに、JOB_IDに応じて給与の値上げを推奨させるとします。
例7-5に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)
例7-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 INTO h_date FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; SELECT JOB_ID INTO j_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; 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年間毎年、推奨された額の値上げをした場合の給与額もレポートさせるとします。
例7-6に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)
例7-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 INTO h_date FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; SELECT JOB_ID INTO j_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; SELECT SALARY INTO sal FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; 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の給与の最大値を超過した後に終了するとします。
例7-7に太字で示されているように、eval_frequencyファンクションを変更します。(文字列を出力するプロシージャDBMS_OUTPUT.PUT_LINEの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。)
例7-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 INTO h_date FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; SELECT JOB_ID INTO j_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; SELECT SALARY INTO sal FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; SELECT MAX_SALARY INTO sal_max FROM JOBS WHERE JOB_ID = j_id; 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文の最後の反復では、最後に計算された値は通常、給与の最大値を超過します。
例7-8に示すように、WHILE LOOP文を、EXIT WHEN文を含む基本のLOOP文に変更します。
WHILE LOOP
例7-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 INTO h_date
FROM EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
eval_freq := 1;
SELECT JOB_ID INTO j_id
FROM EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
SELECT SALARY INTO sal
FROM EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
SELECT MAX_SALARY INTO sal_max
FROM JOBS
WHERE JOB_ID = j_id;
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を使用します。
レコード・フィールドは、スカラー変数のように扱うことができます。レコード全体をサブプログラム・パラメータとして渡すこともできます(ただし、送受信されるサブプログラムがいずれもスタンドアロン・ストアド・サブプログラムではない場合)。
レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。各レコード・フィールドは表の列に対応しています。
RECORD型を宣言し、その型の変数を宣言します。
次に構文を示します。
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の編集ツールを使用してRECORD型のsal_infoを宣言する方法を示します。フィールドには、ジョブID、そのジョブ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の編集ツールを使用して、次の手順を実行する方法を示します。
eval_frequencyはsalary_scheduleを起動するため、salary_scheduleの宣言はeval_frequencyの宣言に先行する必要があります(そうでない場合、パッケージはコンパイルされません)。ただし、salary_scheduleの定義はパッケージ本体の任意の位置に置くことができます。
salary_scheduleを作成しeval_frequencyを変更するには、次の手順を実行します。
「接続」タブで、hr_connを展開します。
hr_connアイコンの下に、スキーマ・オブジェクト型のリストが表示されます。
「パッケージ」を展開します。
パッケージのリストが表示されます。
EMP_EVALを展開します。
リストが表示されます。
EMP_EVAL Bodyを右クリックします。
選択肢のリストが表示されます。
「編集」をクリックします。
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 = emp_id; IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN eval_freq := 1; /* populate emp_sal */ SELECT JOB_ID INTO emp_sal.j_id FROM EMPLOYEES WHERE EMPLOYEE_ID = emp_id; SELECT MIN_SALARY INTO emp_sal.sal_min FROM JOBS WHERE JOB_ID = emp_sal.j_id; SELECT MAX_SALARY INTO emp_sal.sal_max FROM JOBS WHERE JOB_ID = emp_sal.j_id; SELECT SALARY INTO emp_sal.sal FROM EMPLOYEES WHERE EMPLOYEE_ID = 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 XEは、SQL文の実行時に、結果セットおよび処理情報を無名のプライベートSQL領域に保存します。この無名の領域へのポインタはカーソルと呼ばれ、結果セットの行を1つずつ取得できます。 カーソル属性は、カーソルの状態に関する情報を戻します。
SQL DML文またはPL/SQL SELECT INTO文を実行するたびに、PL/SQLは暗黙カーソルをオープンします。このカーソルに関する情報は属性から得られますが、制御はできません。文の実行後、データベースはカーソルをクローズしますが、属性の値は別のDMLまたはSELECT INTO文が実行されるまで使用可能です。
PL/SQLでは、明示カーソルも宣言できます。明示カーソルには名前があり、通常は複数行を戻す問合せ(SQL SELECT文)に関連付けられています。明示カーソルを宣言した後、カーソルをオープンし(OPEN文)、結果セットから行を1つずつフェッチし(FETCH文)、カーソルをクローズする(CLOSE文)必要があります。カーソルのクローズ後は、結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。
暗黙カーソル属性の値の構文は、SQL属性(SQL%FOUNDなど)です。 SQL属性は常に、最後に実行されたDMLまたはSELECT INTO文を参照します。
明示カーソル属性の値の構文は、直後に属性のあるcursor_name(c1%FOUNDなど)です。
表7-1に、カーソル属性および戻すことのできる値のリストを示します。(暗黙カーソルには、このマニュアルの範囲外の追加属性があります。)
表7-1 カーソル属性の値
| 属性 | 明示カーソルの値 | 暗黙カーソルの値 |
|---|---|---|
|
カーソルがオープンされていない場合、 カーソルはオープンされているが、フェッチが試みられていない場合、 最後のフェッチが行を戻した場合、 最後のフェッチが行を戻さなかった場合、 |
DMLまたは 最後のDMLまたは 最後のDMLまたは |
|
|
カーソルがオープンされていない場合、 カーソルはオープンされているが、フェッチが試みられていない場合、 最後のフェッチが行を戻した場合、 最後のフェッチが行を戻さなかった場合、 |
DMLまたは 最後のDMLまたは 最後のDMLまたは |
|
|
カーソルがオープンされていない場合、 |
DMLまたは |
|
|
カーソルがオープンされている場合、 |
常に |
|
参照:
|
次の手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。
明示カーソルを使用して結果セット行を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_EVAL.eval_departmentプロシージャを実装する方法を示します。これには、emp_cursor明示カーソルを使用します。
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 = 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 = 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_seq.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
keyが配列内にない場合、代入文はkey-valueペアを配列に追加します。そうでない場合、文がarray_name(key)の値をvalueに変更します。
連想配列は、データの一時的な格納に便利です。連想配列では、表が必要とするディスク領域やネットワーク操作を使用しません。ただし、連想配列は一時的な格納を目的としているため、DML文で操作したり、SELECT INTO文を使用して値を変数に割り当てることはできません。
パッケージで連想配列を宣言し、値をパッケージ本体の変数に割り当てる場合、連想配列はデータベース・セッションが存続するかぎり存在します。そうでない場合は、宣言をしたサブプログラムが存続するかぎり存在します。
|
参照: 連想配列の詳細は、『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言語リファレンス』を参照してください。
例7-9では、前述の手順を使用して2つの連想配列employees_jobsおよびjobs_を宣言し、カーソルを使用せずに3つ目の連想配列job_titles_typeを宣言します。初めの2つの配列は整数によって索引付けされ、3つ目の配列は文字列によって索引付けされます。
|
注意: employees_jobs_cursorの宣言のORDER BY句が、連想配列employee_jobsの要素の格納順序を決定します。 |
例7-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句のあるFETCH文を使用します。この手順では、必要な各文を最も単純な形式で使用しますが、構文の詳細の参照先も示します。
次の手順を使用してスパース連想配列を移入することはできません。かわりに、繰り返し文の中の代入文を使用する必要があります。繰り返し文の詳細は、「プログラム・フローの制御」を参照してください。
カーソルを使用して整数に索引付けされた連想配列を移入するには、次の手順を実行します。
連想配列を宣言していない場合は、「連想配列の宣言」の手順に従って、カーソルを使用して連想配列を宣言します。
連想配列を宣言したPL/SQLユニットの実行可能部分で、次の手順を実行します。
カーソルをオープンします。
OPEN cursor_name;
OPEN文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
BULK COLLECT INTO句のあるFETCH文を使用して、一度にすべての行をカーソルから連想配列変数にフェッチします。
FETCH cursor_name BULK COLLECT INTO aa_variable;
FETCH文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
カーソルをクローズします。
CLOSE cursor_name;
CLOSE文の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例7-10では、前述の手順を使用して、整数によって索引付けされた連想配列employees_jobsおよびjobs_を移入します。次に、FOR LOOP文にある代入文を使用して、文字列によって索引付けされた連想配列job_titles_typeを移入します。
例7-10 連想配列の移入
-- Declarative part from Example 7-9 goes here. BEGIN -- Populate associative arrays indexed by integer: OPEN employees_jobs_cursor; FETCH employees_jobs_cursor BULK COLLECT INTO employees_jobs; CLOSE employees_jobs_cursor; OPEN jobs_cursor; FETCH jobs_cursor BULK COLLECT INTO jobs_; CLOSE jobs_cursor; -- 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の場合もあります）。例7-11のように、FOR LOOP文を使用して稠密な配列を横断できます。
例7-11のFOR LOOP文は、例7-10の実行可能部分に挿入すると、employees_jobs配列を移入するコードの後に、employees_jobs配列の要素を、格納された順序で出力します。格納順序は、employees_jobsを宣言するために使用されたemployees_jobs_cursor宣言のORDER BY句によって決定されます(例7-9を参照)。
FOR LOOPFOR LOOP
FOR LOOP文の上限employees_jobs.COUNTにより、配列内の要素の数を戻すコレクション・メソッドが起動されます。COUNTの詳細は、Oracle Database PL/SQL言語リファレンスを参照してください。
例7-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
スパース連想配列(文字列によって索引付けされたもの)は、要素間に差分がある場合があります。例7-12のように、WHILE LOOP文を使用して横断できます。
例7-12でコードを実行し、job_titles配列の要素を出力するには、次の手順を実行します。
例7-9の宣言部分の終わりに、次の変数宣言を挿入します。
i jobs_.job_id%TYPE;
例7-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言語リファレンス』を参照してください。
例7-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 XEには多くの事前定義済の例外があり、プログラムがデータベース・ルールに違反したり、システム依存の限度を超えた場合に自動的に発生します。たとえば、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; ]... END;
WHEN OTHERS例外ハンドラのかわりに、EXCEPTION_INITプラグマを使用することもできます。このプラグマによって、ユーザー定義の例外の名前がOracle Databaseのエラー番号に関連付けられます。
|
参照:
|
例7-13では、EMP_EVAL.eval_departmentプロシージャを変更して事前定義済の例外NO_DATA_FOUNDを処理する方法を太字で示しています。この変更を行い、変更したプロシージャをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
例7-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 = 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;
|
参照:
|
例7-14では、EMP_EVAL.calculate_scoreファンクションを変更して2つのユーザー定義の例外wrong_weightおよびwrong_scoreを宣言および処理する方法を太字で示しています。この変更を行い、変更したファンクションをコンパイルします。(パッケージ本体を変更する方法の例は、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。)
例7-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;
|
参照:
|
