plscope_settings.TXT

于 2013-08-13 14:57:53 发布
阅读量282 收藏
点赞数
文章标签： 数据库

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL> select name,type,plscope_settings from user_plsql_object_settings;

NAME                           TYPE         PLSCOPE_SETTINGS
------------------------------ ------------ --------------------------------------------------------------------------------
AWR_RPT                        PROCEDURE    IDENTIFIERS:NONE
COLORLIST                      TYPE         IDENTIFIERS:NONE
COLOR_HEX_CODE                 TYPE         IDENTIFIERS:NONE
COLOR_TYPE                     TYPE         IDENTIFIERS:NONE
COURSELIST                     TYPE         IDENTIFIERS:NONE
DEAD_CODE                      PROCEDURE    IDENTIFIERS:NONE
INSERT_EMP                     PROCEDURE    IDENTIFIERS:NONE
MANAGER                        TYPE         IDENTIFIERS:NONE
PLCH_NUMBER_NT                 TYPE         IDENTIFIERS:NONE
PLCH_PKG                       PACKAGE      IDENTIFIERS:NONE
PLCH_PKG                       PACKAGE BODY IDENTIFIERS:NONE
PROC                           PROCEDURE    IDENTIFIERS:NONE
PROJECTLIST                    TYPE         IDENTIFIERS:NONE
P_A                            PROCEDURE    IDENTIFIERS:NONE
P_DEAL                         PROCEDURE    IDENTIFIERS:NONE
P_EXP_USER_OBJECTS             PROCEDURE    IDENTIFIERS:NONE
P_SCOTT_UNDO                   PROCEDURE    IDENTIFIERS:NONE
P_TEST                         PROCEDURE    IDENTIFIERS:NONE
P_TIMESTAT                     PROCEDURE    IDENTIFIERS:NONE
RESOLVE_STR                    TYPE         IDENTIFIERS:NONE

NAME                           TYPE         PLSCOPE_SETTINGS
------------------------------ ------------ --------------------------------------------------------------------------------
SHOW_SPACE                     PROCEDURE    IDENTIFIERS:NONE
SYS_PLSQL_75043_18_1           TYPE         IDENTIFIERS:NONE
SYS_PLSQL_75043_DUMMY_1        TYPE         IDENTIFIERS:NONE
XPLAN_NTT                      TYPE         IDENTIFIERS:NONE
XPLAN_OT                       TYPE         IDENTIFIERS:NONE

25 rows selected

11g中默认情况下程序的plscope_settings设置为IDENTIFIERS：NONE。
要追踪标识符的详细信息，需要在编译之前对会话或者程序进行plscope_settings的设置。


SQL> alter session set plscope_settings='IDENTIFIERS:ALL';

Session altered


SQL> desc user_identifiers
Name             Type         Nullable Default Comments                                              
---------------- ------------ -------- ------- ------------------------------------------------------
NAME             VARCHAR2(30) Y                Name of the identifier                                
SIGNATURE        VARCHAR2(32) Y                Signature of the identifier                           
TYPE             VARCHAR2(18) Y                Type of the identifier                                
OBJECT_NAME      VARCHAR2(30)                  Name of the object where the identifier usage occurred
OBJECT_TYPE      VARCHAR2(13) Y                Type of the object where the identifier usage occurred
USAGE            VARCHAR2(11) Y                Type of the identifier usage                          
USAGE_ID         NUMBER       Y                Unique key for an identifier usage within the object  
LINE             NUMBER       Y                Line number of the identifier usage                   
COL              NUMBER       Y                Column number of the identifier usage                 
USAGE_CONTEXT_ID NUMBER       Y                Context USAGE_ID of an identifier usage


SQL>
SQL> create or replace package pck_emp is
  2 
  3    -- Author  : TIAN
  4    -- Created : 2013-7-10 10:47:11
  5    -- Purpose :
  6 
  7    -- Public type declarations
  8    procedure emp(e_name in out varchar2) ;
  9 
 10  end ;
 11  /

Package created

