PL/SQLパッケージ

この項では、TimesTenでPL/SQLパッケージを作成および使用する方法について説明します。

• パッケージの概念

• パッケージの作成および使用

• パッケージのシノニムの使用

TimesTenで提供されるPL/SQLパッケージの詳細は、TimesTenが提供するPL/SQLパッケージを参照してください。

パッケージの概念

パッケージは、論理的に関連するPL/SQLデータ型、変数およびサブプログラムをグループ化したデータベース・オブジェクトです。パッケージを指定した後、その本体を個別のステップで定義します。

パッケージ仕様部はパッケージに対するインタフェースで、パッケージの有効範囲外で参照できるパブリックなデータ型、変数、定数、例外、カーソルおよびサブプログラムを宣言します。本体では、仕様部で宣言したオブジェクト以外に、カーソルの問合せ、サブプログラムのコードおよびパッケージ外のアプリケーションからは参照できないプライベートなオブジェクトを定義します。

TimesTenでは、パッケージ仕様部をパッケージ本体とは別にデータベースに格納します。パブリックなプログラム・オブジェクトをコールまたは参照する他のスキーマ・オブジェクトは、パッケージ本体ではなく、パッケージ仕様部にのみ依存します。

ノート:

パッケージおよびパッケージ本体を作成する構文は、Oracle Databaseと同様ですが、Oracle Databaseのマニュアルに記載されているDBMSSTDX.SQLというスクリプトの実行については、TimesTenには該当しません。

パッケージの作成および使用

TimesTenで、CREATE PACKAGE文およびCREATE PACKAGE BODY文を使用してパッケージを作成してTimesTenデータベースに永続的に格納します。

新しいパッケージを作成するには、次の手順を実行します。

1. CREATE PACKAGE文でパッケージ仕様部を作成します。

   パッケージ仕様部ではプログラム・オブジェクトを宣言できます。このようなオブジェクトはパブリック・オブジェクトと呼ばれ、パッケージ内の他のオブジェクト、およびパッケージの外部からも参照できます。

   すでにパッケージが存在している場合にそのパッケージ仕様部を置換するには、オプションでCREATE OR REPLACE PACKAGEを使用します。

2. CREATE PACKAGE BODY(またはCREATE OR REPLACE PACKAGE BODY)文を使用して、パッケージ本体を作成します。

   パッケージ本体ではプログラム・オブジェクトを宣言および定義できます。

   • パッケージ仕様部で宣言したパブリック・オブジェクトを定義する必要があります。

   • プライベート・オブジェクトと呼ばれる追加のパッケージ・オブジェクトを宣言および定義できます。プライベート・オブジェクトは、パッケージ内の他のオブジェクトからのみ参照できるよう、パッケージ仕様部ではなくパッケージ本体で宣言します。パッケージの外部からは参照できません。

パッケージのメンバーであるプロシージャおよびファンクションを明示的にコンパイルするか、またはコンパイル・オプションを変更する場合は、ALTER PACKAGEを使用します。

CREATE PACKAGE、CREATE PACKAGE BODYおよびALTER PACKAGE文の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文を参照してください。

ノート:

• ALTERは、TimesTen ScaleoutのPL/SQLオブジェクトではサポートされていません。

• レプリケーションを使用する場合、PL/SQLファンクション、プロシージャおよびパッケージのCREATE文などのPL/SQL DDL文はレプリケートされません。これに対処する手順については、『Oracle TimesTen In-Memory Databaseレプリケーション・ガイド』の既存のアクティブ・スタンバイ・ペアでの新しいPL/SQLの作成および既存のクラシック・レプリケーション・スキームへのPL/SQLオブジェクトの追加を参照してください。

• パッケージのコンパイル中にエラーが発生した場合の情報の入手方法については、ttIsqlでのエラーの表示を参照してください。

次の例では、新しい従業員を雇用したときに従業員表に1行追加するケース、および従業員が退社したときに従業員表から1行削除するケースについて考えてみます。次の例では、これらのタスクを実行する2つのプロシージャを作成して、1つのパッケージにまとめます。パッケージには、特定の従業員よりも給与が高い従業員の数を返すファンクションも含まれています。例では、この後ファンクションおよびプロシージャを実行して、結果を確認しています。

