10 PL/SQLパッケージ

この章では、互いに関連するPL/SQLコードとデータを1つにまとめ、複数のアプリケーションで使用できる内容を持つパッケージにする方法について説明します。

ここでのトピック

• パッケージ

• パッケージを使用する理由

• パッケージ仕様部

• パッケージ本体

• パッケージのインスタンス化および初期化

• パッケージの状態

• SERIALLY_REUSABLEパッケージ

• パッケージ作成のガイドライン

• パッケージの例

• STANDARDパッケージによるPL/SQL環境の定義

10.1 パッケージ

パッケージとは、論理的に関連するPL/SQLの型、変数、定数、サブプログラム、カーソルおよび例外をグループにまとめたスキーマ・オブジェクトのことです。パッケージをコンパイルしてデータベースに格納し、その内容を複数のアプリケーションで共有できます。

パッケージには必ず仕様部があり、パッケージの外から参照できるパブリック項目はここで宣言します。

パブリック項目にカーソルまたはサブプログラムが含まれる場合、パッケージには本体も必要です。本体には、パブリック・カーソルの問合せとパブリック・サブプログラムのコードを定義する必要があります。本体では、パッケージの外からは参照できなくてもパッケージの内部動作に必要なプライベート項目を宣言および定義することもできます。本体の最後には初期化部(ここには、変数を初期化する文と、その他の1回のみの設定手順を実行する文を入れます)、および例外処理部を配置することができます。本体は、仕様部またはパブリック項目への参照を変更せずに変更できるため、パッケージ本体はブラック・ボックスと考えることができます。

パッケージ仕様部またはパッケージ本体のいずれでも、パッケージ・サブプログラムを外部のJavaサブプログラムまたはCサブプログラムにマップできますが、その際にはコール仕様を使用して、外部サブプログラム名、パラメータ型および戻り型を対応するSQLにマップします。

パッケージ仕様部のAUTHID句は、パッケージのサブプログラムとカーソルをその定義者(デフォルト)と実行者のどちらの権限で実行するか、およびスキーマ・オブジェクトへの未修飾の参照が定義者と実行者のどちらのスキーマで解決されるかを決定します。

パッケージ仕様部のACCESSIBLE BY句を使用すると、パッケージにアクセスできるPL/SQLユニットのホワイト・リストを指定できます。この句は次のような状況で使用します。

• PL/SQLアプリケーションを複数のパッケージとして実装します。このうち、1つのパッケージがアプリケーション・プログラミング・インタフェース(API)を提供し、ヘルパー・パッケージが処理を実行します。このとき、クライアントがAPIにアクセスするが、ヘルパー・パッケージにはアクセスしないようにします。したがって、ACCESSIBLE BY句をAPIパッケージの仕様から省略し、ヘルパー・パッケージの仕様ごとに組み込み、ここで、APIパッケージのみがヘルパー・パッケージにアクセスできるように指定します。

• 同じスキーマ内のすべてではなく一部のPL/SQLユニットにサービスを提供するユーティリティ・パッケージを作成します。パッケージの使用対象を目的のユニットに制限するには、パッケージ仕様内のACCESSIBLE BY句にこれらをリストします。

関連項目:

• パッケージ仕様部の詳細は、「パッケージ仕様部」を参照してください

• パッケージ本体の詳細は、「パッケージ本体」を参照してください

• 「ファンクションの宣言および定義」

• 「プロシージャの宣言および定義」

• 「実行者権限および定義者権限(AUTHIDプロパティ)」

10.2 パッケージを使用する理由

パッケージは、信頼性のある再利用可能なコードの開発およびメンテナンスを次の機能で支援します。

• モジュール性

  パッケージを使用すると、論理的に関連した型、変数、定数、サブプログラム、カーソルおよび例外を、名前付きのPL/SQLモジュールにカプセル化できます。個々のパッケージを理解しやすくし、パッケージ間のインタフェースを単純かつ明快で、明確に定義することができます。この方法はアプリケーション開発に役立ちます。

• アプリケーションの設計の容易さ

  アプリケーション設計の最初の段階では、パッケージの仕様部に含まれるインタフェース情報のみが必要です。仕様部は本体がなくてもコーディングし、コンパイルできます。その後、このパッケージを参照するスタンドアロン・サブプログラムをコンパイルできます。アプリケーション作成の最終段階になるまで、パッケージ本体を完全に定義する必要はありません。

