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

　本連載第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

解説

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

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

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

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

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

問題2

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

PLSQL_WARNINGS=ENABLE:PERFORMANCE

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

正解：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

解説

　PL/SQLのプログラムからサーバ上のテキストファイルを読み書きするには、UTL_FILEパッケージを使用します。このパッケージでは、アクセスするディレクトリにディレクトリオブジェクト、またはUTL_FILE_DIR初期化パラメータで指定された物理ディレクトリパスを指定することができます。

　UTL_FILE_DIR初期化パラメータは、静的初期化パラメータです。修正した場合はインスタンスを再起動する必要があります（正解a）。

　そのほかの選択肢の不正解の理由は次のとおりです。

●選択肢b：UTL_FILE_DIR初期化パラメータで指定した物理ディレクトリパスは、すべてのユーザーが自動的に使用可能になります。権限を付与して管理するのは、ディレクトリオブジェクトを使用した場合です。

●選択肢c、d：DIRECTORYという初期化パラメータはありません。ディレクトリオブジェクトはCREATE DIRECTORYコマンドで作成し、使用許可を与えたいユーザーには、READとWRITEの権限を付与して管理します。

宿題

　次回から2回にわたって、「Oracle Databaseのセキュリティ」を確認します。次の宿題を解いておいてください。

問題

ユーザーを作成後、パスワードをすぐに変更させ、パスワードには数値を1文字、アンダーバーを1文字含めるために設定する機能を2つ選択しなさい。