使用PL/Scope分析PL/SQL程式碼

使用PL/Scope分析你的PL/SQL程式碼

從11g開始Oracle引入了PL/Scope 用於編譯器收集PL/SQL程式單元的所有識別符號（變數名、常量名、程式名等）。
收集到的資訊可通過一系列靜態資料字典檢視獲取。
可幫助我們瞭解識別符號的宣告，定義，引用，呼叫或賦值以及所在原始碼的位置。

使用PL/Scope, 開發者可以執行復雜的程式碼分析。

1、啟用 Enabling PL/Scope

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
/

plscope_settings 引數有2個可選值：

IDENTIFIERS:ALL or IDENTIFIERS:NONE（預設不收集）

2、關於檢視 ALL_IDENTIFIERS View
當1中引數設定為IDENTIFIERS:ALL，同時在同一會話中編譯程式單元后，該單元所有識別符號資訊被收集到檢視ALL_IDENTIFIERS中。
以下是該檢視欄位簡介：
【OWNER】 The owner of the program unit containing the identifier
【NAME】 The name of the identifier
【TYPE】 The type of the identifier, such as FORALL OUT (an out argument), CONSTANT, PACKAGE, or RECORD
【SIGNATURE】 簽名，用於區分同名識別符號的唯一字串；
A unique string for each distinct identifier, across all program units, useful for distinguishing between different identifiers that happen to have the same name
【OBJECT_NAME】 The name of the program unit containing the identifier OBJECT_TYPE The type of the program unit containing the identifier, such as PACKAGE, TRIGGER, or PROCEDURE
【USAGE】 針對識別符號的操作型別 The type of usage of the identifier (such as a declaration or an assignment)
【USAGE_ID】 A sequentially generated integer value for an identifier, unique within its program unit
【USAGE_CONTEXT_ID】A foreign key reflexive back to USAGE_ID; in essence, the parent of this identifier appearance (for example, the context of a variable’s declaration is the name of the subprogram in which the variable is declared)
【LINE】 識別符號出現的行 The number of the line on which the identifier appears
【COL】 識別符號出現的列 The column position in the line at which the identifier appears

你可以獲取給定程式單元的所有識別符號資訊：

SELECT *
  FROM all_identifiers ai
 WHERE ai.owner = USER 
   AND ai.object_type = '<program_type>' 
   AND ai.object_name = '<program_name>'
ORDER BY line

3、PL/Scope追蹤的識別符號用法 Usages Tracked by PL/Scope
ASSIGNMENT： 賦值操作。包括:=，FETCH.. INTO以及OUT 、IN OUT模式引數。

CALL：呼叫操作。

DECLARATION： 宣告。Indicates that the identifier is declared.

REFERENCE： 引用。Indicates that an identifier is used in the program without a change in its value. Examples include raising an exception, passing the identifier to an IN or IN OUT mode parameter of a subprogram or USING clause of EXECUTE IMMEDIATE, and using the identifier in a %TYPE declaration.

DEFINITION：定義。Tells the compiler how to implement or use a previously declared identifier. The following identifier types will have a DEFINITION row in ALL_IDENTIFIERS: FUNCTION, OBJECT, PACKAGE, PROCEDURE, TRIGGER, and EXCEPTION.

這些用法便於更加容易獲取關於程式單元的詳細資訊。
如果我想看看程式單元中的變數的宣告部分：

SELECT ai.object_name
     , ai.object_type
     , ai.name variable_name
     , ai.name context_name
  FROM all_identifiers ai
 WHERE ai.owner = USER AND 
       ai.TYPE = 'VARIABLE' AND 
       ai.usage = 'DECLARATION'
ORDER BY ai.object_name, 
ai.object_type, ai.usage_id

4、理解識別符號的層級關係 Using Usage IDs to Understand Identifier Hierarchy

一個包可以包含一個或多個子程式；一個子程式可以有一個或多個引數。你可以使用PL/Scope探索這種層級關係。
例如：