SQL>
SQL> create or replace package body pck_emp is
  2 
  3    procedure emp(e_name in out varchar2)
  4    is
  5      e_name2 varchar2(10);
  6    begin
  7      select ename into e_name2 from emp
  8      where sal  9      and rownum=1;
 10      e_name:=e_name2;
 11    end;
 12 
 13  end ;
 14  /

Package body created


SQL> select * from sys.user_identifiers t;

NAME       SIGNATURE                        TYPE               OBJECT_NAME  OBJECT_TYPE   USAGE         USAGE_ID       LINE        COL USAGE_CONTEXT_ID
---------- -------------------------------- ------------------ ------------ ------------- ----------- ---------- ---------- ---------- ----------------
VARCHAR2   FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP      PACKAGE       REFERENCE            4          8         31                3
E_NAME     EEC680F737C81AC6FCE099599CFE8EA5 FORMAL IN OUT      PCK_EMP      PACKAGE       DECLARATION          3          8         17                2
EMP        639B48ACE35ABA0B34166F9D0F380AE9 PROCEDURE          PCK_EMP      PACKAGE       DECLARATION          2          8         13                1
PCK_EMP    0769B61A5B5AE1A77D87F29181DFB354 PACKAGE            PCK_EMP      PACKAGE       DECLARATION          1          1          9                0
E_NAME2    28300FF547247DEE8135AF1551055F60 VARIABLE           PCK_EMP      PACKAGE BODY  REFERENCE           11         10         13               10
E_NAME     DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT      PCK_EMP      PACKAGE BODY  ASSIGNMENT          10         10          5                2
E_NAME2    28300FF547247DEE8135AF1551055F60 VARIABLE           PCK_EMP      PACKAGE BODY  ASSIGNMENT           9          7         23                2
ROWNUM     65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION           PCK_EMP      PACKAGE BODY  CALL                 8          9          9                2
E_NAME     DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT      PCK_EMP      PACKAGE BODY  REFERENCE            7          8         48                2
VARCHAR2   FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP      PACKAGE BODY  REFERENCE            6          5         13                5
E_NAME2    28300FF547247DEE8135AF1551055F60 VARIABLE           PCK_EMP      PACKAGE BODY  DECLARATION          5          5          5                2
VARCHAR2   FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP      PACKAGE BODY  REFERENCE            4          3         31                3
E_NAME     DF1D12D919467FDA379ACAB7729B3638 FORMAL IN OUT      PCK_EMP      PACKAGE BODY  DECLARATION          3          3         17                2
EMP        639B48ACE35ABA0B34166F9D0F380AE9 PROCEDURE          PCK_EMP      PACKAGE BODY  DEFINITION           2          3         13                1
PCK_EMP    0769B61A5B5AE1A77D87F29181DFB354 PACKAGE            PCK_EMP      PACKAGE BODY  DEFINITION           1          1         14                0

15 rows selected

所有标示符的详细信息都被记录下来了。该视图中的内容不会重复，多次编译一个程序，产生的结果集是最后编译生成的结果。
如果将上面的pck_emp包删除，该视图中的内容也一并被删掉了。

通过该视图，我们可以进行命名规范的审核。
比如，规范中约定“所有输入输出参数的命名格式为IO_”，
我们就可以找到此视图中TYPE=‘FORMAL IN OUT’的列，检查NAME是否是“IO_”开头，将不符合该条件的列显示出来。

作为数据库管理员，可以将这些信息详细地反馈给开发人员，方便进行修改。

 

该视图将包级别、存储过程及函数（子程序）级别、局部变量和参数级别的内容显示在一起，
可以通过usage_id和usage_context_id作为连接条件，将结果关联起来。

如：
SQL> select distinct level,type,name from user_identifiers
  2  where object_type='PACKAGE'
  3  start with usage_context_id=0
  4  connect by usage_context_id=prior usage_id
  5  order by level;

     LEVEL TYPE               NAME
---------- ------------------ ------------------------------
         1 PACKAGE            PCK_EMP
         2 PROCEDURE          EMP
         3 FORMAL IN OUT      E_NAME
         4 CHARACTER DATATYPE VARCHAR2
        
        
