oracle定义变量sql赋值_ORACLE获取SQL绑定变量值的方法总结

本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法，在SQL优化调优过程中，经常会用到这方面的知识点。在此梳理、总结一下这方面的知识点，方面日后查找、翻阅。

方法1：查询V$SQL

V$SQL视图中的BIND_DATA字段用来存储绑定变量的值，但是从这个视图查询绑定变量的值，有很大的局限性：

1. 它的记录频率受_cursor_bind_capture_interval隐含参数控制，默认值为900，表示每900秒记录一次绑定值，也就是说在900内，绑定变量值的改变不会反应在这个视图中。除非你调整隐含参数_cursor_bind_capture_interval

2. 它记录的仅仅最后一次捕获的绑定变量值。

3. BIND_DATA数据类型为RAW，需要进行转换。

可以使用下面两种方式来查看绑定变量的值。

COL SQL_ID FOR A14;
COL SQL_TEXT FOR A32;
COL HASH_VALUE FOR 99999999999;
COL BIND_DATA FOR A32;
SELECT SQL_ID          
      ,SQL_TEXT
      ,LITERAL_HASH_VALUE
      ,HASH_VALUE
      ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';

COL SQL_ID FOR A14;
COL SQL_TEXT FOR A32;
COL HASH_VALUE FOR 99999999999;
COL BIND_DATA FOR A32;
SELECT SQL_ID          
      ,SQL_TEXT
      ,LITERAL_HASH_VALUE
      ,HASH_VALUE
      ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA
FROM V$SQL
WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';

如下实验所示，我们在一个会话中使用绑定变量的查询SQL语句，然后，我们来尝试获取绑定变量的值，如下所示：

SQL> SHOW USER;
USER is "TEST"
SQL> DESC TEST;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NAME                                               VARCHAR2(32)

SQL> 
SQL> VARIABLE NAME NVARCHAR2(32);
SQL> EXEC :NAME :='KKKK';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TEST WHERE NAME=:NAME;

no rows selected

SQL> 

SQL>SHOW USER;
USER is "SYS"
SQL> COL SQL_ID FOR A14;
SQL> COL SQL_TEXT FOR A32;
SQL> COL HASH_VALUE FOR 99999999999;
SQL> COL BIND_DATA FOR A32;
SQL> SELECT SQL_ID          
  2        ,SQL_TEXT
  3        ,LITERAL_HASH_VALUE
  4        ,HASH_VALUE
  5        ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA
  6  FROM V$SQL
  7  WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';

SQL_ID         SQL_TEXT                         LITERAL_HASH_VALUE   HASH_VALUE BIND_DATA(NAME, POSITION, DUP_PO
-------------- -------------------------------- ------------------ ------------ --------------------------------
0r7m5jyz9ng09  SELECT * FROM TEST WHERE NAME=:N                  0   3197778953 SQL_BIND_SET(SQL