5 ストアド・サブプログラムおよびパッケージの開発
ストアド・プログラムとパッケージは、多くの様々なデータベース・アプリケーションのビルディング・ブロックとして使用できます。
- ストアド・サブプログラムについて
ストアド・サブプログラムは、データベースに格納されたサブプログラムです。ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。 - パッケージについて
パッケージとは、関連するサブプログラムとそれらで使用する宣言カーソルと変数からなるPL/SQLユニットです。サブプログラムをパッケージに配置することをお薦めします。 - PL/SQL識別子について
PL/SQL、サブプログラム、パッケージ、パラメータ、変数、定数、例外および宣言カーソルには、それぞれ名前があり、それぞれの名前がPL/SQL識別子です。 - PL/SQLデータ型について
PL/SQLの定数、変数、サブプログラム・パラメータおよび関数の戻り値のそれぞれに、データ型があり、このデータ型により、格納の形式、制約、値の有効範囲および実行される可能性がある操作が決定されます。 - スタンドアロンのサブプログラムの作成および管理
スタンドアロンのPL/SQLサブプログラムを作成および管理できます。 - パッケージの作成および管理
PL/SQLサブプログラムを作成および管理できます。 - 変数および定数の宣言と値の割当て
パッケージ仕様で宣言された変数または定数は、このパッケージにアクセスしている任意のプログラムで使用可能です。パッケージ本体またはサブプログラムで宣言された変数または定数は、そのパッケージまたはサブプログラムに対してローカルです。定数を宣言する場合、その定数に初期値を割り当てる必要があります。 - プログラム・フローの制御
入力の順序に従って文を実行するSQLとは異なり、PL/SQLには、プログラム・フローを制御できる制御文があります。 - レコードおよびカーソルの使用
レコードにデータ値を格納し、カーソルを結果セットおよび関連の処理情報へのポインタとして使用できます。 - 連想配列の使用
連想配列はコレクションの型です。 - 例外の処理(実行時エラー)
PL/SQLコードで実行時に発生する例外を処理できます。
5.1 ストアド・サブプログラムについて
ストアド・サブプログラムは、データベースに格納されたサブプログラムです。ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。
サブプログラムは、特定の問題を解決したり、関連する一連のタスクを実行するSQL文およびPL/SQL文で構成されているPL/SQLユニットです。サブプログラムはパラメータを持つことができ、値は起動元から提供されます。サブプログラムは、プロシージャの場合もファンクションの場合もあります。通常、プロシージャはアクションを実行するために使用し、ファンクションは計算を行って値を戻すために使用します。
ストアド・サブプログラムは、データベースに格納されているため、多様なデータベース・アプリケーションのビルディング・ブロックとして使用できます。別のサブプログラムまたは無名ブロック内で宣言されたサブプログラムは、ネストされたサブプログラムまたはローカル・サブプログラムと呼ばれます。宣言されたサブプログラムまたはブロックの外部から呼び出すことはできません。無名ブロックとは、データベースに格納されていないブロックです。
ストアド・サブプログラムは2種類あります。
-
スキーマ・レベルで作成されたスタンドアロンのサブプログラム
-
パッケージ内で作成されるパッケージ・サブプログラム
スタンドアロンのサブプログラムは、一部のプログラム・ロジックのテストに有用ですが、確実にこれらを意図したとおりに作動させる場合、これらをパッケージ内に配置することをお薦めします。
関連項目:
-
ストアド・サブプログラムの一般情報は、『Oracle Database概要』を参照してください。
-
PL/SQLサブプログラムの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.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パッケージおよびタイプ・リファレンス』を参照してください。
関連項目:
-
パッケージの一般情報は、『Oracle Database概要』を参照してください。
-
パッケージを使用する理由は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLパッケージの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
Oracleが提供するPL/SQLパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.3 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識別子の追加の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLのネーミング規則の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQL識別子の適用範囲および可視性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQL識別子に関するデータの収集方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLが識別子名を解決する方法の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.4 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データ型は使用できません。したがって、ストアド・サブプログラムでこれらのデータ型を使用するには、パッケージに配置する必要があります。
関連項目:
-
PL/SQLデータ型の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PLS_INTEGERデータ型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.5 スタンドアロンのサブプログラムの作成および管理
スタンドアロンのPL/SQLサブプログラムを作成および管理できます。
注意:
このマニュアルのチュートリアルを行うには、ユーザーHRとして、SQL DeveloperからOracle Databaseに接続している必要があります。
- サブプログラム構造について
- チュートリアル: スタンドアロンのプロシージャの作成
このチュートリアルでは、プロシージャの作成ツールを使用して、行をEVALUATIONS表に追加するADD_EVALUATIONという名前のスタンドアロンのプロシージャを作成する方法を示します。 - チュートリアル: スタンドアロンのファンクションの作成
このチュートリアルでは、ファンクションの作成ツールを使用して、3つのパラメータがあり、NUMBER型の値を戻す、CALCULATE_SCOREという名前のスタンドアロン・ファンクションを作成する方法を表示します。 - スタンドアロンのサブプログラムの変更
スタンドアロンのサブプログラムを変更するには、SQL Developerの編集ツールまたはDDL文ALTER PROCEDUREかALTER FUNCTIONのいずれかを使用します。 - チュートリアル: スタンドアロンのファンクションのテスト
このチュートリアルでは、SQL Developerの実行ツールを使用して、スタンドアロンのファンクションCALCULATE_SCOREをテストする方法について説明します。 - スタンドアロンのサブプログラムの削除
スタンドアロンのサブプログラムを削除するには、SQL Developerの「接続」フレームと削除ツール、またはDDL文DROP PROCEDUREかDROP FUNCTIONのいずれかを使用します。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.5.1 サブプログラム構造について
サブプログラムは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言語リファレンス』を参照してください。
親トピック: スタンドアロンのサブプログラムの作成および管理
5.5.2 チュートリアル: スタンドアロンのプロシージャの作成
このチュートリアルでは、プロシージャの作成ツールを使用して、EVALUATIONS表に行を追加するADD_EVALUATIONというスタンドアロン・ストアド・プロシージャを作成する方法を示します。
EVALUATIONS表は、例4-1で作成しました。
スタンドアロン・プロシージャを作成するには、SQL Developerツールのプロシージャの作成またはDDL文CREATE PROCEDUREのいずれかを使用します。
プロシージャの作成ツールを使用してスタンドアロン・プロシージャを作成するには、次の手順を実行します。
関連項目:
-
SQL Developerを使用してスタンドアロン・プロシージャを作成する別の例は、『Oracle SQL Developerユーザーズ・ガイド』を参照してください。
-
CREATE PROCEDURE文に適用される一般情報は、"「データ定義言語(DDL)文について」を参照してください。
-
CREATE PROCEDURE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: スタンドアロンのサブプログラムの作成および管理
5.5.3 チュートリアル: スタンドアロンのファンクションの作成
このチュートリアルでは、ファンクションの作成ツールを使用して、3つのパラメータがあり、NUMBER型の値を戻す、CALCULATE_SCOREという名前のスタンドアロン・ファンクションを作成する方法を表示します。
スタンドアロン・ファンクションを作成するには、SQL Developerツールのファンクションの作成またはDDL文CREATE FUNCTIONのいずれかを使用します。
ファンクションの作成ツールを使用してスタンドアロン・ファンクションを作成するには、次の手順を実行します。
関連項目:
-
CREATE FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。
-
CREATE FUNCTION文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: スタンドアロンのサブプログラムの作成および管理
5.5.4 スタンドアロンのサブプログラムの変更
スタンドアロン・サブプログラムを変更するには、SQL Developerツールの編集またはDDL文ALTER PROCEDUREまたはALTER FUNCTIONのいずれかを使用します。
編集ツールを使用してスタンドアロンのサブプログラムを変更するには、次の手順を実行します。
関連項目:
-
ALTER PROCEDUREおよびALTER FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。
-
ALTER PROCEDURE文の詳細は、Oracle Database PL/SQL言語リファレンスを参照
-
ALTER FUNCTION文の詳細は、Oracle Database PL/SQLリファレンスを参照
親トピック: スタンドアロンのサブプログラムの作成および管理
5.5.5 チュートリアル: スタンドアロンのファンクションのテスト
このチュートリアルでは、SQL Developer実行ツールを使用して、スタンドアロンのファンクションCALCULATE_SCOREをテストする方法について説明します。
実行ツールを使用してCALCULATE_SCOREファンクションをテストするには、次の手順を実行します。
関連項目:
SQL Developerを使用したプロシージャおよびファンクションの実行とデバッグについては、『Oracle SQL Developerユーザーズ・ガイド』を参照してください。
親トピック: スタンドアロンのサブプログラムの作成および管理
5.5.6 スタンドアロンのサブプログラムの削除
スタンドアロンのサブプログラムを削除するには、SQL Developerの「接続」フレームと削除ツール、またはDDL文DROP PROCEDUREかDROP FUNCTIONのいずれかを使用します。
注意:
プロシージャADD_EVALUATION
またはファンクションCALCULATE_SCORE
は今後のチュートリアルで必要なため、削除しないでください。サブプログラムの削除の実習を行う場合は、簡単なサブプログラムを作成してから削除してください。
Dropツールを使用してスタンドアロンのサブプログラムを削除するには、次の操作を実行します。
- 「接続」フレームで、hr_connを展開します。
- スキーマ・オブジェクト・タイプのリストで、「ファンクション」または「プロシージャ」を展開します。
- ファンクションまたはプロシージャのリストで、削除するファンクションまたはプロシージャの名前を右クリックします。
- 選択肢のリストで、「削除」をクリックします。
- 「削除」ウィンドウで、「適用」をクリックします。
- 「確認」ウィンドウで「OK」をクリックします。
関連項目:
-
DROP PROCEDUREおよびDROP FUNCTION文に適用される一般情報は、「データ定義言語(DDL)文について」を参照してください。
-
DROP PROCEDURE文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
DROP FUNCTION文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: スタンドアロンのサブプログラムの作成および管理
5.6 パッケージの作成および管理
PL/SQLパッケージを作成および管理できます。
- パッケージ構造について
パッケージには必ず仕様部があり、通常、さらに本体があります。仕様部はパッケージそのものを定義する、Application Program Interface (API)です。パッケージ本体では、宣言されたカーソルの問合せ、およびパッケージ仕様で宣言されているサブプログラムのコードを定義します。 - チュートリアル: パッケージ仕様部の作成
このチュートリアルでは、パッケージの作成ツールを使用して、このドキュメントの多くのチュートリアルおよび例に示されているEMP_EVALというパッケージの仕様を作成する方法を示します。 - チュートリアル: パッケージ仕様部の変更
このチュートリアルでは、編集ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの仕様を変更する方法を示します。具体的には、このチュートリアルでは、プロシージャ、EVAL_DEPARTMENTおよび関数CALCULATE_SCOREの宣言を追加する方法を表示されます。 - チュートリアル: パッケージ本体の作成
このチュートリアルでは、本体の作成ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの本体を作成する方法を示します。 - パッケージの削除
パッケージ(仕様および本体)を削除するには、SQL Developerの「接続」フレームと削除ツールまたはDDL文DROP PACKAGEのいずれかを使用します。
関連項目:
パッケージ本体を変更する方法については、「チュートリアル: サブプログラムでの変数および定数の宣言」を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.6.1 パッケージ構造について
パッケージには常に仕様があり、本体があります。仕様部はパッケージそのものを定義する、Application Program Interface (API)です。パッケージ本体では、宣言されたカーソルの問合せ、およびパッケージ仕様で宣言されているサブプログラムのコードを定義します。
パッケージ仕様は、パッケージを定義し、型、変数、定数、例外、宣言カーソル、およびパッケージ外部から参照される可能性のあるサブプログラムを宣言します。パッケージ仕様はApplication Program Interface (API)です。クライアント・プログラムからサブプログラムを起動するために必要な情報はすべて含まれていますが、それらの実装に関する情報は含まれません。
パッケージ本体には、パッケージ仕様で宣言されている宣言カーソルやサブプログラムについて、対応する問合せやコードを定義します(そのため、宣言カーソルもサブプログラムもないパッケージについては本体は必要ありません)。また、パッケージ本体は、仕様部で宣言されずパッケージの他のサブプログラムでのみ起動できるローカル・サブプログラムも定義できます。パッケージ本体の内容は、クライアント・プログラムに対して非表示です。パッケージ本体は、パッケージをコールするアプリケーションを無効にすることなく変更できます。
関連項目:
-
パッケージ仕様部の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
パッケージ本体の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: パッケージの作成および管理
5.6.2 チュートリアル: パッケージ仕様部の作成
このチュートリアルでは、パッケージの作成ツールを使用して、このドキュメントの多くのチュートリアルおよび例に示されているEMP_EVALというパッケージの仕様を作成する方法について説明します。
パッケージ仕様部を作成するには、SQL Developerのパッケージの作成ツールまたはDDL文のCREATE PACKAGEを使用します。
パッケージの作成ツールを使用してパッケージ仕様部を作成するには、次の手順を実行します。
関連項目:
CREATE PACKAGE文(パッケージ仕様部)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: パッケージの作成および管理
5.6.3 チュートリアル: パッケージ仕様部の変更
このチュートリアルでは、編集ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの仕様を変更する方法について説明します。具体的には、このチュートリアルでは、プロシージャ、EVAL_DEPARTMENTおよび関数CALCULATE_SCOREの宣言を追加する方法を表示されます。
パッケージ仕様部を変更するには、SQL Developerの編集ツール、またはOR REPLACE句を持つCREATE PACKAGEDDL文を使用します。
編集ツールを使用してEMP_EVALパッケージ仕様部を変更するには、次の手順を実行します。
関連項目:
OR REPLACE句が指定されたCREATE PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』
を参照してください。
親トピック: パッケージの作成および管理
5.6.4 チュートリアル: パッケージ本体の作成
このチュートリアルでは、本体の作成ツールを使用して、このドキュメントの多くのチュートリアルおよび列に示されているEMP_EVALパッケージの本体を作成する方法について説明します。
パッケージ本体を作成するには、SQL Developerの本体の作成ツールまたはDDL文のCREATE PACKAGE BODYを使用します。
本体の作成ツールを使用してEMP_EVALパッケージの本体を作成するには、次の手順を実行します。
関連項目:
CREATE PACKAGE BODY文(パッケージ本体)の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: パッケージの作成および管理
5.6.5 パッケージの削除
パッケージ(仕様および本体)を削除するには、SQL Developerの「接続」フレームと削除ツールまたはDDL文DROP PACKAGEのいずれかを使用します。
注意:
パッケージEMP_EVALは今後のチュートリアルで必要なため、削除しないでください。パッケージの削除の実習を行う場合は、簡単なパッケージを作成してから削除してください。
削除ツールを使用してパッケージを削除するには、次の手順を実行します。
関連項目:
DROP PACKAGE文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: パッケージの作成および管理
5.7 変数および定数の宣言と値の割当て
パッケージ仕様で宣言された変数または定数は、このパッケージにアクセスしている任意のプログラムで使用可能です。パッケージ本体またはサブプログラムで宣言された変数または定数は、そのパッケージまたはサブプログラムに対してローカルです。定数を宣言する場合、その定数に初期値を割り当てる必要があります。
PL/SQLがSQLより優れている点の1つは、PL/SQLでは変数および定数を宣言して使用できることです。
パッケージ仕様で宣言された変数または定数は、このパッケージにアクセスしている任意のプログラムで使用可能です。パッケージ本体またはサブプログラムで宣言された変数または定数は、そのパッケージまたはサブプログラムに対してローカルです。
変数には特定のデータ型の値が格納されます。ご使用のプログラムで、実行時に値を変更できます。 定数には、変更できない値が格納されます。
変数または定数には、任意のPL/SQLデータ型を指定できます。変数を宣言する際に初期値を割り当てることができ、割り当てない場合はこの値がNULLになります。定数を宣言する場合、その定数に初期値を割り当てる必要があります。初期値を変数または定数に割り当てるには、代入演算子(:=
)を使用します。
ヒント:
変わらないすべての値を定数として宣言します。これによってコンパイル・コードが最適化され、ソース・コードがメンテナンスしやすくなります。
- チュートリアル: サブプログラムでの変数および定数の宣言
このチュートリアルでは、SQL Developerの編集ツールを使用してEMP_EVAL.CALCULATE_SCOREファンクションで変数および制約を宣言する方法を示します。(このチュートリアルは、パッケージ本体を変更する例でもあります。) - 変数、定数およびパラメータのデータ型が正しいことの確認
変数、定数およびパラメータのデータ型が正しいことを確認するには、これらを%TYPE属性で宣言します。 - チュートリアル: %TYPE属性を使用するための宣言の変更
このチュートリアルでは、SQL Developerツールの「編集」を使用して、EMP_EVAL.CALCULATE_SCOREファンクションの変数、定数および仮パラメータの宣言を変更して%TYPE属性を使用する方法を示します。 - 変数への値の代入
関連項目:
変数および定数の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.7.1 チュートリアル: サブプログラムでの変数および定数の宣言
このチュートリアルでは、SQL Developerの編集ツールを使用してEMP_EVAL.CALCULATE_SCOREファンクションで変数および制約を宣言する方法を示します。(このチュートリアルは、パッケージ本体を変更する例でもあります。)
EMP_EVAL.CALCULATE_SCOREファンクションは、「チュートリアル: パッケージ仕様部の作成」で指定されています。
CALCULATE_SCORE関数の変数および定数を宣言するには、次のようにします。
関連項目:
-
変数および定数の宣言の一般情報は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 変数および定数の宣言と値の割当て
5.7.2 変数、定数およびパラメータのデータ型が正しいことの確認
変数、定数およびパラメータのデータ型が正しいことを確認するには、これらを%TYPE属性で宣言します。
「チュートリアル: サブプログラムでの変数および定数の宣言」の後の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属性は、表の列または別の変数のデータ型を提供し、正しいデータ型の割当てを保証します。
関連項目:
-
%TYPE
属性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
%TYPE属性の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 変数および定数の宣言と値の割当て
5.7.3 チュートリアル: %TYPE属性を使用するための宣言の変更
このチュートリアルでは、SQL Developerの編集ツールを使用して、EMP_EVAL.CALCULATE_SCOREファンクションの変数、定数および仮パラメータの宣言を変更して%TYPE属性を使用する方法を示します。
EMP_EVAL.CALCULATE_SCOREファンクションは、「チュートリアル: サブプログラムでの変数および定数の宣言」で指定されています。
CALCULATE_SCOREの宣言を変更して%TYPEを使用するには、次のようにします。
親トピック: 変数および定数の宣言と値の割当て
5.7.4 変数への値の代入
次の方法で変数に値を割り当てることができます。
-
代入演算子を使用して、式の値を割り当てます。
-
SELECT INTO文またはFETCH文を使用して、表の値を割り当てます。
-
OUTパラメータまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で値を代入する方法。
-
変数を値にバインドします。
- 代入演算子を使用した変数への値の割当て
代入演算子(:=
)を使用して、サブプログラムの宣言部分または実行可能部分の変数に式の値を割り当てることができます。 - SELECT INTO文を使用した変数への値の割当て
サブプログラムまたはパッケージの表の値を使用するには、SELECT INTO文によって変数に値を割り当てる必要があります。
関連項目:
-
変数への値の割当ての詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
変数のバインドの詳細は、『Oracle Database 2日でJava開発者ガイド』を参照してください。
親トピック: 変数および定数の宣言と値の割当て
5.7.4.1 代入演算子を使用した変数への値の割当て
代入演算子(:=
)を使用して、サブプログラムの宣言部または実行可能部の変数に式の値を割り当てることができます。
サブプログラムの宣言部分では、宣言時に、変数に初期値を割り当てることができます。構文は次のとおりです。
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である必要があります。)
関連項目:
-
変数宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
代入文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例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;
親トピック: 変数への値の代入
5.7.4.2 SELECT INTO文を使用した変数への値の代入
サブプログラムまたはパッケージの表の値を使用するには、SELECT INTO文によって変数に値を割り当てる必要があります。
例5-2では、表の値からrunning_totalを計算させるためにEMP_EVAL.CALCULATE_SCOREファンクションに対して加える変更を太字で示しています。
例5-3に示すADD_EVALプロシージャは、EVALUATIONS表への行挿入に、EMPLOYEES表の対応する行の値を使用する場合の例です。ADD_EVALプロシージャは、EMP_EVALパッケージの本体にのみ追加し、仕様には追加しません。ADD_EVALは仕様内には定義しないので、そのパッケージのローカル・プロシージャになり、パッケージ内の他のサブプログラムからのみ起動でき、パッケージ外部からは起動できません。
関連項目:
SELECT INTO文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例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 他の表からの値を使用した表の行の挿入
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;
親トピック: 変数への値の代入
5.8 プログラム・フローの制御
入力順に実行する文であるSQLとは異なり、PL/SQLには、プログラムのフローを制御できる制御文があります。
- 制御文について
PL/SQLには、条件付き選択文、繰り返し文および順次制御文の3つのカテゴリの制御文があります。 - IF文の使用
IF文は、ブール式の値に応じて一連の文を実行またはスキップします。 - CASE文の使用
CASE文は、一連の条件から選択し、対応する文を実行します。 - FOR LOOP文の使用
FOR LOOP文は、lower_boundからupper_boundまでの範囲の各整数に対して1回ずつ、一連の文を繰り返します。 - WHILE LOOP文の使用
WHILE LOOP文は、条件がTRUEであるかぎり一連の文を繰り返します。 - 基本のLOOPおよびEXIT WHEN文の使用
基本のLOOP文は、一連の文を繰り返します。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.8.1 制御文について
PL/SQLには、条件付き選択文、繰り返し文および順次制御文の3つのカテゴリの制御文があります。
条件選択文では、異なるデータ値に対して異なる文を実行できます。条件選択文は、IF
およびCASE
です。
繰り返し文では、一連の異なるデータ値で同じ文を繰り返すことができます。繰り返し文は、FOR
LOOP
、WHILE
LOOP
,および基本のLOOP
です。EXIT
文は、制御をループの終わりに転送します。 CONTINUE
文は、現在のループの反復を終了し、制御を次の反復に転送します。 EXIT
およびCONTINUE
には、オプションのWHEN
句があり、条件を指定できます。
順次制御文では、指定されたラベル付き文に移動するか、または何も処理をしません。順次制御文は、GOTO
およびNULL
です。
関連項目:
PL/SQL制御文の概要は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: プログラム・フローの制御
5.8.2 IF文の使用
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は、仕様にはないため、パッケージに対してローカルであり、パッケージ内の他のサブプログラムでのみ起動でき、パッケージ外からは起動できません。
ヒント:
SQL文でPL/SQL変数を使用する場合、例5-4の2つ目のSELECT文に示すとおり、変数をサブプログラム名で修飾して、表の列と間違えないようにします。
関連項目:
-
IF文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
IF文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
例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;
親トピック: プログラム・フローの制御
5.8.3 CASE文の使用
CASE文は、一連の条件から選択し、対応する文を実行します。
単純な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回のみ評価され、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;
関連項目:
-
CASE文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
CASE文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: プログラム・フローの制御
5.8.4 FOR LOOP文の使用
FOR LOOP文は、lower_boundからupper_boundまでの範囲の各整数に対して1回ずつ、一連の文を繰り返します。
FOR LOOPの構文は次のとおりです。
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;
関連項目:
親トピック: プログラム・フローの制御
5.8.5 WHILE LOOP文の使用
WHILE LOOP文は、条件がTRUEであるかぎり一連の文を繰り返します。
WHILE LOOP文の構文は次のとおりです。
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;
関連項目:
-
WHILE LOOP文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
WHILE LOOP文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: プログラム・フローの制御
5.8.6 基本のLOOPおよびEXIT WHEN文の使用
基本のLOOP文は、一連の文を繰り返します。
基本の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;
関連項目:
-
LOOP文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
EXIT文の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
LOOP文およびEXIT文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: プログラム・フローの制御
5.9 レコードおよびカーソルの使用
レコードにデータ値を格納し、カーソルを結果セットおよび関連の処理情報へのポインタとして使用できます。
- レコードについて
レコードは、様々な型のデータ値を格納できるPL/SQLコンポジット変数です。内部コンポーネント(フィールド)はスカラー変数と同様に処理できます。サブプログラム・パラメータとしてレコード全体を渡すことができます。レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。 - チュートリアル: RECORD型の宣言
このチュートリアルでは、SQL Developerの編集ツールを使用してRECORD型のsal_infoを宣言する方法を示します。フィールドには、ジョブID、そのジョブIDの給与の最小値および最大値、現在の給与、推奨される値上げなどの、従業員の給与情報を格納できます。 - チュートリアル: レコード・パラメータによるサブプログラムの作成および起動
このチュートリアルでは、SQL Developerの編集ツールを使用してレコード型sal_infoのパラメータでサブプログラムを作成および起動する方法を示します。 - カーソルについて
Oracle Databaseは、SQL文の実行時に、結果セットおよび処理情報を無名のプライベートSQL領域に保存します。この名前のない領域へのポインタは、カーソルと呼ばれ、これを使用して結果セットを1行ずつ取得することができます。 カーソル属性は、カーソルの状態に関する情報を戻します。 - チュートリアル: 宣言カーソルを使用して結果セットの行を1行ずつ取得
宣言カーソルを使用して結果セットの行を1行ずつ取得できます。 - チュートリアル: 宣言カーソルを使用して結果セットの行を1行ずつ取得
このチュートリアルでは、宣言カーソルemp_cursorを使用するプロシージャEMP_EVAL.EVAL_DEPARTMENTの実装方法を示します。 - カーソル変数について
カーソル変数は、問合せを1つに制限しないカーソルと似ています。カーソル変数を問合せに対してオープンし、結果セットを処理した後、カーソル変数を別の問合せのために使用できます。カーソル変数は、サブプログラム間で問合せ結果を渡すときに有用です。 - 結果セットの行を1行ずつ取得するためのカーソル変数の使用
カーソル変数を使用して結果セットの行を1行ずつ取得できます。 - チュートリアル: 結果セット行を1つずつ取得するためのカーソル変数の使用
このチュートリアルでは、EMP_EVAL.EVAL_DEPARTMENTプロシージャを変更して、宣言カーソル(複数の部門を処理できる)のかわりにカーソル変数を使用する方法と、EMP_EVAL.EVAL_DEPARTMENTおよびEMP_EVAL.ADD_EVALのより効率的な使用方法を示します。
関連項目:
レコードの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.9.1 レコードについて
レコードは、様々な型のデータ値を格納できるPL/SQLコンポジット変数です。内部コンポーネント(フィールド)はスカラー変数と同様に処理できます。サブプログラム・パラメータとしてレコード全体を渡すことができます。レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。
レコードは、C、C++、Javaなどのstruct型に似た、異なる型のデータ値を格納できるPL/SQLの複合変数です。レコードの内部コンポーネントは、フィールドと呼ばれます。レコード・フィールドにアクセスするには、ドット表記法record_name.field_nameを使用します。
レコード・フィールドは、スカラー変数のように扱うことができます。サブプログラム・パラメータとしてレコード全体を渡すこともできます。
レコードは、表の行からのデータ、または表の行の特定列からのデータを格納するのに便利です。各レコード・フィールドは表の列に対応しています。
レコードを作成するには、3つの方法があります。
-
レコード型を宣言し、その型の変数を宣言する。
構文は次のとおりです。
TYPE record_name IS RECORD ( field_name data_type [:= initial_value] [, field_name data_type [:= initial_value ] ]... ); variable_name record_name;
-
table_name%ROWTYPE型の変数を宣言します。
レコードのフィールドの名前およびデータ型は、表の列と同じです。
-
cursor_name%ROWTYPE型の変数を宣言します。
レコードのフィールドの名前およびデータ型は、カーソルSELECT文のFROM句の表の列と同じです。
関連項目:
-
RECORD型の定義およびその型のレコードの宣言の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
RECORD型の定義の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
%ROWTYPE属性の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
%ROWTYPE属性の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: レコードおよびカーソルの使用
5.9.2 チュートリアル: RECORD型の宣言
このチュートリアルでは、SQL Developerの編集ツールを使用してsal_infoというRECORD型を宣言する方法について説明します。この型には、各従業員の給与情報(ジョブID、最小および最大給与、現在の給与、推奨される増加額など)を保持するためのフィールドを指定します。
RECORD型sal_infoを宣言するには、次の手順を実行します。
親トピック: レコードおよびカーソルの使用
5.9.3 チュートリアル: レコード・パラメータによるサブプログラムの作成および起動
このチュートリアルでは、SQL Developerの編集ツールを使用してレコード型sal_infoのパラメータでサブプログラムを作成および起動する方法を示します。
レコード型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を変更するには、次の手順を実行します。
親トピック: レコードおよびカーソルの使用
5.9.4 カーソルについて
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 カーソル属性の値
属性 | 宣言カーソルの値 | 暗黙カーソルの値 |
---|---|---|
%FOUND |
カーソルは開くが脚注1フェッチが試行されない場合は、NULLです。 最後のフェッチが行を戻した場合、TRUE。 最後のフェッチが行を戻さなかった場合、FALSE。 |
DMLまたはSELECT INTO文が実行されていない場合、NULL。 最後のDMLまたはSELECT INTO文が行を戻した場合、TRUE。 最後のDMLまたはSELECT INTO文が行を戻さなかった場合、FALSE。 |
%NOTFOUND |
カーソルは開くが脚注1フェッチが試行されない場合は、NULLです。 最後のフェッチが行を戻した場合、FALSE。 最後のフェッチが行を戻さなかった場合、TRUE。 |
DMLまたはSELECT INTO文が実行されていない場合、NULL。 最後のDMLまたはSELECT INTO文が行を戻した場合、 最後のDMLまたはSELECT INTO文が行を戻さなかった場合、 |
%ROWCOUNT |
カーソルが開く場合脚注1、0以上の数字です。 |
DMLまたはSELECT INTO文が実行されていない場合、NULL。それ以外の場合は、0以上の数字。 |
%ISOPEN |
カーソルがオープンされている場合、TRUE。オープンされていない場合、FALSE。 |
常にFALSE。 |
脚注1
カーソルが開いていない場合、属性は事前定義済の例外INVALID_CURSORを発生します。
関連項目:
-
SELECT INTO文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
PL/SQLでのカーソルの管理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: レコードおよびカーソルの使用
5.9.5 宣言カーソルを使用して結果セットの行を1行ずつ取得
宣言カーソルを使用して結果セットの行を1行ずつ取得できます。
次の手順では、最も単純な形式で必要な各文を使用し、複雑な構文への参照も提示します。
宣言カーソルを使用して、結果セットの行を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言語リファレンス』を参照してください。
-
親トピック: レコードおよびカーソルの使用
5.9.6 チュートリアル: 宣言カーソルを使用して結果セットの行を1行ずつ取得
このチュートリアルでは、宣言カーソルemp_cursorを使用するプロシージャEMP_EVAL.EVAL_DEPARTMENTの実装方法を示します。
EMP_EVAL.EVAL_DEPARTMENTプロシージャを実装するには、次の手順を実行します。
親トピック: レコードおよびカーソルの使用
5.9.7 カーソル変数について
カーソル変数は、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文を使用して)別の特定の問合せに対してオープンする必要があります。カーソル変数を別の問合せに対してオープンすると、前の問合せに対してはクローズされます。特定の問合せに対してカーソル変数をクローズした後は、その問合せの結果セットからレコードをフェッチしたり、カーソル属性の値を参照することはできません。
関連項目:
-
カーソル変数の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
カーソル変数宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: レコードおよびカーソルの使用
5.9.8 結果セット行を1つずつ取得するためのカーソル変数の使用
カーソル変数を使用して結果セットの行を1行ずつ取得できます。
次の手順では、最も単純な形式で必要な各文を使用し、複雑な構文への参照も提示します。
カーソル変数を使用して結果セット行を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言語リファレンス』を参照してください。
-
親トピック: レコードおよびカーソルの使用
5.9.9 チュートリアル: 結果セット行を1つずつ取得するためのカーソル変数の使用
このチュートリアルでは、EMP_EVAL.EVAL_DEPARTMENTプロシージャを変更して、宣言カーソル(複数の部門を処理できる)のかわりにカーソル変数を使用する方法と、EMP_EVAL.EVAL_DEPARTMENTおよびEMP_EVAL.ADD_EVALのより効率的な使用方法を示します。
このチュートリアルでは、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プロシージャを変更して、カーソル変数を使用するには、次の手順を実行します。
親トピック: レコードおよびカーソルの使用
5.10 連想配列の使用
連想配列は、コレクションの型です。
- コレクションについて
コレクションは、指定した順序で同じ型の要素を格納するPL/SQLのコンポジット変数で、一次元配列に似ています。コレクションの内部コンポーネントは、要素と呼ばれます。各要素には、コレクション内での位置を識別する一意のサブスクリプトがあります。 - 連想配列について
連想配列は、制限のない一連のキーと値のペアです。各キーは一意であり、対応する値を保持する要素のサブスクリプトとして機能します。そのため、配列内の位置がわからなくても、また配列を横断しなくても要素にアクセスできます。 - 連想配列の宣言
連想配列を宣言するには、連想配列の型を宣言し、その型の変数を宣言します。 - 連想配列の移入
通常、稠密な連想配列を移入する最も効率的な方法は、BULK COLLECT INTO句を含むSELECT文を使用することです。 - 稠密な連想配列のトラバース
稠密な連想配列(整数による索引付け)には、要素間の差異がなく、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値はNULLの場合もあります)。 - スパースな連想配列のトラバース
スパースな連想配列(文字列による索引付け)には、要素間に差異がある可能性があります。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.10.1 コレクションについて
コレクションは、1次元配列に似ていて、同じ型の要素を指定された順序で格納するPL/SQLの複合変数です。コレクションの内部コンポーネントは、要素と呼ばれます。各要素には、コレクション内での位置を識別する一意のサブスクリプトがあります。
コレクション要素にアクセスするには、サブスクリプト表記法collection_name(element_subscript)を使用します。
コレクション要素は、スカラー変数のように扱うことができます。また、コレクション全体をサブプログラムのパラメータとして渡すこともできます(送信または受信サブプログラムのどちらもスタンドアロンのサブプログラムでない場合)。
コレクション・メソッドは、コレクションに関する情報を戻す、またはコレクション上で動作する埋込みPL/SQLサブプログラムです。コレクション・メソッドを起動するには、ドット表記法collection_name.method_nameを使用します。たとえば、collection_name.COUNTはコレクションの要素の数を戻します。
PL/SQLには、次の3つの型のコレクションがあります。
-
連想配列(以前の「PL/SQL表」または「索引付き表」)
-
ネストした表
-
可変配列(VARRAY)
このマニュアルでは、連想配列のみを説明します。
関連項目:
-
PL/SQLコレクション型の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
コレクション・メソッドの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 連想配列の使用
5.10.2 連想配列について
連想配列は、制限のない一連のキーと値のペアです。各キーは一意であり、対応する値を保持する要素のサブスクリプトとして機能します。そのため、配列内の位置がわからなくても、また配列を横断しなくても要素にアクセスできます。
キーのデータ型は、PLS_INTEGERまたはVARCHAR2(length).です
キーのデータ型がPLS_INTEGERで、連想配列が整数で索引付けされ、稠密(つまり、要素間に差異がない)である場合、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値はNULLの場合もあります)。
キー・タイプがVARCHAR2 (length)の場合、連想配列は文字列(length文字)で索引付けされ、スパースです(つまり、要素間に差異がある可能性があります)。
稠密な連想配列をトラバースする場合、要素間の差異を考慮する必要はありません。スパースな連想配列をトラバースする場合は、要素間の差異を考慮する必要があります。
連想配列の要素に値を割り当てるには、代入演算子を使用できます。
array_name(key) := value
キーが配列にない場合、代入文によって配列にキー-値のペアが追加されます。そうでない場合、文がarray_name(key)の値をvalueに変更します。
連想配列は、データの一時的な格納に便利です。連想配列では、表が必要とするディスク領域やネットワーク操作を使用しません。ただし、連想配列は、データを一時的に格納する用途のため、DML文で操作できません。
パッケージ内で連想配列を宣言し、パッケージ本体の変数に値を割り当てると、連想配列はデータベース・セッションの存続期間中に存在します。そうでない場合は、宣言をしたサブプログラムが存続するかぎり存在します。
関連項目:
連想配列の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 連想配列の使用
5.10.3 連想配列の宣言
連想配列を宣言するには、連想配列の型を宣言し、その型の変数を宣言します。
次に、最も単純な構文を示します。
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; /
関連項目:
-
連想配列宣言の構文については、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 連想配列の使用
5.10.4 連想配列の移入
通常、稠密な連想配列を移入する最も効率的な方法は、BULK COLLECT INTO句を含むSELECT文を使用することです。
注意:
稠密な連想配列が非常に大きいため、SELECT文が大きすぎてメモリーに収まらない結果セットを戻す場合、SELECT文を使用しないでください。かわりに、カーソルおよびBULK COLLECT INTOおよびLIMIT句を含むFETCH文で配列を移入します。BULK COLLECT INTO句を含むFETCH文の使用の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
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; /
関連項目:
親トピック: 連想配列の使用
5.10.5 稠密連想配列の横断
稠密な連想配列(整数による索引付け)には、要素間の差異がなく、最初と最後の要素の間のすべての要素が定義されていて、それぞれに値があります(値は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.10.6 スパース連想配列の横断
スパース連想配列(文字列によって索引付けされたもの)は、要素間に差分がある場合があります。
例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
例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.11 例外の処理(実行時エラー)
PL/SQLコードで実行時に発生する例外を処理できます。
- 例外および例外ハンドラについて
PL/SQLコードで実行時エラーが発生すると、例外が発生します。例外が発生したサブプログラム(またはブロック)に例外処理部分がある場合は制御が転送され、そうでない場合は実行が停止します。 - 例外ハンドラを使用するタイミング
次の状況でのみ、例外ハンドラを使用することをお薦めします。 - 事前定義済の例外の処理
事前定義済の例外を処理できます。 - ユーザー定義の例外の宣言および処理
ユーザー定義の例外を宣言および処理できます。
関連項目:
PL/SQLエラーの処理の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: ストアド・サブプログラムおよびパッケージの開発
5.11.1 例外および例外ハンドラについて
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のエラー番号に関連付けられます。
関連項目:
-
例外宣言の構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
例外ハンドラの構文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
-
EXCEPTION_INITプラグマの詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
親トピック: 例外の処理(実行時エラー)
5.11.2 例外ハンドラを使用するタイミング
次の状況でのみ、例外ハンドラを使用することをお薦めします。
-
例外を予期して処理します。
たとえば、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.11.3 事前定義済の例外の処理
事前定義済の例外を処理できます。
例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.11.4 ユーザー定義の例外の宣言および処理
ユーザー定義の例外を宣言および処理できます。
例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言語リファレンス』を参照してください。
親トピック: 例外の処理(実行時エラー)