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