SQL代码

使用PL/Scope分析你的PL/SQL代码从11g开始Oracle引入了PL/Scope 用于编译器收集PL/SQL程序单元的所有标识符（变量名、常量名、程序名等）。
收集到的信息可通过一系列静态数据字典视图获取。
可帮助我们了解标识符的声明，定义，引用，调用或赋值以及所在源代码的位置。使用PL/Scope, 开发者可以执行复杂的代码分析。1、启用 Enabling PL/ScopeALTER 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 = USERAND 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_nameFROM all_identifiers ai WHERE ai.owner = USER ANDai.TYPE = "VARIABLE" ANDai.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_demoIS PROCEDURE my_procedure （param1_in IN INTEGER , param2 IN employees.last_name%TYPE）;END plscope_demo;/CREATE OR REPLACE PACKAGE BODY plscope_demoIS PROCEDURE my_procedure （param1_in IN INTEGER , param2 IN employees.last_name%TYPE） ISc_no_such CONSTANT NUMBER := 100;l_local_variable NUMBER; BEGINIF param1_in > l_local_variableTHEN 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_hierarchyAS （SELECT line , col , name , TYPE , usage , usage_id , usage_context_idFROM all_identifiers WHERE owner = USER AND object_name = "PLSCOPE_DEMO" AND object_type = "PACKAGE BODY"）SELECTLPAD （"-", 3 * （LEVEL - 1）） || TYPE || " " || name || " （" || usage || "）"identifier_hierarchyFROM plscope_hierarchySTART WITH usage_context_id = 0CONNECT BY PRIOR usage_id = usage_context_idORDER SIBLINGS BY line, colPACKAGE 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_procISplscope_demo_proc NUMBER;BEGINDECLAREplscope_demo_proc EXCEPTION;BEGINRAISE 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 , signatureFROM all_identifiers WHERE owner = USER AND object_name = "PLSCOPE_DEMO_PROC" AND name = "PLSCOPE_DEMO_PROC"ORDER BY lineLINETYPE USAGESIGNATURE1 PROCEDUREDEFINITION 51B3B5C5404AE8307DA49F42E0279915 1 PROCEDUREDECLARATION51B3B5C5404AE8307DA49F42E0279915 3 VARIABLE DECLARATION021B597943C0F31AD3938ACDAAF276F3 6 EXCEPTIONDECLARATION98E0183501FB350439CA44E3E511F60C 8 EXCEPTIONREFERENCE98E0183501FB350439CA44E3E511F60C 11VARIABLE ASSIGNMENT 021B597943C0F31AD3938ACDAAF276F3

还有一个小问题，同一个签名出现2次？
原因是同一标识符有多个USAGE，那么我们假如我只需查看所有变量的赋值和引用操作：

Code Listing 4: Querying all assignments and references to the PLSCOPE_DEMO_PROC variable SELECT usg.line , usg.TYPE , usg.usageFROM 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_demoIS PROCEDURE my_procedure （param1_in IN INTEGER, param2 IN DATE）; FUNCTION my_function （param1IN 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 parameterFROM 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 Practices1）声明在包说明中的变量 Variables declared in the specification of a package，
这种情况下任何对包有执行权限的用户都可直接读取该变量。2）已声明但未在程序中抛出的异常 Exception declared but not raised in a program unit.以上2类操作都是不合理的。检查第一种情况简单：

SELECT object_name, name, lineFROM all_identifiers ai WHERE ai.owner = USER AND ai.TYPE = "VARIABLE" AND ai.usage = "DECLARATION" AND ai.object_type = "PACKAGE";

第二种情况，先要观察一下异常在程序中的各种使用类型（USAGES）

PROCEDURE plscope_demo_procIS e_bad_data EXCEPTION; PRAGMA EXCEPTION_INIT （e_bad_data, -20900）;BEGIN RAISE e_bad_data;EXCEPTION WHEN e_bad_data THENlog_error （）;END plscope_demo_proc;

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

SELECT line , TYPE , usageFROM all_identifiers WHERE owner = USER AND object_name = "PLSCOPE_DEMO_PROC" AND name = "E_BAD_DATA"ORDER BY line/LINETYPE USAGE--------------------------------3 EXCEPTIONDECLARATION 4 EXCEPTIONASSIGNMENT6 EXCEPTIONREFERENCE 8 EXCEPTIONREFERENCE

可以推断出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_exceptionAS （SELECT DISTINCT owner, object_name, object_type, nameFROM all_identifiers has_exc WHERE has_exc.owner = USER AND has_exc.usage = "DECLARATION" AND has_exc.TYPE = "EXCEPTION"）, subprograms_with_raise_handleAS （SELECT DISTINCT owner, object_name, object_type, nameFROM all_identifiers with_rh WHERE with_rh.owner = USER AND with_rh.usage = "REFERENCE" AND with_rh.TYPE = "EXCEPTION"）SELECT *FROM subprograms_with_exceptionMINUSSELECT *FROM subprograms_with_raise_handle;

Oracle数据库之PL/SQL程序基础设计 http://www.linuxidc.com/Linux/2015-06/119013.htmPL/SQL Developer实用技巧分享 http://www.linuxidc.com/Linux/2014-09/107391.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址