使用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
  ;