テクノロジー：PL/SQL
データ・ディクショナリ：ビューの有効活用

記事一覧へ戻る

PL/SQLの理解と利用に関するシリーズ記事のパート10

この記事の読者であれば、PL/SQLコードを記述する機会は本当に多いでしょう。そのため、頻度は高くないとしても、今後そのコードを分析して、次のような疑問に答える必要があります。 

• プログラムが依存しているデータベース・オブジェクトはどれか  

• 他のパッケージ内のサブプログラムへのコールや、グローバル変数への参照を含むパッケージはどれか  

• 使用すべきでないデータ型のパラメータを含むサブプログラムはあるか  

• すべてのサブプログラムが十分に高い最適化レベルでコンパイルされているか 

言うまでもなく、エディタや統合開発環境の検索機能を使用して、複数のデータベース・オブジェクトやファイルを調査し、特定のテキストのまとまりを見つけることはいつでもできます。しかし、それだけでは、これらの疑問や、その他の多くの疑問のすべてに答えることはできません。

落ち込む必要はありません。PL/SQLコードを記述してデータベース内にコンパイルすることの大きな利点は、Oracle Databaseが提供しているデータ・ディクショナリと総称される一連のビューを使用できることです。データ・ディクショナリを使用すれば、SQL言語に加えPL/SQL言語も使用して、コードに関するあらゆる疑問に答えることができます。表1に、PL/SQLコードの管理によく使用されるデータ・ディクショナリ・ビューの概要を示します。

表1：PL/SQLプログラマーにとって有益なビュー

この記事では、この表のビューの多くについて詳しく取り上げ、ビューの中で特に便利な列について説明し、さらにこれらのビューの使用例を示します。

データ・ディクショナリの基本

データ・ディクショナリは、データベース・インスタンスが作成した多数の表とビューにより構成されます。一般的に、ユーザー・スキーマにはこれらの表に対する権限がなく、Oracle Databaseにより、ビューに対するSELECTのアクセス権のみが付与されます。

ほとんどのデータ・ディクショナリに、次の3つのバージョンがあります。 

1. USERビュー：接続先のスキーマが所有するデータベース・オブジェクトに関する情報  

2. ALLビュー：現在接続中のスキーマがアクセス権を持つデータベース・オブジェクトに関する情報  

3. DBAビュー：データベース・インスタンス内のすべてのデータベース・オブジェクトに関する制限なしの情報（DBA以外のスキーマは通常、DBAビューの問合せ権限を持たない） 

例を見てみましょう。データベース内に定義されたオブジェクト（表、ビュー、パッケージなど）のリストを取得することにします。

次の問合せは、このスキーマに定義されているすべてのオブジェクトを返します。 

SELECT * FROM user_objects  

次の問合せは、スキーマに定義されているか、ユーザーが何らかの手段で使用権限を付与されている、すべてのオブジェクトを返します。 

SELECT * FROM all_objects  

さらに、次の問合せは、データベース・インスタンス内に定義されているすべてのオブジェクトのリストを返します（ユーザーにそのビューの検索権限がある場合）。 

SELECT * FROM dba_objects  

通常、USERビューとALLビューの違いは、ALLビューにはオブジェクトを所有するスキーマを示すOWNERという列が追加されていることだけです。

この記事の以降の説明では、USERビューに基づいて例を示します。

ストアド・オブジェクトに関する情報の表示

USER_OBJECTSビューには、スキーマが所有するデータベース・オブジェクトごとに1行表示されます。よく使用される列は次のとおりです。 

• OBJECT_NAME：オブジェクト名

• OBJECT_TYPE：オブジェクトのタイプ（PACKAGE、FUNCTION、TRIGGERなど）

• STATUS:オブジェクトのステータス（VALIDまたはINVALID）

• LAST_DDL_TIME：このオブジェクトの最終変更日時を示すタイムスタンプ 

次に、USER_OBJECTSに対する問合せの例を示します。 

• スキーマにあるすべての表の名前を表示します。  

  Copy

  
  SELECT object_name
    FROM user_objects
   WHERE object_type = 'TABLE'
   ORDER BY object_name 

