Database
技術記事
PL/SQL 101
テクノロジー：PL/SQL
コードを整然としたパッケージにまとめるSteven Feuerstein著
PL/SQLの理解と利用に関するシリーズ記事のパート11 ほとんどのPL/SQLベースのアプリケーションは、数十万行のコードで構成され、さらにそのうちの多くは、変化し続ける詳細なユーザー要件に対応するために数百万行にも達します。ビジネス・ロジックはおもにプロシージャやファンクション内に実装されますが、PL/SQL開発者はそれらのプロシージャやファンクションをどこに配置するかを決断する必要があります。この記事では、コードベースの開発、保守、最適化が簡単になるようにアプリケーションの機能を整理して公開するためのパッケージの使用方法について説明します。 パッケージとは、PL/SQLコードの要素を名前付きのプログラム・ユニットにグループ化（つまりパッケージ化）したものであり、データベース内に保管されます。パッケージは構造体を（論理的にも物理的にも）提供します。この構造体の中でプログラムやその他のPL/SQL要素（カーソル、型、変数など）を整理できます。また、パッケージは、ビューからロジックとデータを隠す機能や、"グローバルな"データ、つまりセッション実行中に存続するデータを定義し、操作する機能などの重要な機能も提供します。 パッケージにはかならず仕様部があります。この仕様部には、パッケージの外部から参照可能なパッケージ項目を定義します。また、ほとんどのパッケージには本体もあります。本体では、パッケージ内のすべてのサブプログラム（プロシージャとファンクション）を実装します。本体には、パッケージレベルの変数や型など、パッケージ内部からのみ参照可能なプライベート項目を記述できます。パッケージの本体には初期化セクションが含まれる場合もあります。初期化セクションは、開発者がパッケージに関する複雑な設定手順や検証を実装するために使用する部分です。 なぜパッケージかパッケージは、強力かつ重要なPL/SQL言語要素です。実際、あらゆるPL/SQLベースのアプリケーションにおいて、パッケージが基本的な構築ブロックとなるでしょう。パッケージがそのように強力かつ重要な要素となる理由は何でしょうか。次のようなメリットが考えられます。 関連する機能のグループ化：基本的なパッケージのメリットとして、関連する機能をグループ化し、API経由でその機能を利用できるようにすることが挙げられます。数十万ある個別のプロシージャやファンクションを調査しなくても、必要な機能を含むパッケージ（名前によって簡単に識別できる必要がある）を見つけて、そのパッケージ内にある必要なサブプログラムまでドリルダウンできます。さらに、新しいプログラムをアプリケーションに追加する必要がある場合に、パッケージはその機能の"住居"になります。パッケージを使用しなければ、コードの理解と保守がはるかに難しくなります。 実装の詳細を隠す：パッケージを使用する場合、すべての人が参照して使用できるように仕様部に配置するものと、パッケージの所有者のみが参照できるように本体に配置するものを選別します。このように実装の詳細を隠せることには2つの重要なメリットがあります。1つは、仕様部を変更せずに実装を変更できることです。つまり、パッケージ内のプロシージャやファンクションのコール元となるすべてのプログラムは変更する必要がありません。この機能によって、アプリケーションへの影響を抑えながらコードを拡張するための柔軟性が飛躍的に向上します。また、2つ目のメリットとして、パッケージのユーザーに知られたくない実装の詳細を隠すことができます。 パフォーマンスの向上：パッケージ内の要素を最初に呼び出すときに、Oracle Databaseではそのパッケージ全体がメモリに読み込まれます。それ以降、同じパッケージ内の他の要素を参照するときには、追加のディスクI/Oは不要です。また、パッケージレベルの変数をセッションレベルのキャッシュとして使用できるため、さまざまな方法でデータへのアクセスにかかる時間を削減できるようになります。 プログラム・ユニットの再コンパイルが最小限に：パッケージ内に定義していない外部プログラムでは、仕様部に記載されたサブプログラムのみをコールできます。パッケージの本体を変更して再コンパイルした場合に、それらの外部プログラムは無効化されません。コードの再コンパイルの必要性を最小限に抑えることは、大規模なアプリケーション・ロジックの本体を管理するにあたって重要な要素です。 パッケージを使用する理由は多くありますが、まずはパッケージを作成する方法と、パッケージ固有の機能を利用する方法について学習する必要があります。この記事では、最初に単純なパッケージを作成して、この強力なアプリケーション構築ブロックの例を示します。次に、パッケージレベルのデータとオーバーロードという2つの機能について詳しく説明します。
単純なパッケージの例employees表が次のように定義されているとします。 SQL> desc employees Name Type ———————————— ————————————— EMPLOYEE_ID NUMBER(38) FIRST_NAME VARCHAR2(30) LAST_NAME VARCHAR2(50) コード・リスト1：process_employeeプロシージャ
CREATE OR REPLACE PROCEDURE process_employee (
employee_id_in IN employees.employee_id%TYPE)
IS
l_fullname VARCHAR2(100);
BEGIN
SELECT last_name || ',' || first_name
INTO l_fullname
FROM employees
WHERE employee_id = employee_id_in;
...
END;
そのようなハードコーディングや繰り返しを防ぐようにこのプロシージャを記述できれば、それはすばらしいことです。つまり、"フルネーム"データ型の定義、式の表現、問合せのバージョンなどをそれぞれ1回だけ記述して、必要な場所でコールするようにします。パッケージは、そのような単一ソースの実装を格納するのに最適なリポジトリです。 リスト2のパッケージ仕様部を見てみましょう。 コード・リスト2：employee_pkgの仕様部 1 CREATE OR REPLACE PACKAGE employee_pkg 2 AS 3 SUBTYPE fullname_t IS VARCHAR2 (100); 4 5 FUNCTION fullname ( 6 last_in employees.last_name%TYPE, 7 first_in employees.first_name%TYPE) 8 RETURN fullname_t; 9 10 FUNCTION fullname ( 11 employee_id_in IN employees.employee_id%TYPE) 12 RETURN fullname_t; 13 END employee_pkg; このパッケージ仕様部の2つのファンクションについてまだ実装していませんが、この時点でも先ほどのprocess_employeeプロシージャを見直し、これらのパッケージ化された要素の使用方法を確認できます。パッケージ化された要素は、package_name.element_nameという形式で参照します。リスト3では、サブタイプを使用してl_name変数を宣言し、ファンクション内部に直接問合せを記述するのではなく、作成したファンクションをコールしています。 コード・リスト3：employee_pkgパッケージをコールするブロック CREATE OR REPLACE PROCEDURE process_employee ( employee_id_in IN employees.employee_id%TYPE) IS l_name employee_pkg.fullname_t; employee_id_in employees.employee_id%TYPE := 1; BEGIN l_name := employee_pkg.fullname (employee_id_in); ... END; 特に優れているのは、フルネームの式を変更する必要がある場合や、fullname_tデータ型の最大サイズを拡張する必要がある場合に、パッケージの該当する部分を変更して再コンパイルするだけで良い点です。パッケージ化された要素を使用するプログラムは、どれも変更する必要はありません。 リスト4は、employee_pkgの本体の実装です。 コード・リスト4：employee_pkgパッケージの本体 1 CREATE OR REPLACE PACKAGE BODY employee_pkg 2 AS 3 FUNCTION fullname ( 4 last_in employees.last_name%TYPE, 5 first_in employees.first_name%TYPE 6 ) 7 RETURN fullname_t 8 IS 9 BEGIN 10 RETURN last_in || ', ' || first_in; 11 END; 12 13 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE) 14 RETURN fullname_t 15 IS 16 l_fullname fullname_t; 17 BEGIN 18 SELECT fullname (last_name, first_name) INTO l_fullname 19 FROM employees 20 WHERE employee_id = employee_id_in; 21 22 RETURN l_fullname; 23 END; 24 END employee_pkg; ここで、ユーザーに「名、空白、姓の形式にしてほしい」と言われた場合はどうしますか。不平を言いながら夜遅くまで残業して、コード内で"|| ‘, ‘ ||"と記述している箇所を探す必要はありません。そうではなく、employee_pkg.fullnameファンクションの実装を約5秒で変更し、パッケージの本体を再コンパイルし、準備ができたことをユーザーに知らせて度肝を抜かせるのです。 また、Oracle Database 11gにアップグレードして、PL/SQLファンクション結果キャッシュという驚くべき機能について調べる場合にはどうしますか。この場合は、ファンクションのヘッダーにRESULT_CACHE句を追加してパッケージを再コンパイルするだけで、同じ従業員のフルネームを繰り返し取得するために必要となるCPUサイクルを大幅に削減できます。 これが、パッケージ内に実装の詳細を隠すことによる優れた効果です。 パッケージレベルのデータパッケージレベルのデータは、パッケージレベルで定義された変数と定数により構成されます。パッケージ内の特定のファンクションやプロシージャ内部に定義されたものではありません。たとえば、次のパッケージ仕様部では、1つの変数と1つの定数をパッケージレベルで宣言しています。
CREATE OR REPLACE PACKAGE plsql_limits
IS
c_varchar2_length CONSTANT
PLS_INTEGER := 32767;
g_start_time PLS_INTEGER;
END;
一方、パッケージ・データのスコープは個々のプログラムやブロックではなく、パッケージ全体に及びます。PL/SQLランタイム・アーキテクチャでは、パッケージ・データ構造体は、特定のプログラムが実行する間ではなく、セッションが実行する間存続します（値が保持されます）。 パッケージ・データがパッケージ本体の内部で宣言されている場合、そのデータはセッションが実行する間存続しますが、パッケージ自体に定義された要素からしかアクセスできません。つまり、そのデータはプライベート・データです。一方、パッケージ・データがパッケージ仕様部の内部で宣言されている場合は、そのデータはセッションの間中存続し、そのパッケージのEXECUTE権限があるすべてのプログラムから直接（読取りと変更の両方のために）アクセスできます。つまり、そのデータはパブリック・データです。 パッケージレベルのデータの使用例を見ていきましょう。DBMS_UTILITYパッケージには、GET_CPU_TIMEというファンクションがあります。このファンクションは、100分の1秒の精度でコードの経過時間を計算するために使用できます。リスト5の無名ブロックは、このファンクションを使用して、無操作を10,000回繰り返した場合の経過時間を算出する方法を示しています。 コード・リスト5：DBMS_UTILITY.GET_CPU_TIMEによる測定
DECLARE
l_start PLS_INTEGER;
BEGIN
/* Get and save the starting time.*/
l_start := DBMS_UTILITY.get_cpu_time;
/* Run your code.*/
FOR indx IN 1 ..10000
LOOP
NULL;
END LOOP;
/* Subtract starting time from current time.*/
DBMS_OUTPUT.put_line (
DBMS_UTILITY.get_cpu_time - l_start);
END;
/
ほとんどの開発者に、他にもっと良い時間の使い方があるはずです。おそらくパッケージが役に立つでしょう。たとえば、リスト6のようなタイマー・パッケージ（timer_pkg）があります。 コード・リスト6：timer_pkgパッケージ
CREATE OR REPLACE PACKAGE timer_pkg
IS
PROCEDURE start_timer;
PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);
END timer_pkg;
/
CREATE OR REPLACE PACKAGE BODY timer_pkg
IS
g_start_time NUMBER := NULL;
PROCEDURE start_timer
IS
BEGIN
g_start_time := DBMS_UTILITY.get_cpu_time;
END;
PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL)
IS
BEGIN
DBMS_OUTPUT.put_line (
message_in
|| ':'
|| TO_CHAR (DBMS_UTILITY.get_cpu_time - g_start_time));
start_timer;
END;
END timer_pkg;
/
BEGIN
timer_pkg.start_timer;
FOR indx IN 1 ..10000
LOOP
NULL;
END LOOP;
timer_pkg.show_elapsed ('10000 Nothings');
END;
/
timer_pkgパッケージは、g_start_timeという1つのパッケージレベル変数を使用して、パッケージ内の各サブプログラムのコール開始時刻を記録しています。これは、パッケージ・データがセッションレベルで存続できるからこそ実現できます。 このタイマー・パッケージは、APIの優れた例でもあります。APIとは、基本機能へのインタフェースを提供するサブプログラムのセットです。パッケージ仕様部には実行可能なタスクが記載され、パッケージの本体にタイマーの実装方法（この例の場合はGET_CPU_TIMEファンクションを利用すること）が隠されます。今後、この種の経過時間分析を実行するためのより優れたメカニズムをオラクルが考え出した場合に、開発者はパッケージ本体のみを変更するだけで、ユーザーはその改善された新技術をすぐに利用できます。 サブプログラムのオーバーロードオーバーロードは、あるプログラム・ユニット内で同じ名前のサブプログラムを2つ以上作成した場合に行われます。サブプログラムのオーバーロードは、任意の宣言セクションで行うことができます。つまり、この機能はパッケージに限定されるわけではありません。ただし、ほとんどのオーバーロードはパッケージ内部で行われます。オーバーロードはAPI内においてもっとも効果を発揮するからです。 オーバーロードによって、開発者の日常の開発業務が大幅に楽になります。APIを作成する際に、異なる型のデータや、異なる入力を必要とするさまざまなシナリオに対して、同じようなロジックを実行する必要があることは多いでしょう。オーバーロード機能がなければ、バリエーションごとに異なる名前を使用する必要があり、そのコードのユーザーはどの名前がどのバリエーションに対応するのかを覚える必要があります。 オーバーロードを使用することで、パッケージのユーザーは、必要となる機能を表す1つの名前のみを知っておけばよくなります。残りの仕事はコンパイラの役割です。コンパイラがサブプログラムに渡された引数を分析して、オーバーロードされた要素に対応させます。 オーバーロードを使用して、PL/SQLブロック内から画面上に情報を表示しやすくする方法を見ていきましょう。 Oracle Databaseには、テキストをシステム出力に送信するDBMS_OUTPUT.PUT_LINEというプロシージャがあります。このプロシージャはオーバーロードされていません。1つのVARCHAR2値を受け取る実装が1つあるだけです。 そのため、VARCHAR2値に評価される式をDBMS_OUTPUT.PUT_LINEに渡した場合は正常に動作します。たとえば、数値は暗黙的に文字列に変換されるため、次の文を実行した結果、画面上に"100"と表示されます。 BEGIN DBMS_OUTPUT.PUT_LINE (100); END; SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE (TRUE); 3 END; 4 / DBMS_OUTPUT.PUT_LINE (TRUE); * ERROR at line 2: ORA-06550: line 2, column 4: PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’ そのため、Booleanを表示する場合には、多くの開発者が次のようなコードを記述します。
IF l_student_is_registered
THEN
DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE ('FALSE');
END IF;
Boolean値と、タイムスタンプを含む日付値を簡単に表示できるようにします（デフォルトの書式マスクを使用して日付を文字列に暗黙的に変換する際には、時刻が無視されます）。 オーバーロードを使用しない場合、リスト7のようにデータの型ごとに異なるプロシージャ名を使用する必要があります。 コード・リスト7：オーバーロードを使用しないmy_outputパッケージ
CREATE OR REPLACE PACKAGE my_output
IS
PROCEDURE put_string (value_in IN VARCHAR2);
PROCEDURE put_boolean (value_in IN BOOLEAN);
PROCEDURE put_date (
value_in IN DATE,
mask_in IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/
しかし、オーバーロードを使用すれば、パッケージ仕様部のプロシージャがリスト8のように覚えやすく、検索しやすいものになります。 コード・リスト8：オーバーロードを使用したmy_outputパッケージ
CREATE OR REPLACE PACKAGE my_output
IS
PROCEDURE put_line (value_in IN VARCHAR2);
PROCEDURE put_line (value_in IN BOOLEAN);
PROCEDURE put_line (
value_in IN DATE,
mask_in IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/
賢いソフトウェアがそのような"ささいな詳細"に注意を払い、開発者はユーザー要件の実装に集中し続けられることはすばらしいことです。 オーバーロードには多くのルールや制限がありますが、それらについてはこの記事では割愛します。独自のパッケージの作成を開始するにあたって留意すべきおもなポイントは次のとおりです。
オーバーロードされたモジュールを作成するときには、個別のスタンドアロン・モジュールを作成するときよりも、設計と実装に多くの時間がかかります。このように事前に多くの時間をかけておくと、その分の効果が後になって十分に現れます。開発者やユーザーがプログラムをはるかに利用しやすくなるためです。 アプリケーションの構築ブロックPL/SQLではパッケージを使用せずにアプリケーションを構築することも可能です。その場合は、数百のスキーマレベルのファンクションやプロシージャを作成することになります。しかし、この道を選ぶと、かならず後悔することになるでしょう。コードの検索が難しく、アプリケーション内の既存のコードを変更することはもっと難しいということがすぐに分かります。
そうではなく、プロシージャやファンクションは常にパッケージ内に配置すること、およびトリガー内のコードやオブジェクト型のコードをできる限り多くパッケージに移動することをお勧めします。そうすれば、提示された変更の影響を分析する場合でも、既存のコードへの影響を最小限に抑えながら修正を適用する場合でも、肥大化するコードベースが管理しやすくなります。 パッケージのユーザーに示すAPIを単純化するために、オーバーロードを十分に活用してください。パッケージレベルのデータの使用方法を模索しましょう。 パッケージの状態とORA-04068パッケージ内で少なくとも1つの定数または宣言がパッケージレベルで宣言されている場合、そのパッケージは状態を持つ、つまりステートフルであると言います。セッションでステートフル・パッケージを使用している場合、Oracle Databaseではセッション固有のPGA（プロセス・グローバル領域）に、すべてのパッケージレベルのデータに関するそのセッションの値が保管されます。 ステートフル・パッケージを再コンパイルした場合、そのパッケージを使用していたすべてのセッションが、次回そのパッケージにアクセスしようとしたときにORA-04068エラーにより失敗します。これは、各セッション内のパッケージの状態が古くなり、パッケージの再初期化が必要になるためです。 さらに、ORA-04068エラーがいったん発生すると、そのセッション内のすべてのパッケージ（DBMS_OUTPUTなどを含む）の状態について、初期化されていない状態になります。これは通常、ユーザーがアプリケーションを問題なく使用するためには、そのセッションから切断して再接続する必要があることを意味します。 これまでは、ORA-04068エラーが発生する可能性があるために、IT部門がアプリケーション内のコードをアップグレードする必要がある場合は、最初にすべてのユーザーをアプリケーションからログアウトさせる必要がありました。しかし、24時間365日稼働するインターネットの世界で、このような運用は多くの企業に受け入れられません。 そのため、Oracle Database 11g Release 2では、エディションベースの再定義機能が提供されています。この機能を使用することで、表の構造体への変更やコードへの変更が必要になる場合でも、アプリケーションに"ホット・パッチ"をあてることができます。アップグレードの実行中でもユーザーが切断する必要はなくなりました。 エディションベースの再定義について詳しくは、ホワイト･ペーパーとドキュメントを参照してください。 PL/SQLの基礎に関する次回の記事では、暗黙的問合せからカーソル変数への明示カーソルまで、PL/SQLでのカーソルの操作について取り上げます。
|
ダウンロード
Printer View