Oracle之綁定變量 2

綁定變量(bind variable)我們經常在寫plsql中用到，那我們承接上一節的來看看在plsql中靜態sql和動態sql在綁定變量的區別

declare
    v_value_a varchar2(100);
    v_value_b varchar2(100);
    v_name  varchar2(50);
begin
    v_value_a := 'SMITH';
    select e.ename/*+test*/into v_name from scott.emp e where e.ename = v_value_a;
    v_value_b := 'ALLEN';
    select e.ename/*+test*/into v_name from scott.emp e where e.ename = v_value_b;
end;
/

查詢v$sql

select * from v$sql d where d.SQL_TEXT like '%/*+test*/%';

這里看到只產生了一條sql_id，oracle會自動把變量v_value_a,v_value_b換成綁定變量:B1.

再來看看動態sql

PLSQL_1:
declare
    v_value_a varchar2(100);
    v_value_b varchar2(100);
    v_name  varchar2(50);
begin
    v_value_a := 'SMITH';
    
    v_value_b := 'ALLEN';

    execute immediate 'select e.ename/*+sql*/ from scott.emp e where e.ename = :xxx' 
    using v_value_a;
    
    execute immediate 'select e.ename/*+sql*/ from scott.emp e where e.ename = :yyy' 
    using v_value_b;
end;
/

查詢v$sql

select * from v$sql d where d.SQL_TEXT like '%/*+sql*/%';

這里可以看到動態sql就是以執行sql為文本進行解析的，如果兩個sql有不同就是不同的sql語句。

2.獲取綁定變量的值

那我們可以得到綁定的變量的值嗎？通過v$sql_bind_capture就可以查詢到

SQL_1：
select d.NAME, d.POSITION, d.SQL_ID, value_string
  from v$sql_bind_capture d
 where d.SQL_ID in
       (select sql_id from v$sql v where v.SQL_TEXT like '%/*+sql*/%')

這時我們看到綁定變量的值是上文看到的v_value_a和v_value_b的值。

接著往下走，改變v_value_a的值：

v_value_a := 'CLARK';

執行PLSQL_1，SQL_1

綁定變量的值并沒有發生變化，這時為什么呢？

• v$sql_bind_capture這個視圖是一個快照視圖，并不會保存所有執行過的SQL的綁定變量，只會記錄最近一次捕獲到的綁定值，而已每次捕獲都是有間隔時間的。ORACLE為我們提供了一個隱含參數_cursor_bind_capture_interval(默認900s)，這個就是控制綁定變量抓取頻率的參數。

• 如果綁定變量是DATE類型則無法直接查看到的。