掲載元 2013年11/12月

テクノロジー：PL/SQL

PL/SQLでのSQLの機能強化

Steven Feuerstein著

Oracle Database 12cでは、PL/SQLファンクション結果キャッシュの機能強化、SQLでのPL/SQL実行の改善、ホワイトリストの追加、権限の微調整が実現しています。

Oracle Database 12cのリリースに伴い、PL/SQL内でのSQL文の記述と実行に対して、いくつかの機能強化が実施されています。この記事では、次の処理を可能にするOracle Database 12cの新機能について説明します。

• PL/SQL固有のデータ型からSQL文に対して値をバインドする

• SQL問合せから暗黙的に結果セットを返すことで、Transact-SQLなどの言語からOracle PL/SQLへの移行パスを簡素化する

• Invoker権限のプログラム・ユニットと同様の動作をするビューを定義する 

SQLバインディングでのPL/SQLデータ型のサポート

Oracle Database 12cより前のリリースでは、EXECUTE IMMEDIATEやDBMS_SQLを使用してPL/SQL式をバインドする場合は、常に、この式のデータ型が既知のSQLデータ型でなければなりませんでした。特に、レコードやコレクションを含むパッケージ仕様で宣言されたユーザー定義型やBOOLEANをバインドすることが不可能でした。

Oracle Database 12cではこの制限がほとんど解消されています。

たとえば、EXECUTE IMMEDIATEを使用して動的なPL/SQLブロックを実行する場合にブール値をバインドできます。リスト1に、ブロック実行の例を示します。

コード・リスト1：ブール値のバインドとEXECUTE IMMEDIATEの使用 

CREATE OR REPLACE PACKAGE restaurant_pkg

AS
   TYPE item_list_t
      IS TABLE OF VARCHAR2 (30);

   PROCEDURE eat_that (
      items_in              IN item_list_t,
      make_it_spicy_in_in   IN BOOLEAN);
END;
/

CREATE OR REPLACE PACKAGE BODY restaurant_pkg
AS
   PROCEDURE eat_that (
      items_in              IN item_list_t,
      make_it_spicy_in_in   IN BOOLEAN)
   IS
   BEGIN
      FOR indx IN 1 .. items_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
               CASE
                  WHEN make_it_spicy_in_in
                  THEN
                     'Spicy '
               END
            || items_in (indx));
      END LOOP;
   END;
END;
/

DECLARE
   things   restaurant_pkg.item_list_t
      := restaurant_pkg.item_list_t (
            'steak',
            'quiche',
            'eggplant');
BEGIN
   /* Requires Oracle Database 12c or later */
   EXECUTE IMMEDIATE
      'BEGIN restaurant_pkg.eat_that(:l, :s); END;'
      USING things, TRUE;
END;
/ 

また、連想配列をバインドし、TABLE演算子に対するコール内でこれを使用することもできます。Oracle Database 12c以前では、これらの処理はいずれも不可能でした。リスト2のコードは、SELECT文をバインドおよび使用して、連想配列からデータを取得する例を示します。

コード・リスト2：連想配列のバインドと使用 

CREATE OR REPLACE PACKAGE names_pkg
   AUTHID CURRENT_USER
AS
   TYPE names_t 
   IS TABLE OF VARCHAR2 (100)
      INDEX BY PLS_INTEGER;

   PROCEDURE display_names (
      names_in   IN names_t);
END names_pkg;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY names_pkg
AS
   PROCEDURE display_names (
      names_in   IN names_t)
   IS
   BEGIN
      FOR indx IN 1 .. names_in.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
            names_in (indx));
      END LOOP;
   END;
END names_pkg;
/

SHO ERR

DECLARE
   l_names   names_pkg.names_t;
BEGIN
   l_names (1) := 'Loey';
   l_names (2) := 'Dylan';
   l_names (3) := 'Indigo';
   l_names (4) := 'Saul';
   l_names (5) := 'Sally';

   EXECUTE IMMEDIATE
      'BEGIN names_pkg.display_names (:names); END;'
      USING l_names;

   FOR rec
      IN (SELECT * FROM TABLE (l_names))
   LOOP
      DBMS_OUTPUT.put_line (
         rec.COLUMN_VALUE);
   END LOOP;
END;
/ 

SQLデータ型に関する機能強化はその他にもあります。Oracle Database 12cより前のリリースでは、SQLでのVARCHAR2データ型の最大長が4,000バイトであり、PL/SQLでの最大長が32,767バイトでした。Oracle Database 12cでは、VARCHAR2とNVARCHAR2の最大長が32,767バイトになりました。