在另外一个会话中编译：
create or replace package pck_emp is

  -- Author  : TIAN
  -- Created : 2013-7-10 10:47:11
  -- Purpose :
 
  -- Public type declarations
  procedure emp(e_name in out varchar2) ;
  procedure emp2(e_name in out varchar2) ;

end ;


再回到当前会话，编译：
SQL> create or replace package body pck_emp is
  2 
  3    procedure emp(e_name in out varchar2)
  4    is
  5      e_name2 varchar2(10);
  6    begin
  7      select ename into e_name2 from emp
  8      where sal  9      and rownum=1;
 10      e_name:=e_name2;
 11    end;
 12 
 13     procedure emp2(e_name in out varchar2)
 14    is
 15      e_name2 varchar2(10);
 16    begin
 17      select ename into e_name2 from emp
 18      where sal 19      and rownum=1;
 20      e_name:=e_name2;
 21    end;
 22  end ;
 23  /

Package body created

再次查看user_identifiers，发现包声明中的相关信息消失了。
SQL> select * from sys.user_identifiers t;

NAME                           SIGNATURE                        TYPE               OBJECT_NAME                    OBJECT_TYPE   USAGE         USAGE_ID       LINE        COL USAGE_CONTEXT_ID
------------------------------ -------------------------------- ------------------ ------------------------------ ------------- ----------- ---------- ---------- ---------- ----------------
E_NAME2                        19F69C47278618C3FBA2E509FF6ED130 VARIABLE           PCK_EMP                        PACKAGE BODY  REFERENCE           18         20         13               17
E_NAME                         3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  ASSIGNMENT          17         20          5                0
E_NAME2                        19F69C47278618C3FBA2E509FF6ED130 VARIABLE           PCK_EMP                        PACKAGE BODY  ASSIGNMENT          16         17         23                0
ROWNUM                         65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION           PCK_EMP                        PACKAGE BODY  CALL                15         19          9                0
E_NAME                         3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  REFERENCE           14         18         48                0
VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP                        PACKAGE BODY  REFERENCE           13         15         13               12
E_NAME2                        19F69C47278618C3FBA2E509FF6ED130 VARIABLE           PCK_EMP                        PACKAGE BODY  DECLARATION         12         15          5                0
VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP                        PACKAGE BODY  REFERENCE           11         13         33               10
E_NAME                         3682556E9679BA98D6481B681B3769E9 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  DECLARATION         10         13         19                0
E_NAME2                        AA38A15360CD117AAE623EEAFBCE5871 VARIABLE           PCK_EMP                        PACKAGE BODY  REFERENCE            9         10         13                8
E_NAME                         BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  ASSIGNMENT           8         10          5                0
E_NAME2                        AA38A15360CD117AAE623EEAFBCE5871 VARIABLE           PCK_EMP                        PACKAGE BODY  ASSIGNMENT           7          7         23                0
ROWNUM                         65C4C98CB2DD1DA9D438AAEFCCC8BD58 FUNCTION           PCK_EMP                        PACKAGE BODY  CALL                 6          9          9                0
E_NAME                         BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  REFERENCE            5          8         48                0
VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP                        PACKAGE BODY  REFERENCE            4          5         13                3
E_NAME2                        AA38A15360CD117AAE623EEAFBCE5871 VARIABLE           PCK_EMP                        PACKAGE BODY  DECLARATION          3          5          5                0
VARCHAR2                       FEE1E7EB248ADD1FACE928CB4F8C0D50 CHARACTER DATATYPE PCK_EMP                        PACKAGE BODY  REFERENCE            2          3         31                1
E_NAME                         BF9E5F074D05E5AFD55304DFA0DF2F01 FORMAL IN OUT      PCK_EMP                        PACKAGE BODY  DECLARATION          1          3         17                0

18 rows selected

SQL>

实验结束后，将会话关闭或将plscope_settings设置修改回默认：
SQL> alter session set plscope_settings='IDENTIFIERS:NONE';

Session altered


要设置所有源代码编译时都记录标识符详细信息，可以在系统级别设置：
开启：
SQL> alter system set plscope_settings='IDENTIFIERS:ALL';

System altered

关闭：
SQL> alter system set plscope_settings='IDENTIFIERS:NONE';

System altered

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-768391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-768391/

cuili9760
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值