この章の内容は次のとおりです。
サブプログラムは、特定の問題を解決したり、関連する一連のタスクを実行する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
PACKAGE
DDL文を使用します。
このチュートリアルでは、編集ツールを使用して、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
LOOP
FOR
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;
参照:
|