注：拡張された最大長を利用するには、MAX_STRING_SIZEデータベース・パラメータをEXTENDEDに設定する必要があります。デフォルト値はSTANDARDです。

SQLデータ型について、詳しくはOracle Database SQL言語リファレンス12cリリース1（12.1）を参照してください。

暗黙文の結果

Oracle Database 12c以前のPL/SQLでは、結果セットの内容を画面に返すだけのプロシージャを作成する機能がサポートされていませんでした。そのため、開発者はPL/SQL内に問合せを作成し、結果セットに対する繰り返し処理を行い、DBMS_OUTPUT.PUT_ LINEをコールして結果を表示する必要がありました。

Oracle Database 12cのPL/SQLでは、結果を直接画面に返すプロシージャを作成できます。これはおもに、Transact-SQLからPL/SQLに移行する開発者およびアプリケーション（PL/SQLへようこそ！）と、テスト支援用（表の内容を検証するプロシージャをすばやく簡単に作成できるようになったため）に役立ちます。Oracle Database 12cにこの機能を実装するため、DBMS_SQLパッケージに新機能が追加されています。

たとえば、特定の部門内の全従業員の姓を表示する必要があるとします。この場合は、新しいDBMS_SQL.RETURN_RESULTプロシージャを使用して次のようなコードを作成できます。 

CREATE OR REPLACE PROCEDURE show_emps (
   department_id_in IN 
   employees.department_id%TYPE)
IS
   l_cursor   SYS_REFCURSOR;
BEGIN
   OPEN l_cursor FOR
        SELECT last_name
          FROM employees
         WHERE department_id = 
               department_id_in
      ORDER BY last_name;

   DBMS_SQL.return_result (l_cursor);
END;
/ 

このプロシージャをSQL*Plusで部門ID=20に対して実行すると、次の結果が表示されます。 

BEGIN
   show_emps (20);
END;
/
PL/SQL procedure successfully completed.

ResultSet #1

LAST_NAME
———————————————
Fay
Hartstein 

1つのプロシージャから複数の結果を返すこともできます。PL/SQLプログラム内から結果の行を取得するには、DBMS_SQL.GET_NEXT_RESULTプロシージャをコールして次のカーソルを取得してから、別のDBMS_SQLサブプログラムを使用してデータ行をフェッチします。DBMS_SQL.RETURN_RESULTとDBMS_SQL.GET_NEXT_RESULTについて、詳しくはOracle Database PL/SQLパッケージおよびタイプ・リファレンス12cリリース1（12.1）を参照してください。

ビューに対する事実上のInvoker権限

Oracle Database 12c以前のビューでファンクションを実行すると、ファンクション所有者の権限ではなく、ビュー所有者の権限でファンクションが実行されていました。そのため、ファンクションがInvoker権限として定義されている場合は、期待した動作とはかなり異なる結果になる場合がありました。

Oracle Database 12cのビューにはBEQUEATH句が追加されているため、Invoker権限のファンクションをビュー内から参照できるようなビューが定義できます。この機能を利用しやすくするため、Oracle Database 12cでは、Invoker権限とDefiner権限のいずれが使用されているかに基づいて起動ユーザーを返す、2つの新しいファンクションが提供されています。

• ORA_INVOKING_USER：現在の文またはビューを起動しているユーザーの名前を返します。起動ユーザーがOracle DatabaseのReal Application Security機能によって定義されている場合、このファンションはXS$NULLを返します。

• ORA_INVOKING_USERID：現在の文またはビューを起動しているユーザーの識別子（ID）を返します。起動ユーザーがReal Application Securityで定義されている場合、このファンクションはすべてのReal Application Securityセッションに共通するIDかつ、どのデータベース・ユーザーとも異なるIDを返します。

では、この機能の仕組みについて確認しましょう。HRスキーマ内にemps_12cempsという表と、emps_countというファンクションを作成しました。リスト3に示すように、このファンクションはORA_INVOKING*ファンクションをコールします。

コード・リスト3：BEQUEATH句をテストするための表とファンクションの作成 

CREATE TABLE emps_12cemps
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO emps_12cemps VALUES (1, 100, 'abc');
   INSERT INTO emps_12cemps VALUES (2, 100, 'def');
   INSERT INTO emps_12cemps VALUES (3, 200, '123');
   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION emps_count (
   department_id_in IN INTEGER)
   RETURN PLS_INTEGER
   AUTHID CURRENT_USER
IS
   l_count    PLS_INTEGER;
   l_user     VARCHAR2 (100);
   l_userid   VARCHAR2 (100);