Code Listing 1: Defining the plscope_demo package 

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          );
END plscope_demo;
/
CREATE OR REPLACE PACKAGE BODY plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER
                         , param2 IN employees.last_name%TYPE
                          )
   IS
      c_no_such   CONSTANT NUMBER := 100;
      l_local_variable     NUMBER;
   BEGIN
      IF param1_in > l_local_variable
      THEN
         DBMS_OUTPUT.put_line (param2);
      ELSE
         DBMS_OUTPUT.put_line (c_no_such);
      END IF;
   END my_procedure;
END plscope_demo;
/

You can then execute a hierarchical query, specifying the usage_context_id column as the parent of a row in the ALL_IDENTIFIERS view, to see the hierarchy of identifiers shown in Listing 2.

你可以執行一個層級查詢，指定usage_context_id作為父級行：

Code Listing 2: Querying against ALL_IDENTIFIERS view to see the hierarchy of identifiers 

WITH plscope_hierarchy
        AS (SELECT line
                 , col
                 , name
                 , TYPE
                 , usage
                 , usage_id
                 , usage_context_id
              FROM all_identifiers
             WHERE     owner = USER
                   AND object_name = 'PLSCOPE_DEMO'
                   AND object_type = 'PACKAGE BODY')
SELECT    LPAD ('-', 3 * (LEVEL - 1))
       || TYPE
       || ' '
       || name
       || ' ('
       || usage
       || ')'
          identifier_hierarchy
  FROM plscope_hierarchy
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
ORDER SIBLINGS BY line, col

