PL/SQLを使いこなしてデータベース管理：ORACLE MASTER Silver DBA講座（11）

ORACLE MASTER資格の中級に位置付けられ、取得すればOracle技術者としてグローバルに認定される「ORACLE MASTER Silver Oracle Database 10g」。例題を利用してポイントを押さえ、確実な合格を目指そう！

» 2007年04月03日 00時00分公開

[有限会社 G.F.インフィニティ（Project - ∞）]

　本連載第9回「SQLでのデータ操作方法を覚えよう」、第10回「Data PumpとSQL*Loaderによるデータのロード」で、データの管理方法を紹介しました。今回はPL/SQLについて学びます。

PL/SQL

ポイント

　PL/SQLを使用したプログラム構造体（プロシージャ、ファンクション、パッケージ、トリガー）が対象です。詳細なコーディングが対象となるわけではなく、プログラム構造体の使い分け方や、関連する設定についてが対象となっています。

プログラム構造体

　PL/SQL（Procedure Language/Structured Query Language）は、SQL文に手続き型の拡張機能を提供する言語です。プログラミング言語ですので、IF-THEN、CASE、LOOPといった処理を行うことができます。

　PL/SQLを使用すると、データベースオブジェクトとして、次のものを作成することができます。

プロシージャ
特定のアクションを実行するためのPL/SQLブロックです。例えば、次のプロシージャでは、指定した従業員（EMPNO）の給与（SAL）を1.1倍にします。

SQL> CREATE OR REPLACE PROCEDURE update_sal
  2  (p_empno emp.empno%TYPE)
  3  AS
  4  BEGIN
  5    UPDATE emp SET sal=sal*1.1 WHERE empno=p_empno;
  6  END;
  7  /
プロシージャが作成されました。
SQL> SELECT sal FROM emp WHERE empno=7900;
       SAL
----------
       950
SQL> EXEC update_sal(7900)
PL/SQLプロシージャが正常に完了しました。
SQL> SELECT sal FROM emp WHERE empno=7900;
       SAL
----------
      1045
SQL> COMMIT;
コミットが完了しました。

ファンクション
1つの値を戻すためのPL/SQLブロックです。条件を満たしたファンクションであれば、SQL文でシステム関数と同様に使用することができます。例えば、次のファンクションでは受け取った値を12倍にして戻しますが、値が30000を超える場合は30000という値を戻します。

SQL> CREATE OR REPLACE FUNCTION annual_sal
  2  (p_sal NUMBER)
  3  RETURN NUMBER
  4  AS
  5    v_sal PLS_INTEGER;
  6  BEGIN
  7    v_sal := p_sal * 12;
  8    IF v_sal > 30000 THEN
  9      v_sal := 30000;
 10    END IF;
 11    RETURN v_sal;
 12  END;
 13  /
ファンクションが作成されました。
SQL> SELECT empno,sal,annual_sal(sal) FROM emp
  2  WHERE deptno=10;
     EMPNO        SAL ANNUAL_SAL(SAL)
---------- ---------- ---------------
      7782       2450           29400
      7839       5000           30000
      7934       1300           15600

パッケージ
プロシージャやファンクションをグループ化したオブジェクトです。パッケージ仕様部とパッケージ本体で構成されます。パッケージ仕様部には定義のみが格納され、実際のコードはパッケージ本体に格納されています。パッケージの一部がコールされるとパッケージ全体がメモリにロードされるため、同時に使用するプログラムをパッケージ化することで効率化ができます。

　Oracleソフトウェアとして提供されるビルトインパッケージもあります。独自にコーディングしなくても即使用可能な拡張機能です。

データベーストリガー
テーブル、ビュー、データベースに作成できる自動起動プログラムです。トリガーイベントが発生すると自動起動し、監査や複雑な制約、自動化したいタスクの記述に使用できます。

パフォーマンスに影響を与える初期化パラメータ

　PL/SQLによるプログラム構造体をコンパイルするときに関連する初期化パラメータとして、次のものがあります。