• ステータスが無効になっているすべてのオブジェクトの名前を表示します。  

  Copy

  
  SELECT object_type, object_name
    FROM user_objects
   WHERE status = 'INVALID'
   ORDER BY object_type, object_name
    

  プログラム・ユニット（PL/SQLパッケージ、プロシージャ、またはファンクション）のステータスは、依存先のデータベース・オブジェクトが変更された場合にINVALIDに設定されます。そのプログラム・ユニットは再コンパイルする必要があります（再コンパイルは多くの場合、そのプログラム・ユニットを次回使用したときに、Oracle Databaseによって自動的に実行されます）。

• 今日変更されたすべてのオブジェクトを表示します。  

  Copy

  
  SELECT object_type, object_name, 
         last_ddl_time
    FROM user_objects
   WHERE last_ddl_time >= TRUNC (SYSDATE)
   ORDER BY object_type, object_name 

ソース・コードの表示と検索

データベース内にコンパイルしたすべてのプログラム・ユニットのソース・コードは、USER_SOURCEビューによりアクセスできます。USER_SOURCEには次の列があります。 

• NAME：オブジェクト名

• TYPE:オブジェクトのタイプ（PL/SQLプログラム・ユニット、Javaソース、トリガーのソースなど広範囲に及ぶ）

• LINE：ソース・コードの行番号

• TEXT：ソース・コードのテキスト

USER_SOURCEに対する問合せを記述する目的は、次のとおりです。 

• パッケージの特定のサブプログラムをコールするすべてのプログラム・ユニットを検索する

• コーディング標準に従っているかを検証する

• 変更する必要のあるリテラル値のすべての使用箇所を検索する

次に例を示します。SALES_MGRパッケージ内のCALC_TOTALSというプロシージャについて、パラメータ・リストとコードを変更する必要があります。そこで、SALES_MGRパッケージの外部で、このプロシージャをコールしている場所を検索します。  

SELECT name, line, text
  FROM user_source
 WHERE UPPER (text) 
  LIKE '%SALES_MGR.CALC_TOTALS%'
 ORDER BY name,  

言うまでもなく、この問合せによって、この文字列を含むすべてのコメントも検索されます。また、次のような、このSELECT文では見つからないCALC_TOTALSの呼出し部も存在する可能性があります。 

SALES_MGR.
CALC_TOTALS 

ただし、このようにサブプログラムのコールを分割しないようにコードを記述、整形するという前提であれば、この問合せでも、確認の必要があるコード内の場所を特定するのに十分です。

また、Oracle Database 11gのインスタンスでは、PL/Scope機能も使用できます。詳しくは、補足の"より優れたUSER_SOURCE"を参照してください。

ストアド・コードのコンパイラ設定

USER_PLSQL_OBJECT_SETTINGSビューには、PL/SQLストアド・オブジェクトのコンパイラ設定に関する情報が表示されます。おもな列は次のとおりです。 

• PLSQL_OPTIMIZE_LEVEL：オブジェクトのコンパイルに使用された最適化レベル

• PLSQL_CODE_TYPE：オブジェクトのコンパイル・モード

• PLSQL_DEBUG：オブジェクトがデバッグ用にコンパイルされたかどうか

• PLSQL_WARNINGS：オブジェクトのコンパイルに使用されたコンパイラ警告設定

• NLS_LENGTH_SEMANTICS：オブジェクトのコンパイルに使用されたNLS長さセマンティクス

次に、USER_PLSQL_OBJECT_SETTINGSに対する問合せの例を示します。  

• Oracle Databaseのコンパイル時の最適化を十分に利用していないすべてのプログラム・ユニットを検索します。

  Copy

  
  SELECT name
    FROM user_plsql_object_settings
   WHERE plsql_optimize_level < 2 

  最適化レベル0は最適化されていないことを示し、最適化レベル1は最適化が最小限であることを示します。これらのレベルはいずれも、本番環境で使用すべきではありません。