BEGIN
   SELECT COUNT (*)
     INTO l_count
     FROM emps_12cemps
    WHERE department_id = department_id_in;

   /* Show who is invoking the function */

   SELECT ora_invoking_user INTO l_user FROM DUAL;
   SELECT ora_invoking_userid INTO l_userid FROM DUAL;
   DBMS_OUTPUT.put_line (l_user);
   DBMS_OUTPUT.put_line (l_userid);

   RETURN l_count;
END;
/ 

次に、emp_counts_vというビューを作成し、BEQUEATH句にInvoker権限（CURRENT_USER）を指定することで、SCOTTがこのビューに問合せを実行できるようにします。 

CREATE OR REPLACE VIEW emp_counts_v  
   BEQUEATH CURRENT_USER
AS
   SELECT department_id, 
          emps_count (department_id) 
          emps_in_dept
     FROM emps_12cemps
/

GRANT SELECT ON emp_counts_v TO scott
/ 

SCOTTスキーマに、emps_12cempsという表をもう1つ作成し、異なるデータを挿入します。 

CREATE TABLE emps_12cemps
(
   employee_id     INTEGER,
   department_id   INTEGER,
   last_name       VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO emps_12cemps 
   VALUES (1, 200, 'SCOTT.ABC');
   INSERT INTO emps_12cemps 
   VALUES (2, 200, 'SCOTT.DEF');
   INSERT INTO emps_12cemps 
   VALUES (3, 400, 'SCOTT.123');
   COMMIT;
END;
/ 

次に、SERVEROUTPUTをONにして、emp_counts_vビューからすべての行を取得します。問合せの出力は次のとおりです。 

SQL> SELECT * FROM hr.emp_counts_v
  2  /

DEPARTMENT_ID  EMPS_IN_DEPT
—————————————— —————————————
          100             0
          100             0
          200             2
SCOTT
107
SCOTT
107
SCOTT
107 

お分かりのとおり、ビューから返されたデータはHR表のものですが（部門IDの値に100が含まれる）、emps_countファンクションのコールで返された合計数はSCOTT表のものです。また、ORA_INVOKING*ファンクションはSCOTTの情報を返しています。

BEQUEATH CURRENT_USERによって、ビュー自体がInvoker権限オブジェクトに変換されるわけではない点に注意が必要です。ビュー内での名前解決はビュー所有者のスキーマを使用して処理されますが、ビューの権限チェックはビュー所有者の権限を使用して実施されます。

この機能の一番のメリットは、SYS_CONTEXTやUSERENVなどのファンクションをビューから参照した場合も、一貫した結果が返されるようになることです。

次号：残りの機能

次号のOracle Magazineでは、Oracle Database 12cのPL/SQL言語でもっとも興味深い残りの新機能について考察する予定です。これには、UTL_CALLSTACKパッケージ（実行コール・スタックやエラー・スタック、エラー バックトレースに関する非常に詳細な情報を提供）と新しいDBMS_UTILITY.EXPAND_SQL_TEXTプロシージャ（入力されたSQL問合せ内のビュー参照を、該当するビューの副問合せで再帰的に置換）が含まれます。

CREATE TABLE plch_autos
(
   auto_name   VARCHAR2 (100),
   auto_type   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_autos
        VALUES ('Corvette', 'Sports');

   INSERT INTO plch_autos
        VALUES ('Yugo', 'Not Really');

   INSERT INTO plch_autos
        VALUES ('Carrera', 'Sports');

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE 
plch_show_autos (
   auto_type_in   IN plch_autos.auto_type%TYPE)
IS
BEGIN
   FOR rec IN (  SELECT auto_name
                   FROM plch_autos
                  WHERE auto_type = auto_type_in
               ORDER BY auto_name)
   LOOP
      DBMS_OUTPUT.put_line (rec.auto_name);
   END LOOP;
END;
/

BEGIN
   plch_show_autos ('Sports');
END;
/

CREATE OR REPLACE PROCEDURE 
plch_show_autos (
   auto_type_in   IN plch_autos.auto_type%TYPE)
IS
   l_cursor   SYS_REFCURSOR;
BEGIN
   OPEN l_cursor FOR
        SELECT auto_name
          FROM plch_autos
         WHERE auto_type = auto_type_in
      ORDER BY auto_name;

   DBMS_SQL.return_result (l_cursor);
END;
/

BEGIN
   plch_show_autos ('Sports');
END;
/

SELECT auto_name
  FROM plch_autos
 WHERE auto_type = 'Sports'
ORDER BY auto_name
/

Steven Feuersteinの経歴とその他のOracle MagazineのPL/SQL記事へのリンク

▲ ページTOPに戻る

記事一覧へ戻る