Oracle動态sql

Oracle動态sql

一、靜态SQL與動态SQL

Oracle編譯PL/SQL程式塊分為兩個種：其一為前期聯編（early binding），即SQL語句在程式編譯期間就已經确定，大多數的編譯情況屬于這種類型；另外一種是後期聯編（late binding），即SQL語句隻有在運作階段才能建立，例如當查詢條件為使用者輸入時，那麼Oracle的SQL引擎就無法在編譯期對該程式語句進行确定，隻能在使用者輸入一定的查詢條件後才能送出給SQL引擎進行處理。通常，靜态SQL采用前一種編譯方式，而動态SQL采用後一種編譯方式。

二、動态SQL文法：

Excute immediate 動态SQL語句 using 綁定參數清單 returning into 輸出參數清單;

說明：

1)動态SQL是指DDL和不确定的DML（即帶參數的DML）

2)綁定參數清單為輸入參數清單，即其類型為in類型，在運作時刻與動态SQL語句中的參數（實際上占位符，可以了解為函數裡面的形式參數）進行綁定。

3)輸出參數清單為動态SQL語句執行後傳回的參數清單。

4)由于動态SQL是在運作時刻進行确定的，是以相對于靜态而言，其更多的會損失一些系統性能來換取其靈活性。

三、執行動态SQL的方法：

1、使用 EXECUTE IMMEDIATE

EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并馬上執行動态的SQL語句或非運作時建立的PL/SQL塊.動态建立和執行SQL語句性能超前，EXECUTE IMMEDIATE的目标在于減小企業費用并獲得較高的性能，較之以前它相當容易編碼。

使用技巧

1. EXECUTE IMMEDIATE将不會送出一個DML事務執行，應該顯式送出

 如果通過EXECUTE IMMEDIATE處理DML指令，那麼在完成以前需要顯式送出或者作為EXECUTE IMMEDIATE自己的一部分. 如果通過EXECUTE IMMEDIATE處理DDL指令,它送出所有以前改變的資料

2. 不支援傳回多行的查詢,這種互動将用臨時表來存儲記錄(參照例子如下)或者用REF cursors.

3. 當執行SQL語句時，不要用分号，當執行PL/SQL塊時，在其尾部用分号.

4. 在Oracle手冊中，未詳細覆寫這些功能。下面的例子展示了所有用到Execute immediate的可能方面.希望能給你帶來友善.

5. 對于Forms開發者,應該在PL/SQL 8.0.6.3.版本中使用，Forms 6i不能使用此功能

四、EXECUTE IMMEDIATE用法例子：

1、在PL/SQL運作DDL語句

BEGIN
  EXECUTE IMMEDIATE 'set role all';
END;
           

2、給動态語句傳值(USING 子句)

BEGIN
  EXECUTE IMMEDIATE 'insert into dept values  (:1, :2, :3)'
    USING 50, '董事會', '010001';
  COMMIT;
END;
           

3、從動态語句檢索值(INTO子句)

DECLARE
  l_cnt VARCHAR2(20);
BEGIN
  EXECUTE IMMEDIATE 'select count(1) from emp'
    INTO l_cnt;
  dbms_output.put_line(l_cnt);
END;
           

4、USING、INTO子句結合使用

DECLARE
  l_sql   VARCHAR2(2000);
  l_count NUMBER;
BEGIN
  l_sql := 'select nvl(count(*),0) total_count' ||
           ' from cux_payment_history where process_status = :1 group by trunc(process_date)';
  dbms_output.put_line('l_sql = ' || l_sql);

  EXECUTE IMMEDIATE l_sql
    INTO l_count
    USING 'PENDING';
  dbms_output.put_line('l_count = ' || l_count);

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('other error occoured!');
END;
           

5、動态SQL執行程式包

BEGIN
  EXECUTE IMMEDIATE 'begin ' || g_import_package ||
                    '.validate_data(:0,:1);end;'
    USING IN p_account_id, OUT x_message;
END;
           

五、對于處理動态語句,EXECUTE IMMEDIATE比以前可能用到的更容易并且更高效.當意圖執行動态語句時，适當地處理異常更加重要.應該關注于捕獲所有可能的異常