| Oracle® Database PL/SQL言語リファレンス 11gリリース2 (11.2) B56260-09 |
|
 前 |
 次 |
この章では、互いに関連するPL/SQLコードとデータを1つにまとめ、複数のアプリケーションで使用できる内容を持つパッケージにする方法について説明します。
ここでのトピック
|
関連項目:
|
パッケージとは、論理的に関連するPL/SQLの型、変数、定数、サブプログラム、カーソルおよび例外をグループにまとめたスキーマ・オブジェクトのことです。パッケージをコンパイルしてデータベースに格納し、その内容を複数のアプリケーションで共有できます。パッケージはアプリケーションと考えることができます。
パッケージには必ず仕様部があり、パッケージの外から参照できるパブリック項目はここで宣言します。パッケージ仕様部はApplication Programming Interface(API)と考えることができます。パッケージ仕様部の詳細は、「パッケージ仕様部」を参照してください。
パブリック項目にカーソルまたはサブプログラムが含まれる場合、パッケージには本体も必要です。本体には、パブリック・カーソルの問合せとパブリック・サブプログラムのコードを定義する必要があります。本体では、パッケージの外からは参照できなくてもパッケージの内部動作に必要なプライベート項目を宣言および定義することもできます。本体の最後には初期化部(ここには、変数を初期化する文と、その他の1回のみの設定手順を実行する文を入れます)、および例外処理部を配置することができます。本体は、仕様部またはパブリック項目への参照を変更せずに変更できるため、パッケージ本体はブラック・ボックスと考えることができます。パッケージ本体の詳細は、「パッケージ本体」を参照してください。
パッケージ仕様部またはパッケージ本体のいずれでも、パッケージ・サブプログラムを外部のJavaサブプログラムまたはCサブプログラムにマップできますが、その際にはコール仕様を使用して、外部サブプログラム名、パラメータ型および戻り型を対応するSQLにマップします。詳細は、「ファンクションの宣言および定義」と「プロシージャの宣言および定義」を参照してください。
パッケージ仕様部のAUTHID句は、パッケージのサブプログラムとカーソルをその定義者(デフォルト)と実行者のどちらの権限で実行するか、およびスキーマ・オブジェクトへの未修飾の参照が定義者と実行者のどちらのスキーマで解決されるかを決定します。詳細は、「実行者権限および定義者権限(AUTHIDプロパティ)」を参照してください。
パッケージは、信頼性のある再利用可能なコードの開発およびメンテナンスを次の機能で支援します。
モジュール性
パッケージを使用すると、論理的に関連した型、変数、定数、サブプログラム、カーソルおよび例外を、名前付きのPL/SQLモジュールにカプセル化できます。個々のパッケージを理解しやすくし、パッケージ間のインタフェースを単純かつ明快で、明確に定義することができます。この方法はアプリケーション開発に役立ちます。
アプリケーションの設計の容易さ
アプリケーション設計の最初の段階では、パッケージの仕様部に含まれるインタフェース情報のみが必要です。仕様部は本体がなくてもコーディングし、コンパイルできます。その後、このパッケージを参照するスタンドアロン・サブプログラムをコンパイルできます。アプリケーション作成の最終段階になるまで、パッケージ本体を完全に定義する必要はありません。
情報の隠ぺい
パッケージを使用すると、パッケージ仕様部のインタフェース情報を共有でき、パッケージ本体の実装の細部を隠ぺいできます。本体の実装の細部を隠ぺいすることには次のメリットがあります。
アプリケーション・インタフェースに影響を与えずに実装の細部を変更できます。
アプリケーション・ユーザーは、開発者が変更する可能性のある実装の細部に依存するコードを開発できません。
機能の追加
パッケージのパブリック変数およびカーソルは、セッションが存続する間維持されます。このため、同じ環境の中で実行するすべてのサブプログラムで共有できます。これによって、データベースに格納することなくトランザクション間でデータをメンテナンスできます。(パッケージのパブリック変数およびカーソルが、セッションの存続する間維持されない状況は、「パッケージの状態」を参照してください。)
高いパフォーマンス
パッケージ・サブプログラムを初めて起動すると、パッケージ全体がOracle Databaseによってメモリーにロードされます。同じパッケージ内の他のサブプログラムの2度目以降の起動では、ディスクI/Oは必要ありません。
パッケージ化すると互いに依存することがなくなるため、不要な再コンパイルを避けることができます。たとえば、パッケージ・ファンクションの本体を変更した場合、そのファンクションを起動する他のサブプログラムは、仕様部で宣言されたパラメータと戻り値にのみ依存するため、Oracle Databaseによって再コンパイルされません。
パッケージ仕様部ではパブリック項目を宣言します。パブリック項目の有効範囲は、パッケージのスキーマです。パブリック項目はスキーマ内のあらゆる場所から参照できます。有効範囲内にあっても参照できないパブリック項目を参照するには、パッケージ名で修飾します。(有効範囲、可視性および修飾の詳細は、「識別子の有効範囲と可視性」を参照してください。)
各パブリック項目の宣言には、その項目を使用するうえで必要なすべての情報を含めます。たとえば、パッケージ仕様部でファンクションfactorialを次の方法で宣言するとします。
FUNCTION factorial (n INTEGER) RETURN INTEGER; -- returns n!
この宣言は、factorialという名前のファンクションがINTEGER型の引数を1つ取り、INTEGER型の値を戻すことを示しており、これがfactorialを起動するうえで知っておく必要のあるすべてです。ユーザーはfactorialがどのように実装されているか(たとえば、それが反復を利用しているのか、再帰を利用しているのかなど)を知る必要はありません。
ここでのトピック
複数のサブプログラムで使用される型、変数、定数、サブプログラム、カーソルおよび例外
パッケージ仕様部で定義される型は、ユーザー定義のPL/SQLサブタイプ(「ユーザー定義のPL/SQLサブタイプ」を参照)またはPL/SQLコンポジット型(第5章「PL/SQLのコレクションおよびレコード」を参照)のいずれかです。
スタンドアロン・サブプログラム・パラメータの連想配列型
スキーマ・レベルでは連想配列型を宣言できません。そのため、連想配列の変数をパラメータとしてスタンドアロン・サブプログラムに渡すには、その変数の型をパッケージ仕様部で宣言する必要があります。こうすると、起動されるサブプログラム(この型の仮パラメータを宣言する側)と起動元のサブプログラムまたは無名ブロック(この型の変数を宣言する側)の両方でこの型を使用できます。例10-2を参照してください。
同じセッション内のサブプログラム起動間で使用可能な状態に保つ必要がある変数
パブリック変数の読取りおよび書込みを実行するサブプログラム("get"および"set"サブプログラム)
これらのサブプログラムは、パッケージ・ユーザーにパブリック変数の読取りおよび書込みを直接実行させないために提供します。
相互に起動し合うサブプログラム
互いに起動し合うスタンドアロン・サブプログラムの場合はコンパイルの順序を気にする必要がありますが、パッケージ・サブプログラムの場合はとらわれる必要はありません。
オーバーロードされたサブプログラム
オーバーロードされたサブプログラムとは、同じサブプログラムのバリエーションです。つまり、名前は同じですが、仮パラメータが異なります。これらの詳細は、「オーバーロードされたサブプログラム」を参照してください。
パッケージ仕様部を作成するには、「CREATE PACKAGE文」を使用します。
例10-1では、パッケージtrans_dataの仕様部で2つのパブリック型と3つのパブリック変数を宣言しています。
例10-1 単純なパッケージ仕様部
CREATE OR REPLACE PACKAGE trans_data AS
TYPE TimeRec IS RECORD (
minutes SMALLINT,
hours SMALLINT);
TYPE TransRec IS RECORD (
category VARCHAR2(10),
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;
/
例10-2では、パッケージaa_pkgの仕様部で連想配列型、aa_typeを宣言しています。その後、スタンドアロン・プロシージャprint_aaで型aa_typeの仮パラメータを宣言しています。次に、無名ブロックで型aa_typeの変数を宣言してこれに移入し、この変数を出力するプロシージャprint_aaに変数を渡しています。
例10-2 スタンドアロン・サブプログラムへの連想配列の受渡し
CREATE OR REPLACE PACKAGE aa_pkg IS TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15); END; / CREATE OR REPLACE PROCEDURE print_aa ( aa aa_pkg.aa_type ) IS i VARCHAR2(15); BEGIN i := aa.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i); i := aa.NEXT(i); END LOOP; END; / DECLARE aa_var aa_pkg.aa_type; BEGIN aa_var('zero') := 0; aa_var('one') := 1; aa_var('two') := 2; print_aa(aa_var); END; /
結果:
1 one 2 two 0 zero
例10-1および例10-2のパッケージ仕様部ではカーソルまたはサブプログラムを宣言していないため、パッケージtrans_dataおよびaa_pkgに本体は不要です。
パッケージ仕様部でカーソルまたはサブプログラムを宣言している場合は、パッケージ本体が必要ですが、それ以外の場合、本体はオプションです。パッケージ本体とパッケージ仕様部は同じスキーマ内にある必要があります。
パッケージ仕様部内のカーソル宣言またはサブプログラム宣言の1つ1つに対応する定義がパッケージ本体にある必要があります。対応するサブプログラム宣言と定義のヘッダーは、空白以外の一語一語が一致している必要があります。
パッケージ本体を作成するには、CREATE PACKAGE BODY文を使用します。
例10-3では、対応するサブプログラム宣言と定義のヘッダーが完全一致していません。そのため、employees.hire_date%TYPEがDATEであってもPL/SQLは例外を呼び出します。
例10-3 パッケージの仕様部と本体の一致
CREATE PACKAGE emp_bonus AS PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE); END emp_bonus; / CREATE PACKAGE BODY emp_bonus AS -- DATE does not match employees.hire_date%TYPE PROCEDURE calc_bonus (date_hired DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE ('Employees hired on ' || date_hired || ' get bonus.'); END; END emp_bonus; /
結果:
Warning: Package Body created with compilation errors.
SQL*Plusでのエラーの表示:
SHOW ERRORS
結果:
Errors for PACKAGE BODY EMP_BONUS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13 PLS-00323: subprogram or cursor 'CALC_BONUS' is declared in a
package specification and must be defined in the package body
問題の修正:
CREATE OR REPLACE PACKAGE BODY emp_bonus AS
PROCEDURE calc_bonus
(date_hired employees.hire_date%TYPE) IS
BEGIN
DBMS_OUTPUT.PUT_LINE
('Employees hired on ' || date_hired || ' get bonus.');
END;
END emp_bonus;
/
結果:
Package body created.
パッケージ仕様部で宣言し、パッケージ本体で定義したカーソルおよびサブプログラムは、パッケージの外から参照できるパブリック項目です。パッケージ本体では、パッケージの外からは参照できなくてもパッケージの内部動作に必要なプライベート項目を宣言および定義することもできます。
本体の最後には初期化部を配置することができ、ここには、パブリック変数を初期化する文と、その他の1回のみの設定手順を実行する文を入れます。初期化部は、パッケージが初めて参照されたときにのみ実行されます。初期化部には例外ハンドラを含めることができます。
パッケージ本体は、仕様部またはパブリック項目への参照を変更せずに変更できます。
セッションがパッケージ項目を参照すると、Oracle Databaseはそのセッションのためにパッケージをインスタンス化します。パッケージを参照する各セッションは、そのパッケージの独自のインスタンス化を所有します。
Oracle Databaseがパッケージをインスタンス化すると、パッケージは初期化されます。初期化では、次の処理のうち適用できるものがすべて実行されます。
パブリック定数への初期値の代入
宣言で初期値が指定されているパブリック変数への初期値の代入
パッケージ本体の初期化部の実行
パッケージ(の仕様部または本体のいずれか)で宣言している変数、定数およびカーソルの値が、パッケージの状態を構成します。PL/SQLパッケージに1つ以上の変数、定数またはカーソルが宣言されている場合、このパッケージはステートフルですが、それ以外の場合はステートレスです。
パッケージ項目を参照する各セッションは、そのパッケージの独自のインスタンス化を所有します。パッケージがステートフルの場合は、インスタンス化にパッケージの状態が含まれます。パッケージの状態は、次の状況を除き、セッションが存続する間維持されます。
パッケージがSERIALLY_REUSABLEの場合。
詳細は「SERIALLY_REUSABLEパッケージ」を参照してください。
パッケージ本体が再コンパイルされている場合。
インスタンス化されたステートフル・パッケージの本体が再コンパイルされている場合は、(「ALTER PACKAGE文」を使用した明示的な再コンパイルであっても、暗黙的な再コンパイルであっても、)このパッケージで次にサブプログラムを起動すると、Oracle Databaseは既存のパッケージ状態を破棄して例外ORA-04068を呼び出します。
PL/SQLによって例外が呼び出された後にパッケージを参照すると、Oracle Databaseによりパッケージが再インスタンス化され、再初期化されます。したがって、パッケージ状態に対する以前の変更は失われます。(初期化の詳細は、「パッケージのインスタンス化および初期化」を参照してください。)
セッションが所有するインスタンス化されたパッケージのいずれかが、無効化された後に再有効化されている場合。
セッションが所有するインスタンス化されたパッケージのいずれかが無効化された後に再有効化されている場合、セッションのパッケージ・インスタンス化は(パッケージの状態を含め)すべて失われる可能性があります。スキーマ・オブジェクトの無効化および再有効化の詳細は、『Oracle Databaseアドバンスト・アプリケーション開発者ガイド』を参照してください。
Oracle Database 11gリリース2(11.2.0.2)の時点では、セッションが存続する間(またはそれより長い間)パッケージの状態が一定である場合、パッケージはOracle Databaseにステートレスとして処理されます。これは、パッケージの項目がすべてコンパイル時定数になっているパッケージの場合です。
コンパイル時定数とは、コンパイル時にPL/SQLコンパイラで値を決定できる定数のことです。初期値にリテラルが指定されている定数は、必ずコンパイル時定数になります。初期値にリテラル以外が指定されていても、オプティマイザによりリテラルに縮約される定数も、コンパイル時定数です。リテラル以外の式をPL/SQLオプティマイザでリテラルに縮約できるかどうかは、最適化レベルによって異なります。そのため、ある最適化レベルでコンパイルするとステートレスになるパッケージが、別の最適化レベルでコンパイルするとステートフルになることもあります。オプティマイザの詳細は、「PL/SQLオプティマイザ」を参照してください。
SERIALLY_REUSABLEパッケージを使用すると、スケーラビリティを向上させるためにメモリーをより効率的に管理するアプリケーションを設計できます。
パッケージがSERIALLY_REUSABLEでない場合、そのパッケージ状態は各ユーザーのユーザー・グローバル領域(UGA)に格納されます。したがって、UGAのメモリー量をユーザー数に比例して増加させる必要があり、スケーラビリティが制限されます。パッケージの状態はセッションが存続する間維持でき、UGAメモリーはセッションが終了するまでロックされます。Oracle Officeなどの一部のアプリケーションの標準的なセッションは数日間存続します。
パッケージがSERIALLY_REUSABLEである場合、そのパッケージ状態はシステム・グローバル領域(SGA)内の小規模なプールにある作業領域に格納されます。パッケージの状態は、サーバー・コールが存続する間のみ維持されます。サーバー・コールの後、作業領域はプールに戻されます。以降のサーバー・コールがこのパッケージを参照すると、Oracle Databaseはこのプールからインスタンス化を再利用します。インスタンス化を再利用すると、インスタンス化は再初期化されるため、それまでのサーバー・コールで実行されたパッケージ状態に対する変更は参照できなくなります。(初期化の詳細は、「パッケージのインスタンス化および初期化」を参照してください。)
|
注意: データベース・トリガー、またはSQL文から起動される他のPL/SQLサブプログラムからSERIALLY_REUSABLEパッケージにアクセスしようとするとエラーが発生します。 |
ここでのトピック
SERIALLY_REUSABLEパッケージを作成するには、SERIALLY_REUSABLEプラグマをパッケージ仕様部とパッケージ本体(存在する場合)に含めます。
例10-4では、きわめて単純なSERIALLY_REUSABLEパッケージを2つ作成します。1つは仕様部のみで、もう1つは仕様部と本体があります。
例10-4 SERIALLY_REUSABLEパッケージの作成
-- Create bodiless SERIALLY_REUSABLE package: CREATE OR REPLACE PACKAGE bodiless_pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END; / -- Create SERIALLY_REUSABLE package with specification and body: CREATE OR REPLACE PACKAGE pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PRAGMA SERIALLY_REUSABLE; BEGIN n := 5; END; /
SERIALLY_REUSABLEパッケージの作業単位はサーバー・コールです。このパッケージのパブリック変数は、作業単位内のみで使用する必要があります。
|
注意: 誤って以前の作業単位で設定されたパブリック変数の値に依存した場合、そのプログラムは失敗する可能性があります。PL/SQLではこのようなケースはチェックできません。 |
例10-5の本体のないパッケージpkgとpkg_srは、pkg_srはSERIALLY_REUSABLEでpkgはそうでないという点以外同じです。各パッケージで、初期値に5が指定されたパブリック変数nを宣言します。その後、無名ブロックで各変数の値を10に変更します。次に、別の無名ブロックで各変数の値を出力します。pkgの状態はセッションが存続する間維持されるため、pkg.nの値は10のままです。pkg_srの状態はサーバー・コールが存続する間のみ維持されるため、pkg_sr.nの値は5です。
例10-5 SERIALLY_REUSABLEプラグマの影響
CREATE OR REPLACE PACKAGE pkg IS n NUMBER := 5; END pkg; / CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END sr_pkg; / BEGIN pkg.n := 10; sr_pkg.n := 10; END; / BEGIN DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n); DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n); END; /
結果:
pkg.n: 10 sr_pkg.n: 5
SERIALLY_REUSABLEパッケージの作業単位(サーバー・コール)が完了した後、Oracle Databaseにより次の処理が実行されます。
SERIALLY_REUSABLEパッケージの明示カーソルは、パッケージをクローズするかパッケージの作業単位(サーバー・コール)が終了するまでオープンされたままになります。カーソルを再度オープンするには、新しいサーバー・コールを作成する必要があります。例10-6に示すとおり、サーバー・コールがサブプログラム起動と異なる場合があります。
対照的に、SERIALLY_REUSABLEでないパッケージの明示カーソルは、パッケージをクローズするかセッションから切断するまでオープンされたままになります。
例10-6 コール境界でオープンされるSERIALLY_REUSABLEパッケージのカーソル
DROP TABLE people;
CREATE TABLE people (name VARCHAR2(20));
INSERT INTO people (name) VALUES ('John Smith');
INSERT INTO people (name) VALUES ('Mary Jones');
INSERT INTO people (name) VALUES ('Joe Brown');
INSERT INTO people (name) VALUES ('Jane White');
CREATE OR REPLACE PACKAGE sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
CURSOR c IS SELECT name FROM people;
END sr_pkg;
/
CREATE OR REPLACE PROCEDURE fetch_from_cursor IS
name_ VARCHAR2(200);
BEGIN
IF sr_pkg.c%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open.');
ELSE
DBMS_OUTPUT.PUT_LINE('Cursor is closed; opening now.');
OPEN sr_pkg.c;
END IF;
FETCH sr_pkg.c INTO name_;
DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_);
FETCH sr_pkg.c INTO name;
DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_);
END fetch_from_cursor;
/
サーバーへの1回目のコール:
BEGIN fetch_from_cursor; fetch_from_cursor; END; /
結果:
Cursor is closed; opening now. Fetched: John Smith Fetched: Mary Jones Cursor is open. Fetched: Joe Brown Fetched: Jane White
サーバーへの新たなコール:
BEGIN fetch_from_cursor; fetch_from_cursor; END; /
結果:
Cursor is closed; opening now. Fetched: John Smith Fetched: Mary Jones Cursor is open. Fetched: Joe Brown Fetched: Jane White
Oracle Databaseが提供するパッケージをよく理解して、その機能と重複する機能を持つパッケージを作成しないように注意してください。
Oracle Databaseが提供するパッケージの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。
別のアプリケーションで再利用できるように、パッケージに汎用性を持たせてください。
パッケージ本体の前にパッケージ仕様部を設計および定義してください。
パッケージ仕様部では、起動元のプログラムから見える必要のある項目のみを宣言してください。
こうすることで、他の開発者が実装の細部に不適切に依存しないようにすることができ、再コンパイルの必要性を削減できます。
パッケージ仕様部を変更する場合は、そのパッケージのパブリック・サブプログラムを起動するすべてのサブプログラムを再コンパイルする必要があります。パッケージ本体のみ変更する場合は、サブプログラムを再コンパイルする必要はありません。
例10-7に示すとおり、パブリック・カーソルはパッケージ仕様部で宣言し、パッケージ本体で定義してください。
こうすると、カーソルの問合せをパッケージ・ユーザーに見えなくすることができ、カーソル宣言を変更せずにカーソル問合せを変更することができます。
初期値は、宣言部ではなくパッケージ本体の初期化部で代入してください。
この方法には次のメリットがあります。
初期値を計算するコードをより複雑にすることができ、詳しく説明できます。
初期値の計算で例外が呼び出された場合、初期化部で独自の例外ハンドラを使用して例外を処理できます。
例10-7では、カーソルc1の宣言および定義が、パッケージemp_stuffの仕様部と本体にそれぞれ置かれています。このカーソル宣言では戻り値のデータ型のみを指定し、問合せは指定せず、問合せはカーソルの定義に置かれています(構文およびセマンティクスの詳細は、「明示カーソルの宣言および定義」を参照してください)。
例10-8では、表logとパッケージemp_adminを作成し、無名ブロックからパッケージ・サブプログラムを起動しています。パッケージには仕様部と本体の両方があります。
仕様部では、パブリック型、カーソルおよび例外と3つのパブリック・サブプログラムを宣言しています。1つのパブリック・サブプログラムはオーバーロードされたサブプログラムです(オーバーロードされたサブプログラムの詳細は、「オーバーロードされたサブプログラム」を参照してください)。
本体では、プライベート変数を宣言し、仕様部で宣言したパブリック・カーソルとサブプログラムを定義し、プライベート・ファンクションを宣言および定義しています。また、本体には初期化部が含まれています。
初期化部(無名ブロックがこのパッケージを初めて参照したときにのみ実行されます)では、表logに行を1行挿入し、プライベート変数number_hiredを0(ゼロ)に初期化しています。パッケージ・プロシージャhire_employeeが起動されるたびに、プライベート変数number_hiredが更新されます。
例10-8 emp_adminパッケージの作成
-- Log to track changes (not part of package): DROP TABLE log; CREATE TABLE log ( date_of_action DATE, user_id VARCHAR2(20), package_name VARCHAR2(30) ); -- Package specification: CREATE OR REPLACE PACKAGE emp_admin AS -- Declare public type, cursor, and exception: TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER); CURSOR desc_salary RETURN EmpRecTyp; invalid_salary EXCEPTION; -- Declare public subprograms: FUNCTION hire_employee ( last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER ) RETURN NUMBER; -- Overload preceding public subprogram: PROCEDURE fire_employee (emp_id NUMBER); PROCEDURE fire_employee (emp_email VARCHAR2); PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER); FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp; END emp_admin; / -- Package body: CREATE OR REPLACE PACKAGE BODY emp_admin AS number_hired NUMBER; -- private variable, visible only in this package -- Define cursor declared in package specification: CURSOR desc_salary RETURN EmpRecTyp IS SELECT employee_id, salary FROM employees ORDER BY salary DESC; -- Define subprograms declared in package specification: FUNCTION hire_employee ( last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER ) RETURN NUMBER IS new_emp_id NUMBER; BEGIN new_emp_id := employees_seq.NEXTVAL; INSERT INTO employees ( employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( new_emp_id, hire_employee.last_name, hire_employee.first_name, hire_employee.email, hire_employee.phone_number, SYSDATE, hire_employee.job_id, hire_employee.salary, hire_employee.commission_pct, hire_employee.manager_id, hire_employee.department_id ); number_hired := number_hired + 1; DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' || TO_CHAR(number_hired) ); RETURN new_emp_id; END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END fire_employee; PROCEDURE fire_employee (emp_email VARCHAR2) IS BEGIN DELETE FROM employees WHERE email = emp_email; END fire_employee; -- Define private function, available only inside package: FUNCTION sal_ok ( jobid VARCHAR2, sal NUMBER ) RETURN BOOLEAN IS min_sal NUMBER; max_sal NUMBER; BEGIN SELECT MIN(salary), MAX(salary) INTO min_sal, max_sal FROM employees WHERE job_id = jobid; RETURN (sal >= min_sal) AND (sal <= max_sal); END sal_ok; PROCEDURE raise_salary ( emp_id NUMBER, amount NUMBER ) IS sal NUMBER(8,2); jobid VARCHAR2(10); BEGIN SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = emp_id; IF sal_ok(jobid, sal + amount) THEN -- Invoke private function UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; ELSE RAISE invalid_salary; END IF; EXCEPTION WHEN invalid_salary THEN DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.'); END raise_salary; FUNCTION nth_highest_salary ( n NUMBER ) RETURN EmpRecTyp IS emp_rec EmpRecTyp; BEGIN OPEN desc_salary; FOR i IN 1..n LOOP FETCH desc_salary INTO emp_rec; END LOOP; CLOSE desc_salary; RETURN emp_rec; END nth_highest_salary; BEGIN -- initialization part of package body INSERT INTO log (date_of_action, user_id, package_name) VALUES (SYSDATE, USER, 'EMP_ADMIN'); number_hired := 0; END emp_admin; / -- Invoke packages subprograms in anonymous block: DECLARE new_emp_id NUMBER(6); BEGIN new_emp_id := emp_admin.hire_employee ( 'Belden', 'Enrique', 'EBELDEN', '555.111.2222', 'ST_CLERK', 2500, .1, 101, 110 ); DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR(new_emp_id)); emp_admin.raise_salary (new_emp_id, 100); DBMS_OUTPUT.PUT_LINE ( 'The 10th highest salary is '|| TO_CHAR (emp_admin.nth_highest_salary(10).sal) || ', belonging to employee: ' || TO_CHAR (emp_admin.nth_highest_salary(10).emp_id) ); emp_admin.fire_employee(new_emp_id); -- You can also delete the newly added employee as follows: -- emp_admin.fire_employee('EBELDEN'); END; /
結果は次のようになります。
The number of employees hired is 1 The employee id is 212 The 10th highest salary is 12075, belonging to employee: 168 There are now 107 employees.
STANDARDという名前のパッケージではPL/SQL環境を定義しています。このパッケージの仕様部では、パブリック型、変数、例外、サブプログラムを宣言し、それらは自動的にPL/SQLプログラムで使用可能になります。たとえば、パッケージSTANDARDでは、引数の絶対値を戻すファンクションABSを次のように宣言します。
FUNCTION ABS (n NUMBER) RETURN NUMBER;
パッケージSTANDARDの内容は、アプリケーションから直接見ることができます。その内容を参照する場合もパッケージ名に接頭辞を付けて修飾名にする必要はありません。たとえば、ABSはデータベース・トリガー、ストアド・サブプログラム、Oracleのツール製品または3GLアプリケーションから次のように起動できます。
abs_diff := ABS(x - y);
ユーザー独自のABSを宣言すると、ローカル宣言がパブリック宣言をオーバーライドします。ただし、次に示すとおり、完全な名前を指定して、SQLファンクションを起動できます。
abs_diff := STANDARD.ABS(x - y);
ほとんどのSQLファンクションはオーバーロードされています。たとえば、パッケージSTANDARDには次のような宣言があります。
FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2; FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2; FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2; FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;
PL/SQLは、仮パラメータと実パラメータの数とデータ型を比較して、どのTO_CHARの起動かを判定します。