• 実装の詳細の隠ぺい

  パッケージを使用すると、パッケージ仕様部のインタフェース情報を共有でき、パッケージ本体の実装の細部を隠ぺいできます。本体の実装の細部を隠ぺいすることには次のメリットがあります。

  • アプリケーション・インタフェースに影響を与えずに実装の細部を変更できます。

  • アプリケーション・ユーザーは、開発者が変更する可能性のある実装の細部に依存するコードを開発できません。

• 機能の追加

  パッケージのパブリック変数およびカーソルは、セッションが存続する間維持されます。このため、同じ環境の中で実行するすべてのサブプログラムで共有できます。これによって、データベースに格納することなくトランザクション間でデータをメンテナンスできます。(パッケージのパブリック変数およびカーソルが、セッションの存続する間維持されない状況は、「パッケージの状態」を参照してください。)

• より高いパフォーマンス

  パッケージ・サブプログラムを初めて起動すると、パッケージ全体がOracle Databaseによってメモリーにロードされます。同じパッケージ内の他のサブプログラムの2度目以降の起動では、ディスクI/Oは必要ありません。

  パッケージ化すると互いに依存することがなくなるため、不要な再コンパイルを避けることができます。たとえば、パッケージ・ファンクションの本体を変更した場合、そのファンクションを起動する他のサブプログラムは、仕様部で宣言されたパラメータと戻り値にのみ依存するため、Oracle Databaseによって再コンパイルされません。

• ロール付与の容易さ

  パッケージ内の各オブジェクトにロールを付与するかわりに、パッケージ自体にロールを付与できます。

注意:

パッケージ内からはホスト変数を参照できません。

10.3 パッケージ仕様部

パッケージ仕様部ではパブリック項目を宣言します。パブリック項目の有効範囲は、パッケージのスキーマです。パブリック項目はスキーマ内のあらゆる場所から参照できます。有効範囲内にあっても参照できないパブリック項目を参照するには、パッケージ名で修飾します。(有効範囲、可視性および修飾の詳細は、「識別子の有効範囲と可視性」を参照してください。)

各パブリック項目の宣言には、その項目を使用するうえで必要なすべての情報を含めます。たとえば、パッケージ仕様部でファンクションfactorialを次の方法で宣言するとします。

FUNCTION factorial (n INTEGER) RETURN INTEGER; -- returns n!

この宣言は、factorialという名前のファンクションがINTEGER型の引数を1つ取り、INTEGER型の値を戻すことを示しており、起動者がfactorialを起動するうえで知っておく必要のある情報です。起動者はfactorialがどのように実装されているか(たとえば、それが反復を利用しているのか、再帰を利用しているのかなど)を知る必要はありません。

注意:

パッケージの使用を指定したPL/SQLユニットに制限するために、パッケージ仕様部にACCESSIBLE BY句を含めます。

ここでのトピック

• 適切なパブリック項目

• パッケージ仕様部の作成

10.3.1 適切なパブリック項目

適切なパブリック項目は次のとおりです。

• 複数のサブプログラムで使用される型、変数、定数、サブプログラム、カーソルおよび例外

  パッケージ仕様部で定義される型は、ユーザー定義のPL/SQLサブタイプ(「ユーザー定義のPL/SQLサブタイプ」を参照)またはPL/SQLコンポジット型(「PL/SQLのコレクションおよびレコード」を参照)のいずれかです。

  注意:

  パッケージ仕様部で定義されたPL/SQLコンポジット型は、同一定義のローカル型またはスタンドアロン型と互換性がありません(例5-33、例5-34および例5-39を参照)。

• スタンドアロン・サブプログラム・パラメータの連想配列型

  スキーマ・レベルでは結合配列型を宣言できません。そのため、連想配列の変数をパラメータとしてスタンドアロン・サブプログラムに渡すには、その変数の型をパッケージ仕様部で宣言する必要があります。こうすると、起動されるサブプログラム(この型の仮パラメータを宣言する側)と起動元のサブプログラムまたは無名ブロック(この型の変数を宣言する側)の両方でこの型を使用できます。例10-2を参照してください。

