Database
技術記事
PL/SQL 101
テクノロジー：PL/SQL
PL/SQLの機能強化Steven Feuerstein著
Oracle Database 12cでは、PL/SQLファンクション結果キャッシュの機能強化、SQLでのPL/SQL実行の改善、ホワイトリストの追加、権限の微調整が実現されています。 Oracle Database 12cではPL/SQLプログラム・ユニットの定義と実行の方法について、さまざまな機能強化が行われています。この記事では、次のことを実現できるOracle Database 12cの各機能について説明します。
Invoker権限とPL/SQLファンクション結果キャッシュOracle Database 11gではPL/SQLファンクション結果キャッシュが導入されました。ファンクション結果キャッシュは、強力かつ効率的で使いやすいキャッシュ・メカニズムです。このキャッシュのおもな目的は、あるデータ行がデータベースからの前回のフェッチ時点から変更されていない場合に、その行を再度取得するためにSQL文を実行しないで済むようにすることです。
これは、データベース・インスタンス全体に当てはまります。つまり、USER_ONEスキーマに接続するユーザーが、結果キャッシュ・ファンクションを実行して、employees表の従業員ID 100の行を取得したとします。次に、USER_TWOスキーマに接続する別のユーザーが、同じ従業員IDに対して同じファンクション・コールを実行した場合、その行の情報はSELECT文の実行なしに、キャッシュから直接取得されます。 この機能をまだ使用していない場合（かつOracle Database 11gを使用している場合）は、この機能について調査し、適用を始めることを強くお勧めします。また、その際には、DBAと密に連携して、結果キャッシュ・プールのサイズを適切に設定してください。 ただし、Oracle Database 11g Release 2でも、Invoker権限（AUTHID CURRENT_USER句）とファンクション結果キャッシュ（RESULT_CACHEキーワード）を組み合わせて使用することはできません。たとえば、次のファンクションをコンパイルしてみます。
CREATE OR REPLACE FUNCTION last_name (
employee_id_in
IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
AUTHID CURRENT_USER
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
/
PLS-00999: implementation restriction (may be temporary) RESULT_CACHE is disallowed on subprograms in Invoker-Rights modules もっとも、嬉しいことに、この制約は一時的なものでした。Oracle Database 12cでは、上の例のlast_nameのようなファンクションをエラーなしにコンパイルできます。また、言うまでもなくOracle Database 12cでは正しい処理が実行されます。 Oracle Database 12cは水面下で、現在のユーザーの名前を隠しパラメータとして渡します。この値も、ファンクションに渡されるすべての引数の値とともにキャッシュされます。そのため、last_nameファンクションがコールされるたびに、Oracle Database 12cでは、そのファンクションが以前にコールされたときに、同じ従業員IDと同じ現在のユーザーが使用されたかどうかが検証されます。 つまり、Invoker権限のファンクションの結果キャッシュが、現在のユーザー名ごとに（論理的に）パーティション分割されます。結果的に、Invoker権限のファンクションの結果キャッシュでは、同じユーザーが同じ引数の値を使用してファンクションを繰り返しコールする場合に限り、パフォーマンスが向上します。別の角度から説明すると、Oracle Database 11g Release 2では、リスト1のようにlast_nameファンクションの実装を変更した場合に限り、同じ効果を得ることができました。 コード・リスト1："パーティション分割された"Oracle Database 11g Release 2のInvoker権限のファンクション
CREATE OR REPLACE PACKAGE employee_api
AUTHID CURRENT_USER
IS
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE;
END;
/
CREATE OR REPLACE PACKAGE BODY employee_api
IS
FUNCTION i_last_name (
employee_id_in IN employees.employee_id%TYPE,
user_in IN VARCHAR2 DEFAULT USER)
RETURN employees.last_name%TYPE
RESULT_CACHE
IS
l_return employees.last_name%TYPE;
BEGIN
SELECT last_name
INTO l_return
FROM employees
WHERE employee_id = employee_id_in;
RETURN l_return;
END;
FUNCTION last_name (
employee_id_in IN employees.employee_id%TYPE)
RETURN employees.last_name%TYPE
IS
l_return employees.last_name%TYPE;
BEGIN
RETURN i_last_name (employee_id_in,
USER);
END;
END;
/
そのため、employee_api.last_nameをコールするたびに、Oracle Database 11g Release 2ではデータベースによって使用される値のセットにユーザー名が追加され、結果キャッシュ内に一致するものがあるかが判定されます。 しかし、このような工夫は今や必要ありません。Oracle Database 12cでは、Invoker権限のプログラムにRESULT_CACHEを追加する価値があるかを判断するだけで良いのです。 SQL文内でのPL/SQLサブプログラムの定義SQL文内から独自のPL/SQLファンクションをコールできるのは、ずいぶん以前からのことです。たとえば、指定した開始位置から終了位置までのサブストリングを返すBETWNSTRいうファンクションを作成したとします。
FUNCTION betwnstr (
string_in IN VARCHAR2
, start_in IN PLS_INTEGER
, end_in IN PLS_INTEGER
)
RETURN VARCHAR2
IS
BEGIN
RETURN ( SUBSTR (
string_in, start_in,
end_in - start_in + 1 ));
END;
SELECT betwnstr (last_name, 3, 5) FROM employees Oracle Database 12cでは、PL/SQLのファンクションとプロシージャを副問合せのWITH句内で定義し、他の組込みファンクションやユーザー定義ファンクションと同じように使用できるようになりました。この機能により、前述のBETWNSTRファンクションと問合せを次の1文にまとめることができます。
WITH
FUNCTION betwnstr (
string_in IN VARCHAR2,
start_in IN PLS_INTEGER,
end_in IN PLS_INTEGER)
RETURN VARCHAR2
IS
BEGIN
RETURN (SUBSTR (
string_in,
start_in,
end_in - start_in + 1));
END;
SELECT betwnstr (last_name)
FROM employees
パッケージ化された定数の参照パッケージ化されたファンクションはSQL内でコールできますが、パッケージ内で宣言された定数を参照することはできません（そのSQL文がPL/SQLブロック内で実行される場合を除く）。このような定数の参照に関する制限について、次に例を示します。
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 year_number
CONSTANT INTEGER := 2013;
4 END;
5 /
Package created.
SQL> SELECT pkg.year_number
FROM employees
2 WHERE employee_id = 138
3 /
SELECT pkg.year_number FROM employees
ERROR at line 1:
ORA-06553:PLS-221:'YEAR_NUMBER' is not
a procedure or is undefined
SQL> CREATE OR REPLACE PACKAGE pkg
2 IS
3 FUNCTION year_number
4 RETURN INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pkg
2 IS
3 c_year_number
CONSTANT INTEGER := 2013;
4
5 FUNCTION year_number
6 RETURN INTEGER
7 IS
8 BEGIN
9 RETURN c_year_number;
10 END;
11 END;
12 /
Package body created.
SQL> SELECT pkg.year_number
2 FROM employees
3 WHERE employee_id = 138
4 /
YEAR_NUMBER
———————————
2013
WITH FUNCTION year_number RETURN INTEGER IS BEGIN RETURN pkg.year_number; END; SELECT year_number FROM employees WHERE employee_id = 138 このWITH FUNCTION機能は非常に便利なSQL言語拡張です。ただし、WITH FUNCTION機能の利用を検討する際にはかならず次のように自問してください。「この同じ機能がアプリケーションの複数の場所で必要になるだろうか」 必要になる場合は、WITH FUNCTIONの利用によるパフォーマンス改善が、このロジックを複数のSQL文にコピーアンドペーストすることによる潜在的な欠点よりも価値があるかを判断する必要があります。 ホワイトリストとACCESSIBLE BY句ほとんどのPL/SQLベースのアプリケーションは、多数のパッケージで構成されます。その中には、プログラマーがユーザー要件を実装するために使用する"トップ・レベル"のAPIもあれば、他の特定のパッケージにのみ使用されることを想定した"ヘルパー"のパッケージもあります。 Oracle Database 12cより前のPL/SQLでは、セッションのスキーマにEXECUTE権限が付与されているパッケージ内の任意またはすべてのサブプログラムをセッションが使用することを防止する手段はありませんでした。これに対してOracle Database 12cでは、すべてのPL/SQLプログラム・ユニットでオプションのACCESSIBLE BY句を使用できるようになりました。ACCESSIBLE BY句により、作成または変更対象のPL/SQLユニットに対して、アクセス可能な他のPL/SQLユニットのホワイトリストを指定できます。 例を見てみましょう。まず、"パブリック"なパッケージ仕様部を作成します。このパッケージは、他の開発者がアプリケーション構築のために使用することを想定しています。 CREATE OR REPLACE PACKAGE public_pkg IS PROCEDURE do_only_this; END; / CREATE OR REPLACE PACKAGE private_pkg ACCESSIBLE BY (public_pkg) IS PROCEDURE do_this; PROCEDURE do_that; END; / 次にパッケージの本体を実装します。public_pkg.do_only_thisプロシージャは、private_pkgのサブプログラムをコールします。
CREATE OR REPLACE PACKAGE BODY public_pkg
IS
PROCEDURE do_only_this
IS
BEGIN
private_pkg.do_this;
private_pkg.do_that;
END;
END;
/
CREATE OR REPLACE PACKAGE BODY
private_pkg
IS
PROCEDURE do_this
IS
BEGIN
DBMS_OUTPUT.put_line ('THIS');
END;
PROCEDURE do_that
IS
BEGIN
DBMS_OUTPUT.put_line ('THAT');
END;
END;
/
BEGIN public_pkg.do_only_this; END; / THIS THAT BEGIN private_pkg.do_this; END; / ERROR at line 2: ORA-06550: line 2, column 1: PLS-00904: insufficient privilege to access object PRIVATE_PKG ORA-06550: line 2, column 1: PL/SQL：Statement ignored
SQL> CREATE OR REPLACE PROCEDURE
use_private
2 IS
3 BEGIN
4 private_pkg.do_this;
5 END;
6 /
Warning:Procedure created with
compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE USE_PRIVATE:
LINE/COL ERROR
———————— ——————————————————————————
4/4 PL/SQL:Statement ignored
4/4 PLS-00904: insufficient
privilege to access object
PRIVATE_PKG
プログラム・ユニットへの権限付与Oracle Database 12cより前のリリースでは、Definer権限のプログラム・ユニット（AUTHID DEFINER句またはAUTHID句により定義）は常に、そのユニットのDefiner権限を使用して実行されました。一方、Invoker権限のプログラム・ユニット（AUTHID CURRENT_USER句により定義）は常に、そのユニットのInvoker権限を使用して実行されました。 この2種類のAUTHID設定の結果、すべてのユーザーが実行する必要のあるプログラム・ユニットを、Definer権限のユニットとして作成する必要がありました。そのプログラム・ユニットはDefinerのすべての権限を使用して実行されます。これは、セキュリティの観点からは望ましくありません。 Oracle Database 12cでは、PL/SQLのパッケージおよびスキーマレベルのプロシージャとファンクションに対してロールを付与できます。プログラム・ユニットに対するロールベースの権限によって、プログラム・ユニットのInvokerが利用できる権限を微調整できます。 Invoker権限を持つプログラム・ユニットを定義し、そのInvoker権限を、ロール経由で付与された限定的な固有の権限により補完できるのです。 プログラム・ユニットへのロールの付与方法とその影響を示す例を見てみましょう。HRスキーマに、departments表とemployees表が含まれるとします。これらの表を次のように定義してデータを移入します。
CREATE TABLE departments
(
department_id INTEGER,
department_name VARCHAR2 (100),
staff_freeze CHAR (1)
)
/
BEGIN
INSERT INTO departments
VALUES (10, 'IT', 'Y');
INSERT INTO departments
VALUES (20, 'HR', 'N');
COMMIT;
END;
/
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (101, 20, 'Sam');
INSERT INTO employees
VALUES (102, 20, 'Joseph');
INSERT INTO employees
VALUES (103, 20, 'Smith');
COMMIT;
END;
/
CREATE TABLE employees
(
employee_id INTEGER,
department_id INTEGER,
last_name VARCHAR2 (100)
)
/
BEGIN
DELETE FROM employees;
INSERT INTO employees
VALUES (100, 10, 'Price');
INSERT INTO employees
VALUES (104, 20, 'Lakshmi');
INSERT INTO employees
VALUES (105, 20, 'Silva');
INSERT INTO employees
VALUES (106, 20, 'Ling');
COMMIT;
END;
/
コード・リスト2：従業員レコードを削除するDefiner権限のプロシージャ
CREATE OR REPLACE PROCEDURE remove_emps_in_dept (
department_id_in IN employees.department_id%TYPE)
AUTHID DEFINER
IS
l_freeze departments.staff_freeze%TYPE;
BEGIN
SELECT staff_freeze
INTO l_freeze
FROM HR.departments
WHERE department_id = department_id_in;
IF l_freeze = ‘N’
THEN
DELETE FROM employees
WHERE department_id = department_id_in;
END IF;
END;
/
さらに、SCOTTがこのプロシージャを実行できるようにします。 GRANT EXECUTE ON remove_emps_in_dept TO SCOTT /
BEGIN HR.remove_emps_in_dept (20); END; / AUTHID CURRENT_USER BEGIN * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "HR.REMOVE_EMPS_IN_DEPT", line 7 ORA-06512: at line 2 Oracle Database 12cより前のリリースでは、DBAはHR.departmentsへの必要な権限をSCOTTに付与する必要がありました。しかし、Oracle Database 12cでは、次の手順を利用できます。 CREATE ROLE hr_departments / GRANT hr_departments TO hr / GRANT SELECT ON departments TO hr_departments / GRANT hr_departments TO PROCEDURE remove_emps_in_dept /
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
3
BEGIN
hr.remove_emps_in_dept (20);
END;
/
SELECT COUNT (*)
FROM employees
WHERE department_id = 20
/
COUNT(*)
—————————————
0
この機能は、Invoker権限のプログラム・ユニットの場合にもっとも有用です。また、Definer権限のユニットが動的SQLを実行する場合にも、そのユニットにロールを付与するかを検討することになるでしょう。その動的な文の権限が実行時にチェックされるためです。 次のステップ：SQLを実行するためのPL/SQLの機能強化Oracle Database 12cでは、プログラム・ユニットの定義と実行における柔軟性と機能性が大幅に向上しています。Oracle Database 12cの機能により、PL/SQL開発者はInvoker権限をファンクション結果キャッシュとともに使用すること、SQL文内でPL/SQLサブプログラムを定義して実行すること、ホワイトリストによってプログラム・ユニットへのアクセスを制限すること、プログラム・ユニットにロールを付与することが可能です。 Oracle Database 12cでは、PL/SQLプログラム・ユニット内でのSQL実行も、さまざまな方法で強化されています。この点について、次号のOracle Magazineで取り上げます。
|
ダウンロード
Printer View