使用PL/Scope分析PL/SQL代码

从11g开始Oracle引入了PL/Scope 用于编译器收集PL/SQL程序单元的所有标识符（变量名、常量名、程序名等）。 收集到的信息可通过一

使用PL/Scope分析你的PL/SQL代码

从11g开始Oracle引入了PL/Scope 用于编译器收集PL/SQL程序单元的所有标识符（变量名、常量名、程序名等）。
收集到的信息可通过一系列静态数据字典视图获取。
可帮助我们了解标识符的声明，定义，，引用，调用或赋值以及所在源代码的位置。

使用PL/Scope, 开发者可以执行复杂的代码分析。

1、启用 Enabling PL/Scope

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 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.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 INTEGER , param2 IN employees.last_name%TYPE ); END plscope_demo; / CREATE OR REPLACE PACKAGE BODY plscope_demo INTEGER , param2 IN employees.last_name%TYPE ) := 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) ) ) ) ) )