PACKAGE PLSCOPE_DEMO (DEFINITION)
   PROCEDURE MY_PROCEDURE (DEFINITION)
      FORMAL IN PARAM1_IN (DECLARATION)
         SUBTYPE INTEGER (REFERENCE)
      FORMAL IN PARAM2 (DECLARATION)
      CONSTANT C_NO_SUCH (DECLARATION)
         CONSTANT C_NO_SUCH (ASSIGNMENT)
         NUMBER DATATYPE NUMBER (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (DECLARATION)
         NUMBER DATATYPE NUMBER (REFERENCE)
      FORMAL IN PARAM1_IN (REFERENCE)
      VARIABLE L_LOCAL_VARIABLE (REFERENCE)

5、使用簽名區分識別符號 Using a Signature to Differentiate Between Identifiers
考慮下面情況：

PROCEDURE plscope_demo_proc
IS
  plscope_demo_proc   NUMBER;
BEGIN
  DECLARE
    plscope_demo_proc   EXCEPTION;
  BEGIN
    RAISE plscope_demo_proc;
  END;

  plscope_demo_proc := 1;
END plscope_demo_proc;

同一識別符號plscope_demo_proc出現多次代表了不同的物件。
麻煩之處在於它仍然是合法的程式碼。跟誰說理去！！！

按照以往使用ALL_SOURCE很難區分開來。而使用PL/Scope則顯得輕鬆許多：

Code Listing 3: Distinguishing between identifiers with the same name 

SELECT line
     , TYPE
     , usage
     , signature
  FROM all_identifiers
 WHERE     owner = USER
       AND object_name = 'PLSCOPE_DEMO_PROC'
       AND name = 'PLSCOPE_DEMO_PROC'
ORDER BY line

LINE  TYPE       USAGE        SIGNATURE                        
1     PROCEDURE  DEFINITION   51B3B5C5404AE8307DA49F42E0279915 
1     PROCEDURE  DECLARATION  51B3B5C5404AE8307DA49F42E0279915 
3     VARIABLE   DECLARATION  021B597943C0F31AD3938ACDAAF276F3 
6     EXCEPTION  DECLARATION  98E0183501FB350439CA44E3E511F60C 
8     EXCEPTION  REFERENCE    98E0183501FB350439CA44E3E511F60C 
11    VARIABLE   ASSIGNMENT   021B597943C0F31AD3938ACDAAF276F3

還有一個小問題，同一個簽名出現2次？
原因是同一識別符號有多個USAGE， 那麼我們假如我只需檢視所有變數的賦值和引用操作：

Code Listing 4: Querying all assignments and references to the PLSCOPE_DEMO_PROC variable 

SELECT usg.line
     , usg.TYPE
     , usg.usage
  FROM all_identifiers dcl, 
      all_identifiers usg
 WHERE     
    dcl.owner = USER
 AND dcl.object_name = 'PLSCOPE_DEMO_PROC'
 AND dcl.name = 'PLSCOPE_DEMO_PROC'
 and dcl.usage = 'DECLARATION'
 and dcl.type = 'VARIABLE'
 and usg.signature = dcl.signature
 and usg.usage <> 'DECLARATION'
ORDER BY line

6、驗證命名是否規範 Validate Naming Conventions
假設我有以下要求：
IN parameters: end with _in
OUT parameters: end with _out
IN OUT parameters: end with _io

為了驗證一個程式單元符合這個規則，我將針對FORMAL IN, FORMAL OUT, or FORMAL IN OUT檢索其宣告情況。
假設我宣告瞭以下測試包：

Code Listing 5: Creating the package specification for plscope_demo 

CREATE OR REPLACE PACKAGE plscope_demo
IS
   PROCEDURE my_procedure (param1_in IN INTEGER, param2 IN DATE);

   FUNCTION my_function (param1    IN INTEGER
                       , in_param2 IN DATE
                       , param3_in IN employees.last_name%TYPE
                        )
      RETURN VARCHAR2;
END plscope_demo;

Code Listing 6: Querying to find naming violations 

SELECT prog.name subprogram, parm.name parameter
  FROM all_identifiers parm, all_identifiers prog
 WHERE     parm.owner = USER
       AND parm.object_name = 'PLSCOPE_DEMO'
       AND parm.object_type = 'PACKAGE'
       AND prog.owner = parm.owner
       AND prog.object_name = parm.object_name
       AND prog.object_type = parm.object_type
       AND parm.usage_context_id = prog.usage_id
       AND parm.TYPE IN ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
       AND parm.usage = 'DECLARATION'
       AND ( (parm.TYPE = 'FORMAL IN'
              AND LOWER (parm.name) NOT LIKE '%\_in' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL OUT'
                AND LOWER (parm.name) NOT LIKE '%\_out' ESCAPE '\')
            OR (parm.TYPE = 'FORMAL IN OUT'
                AND LOWER (parm.name) NOT LIKE '%\_io' ESCAPE '\'))
ORDER BY prog.name, parm.name

‘7、識別違反最佳做法的操作 Identify Violations of Best Practices

1）宣告在包說明中的變數 Variables declared in the specification of a package，
這種情況下任何對包有執行許可權的使用者都可直接讀取該變數。

2）已宣告但未在程式中丟擲的異常 Exception declared but not raised in a program unit.

以上2類操作都是不合理的。

檢查第一種情況簡單：

SELECT object_name, name, line
  FROM all_identifiers ai
 WHERE ai.owner = USER
     AND ai.TYPE = 'VARIABLE'
     AND ai.usage = 'DECLARATION'
     AND ai.object_type = 'PACKAGE';

第二種情況，先要觀察一下異常在程式中的各種使用型別（USAGES）

PROCEDURE plscope_demo_proc
IS
   e_bad_data   EXCEPTION;
   PRAGMA EXCEPTION_INIT (
                e_bad_data, -20900);
BEGIN
   RAISE e_bad_data;
EXCEPTION
   WHEN e_bad_data
   THEN
      log_error ();
END plscope_demo_proc;

Let’s see what PL/Scope has to say about the e_bad_data identifier:

SELECT line
     , TYPE
     , usage
  FROM all_identifiers
 WHERE owner = USER
   AND object_name = 
              'PLSCOPE_DEMO_PROC'
   AND name = 'E_BAD_DATA'
ORDER BY line
/

LINE  TYPE       USAGE
-----  ------------  ---------------
3     EXCEPTION  DECLARATION 
4     EXCEPTION  ASSIGNMENT  
6     EXCEPTION  REFERENCE   
8     EXCEPTION  REFERENCE 

可以推斷出EXCEPTION_INIT被當做賦值操作；RAISE statement and the WHEN clause被認為是引用操作。
如此一來，我們宣告一下語句即可：

Code Listing 7: Querying all subprograms in which an exception is declared but not referenced 

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
  ;