Command> CREATE OR REPLACE PACKAGE emp_actions AS
           PROCEDURE hire_employee (employee_id NUMBER,
             last_name VARCHAR2,
             first_name VARCHAR2,
             email VARCHAR2,
             phone_number VARCHAR2,
             hire_date DATE,
             job_id VARCHAR2,
             salary NUMBER,
             commission_pct NUMBER,
             manager_id NUMBER,
             department_id NUMBER);
           PROCEDURE remove_employee (emp_id NUMBER);
           FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
         END emp_actions;
         /
 
Package created.
 
Command> -- Package body:
         CREATE OR REPLACE PACKAGE BODY emp_actions AS
         -- Code for procedure hire_employee:
           PROCEDURE hire_employee (employee_id NUMBER,
             last_name VARCHAR2,
             first_name VARCHAR2,
             email VARCHAR2,
             phone_number VARCHAR2,
             hire_date DATE,
             job_id VARCHAR2,
             salary NUMBER,
             commission_pct NUMBER,
             manager_id NUMBER,
             department_id NUMBER) IS
           BEGIN
             INSERT INTO employees VALUES (employee_id,
               last_name,
               first_name,
               email,
               phone_number,
               hire_date,
               job_id,
               salary,
               commission_pct,
               manager_id,
               department_id);
           END hire_employee;
         -- Code for procedure remove_employee:
           PROCEDURE remove_employee (emp_id NUMBER) IS
           BEGIN
             DELETE FROM employees WHERE employee_id = emp_id;
           END remove_employee;
         -- Code for function num_above_salary:
           FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
             emp_sal NUMBER(8,2);
             num_count NUMBER;
           BEGIN
             SELECT salary INTO emp_sal FROM employees
             WHERE employee_id = emp_id;
             SELECT COUNT(*) INTO num_count FROM employees
             WHERE salary > emp_sal;
             RETURN num_count;
           END num_above_salary;
         END emp_actions;
         /
 
Package body created.
 
Command> BEGIN
         /* call function to return count of employees with salary
            greater than salary of employee with employee_id = 120
         */
           DBMS_OUTPUT.PUT_LINE
             ('Number of employees with higher salary: ' ||
               TO_CHAR(emp_actions.num_above_salary(120)));
         END;
         /
Number of employees with higher salary: 33
 
PL/SQL procedure successfully completed.

33という数を確認します。

Command> SELECT salary FROM employees WHERE employee_id = 120;
< 8000 >
1 row found.

Command> SELECT COUNT (*) FROM employees WHERE salary > 8000;
< 33 >
1 row found.

ここで従業員を追加して、結果を確認します。次に、従業員を削除して、その従業員がemployees表から削除されたことを確認します。

Command> BEGIN 
           emp_actions.hire_employee(300,
             'Belden',
             'Enrique',
             'EBELDEN',
             '555.111.2222',
             '31-AUG-04',
             'AC_MGR',
             9000,
             .1,
             101,
             110);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
< 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000, 
.1, 101, 110 >
1 row found.
Command> BEGIN
           emp_actions.remove_employee (300);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
0 rows found.

パッケージのシノニムの使用

TimesTenは、PL/SQLプロシージャ、ファンクション、パッケージなどのデータベース・オブジェクトのプライベートおよびパブリック・シノニム(エイリアス)をサポートしています。シノニムは、オブジェクト名およびオブジェクト所有者をマスクしたり、SQL文を単純化する際によく使用されます。

スキーマ内でパッケージfoopkgのプライベート・シノニムを作成する場合:

CREATE SYNONYM synfoopkg FOR foopkg;

foopkgのパブリック・シノニムを作成する場合:

CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;

このドキュメント内の「プロシージャおよびファンクションのシノニムの使用」、および『Oracle TimesTen In-Memory Databaseセキュリティ・ガイド』のPL/SQL文および操作の権限も参照してください。

ノート:

パッケージの各メンバー・サブプログラムに対するシノニムを作成することはできません。

次は有効です:

create or replace public synonym pubtestpkg for testpkg;

次は無効です。

create or replace public synonym pubtestproc for testpkg.testproc;