• コンパイル時の警告（コードの品質に関するフィードバックを表示するもの）が無効化されているすべてのプログラムを識別します。  

  Copy

  
  SELECT name, plsql_warnings
    FROM user_plsql_object_settings
   WHERE plsql_warnings LIKE '%DISABLE%';  

プロシージャとファンクションに関する詳細情報

USER_PROCEDURESビューには、スキーマ内にあるすべてのファンクションとプロシージャに関する情報が表示されます。スキーマレベルと、パッケージ内に定義されたものの両方が対象となります。このビューの列は次のとおりです。 

• AUTHID：プロシージャまたはファンクションがInvoker権限（CURRENT_USER）とDefiner権限（DEFINER）のどちらのプログラム・ユニットとして定義されているかを示します。

• DETERMINISTIC：ファンクションが決定的として定義されている場合はYESに設定されます。これは理論的に、ファンクションから返される値がファンクションの引数値によって完全に決定されることを意味します。

• PIPELINED：ファンクションがパイプライン・ファンクションとして定義されている場合はYESに設定されます。これは、パラレル問合せの一部としてパラレルに実行できるファンクションであることを意味します。

• OVERLOAD：このサブプログラムがオーバーロードされている場合に正数に設定されます。これは、同じパッケージ内にこの名前のサブプログラムが2つ以上あることを意味します。

次に、USER_PROCEDURESに対する問合せの例を示します。  

• Invoker権限の下に実行されるすべてのプロシージャとファンクションを検索します（プログラムのInvoker権限は、実行時に表などのデータベース・オブジェクトへの参照を解決するために使用されます）。 

  Copy

  
  SELECT   object_name
         , procedure_name 
      FROM user_procedures
     WHERE authid = 'CURRENT_USER'
  ORDER BY object_name, procedure_name 

• 決定論的なものとして宣言されているすべてのファンクションを表示します。  

  Copy

  
  SELECT   object_name
         , procedure_name 
      FROM user_procedures
     WHERE deterministic = 'YES'
  ORDER BY object_name, procedure_name 

トリガーの状態の分析と変更

データベース・トリガーを使用している場合、USER_TRIGGERSが便利です。USER_TRIGGERSでは、スキーマ内に定義されているトリガーごとに1行表示されます。おもな列は次のとおりです。 

• TRIGGER_NAME：トリガー名

• TRIGGER_TYPE：BEFOREとAFTERのどちらのトリガーであり、さらに行レベルと文レベルのどちらのトリガーであるかを示す文字列（たとえば、INSERT文の前に起動されるトリガーの場合、この列の値はBEFORE STATEMENTとなる）

• TRIGGERING_EVENT：トリガーを起動するSQL操作のタイプ（INSERT、INSERT OR UPDATE、DELETE OR UPDATE）

• TABLE_NAME：トリガーが定義されている表の名前

• STATUS:トリガーのステータス（ENABLEDまたはDISABLED）

• WHEN_CLAUSE：トリガー本体が不必要に実行されることを防ぐために使用できるオプションの句

• TRIGGER_BODY：トリガーの起動時に実行されるコード

次に、USER_TRIGGERSに対する問合せの例を示します。 

• 無効化されているすべてのトリガーを検索します。 

  Copy

  
  SELECT *
    FROM user_triggers 
   WHERE status = 'DISABLED' 

• EMPLOYEES表に定義されているすべての行レベル・トリガーを検索します。  

  Copy

  
  SELECT *
    FROM user_triggers 
   WHERE table_name = 'EMPLOYEES'
     AND trigger_type LIKE '%EACH ROW' 

• UPDATE操作の実行時に起動されるすべてのトリガーを検索します。  

  Copy

  
  SELECT *
    FROM user_triggers 
   WHERE triggering_event LIKE '%UPDATE%' 

USER_TRIGGERSビューには制約が1つあります。それは、TRIGGER_BODY列がLONG型であることです。そのため、この列をSQLの比較演算で使用することはできません。

たとえば、トリガー本体に"emp"という文字列が含まれるすべてのトリガーを検索するとします。残念ながら、次の問合せは失敗し、ORA-00932エラーが発生します。 