PLSQL_CODE_TYPE
コンパイル済みコードをデータディクショナリ内だけに格納する（INTERPRETED）のがデフォルトですが、そのプラットフォームでネイティブコンパイルして、共有ライブラリとして格納する（NATIVE）ことが選択できます。
PLSQL_DEBUG
TRUEにすることで、開発時のデバッグ機能が有効化されます。デフォルトはFALSEのため、デバッグ機能は無効になっています。
PLSQL_OPTIMIZER_LEVEL
デフォルト値である2の場合、実行時パフォーマンスを最適化しますが、1に変更した場合、コンパイルパフォーマンスを最適化します。
PLSQL_WARNINGS
コンパイラからの警告メッセージを制御できます。警告メッセージの有効化（ENABLE）／無効化（DISABLE）、メッセージ内容として重大（SEVERE）、情報（INFORMATION）、パフォーマンス（PERFORMANCE）、すべて（ALL）のいずれかを設定できます。

問題

問題1

変更されることがないはずの顧客テーブルのデータが変更されていることが疑われています。PL/SQLを使用して監視する方法として適切なオブジェクトタイプを選択しなさい。

a．データベーストリガー
b．パッケージ
c．プロシージャ
d．ファンクション

正解：a

解説

　前回、宿題とした問題です。

　PL/SQLを使用したプログラム構造体には、次のものがあります。

プロシージャ
実行可能プログラムです。引数なしで使用することも、複数の引数とともに使用することもできます。引数としてIN、OUT、IN OUTが使用可能です。
ファンクション
関数です。式として使用できるため、条件が適合すればSQL文の中での使用も可能です。必ずただ1つの値をRETURN文で戻す必要があるため、通常、引数としてはINを使用します。
パッケージ
プロシージャ、ファンクション、カーソル、変数、定数、データ型などをグループ化したプログラムです。パッケージ仕様と本体部に分かれており、コードのカプセル化に役立ちます。
データベーストリガー
テーブル、ビュー、データベースに対応付けられた自動起動プログラムです。トリガーイベントにより自動起動します。

　問題文にあるように表のデータ変更を監視するということは、表に対するDML文を監視することになります。データベーストリガーであれば、DML文をトリガーイベントとして作成できます（正解a）。

　自動起動ではなく明示的に呼び出すプロシージャ、ファンクション、パッケージは、トリガーコードの中で利用することはできてもDML文での自動起動はできないため、不適切です。

問題2

初期化パラメータに次の設定がされています。

PLSQL_WARNINGS=ENABLE:PERFORMANCE

この場合の動作として正しいものを選択しなさい。

a．プログラムを変更するときにPL/SQLエンジンにより、パフォーマンス関連メッセージが表示される
b．プログラムを削除するときにPL/SQLエンジンにより、パフォーマンス関連メッセージが表示される
c．プログラムにアクセスするときにPL/SQLコンパイラにより、パフォーマンス関連メッセージが表示される
d．プログラムをコンパイルするときにPL/SQLコンパイラにより、パフォーマンス関連メッセージが表示される

正解：d

解説

　PLSQL_WARNINGS初期化パラメータを使用することで、PL/SQLプログラム構造体をコンパイルするときに表示される警告を制御できます（正解d）。

　1つ目の引数にはENABLE（警告を受け取る）またはDISABLE（警告を受け取らない）を指定します。2つ目の引数には次のいずれかの値を指定します。

SEVERE：重大メッセージ
INFORMATION：情報メッセージ
PERFORMANCE：パフォーマンスメッセージ
ALL：上記のすべて

　デフォルトでは、「DISABLE:ALL」となっており、コンパイル時に最適なパフォーマンスが提供されるようになっています。

　コンパイルが行われるのは、プログラムの作成時または変更時、無効プログラムが実行されたとき（自動）です。従って、変更時に限定されるものではありません（選択肢a）。また削除時（選択肢b）、アクセス（実行）時（選択肢c）にはPLSQL_WARNINGS初期化パラメータは影響を与えません。

問題3

PL/SQLで作成されたプログラム内からサーバ上のテキストファイルに対し、複数のユーザーが読み取りと書き込みを実行できるように設定する方法として適切な方法を選択しなさい。

a．UTL_FILE_DIR初期化パラメータにディレクトリを設定し、インスタンスを再起動する
b．UTL_FILE_DIR初期化パラメータにディレクトリを設定し、PUBLICに権限を付与する
c．DIRECTORY初期化パラメータにディレクトリを設定し、インスタンスを再起動する
d．DIRECTORY初期化パラメータにディレクトリを設定し、PUBLICに権限を付与する

正解：a