使用PL/Scope分析PL/SQL代码(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