SELECT *
  FROM user_triggers
 WHERE trigger_body LIKE '%emp%'  

トリガー本体の内容を検索する場合は、PL/SQLを使用して次のようなブロックを記述する必要があります。

BEGIN FOR rec IN (SELECT * FROM user_triggers) LOOP IF rec.trigger_body LIKE '%emp%' THEN DBMS_OUTPUT.put_line ( 'Found in ' || rec.trigger_name); END IF; END LOOP; END;

なお、USER_TRIGGER_COLSビューで、トリガー本体の内部で参照されている列が追跡されます。

オブジェクト依存性の分析

USER_DEPENDENCIESビューには、現在のユーザーがアクセスできるプロシージャ、パッケージ、ファンクション、パッケージ本体、トリガー間の依存性が示されます。このビューを使用して、コードに対する影響分析を実行できます。たとえば、特定の表を変更した場合に変更する必要のあるプログラム数を把握できます。

このビューのおもな列は次のとおりです。 

• NAME：オブジェクト名

• TYPE:オブジェクトのタイプ

• REFERENCED_OWNER：参照オブジェクトの所有者

• REFERENCED_NAME：参照オブジェクトの名前

• REFERENCED_TYPE：参照オブジェクトのタイプ 

次に、USER_DEPENDENCIESに対する問合せの例を示します。 

• EMPLOYEES表に依存する（EMPLOYEES表を参照する）すべてのオブジェクトを検索します。 

  Copy

  
  SELECT type, name 
     FROM user_dependencies
    WHERE  referenced_name = 'EMPLOYEES'
  ORDER BY type, name 

• 現在のスキーマ内にある、ORDER_MGRパッケージが依存するすべてのオブジェクトを検索します。 

  Copy

  
  SELECT referenced_type
       , referenced_name 
      FROM user_dependencies
     WHERE name = 'ORDER_MGR'
       AND referenced_owner = USER
  ORDER BY referenced_type, 
           referenced_name 

ベスト・プラクティスとして私や他の人が強く推奨していることは、プロシージャやファンクションの内部にSQL文を"隠す"ことによるSQL文の繰り返しを避けることです。例を見て、このベスト・プラクティスに違反している箇所をUSER_DEPENDENCIESビューにより特定する方法を確認しましょう。

PL/SQLコードでは、ある主キーの値から1行を取得する問合せが数多く存在するのは非常に一般的です。Oracle Databaseの標準的なEMPLOYEES表を使用する問合せを含むPL/SQLの例を次に示します。 

PROCEDURE process_employee (
   employee_id_in IN INTEGER)
IS
   l_name   VARCHAR2 (100);
BEGIN
   SELECT last_name
     INTO l_name
     FROM employees
    WHERE employee_id = employee_id_in;
END; 

このような問合せを毎回記述するのではなく、この問合せを含み必要な値を返すファンクションを一度だけ記述することをお勧めします。そうすれば、必要に応じてこのファンクションをコールできます。EMPLOYEES_APIというパッケージを作成し、LAST_NAMEというファンクションをそのパッケージに格納しているとすると、上記のプロシージャは次のように変更できます。 

PROCEDURE process_employee (
   employee_id_in IN INTEGER)
IS
   l_name   VARCHAR2 (100);
BEGIN
   l_name := 
      employees_api.
        last_name (employee_id_in);
END;
  

ここで、何らかの理由（Oracle Database 11gのファンクション結果キャッシュ機能を使用するなど）で問合せを変更する必要があるとします。その場合に、アプリケーション・コード内でこの問合せが使用されるすべての箇所を検索する必要はなく、1か所で変更を行うことができます。

では、開発チームがこのベスト・プラクティスをコーディング標準に追加し、「SQL文を含むPL/SQLプログラム・ユニットとして、_APIという接尾辞で終わるパッケージのみを使用する」と定めたとします。

この場合、このルールに違反するすべてのプログラム・ユニットを識別するために、次のようなUSER_DEPENDENCIESに対する問合せを記述できます。 