• 同じセッション内のサブプログラム起動間で使用可能な状態に保つ必要がある変数

• パブリック変数の読取りおよび書込みを実行するサブプログラム("get"および"set"サブプログラム)

  これらのサブプログラムは、パッケージ・ユーザーにパブリック変数の読取りおよび書込みを直接実行させないために提供します。

• 相互に起動し合うサブプログラム

  互いに起動し合うスタンドアロン・サブプログラムの場合はコンパイルの順序を気にする必要がありますが、パッケージ・サブプログラムの場合はとらわれる必要はありません。

• オーバーロードされたサブプログラム

  オーバーロードされたサブプログラムとは、同じサブプログラムのバリエーションです。つまり、名前は同じですが、仮パラメータが異なります。詳細は、「オーバーロードされたサブプログラム」を参照してください。

注意:

リモート・パッケージのパブリック変数は、間接的であっても参照できません。たとえば、サブプログラムがパッケージのパブリック変数を参照する場合、データベース・リンクを通じてサブプログラムを起動することはできません。

10.3.2 パッケージ仕様部の作成

パッケージ仕様部を作成するには、「 CREATE PACKAGE文」を使用します。

例10-1および例10-2のパッケージ仕様部ではカーソルまたはサブプログラムを宣言していないため、パッケージtrans_dataおよびaa_pkgに本体は不要です。

例10-1 単純なパッケージ仕様部

この例では、パッケージtrans_dataの仕様部で2つのパブリック型と3つのパブリック変数を宣言しています。

CREATE OR REPLACE PACKAGE trans_data AUTHID DEFINER 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;
  PRAGMA EXCEPTION_INIT(insufficient_funds, -4097);
END trans_data;
/

例10-2 スタンドアロン・サブプログラムへの連想配列の受渡し

この例では、パッケージaa_pkgの仕様部で連想配列型のaa_typeを宣言しています。その後、スタンドアロン・プロシージャprint_aaで型aa_typeの仮パラメータを宣言しています。次に、無名ブロックで型aa_typeの変数を宣言してこれに移入し、この変数を出力するプロシージャprint_aaに変数を渡しています。

CREATE OR REPLACE PACKAGE aa_pkg AUTHID DEFINER IS
  TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15);
END;
/
CREATE OR REPLACE PROCEDURE print_aa (
  aa aa_pkg.aa_type
) AUTHID DEFINER 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.4 パッケージ本体

パッケージ仕様部でカーソルまたはサブプログラムを宣言している場合は、パッケージ本体が必要ですが、それ以外の場合、本体はオプションです。パッケージ本体とパッケージ仕様部は同じスキーマ内にある必要があります。

パッケージ仕様部内のカーソル宣言またはサブプログラム宣言の1つ1つに対応する定義がパッケージ本体にある必要があります。対応するサブプログラム宣言と定義のヘッダーは、空白以外の一語一語が一致している必要があります。

パッケージ本体を作成するには、「 CREATE PACKAGE BODY文」を使用します。

パッケージ仕様部で宣言し、パッケージ本体で定義したカーソルおよびサブプログラムは、パッケージの外から参照できるパブリック項目です。パッケージ本体では、パッケージの外からは参照できなくてもパッケージの内部動作に必要なプライベート項目を宣言および定義することもできます。

本体の最後には初期化部を配置することができ、ここには、パブリック変数を初期化する文と、その他の1回のみの設定手順を実行する文を入れます。初期化部は、パッケージが初めて参照されたときにのみ実行されます。初期化部には例外ハンドラを含めることができます。

パッケージ本体は、仕様部またはパブリック項目への参照を変更せずに変更できます。

例10-3 パッケージの仕様部と本体の一致

この例では、対応するサブプログラム宣言と定義のヘッダーが完全一致していません。そのため、employees.hire_date%TYPEがDATEであっても、PL/SQLによって例外が生成されます。

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.

10.5 パッケージのインスタンス化および初期化

セッションがパッケージ項目を参照すると、Oracle Databaseはそのセッションのためにパッケージをインスタンス化します。パッケージを参照する各セッションは、そのパッケージの独自のインスタンス化を所有します。

Oracle Databaseがパッケージをインスタンス化すると、パッケージは初期化されます。初期化では、次の処理のうち適用できるものがすべて実行されます。

• パブリック定数への初期値の代入

