你可以执行一个层级查询，指定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
考虑下面情况：
delphi">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.objec