SELECT name,
       TYPE,
       referenced_owner,
       referenced_name
  FROM user_dependencies
 WHERE     TYPE IN
              ('PACKAGE',
               'PACKAGE BODY',
               'PROCEDURE',
               'FUNCTION',
               'TRIGGER',
               'TYPE')
   AND referenced_type = 'TABLE'
   AND name NOT LIKE '%\_API' ESCAPE '\'
ORDER BY name
       , referenced_owner
       , referenced_name 

引数情報の分析

USER_ARGUMENTSは、PL/SQLプログラマーにとって非常に便利なビューです。このビューには、スキーマ内にある各ストアド・プログラムの引数（別名パラメータ）に関する情報が含まれます。同時に、優れた解析情報や複雑な構造に関する豊富な情報も表示されます。おもな列は次のとおりです。 

• OBJECT_NAME：プロシージャまたはファンクションの名前

• PACKAGE_NAME：プロシージャまたはファンクションが定義されているパッケージの名前

• ARGUMENT_NAME：引数名

• POSITION：パラメータ・リストにおける引数の位置（0の場合はファンクションのRETURN句であることを示す）

• IN_OUT：引数のモード（IN、OUT、またはIN OUT）

• DATA_TYPE：引数のデータ型

• DATA_LEVEL：コンポジット型の引数のネストの深さ（たとえば、引数のデータ型の1つがレコード型の場合、USER_ARGUMENTSには、この引数についてDATA_LEVELが0の行が表示され、さらにレコード内のフィールドごとにDATA_LEVELが1の行が表示される） 

次に、USER_ARGUMENTSに対する問合せの例を示します。 

• LONG型の引数があるすべてのプログラムを検索します。LONGは、過去のOracle Databaseのバージョンで、4,000文字を超える大きな文字列を格納するために使用されたデータ型です。現在は、キャラクタ・ラージ・オブジェクト（CLOB）などのラージ・オブジェクト型が使用されます。オラクルは、LONGの使用箇所をCLOBに変換することを推奨しています。USER_ARGUMENTSにより、パラメータ・リストでのそのようなLONGの使用箇所をすべて簡単に検索できます。  

  Copy

  
  SELECT object_name
       , package_name
       , argument_name
    FROM user_arguments
   WHERE data_type = ‘LONG’ 

OUTまたはIN OUTの引数があるすべてのファンクションを検索します。多くのプログラミング専門家が、ファンクションにはIN引数のみを指定すべきだと言います。OUTまたはIN OUTの引数があるファンクションはSQL文の中でコールできず、ファンクション索引で使用できません。複数の情報を返す必要がある場合は、プロシージャを使用するかレコードを返すようにしてください。リスト1に、このベスト・プラクティスに違反する、パッケージ内に定義されたすべてのファンクションを識別する問合せを示します。 

コード・リスト1：OUTまたはIN OUTの引数があるファンクションの検索 

次のステップ 

 ダウンロード Oracle Database 11g

 テスト  PL/SQLの知識

 その他の記事 PL/SQLの基礎、パート1～9  

 詳細情報 データ・ディクショナリについて

• Copy

  
  1  SELECT ua.object_name,
   2         ua.package_name,
   3         ua.argument_name,
   4         ua.in_out
   5    FROM (SELECT *
   6            FROM user_arguments
   7           WHERE position = 0) funcs,
   8         user_arguments ua
   9   WHERE     ua.in_out IN ('OUT', 'IN OUT')
  10         AND ua.position > 0
  11         AND ua.data_level = 0
  12         AND funcs.object_name = ua.object_name
  13         AND funcs.package_name = ua.package_name
  14         AND (   funcs.overload = ua.overload
  15              OR (funcs.overload IS NULL
  16                   AND ua.overload IS NULL)) 

  行	説明
  5–7	FROM句でインライン・ビューを使用して、USER_ARGUMENTS内のRETURN句の行をすべて識別します（すなわち、ファンクションを識別します）。
  9-11	RETURN句になく、レコード引数のフィールドなどの"ネストした"情報でもないOUTまたはIN OUTの引数を検索します。
  12-16	このようなやや長い結合条件を使用して、インライン・ビュー（"funcs"という省略名）とUSER_ARGUMENTSとを結合します。オブジェクト名とパッケージ名は一致する必要があります。また、オーバーロード値は同じか、両方がNULLである必要があります。パッケージに同じ名前のサブプログラムが2つ以上含まれる場合、オーバーロード列はNULLになりません。