• 宣言で初期値が指定されているパブリック変数への初期値の代入

• パッケージ本体の初期化部の実行

10.6 パッケージの状態

パッケージ(の仕様部または本体のいずれか)で宣言している変数、定数およびカーソルの値が、パッケージの状態を構成します。

PL/SQLパッケージに1つ以上の変数、定数またはカーソルが宣言されている場合、このパッケージはステートフルですが、それ以外の場合はステートレスです。

パッケージ項目を参照する各セッションは、そのパッケージの独自のインスタンス化を所有します。パッケージがステートフルの場合は、インスタンス化にパッケージの状態が含まれます。

パッケージの状態は、次の状況を除き、セッションが存続する間維持されます。

• パッケージがSERIALLY_REUSABLEの場合。

• パッケージ本体が再コンパイルされている場合。

  インスタンス化されたステートフル・パッケージの本体が再コンパイルされている場合は(「ALTER PACKAGE文」を使用した明示的な再コンパイルであっても、暗黙的な再コンパイルであっても)、このパッケージで次にサブプログラムを起動すると、Oracle Databaseは既存のパッケージ状態を破棄して例外ORA-04068を呼び出します。

  PL/SQLによって例外が呼び出された後にパッケージを参照すると、Oracle Databaseによりパッケージが再インスタンス化され、再初期化されます。したがって、パッケージ状態に対する以前の変更は失われます。

• セッションが所有するインスタンス化されたパッケージのいずれかが、無効化された後に再有効化されている場合。

  セッションが所有するインスタンス化されたパッケージのいずれかが無効化された後に再有効化されている場合、セッションのパッケージ・インスタンス化は(パッケージの状態を含め)すべて失われる可能性があります。

セッションが存続する間(またはそれより長い間)パッケージの状態が一定である場合、パッケージはOracle Databaseにステートレスとして処理されます。これは、パッケージの項目がすべてコンパイル時定数になっているパッケージの場合です。

コンパイル時定数とは、コンパイル時にPL/SQLコンパイラで値を決定できる定数のことです。初期値にリテラルが指定されている定数は、必ずコンパイル時定数になります。初期値にリテラル以外が指定されていても、オプティマイザによりリテラルに縮約される定数も、コンパイル時定数です。リテラル以外の式をPL/SQLオプティマイザでリテラルに縮約できるかどうかは、最適化レベルによって異なります。そのため、ある最適化レベルでコンパイルするとステートレスになるパッケージが、別の最適化レベルでコンパイルするとステートフルになることもあります。

関連項目:

• SERIALLY_REUSABLEパッケージ

• 初期化の詳細は、「パッケージのインスタンス化および初期化」を参照してください

• スキーマ・オブジェクトの無効化および再有効化の詳細は、『Oracle Database開発ガイド』を参照してください

• オプティマイザの詳細は、「PL/SQLオプティマイザ」を参照してください

10.7 SERIALLY_REUSABLEパッケージ

SERIALLY_REUSABLEパッケージを使用すると、スケーラビリティを向上させるためにメモリーをより効率的に管理するアプリケーションを設計できます。

パッケージがSERIALLY_REUSABLEでない場合、そのパッケージ状態は各ユーザーのユーザー・グローバル領域(UGA)に格納されます。したがって、UGAのメモリー量をユーザー数に比例して増加させる必要があり、スケーラビリティが制限されます。パッケージの状態はセッションが存続する間維持でき、UGAメモリーはセッションが終了するまでロックされます。Oracle Officeなどの一部のアプリケーションの標準的なセッションは数日間存続します。

パッケージがSERIALLY_REUSABLEである場合、そのパッケージ状態はシステム・グローバル領域(SGA)内の小規模なプールにある作業領域に格納されます。パッケージの状態は、サーバー・コールが存続する間のみ維持されます。サーバー・コールの後、作業領域はプールに戻されます。以降のサーバー・コールがこのパッケージを参照すると、Oracle Databaseはこのプールからインスタンス化を再利用します。インスタンス化を再利用すると、インスタンス化は再初期化されるため、それまでのサーバー・コールで実行されたパッケージ状態に対する変更は参照できなくなります。(初期化の詳細は、「パッケージのインスタンス化および初期化」を参照してください。)

注意:

