[20211027]引數plscope_settings分析PLSQL.txt

[20211027]引數plscope_settings分析PLSQL.txt

--//昨天看了一下連結www.cnblogs.com/jhcelue/p/7352952.html，學習引數plscope_settings設定，做一個記錄。

1.環境：
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試：
SCOTT@book> ALTER SESSION SET plscope_settings = 'IDENTIFIERS:ALL';
Session altered.

CREATE OR REPLACE FUNCTION SCOTT.get_dept (p_deptno dept.deptno%TYPE)
   RETURN dept.dname%TYPE
-- DETERMINISTIC
IS
   l_dname   dept.dname%TYPE;
BEGIN
   DBMS_LOCK.sleep (1);

   SELECT dname
     INTO l_dname
     FROM dept
    WHERE deptno = p_deptno;

   RETURN l_dname;
END;
/

--//重新執行以上程式碼建立該函式。

SELECT *
    FROM dba_identifiers
   WHERE owner = USER AND object_type = 'FUNCTION' AND object_name = 'GET_DEPT'
ORDER BY line;

OWNER  NAME     SIGNATURE                        TYPE      OBJECT_NAME OBJECT_TYPE   USAGE         USAGE_ID       LINE        COL USAGE_CONTEXT_ID
------ -------- -------------------------------- --------- ----------- ------------- ----------- ---------- ---------- ---------- ----------------
SCOTT  GET_DEPT 6FA431360CBBFD1B612C650909B9B059 FUNCTION  GET_DEPT    FUNCTION      DEFINITION           2          1         16                1
SCOTT  P_DEPTNO B866C97E66CCF1399F9EE5833824CE79 FORMAL IN GET_DEPT    FUNCTION      DECLARATION          3          1         26                2
SCOTT  GET_DEPT 6FA431360CBBFD1B612C650909B9B059 FUNCTION  GET_DEPT    FUNCTION      DECLARATION          1          1         16                0
SCOTT  L_DNAME  AEE3B3678181677B379A31F8D657260A VARIABLE  GET_DEPT    FUNCTION      DECLARATION          4          5          4                2
SCOTT  L_DNAME  AEE3B3678181677B379A31F8D657260A VARIABLE  GET_DEPT    FUNCTION      ASSIGNMENT           7         10         11                2
SCOTT  P_DEPTNO B866C97E66CCF1399F9EE5833824CE79 FORMAL IN GET_DEPT    FUNCTION      REFERENCE            6         12         20                2
SCOTT  L_DNAME  AEE3B3678181677B379A31F8D657260A VARIABLE  GET_DEPT    FUNCTION      REFERENCE            8         14         11                2
7 rows selected.

--//顯示變數的位置與偏移，也許對於開發調式PLSQL程式碼有用，我是不經常做這類工作。
--//我查詢許多連結。我發現18c還支援STATEMENTS:ALL,通過查詢XXX_STATEMENTS檢視獲取sql語句。測試看看。

3.環境：
TTT@192.168.a.b:1521/orcl> @ prxx
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 18.0.0.0.0
BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

TTT@192.168.a.b:1521/orcl> ALTER SESSION SET plscope_settings = 'IDENTIFIERS:ALL,STATEMENTS:ALL';
Session altered.

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
   RETURN dept.dname%TYPE
-- DETERMINISTIC
IS
   l_dname   dept.dname%TYPE;
BEGIN
   --//DBMS_LOCK.sleep (1);

   SELECT dname
     INTO l_dname
     FROM dept
    WHERE deptno = p_deptno;

   RETURN l_dname;
END;
/

TTT@192.168.a.b:1521/orcl> SELECT * FROM ALL_STATEMENTS
  2  @ prxx
==============================
OWNER                         : TTT
SIGNATURE                     : 0991931515D02FCA85DA302CF7843DE6
TYPE                          : SELECT
OBJECT_NAME                   : GET_DEPT
OBJECT_TYPE                   : FUNCTION
USAGE_ID                      : 11
LINE                          : 9
COL                           : 4
USAGE_CONTEXT_ID              : 2
SQL_ID                        : 56r5sd49t3jrv
HAS_HINT                      : NO
HAS_INTO_BULK                 : NO
HAS_INTO_RETURNING            : NO
HAS_INTO_RECORD               : NO
HAS_CURRENT_OF                : NO
HAS_FOR_UPDATE                : NO
HAS_IN_BINDS                  : YES
TEXT                          : SELECT DNAME FROM DEPT WHERE DEPTNO = :B1
FULL_TEXT                     : SELECT DNAME FROM DEPT WHERE DEPTNO = :B1
ORIGIN_CON_ID                 : 3
PL/SQL procedure successfully completed.

--//這些功能也許對於開發有用。