宝の山はすぐそこに

この記事では、Oracle Databaseのデータ・ディクショナリ・ビューから掘り出せるアプリケーション情報のうち、ごく一部のみを取り上げました。Oracle SQL DeveloperなどのPL/SQLエディタには、これらの多くのビューに対応したユーザー・インタフェースが備わっており、ビューの内容を容易に参照できます。

より優れたUSER_SOURCE 

USER_SOURCEに対して、特定の文字列の存在の有無をチェックする問合せを実行することで、かなり簡単なコード品質保証チェックを実行できます。  

しかし、Oracle Database 11gのPL/Scopeを使用すれば、もっと強力なソース・コード解析が可能になります。

PL/Scopeは、PL/SQLコンパイラによって起動されるツールであり、PL/SQLプログラム・ユニット内のすべての識別子（変数、プロシージャ、ファンクション、タイプなど）に関する情報を収集して、USER_IDENTIFIERSビューでその情報を表示できるようにします。通常は、開発者がPL/SQLプログラム・ユニットを解析してその解析ツリーを分析する必要のあるような疑問でも、このツールを使用すれば比較的簡単に答えが得られます。

次に例を示します。宣言されているが、まったく使用されていない変数、定数、例外、LIKEについてプログラムから削除するようにマネージャーから指示されたとします。この場合、単純にコードを検索してすべての削除候補を見つけることは、エラーが起こりやすく時間もかかる作業です。

しかし、PL/Scopeを使用すればこの作業が簡単になります。USER_IDENTIFIERSには、識別子の宣言ごとに1行表示されます。また、その識別子の使用箇所（識別子への参照、または識別子の値を変更するコード行）に対応する行もあります。

そのため、これらの2種類の行セットに対してMINUS操作を実行すると、宣言されているが、参照や使用はされていないすべての識別子が残されます。例外の場合の問合せは次のとおりです。 

WITH subprograms_with_exception
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers has_exc
             WHERE     has_exc.owner = USER
                   AND has_exc.usage = 'DECLARATION'
                   AND has_exc.TYPE = 'EXCEPTION'),
     subprograms_with_raise_handle
        AS (SELECT DISTINCT owner
                          , object_name
                          , object_type
                          , name
              FROM all_identifiers with_rh
             WHERE     with_rh.owner = USER
                   AND with_rh.usage = 'REFERENCE'
                   AND with_rh.TYPE = 'EXCEPTION')
SELECT *
  FROM subprograms_with_exception
MINUS
SELECT *
  FROM subprograms_with_raise_handle 

PL/Scopeは、コードを分析してコードの改良方法を特定する能力に大きな影響を与えることが可能な強力で柔軟なユーティリティです。PL/Scopeについては、2010年に"Zoom In on Your Code"でより詳細な記事を記述しています。

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは以下の他、 PL/SQL Challenge にも掲載されます。PL/SQL Challengeは、PL/SQL言語やSQL、Oracle Application Expressに関するオンライン・クイズを提供するWebサイトです。 

この記事のクイズ：

スキーマ内のすべてのパッケージに、1つ以上のサブプログラム（プロシージャまたはファンクション）が含まれているとします。次の問合せのうち、スキーマ内にあるすべてのパッケージの名前を表示するものはどれですか。

a. 

  SELECT object_name
    FROM user_objects
   WHERE object_type = 'PACKAGE'
ORDER BY object_name
/ 

b.

SELECT package_name
    FROM user_procedures
   WHERE package_name IS NOT NULL
ORDER BY package_name
/ 

c.

SELECT DISTINCT object_name
    FROM user_procedures
   WHERE procedure_name IS NOT NULL
ORDER BY object_name
/  

d.

SELECT DISTINCT name
    FROM user_dependencies
   WHERE TYPE = 'PACKAGE'
/