データベース・トリガー、またはSQL文から起動される他のPL/SQLサブプログラムからSERIALLY_REUSABLEパッケージにアクセスしようとするとエラーが発生します。

ここでのトピック

• SERIALLY_REUSABLEパッケージの作成

• SERIALLY_REUSABLEパッケージの作業単位

• SERIALLY_REUSABLEパッケージの明示カーソル

10.7.1 SERIALLY_REUSABLEパッケージの作成

SERIALLY_REUSABLEパッケージを作成するには、SERIALLY_REUSABLEプラグマをパッケージ仕様部とパッケージ本体(存在する場合)に含めます。

例10-4では、きわめて単純なSERIALLY_REUSABLEパッケージを2つ作成します。1つは仕様部のみで、もう1つは仕様部と本体があります。

関連項目:

SERIALLY_REUSABLEプラグマ

例10-4 SERIALLY_REUSABLEパッケージの作成

-- Create bodiless SERIALLY_REUSABLE package:
 
CREATE OR REPLACE PACKAGE bodiless_pkg AUTHID DEFINER IS
  PRAGMA SERIALLY_REUSABLE;
  n NUMBER := 5;
END;
/
 
-- Create SERIALLY_REUSABLE package with specification and body:
 
CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS
  PRAGMA SERIALLY_REUSABLE;
  n NUMBER := 5;
END;
/
 
CREATE OR REPLACE PACKAGE BODY pkg IS
  PRAGMA SERIALLY_REUSABLE;
BEGIN
  n := 5;
END;
/

10.7.2 SERIALLY_REUSABLEパッケージの作業単位

SERIALLY_REUSABLEパッケージの作業単位はサーバー・コールです。

このパッケージのパブリック変数は、作業単位内のみで使用する必要があります。

注意:

誤って以前の作業単位で設定されたパブリック変数の値に依存した場合、そのプログラムは失敗する可能性があります。PL/SQLではこのようなケースはチェックできません。

SERIALLY_REUSABLEパッケージの作業単位(サーバー・コール)が完了した後、Oracle Databaseにより次の処理が実行されます。

• オープンされているすべてのカーソルのクローズ処理。

• 再使用不可のメモリーの一部を解放する処理(たとえば、コレクション変数用のメモリー、長いVARCHAR2用のメモリーなど)。

• このパッケージのインスタンス化を、このパッケージ用に保持された再使用可能インスタンス化のプールに戻す処理。

例10-5 SERIALLY_REUSABLEプラグマの影響

この例では、本体のないパッケージpkgとsr_pkgは、sr_pkgはSERIALLY_REUSABLEでpkgはそうでないという点以外同じです。各パッケージで、初期値に5が指定されたパブリック変数nを宣言します。その後、無名ブロックで各変数の値を10に変更します。次に、別の無名ブロックで各変数の値を出力します。pkgの状態はセッションが存続する間維持されるため、pkg.nの値は10のままです。sr_pkgの状態はサーバー・コールが存続する間のみ維持されるため、sr_pkg.nの値は5です。

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

