使用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 line3、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.
这些使用方法便于更加easy获取关于程序单元的具体信息。
如果我想看看程序单元中的变量的声明部分：
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_id4、理解标识符的层级关系 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 line6、验证命名是否规范 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
;