怎么使用ORACLE中的綁定變量

這篇文章主要講解了“怎么使用ORACLE中的綁定變量”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“怎么使用ORACLE中的綁定變量”吧！

一、綁定變量的作用

:variable_name（字母，數字，或者字母數字組合）

有效降低硬解析

二、綁定變量的典型用法

sql中

var x number;

exec :x :=7876;

select * from emp where empno = :x;

pl/sql中，例如

declare

  vc_name varchar2(10);

 begin

    execute immediate 'select ename from emp where empno= :1' into vc_name using 7876;

    dbms_output.put_line(vc_name);

 end;

所以在pl/sql中綁定變量的標準語法為：

execute immediate 【使用綁定變量的語句】  using 對應綁定變量的具體輸入值;

PL/SQL中DML語句的綁定變量典型用法

declare

 v_sql1  varchar2(50);

 v_sql2  varchar2(50);

 v_num1  number;

 v_num2  number;

begin

  v_sql1 := 'insert into emp(empno,ename,sal) values(:1,:1,:1)';

  execute immediate v_sql1 using 8001,'jack',1000;

  v_num1 :=sql%rowcount;  --sql%rowcount中的sql是oracle的內部游標,rowcount的意思是之前的dml sql語句影響的多少行數據

  execute immediate v_sql1 using 8002,'mike',2000;

  v_num2 :=sql%rowcount;

  dbms_output.put_line(v_num1+v_num2);

  end;

動態sql中也可以使用綁定變量，returning 這個關鍵字可以和帶綁定變量的SQL聯用，其作用是將受該SQL影響的行記錄字段取出來。

三、pl/sql中批量綁定的典型用法

批量綁定：一次性處理一批數據

pl/sql引擎是指在Oracle在數據庫中處理pl/sql代碼中除了SQL語句外所有剩余部分（如變量，循環，數組，賦值）的子系統

批量綁定可以有效減少SQL引擎和pl/sql引擎的交互次數

理論上來說，pl/sql中只要執行SQL語句，就會有這兩個引擎的交互，實際上這兩個引擎交互產生的性能影響主要體現在以下兩個方面

1、當顯式游標或者參考游標需要循環執行fetch操作時，這時循環由pl/sql執行，fetch中的SQL由SQL引擎執行，這樣每fetch一條記錄，就需要兩個引擎交互一次

2、當顯式游標或者參考游標的循環內部需要執行SQL語句，跟上面一樣，也是每執行一次，需要交互一次

所以當fetch一批記錄，或者一次執行一批SQL，就會大大提高pl/sql的效率

批量fetch對應的語法

fetch cursorname bulk colletc into 【自定義的數組】 <limit cn_batch_size>

pl/sql中批量執行一批SQL的語法

forall i in 1..[自定義數組長度】

  execute immediate [帶綁定變量的sql] using [綁定變量輸入值】

declare

  cur_emp sys_refcursor;

  v_sql varchar2(4000);

  type namelist is table of varchar2(10);

  enames namelist;

  cn_batch_size constant pls_integer :=1000;

begin

  v_sql:= 'select ename from emp where empno> :1';

  open cur_emp for v_sql using 7900;

  loop

    fetch cur_emp bulk collect into enames limit cn_batch_size;

    for i in 1..enames.count loop

      dbms_output.put_line(enames(i));

      end loop;

    exit when enames.count<cn_batch_size;

    end loop;

    close cur_emp;

    end;

四、綁定變量分級（bind graduation)

根據文本型綁定變量的定義長度而將這些綁定變量分成四級

1、32字節（bytes)以內第一級 分配32字節

2、33--128字節第二級   分配120字節

3、129--2000字節第三級 分配2000字節

4、2000字節以上第四級 ，按實際綁定變量大小，小于等于2000則分配2000字節，大于2000則分配4000字節

注意 oracle只對文本型綁定變量分級，數值型的統一分配22字節，另外一個重要知識點，在pl/sql中，要是文本綁定變量的定義長度發生了變化，因為child cursor 里存儲了該綁定變量的長度和類型，所以這個sql就會新做硬解析。因此，為了避免不必要的硬解析，在pl/sql中，定義文本綁定變量是，最好統一定義長度，比如varchar2(4000);

五、目標sql中的綁定變量不宜過多

六、如何得到已執行sql中綁定變量的值

V$SQL_BIND_CAPTURE，如果已經被aged out 除shared pool,則可以看另外兩張dba_hist_sqlstat,dba_hist_sqlbind

滿足下列條件之一，含有綁定變量的目標sql中的綁定變量值會被捕獲，然后可以從v$sql_bind_capture中查詢到

1、該sql做的是硬解析

2、該SQL做的是軟解析或軟軟解析，這是每隔15分鐘，捕獲一次值

注意對綁定變量值的捕獲只在where 語句中的綁定變量，其他比如insert中的值不會被捕獲

感謝各位的閱讀，以上就是“怎么使用ORACLE中的綁定變量”的內容了，經過本文的學習后，相信大家對怎么使用ORACLE中的綁定變量這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！