10.7.3 SERIALLY_REUSABLEパッケージの明示カーソル

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
  v_name  people.name%TYPE;
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 v_name;
  DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_name);
 
  FETCH sr_pkg.c INTO v_name;
    DBMS_OUTPUT.PUT_LINE('Fetched: ' || v_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

10.8 パッケージ作成のガイドライン

• Oracle Databaseが提供するパッケージをよく理解して、その機能と重複する機能を持つパッケージを作成しないように注意してください。

  Oracle Databaseが提供するパッケージの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。

• 別のアプリケーションで再利用できるように、パッケージに汎用性を持たせてください。

• パッケージ本体の前にパッケージ仕様部を設計および定義してください。

• パッケージ仕様部では、起動元のプログラムから見える必要のある項目のみを宣言してください。

  こうすることで、他の開発者が実装の細部に不適切に依存しないようにすることができ、再コンパイルの必要性を削減できます。

  パッケージ仕様部を変更する場合は、そのパッケージのパブリック・サブプログラムを起動するすべてのサブプログラムを再コンパイルする必要があります。パッケージ本体のみ変更する場合は、サブプログラムを再コンパイルする必要はありません。

• 例10-7に示すとおり、パブリック・カーソルはパッケージ仕様部で宣言し、パッケージ本体で定義してください。

  こうすると、カーソルの問合せをパッケージ・ユーザーに見えなくすることができ、カーソル宣言を変更せずにカーソル問合せを変更することができます。

• 初期値は、宣言部ではなくパッケージ本体の初期化部で代入してください。

  この方法には次のメリットがあります。

  • 初期値を計算するコードをより複雑にすることができ、詳しく説明できます。

  • 初期値の計算で例外が呼び出された場合、初期化部で独自の例外ハンドラを使用して例外を処理できます。

• データベース・アプリケーションを複数のPL/SQLパッケージ(APIを提供する1つのパッケージと機能を提供する複数のヘルパー・パッケージ)として実装する場合、例10-8に示すように、ヘルパー・パッケージをAPIパッケージに対してのみ使用可能にします。

例10-7では、カーソルc1の宣言および定義が、パッケージemp_stuffの仕様部と本体にそれぞれ置かれています。このカーソル宣言では戻り値のデータ型のみを指定し、問合せは指定せず、問合せはカーソルの定義に置かれています(構文およびセマンティクスの詳細は、「明示カーソルの宣言および定義」を参照してください)。

例10-8では、APIパッケージとヘルパー・パッケージを作成します。ヘルパー・パッケージ仕様部のACCESSIBLE BY句によって、APIパッケージのみがヘルパー・パッケージにアクセス可能となります。

例10-7 パッケージでのカーソル宣言と定義の分離

CREATE PACKAGE emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE;  -- Declare cursor
END emp_stuff;
/
CREATE PACKAGE BODY emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE IS
    SELECT * FROM employees WHERE salary > 2500;  -- Define cursor
END emp_stuff;
/

例10-8 ACCESSIBLE BY句

CREATE OR REPLACE PACKAGE helper
  AUTHID DEFINER
  ACCESSIBLE BY (api)
IS
  PROCEDURE h1;
  PROCEDURE h2;
END;
/
 
CREATE OR REPLACE PACKAGE BODY helper
IS
  PROCEDURE h1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Helper procedure h1');
  END;
 
  PROCEDURE h2 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Helper procedure h2');
  END;
END;
/
 
CREATE OR REPLACE PACKAGE api
  AUTHID DEFINER
IS
  PROCEDURE p1;
  PROCEDURE p2;
END;
/
 
CREATE OR REPLACE PACKAGE BODY api
IS
  PROCEDURE p1 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('API procedure p1');
    helper.h1;
  END;
 
  PROCEDURE p2 IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('API procedure p2');
    helper.h2;
  END;
END;
/
 

APIパッケージのプロシージャを起動します。

BEGIN
  api.p1;
  api.p2;
END;
/
 

結果:

API procedure p1
Helper procedure h1
API procedure p2
Helper procedure h2

ヘルパー・パッケージのプロシージャを起動します。

BEGIN
  helper.h1;
END;
/
 

結果:

SQL> BEGIN
  2    helper.h1;
  3  END;
  4  /
  helper.h1;
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00904: insufficient privilege to access object HELPER
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

10.9 パッケージの例

例10-9では、表logとパッケージemp_adminを作成し、無名ブロックからパッケージ・サブプログラムを起動しています。パッケージには仕様部と本体の両方があります。

仕様部では、パブリック型、カーソルおよび例外と3つのパブリック・サブプログラムを宣言しています。1つのパブリック・サブプログラムはオーバーロードされたサブプログラムです(オーバーロードされたサブプログラムの詳細は、「オーバーロードされたサブプログラム」を参照してください)。

本体では、プライベート変数を宣言し、仕様部で宣言したパブリック・カーソルとサブプログラムを定義し、プライベート・ファンクションを宣言および定義しています。また、本体には初期化部が含まれています。

初期化部(無名ブロックがこのパッケージを初めて参照したときにのみ実行されます)では、表logに行を1行挿入し、プライベート変数number_hiredを0(ゼロ)に初期化しています。パッケージ・プロシージャhire_employeeが起動されるたびに、プライベート変数number_hiredが更新されます。

例10-9 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 AUTHID DEFINER 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 210
The 10th highest salary is 11500, belonging to employee: 168

10.10 STANDARDパッケージによるPL/SQL環境の定義

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の起動かを判定します。