【轉】Oracle 執行動態語句

標籤：

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

2．動態SQL程式開發
理解了動態SQL編譯的原理，也就掌握了其基本的開發思想。動態SQL既然是一種”不確定”的SQL，那其執行就有其相應的特點。Oracle中提供了Execute immediate語句來執行動態SQL，文法如下：

Excute immediate 動態SQL語句 using 綁定參數列表 returning into 輸出參數列表;

1)動態SQL是指DDL和不確定的DML（即帶參數的DML）
2)綁定參數列表為輸入參數列表，即其類型為in類型，在運行時刻與動態SQL語句中的參數（實際上預留位置，可以理解為函數裡面的形式參數）進行綁定。
3)輸出參數列表為動態SQL語句執行後返回的參數列表。
4)由於動態SQL是在運行時刻進行確定的，所以相對於靜態而言，其更多的會損失一些系統效能來換取其靈活性。 

設資料庫的emp表，其資料為如下：

ID	NAME	SALARY
100	Jacky	5600
101	Rose	3000
102	John	4500

要求：
1．建立該表並輸入相應的資料。
2．根據特定ID可以查詢到其姓名和薪水的資訊。
3．根據大於特定的薪水的查詢相應的員工資訊。
根據前面的要求，可以分別建立三個過程（均使用動態SQL）來實現：
過程一：

 1 create or replace procedure create_table is  2 begin  3 execute immediate ‘ create table emp( 4 id number,  5 name varchar2(10),  6 salary number )‘; --動態SQL為DDL語句 
 7 end create_table;

過程二：

 1 create or replace procedure find_info(p_id number) is  2 v_name varchar2(10);  3 v_salary number;  4 begin  5 execute immediate ‘  6 select name,salary from emp  7 where id=:1‘  8 using p_id  9 returning into v_name,v_salary; --動態SQL為查詢語句 10 dbms_output.put_line(v_name ||‘的收入為：‘||to_char(v_salary))； 11 exception 12 when others then 13 dbms_output.put_line(‘找不到相應資料‘)； 14 end find_info;

過程三：

 1 create or replace procedure find_emp(p_salary number) is  2 r_emp emp%rowtype;  3 type c_type is ref cursor;  4 c1 c_type;  5 begin  6 open c1 for ‘  7 select * from emp  8 where salary >:1‘  9 using p_salary; 10 loop 11 fetch c1 into r_emp; 12 exit when c1%notfound; 13 dbms_output.put_line(‘薪水大於‘||to_char(p_salary)||‘的員工為：‘); 14 dbms_output.put_line(‘ID為‘to_char(r_emp)||‘ 其姓名為：‘||r_emp.name); 15 end loop; 16 close c1; 17 end create_table;

注意：在過程二中的動態SQL語句使用了預留位置“:1“，其實它相當於函數的形式參數，使用”：“作為首碼，然後使用using語句將p_id在運行時刻將:1給替換掉，這裡p_id相當於函數裡的實參。另外過程三中開啟的遊標為動態資料指標，它也屬於動態SQL的範疇，其整個編譯和開發的過程與execute immediate執行的過程很類似，這裡就不在贅述了。
3． 動態SQL語句開發技巧
前面分析到了，動態SQL的執行是以損失系統效能來換取其靈活性的，所以對它進行一定程度的最佳化也是必要的，筆者根據實際開發經驗給出一些開發的技巧，需要指出的是，這裡很多經驗不僅局限於動態SQL，有些也適用於靜態SQL，在描述中會給予標註。
技巧一：盡量使用類似的SQL語句，這樣Oracle本身通過SGA中的共用池來直接對該SQL語句進行緩衝，那麼在下一次執行類似語句時就直接調用緩衝中已解析過的語句，以此來提高執行效率。
技巧二：當涉及到集合單元的時候，盡量使用批聯編。比如需要對id為100和101的員工的薪水加薪10％，一般情況下應該為如下形式：

 1 declare  2 type num_list is varray(20) of number;  3 v_id num_list :=num_list(100,101);  4 begin  5 ...  6 for i in v_id.first .. v_id.last loop  7 ...  8 execute immediate ‘update emp  9 set =salary*1.2 10 where id=:1 ‘ 11 using v_id(i); 12 end loop; 13 end;

對於上面的處理，當資料量大的時候就會顯得比較慢，那麼如果採用批聯編的話，則整個集合首先一次性的傳入到SQL引擎中進行處理，這樣比單獨處理效率要高的多，進行批聯編處理的代碼如下：

 1 declare  2 type num_list is varray(20) of number;  3 v_id num_list :=num_list(100,101);  4 begin  5 ...  6 forall i in v_id.first .. v_id.last loop  7 ...  8 execute immediate ‘update emp  9 set =salary*1.2 10 where id=:1 ‘ 11 using v_id(i); 12 end loop; 13 end;

這裡是使用forall來進行批聯編，這裡將批聯編處理的情形作一個小結：
1) 如果一個迴圈內執行了insert，delete，update等語句引用了集合元素，那麼可以將其移動到一個forall語句中。
2) 如果select into，fetch into 或returning into 子句引用了一個集合，應該使用bulk collect 子句進行合并。
3) 如有可能，應該使用主機數組來實現在程式和資料庫伺服器之間傳遞參數。
技巧三：使用NOCOPY編譯器來提高PL/SQL效能。預設情況下，out類型和in out類型的參數是由值傳遞的方式進行的。但是對於大的物件類型或者集合類型的參數傳遞而言，其希望損耗將是很大的，為了減少損耗，可以採用引用傳遞的方式，即在進行參數聲明的時候引用NOCOPY關鍵字來說明即可到達這樣的效果。比如建立一個過程：

create or replace procedure test(p_object in nocopy square)...end;

其中square為一個大的物件類型。這樣只是傳遞一個地址，而不是傳遞整個對象了。顯然這樣的處理也是提高了效率。
4． 小結
本文對動態SQL的編譯原理、開發過程以及開發技巧的討論，通過本文的介紹後，相信讀者對動態SQL程式開發有了一個總體的認識，為今後深入的工作打下一個良好的基礎。

 

一、為什麼要使用動態執行語句？

       由於在PL/SQL 塊或者預存程序中只支援DML語句及控制流程語句，並不支援DDL語句，所以Oracle動態執行語句便應允而生了。關於DDL與DML的區別，請參見：DDL語句與DML語句及DCL和TCL。

二、動態執行語句怎麼用？     

     動態執行語句代替了Oracle 8i中的DBMS_SQL Package包。

     1）在PL/SQL中運行SQL語句，例如：

       樣本一：

        BEGIN 
              EXECUTE IMMEDIATE ‘select count(username) from user_users‘;     --DML（每條語句必須以分號結尾）
       END;

       樣本二：

       BEGIN
               EXECUTE IMMEDIATE  ‘ALTER TABLE a RENAME TO EXAMPLE‘;    --DDL
        END;

        你可能會問不是只DDL語句需要用動態語句執行嗎？是的，你說的完全正確。但是DML語句用動態語句執行也可以。

        即：DDL語句只能用動態執行語句來執行，DML語句亦可用動態語句來執行。

    2）使用using給動態語句傳值，例如：

         DECLARE
                e_name VARCHAR2(10); --聲明變數e_name
                e_age INT;                        --聲明變數e_age
         BEGIN
                e_name :=  ‘sillylaura‘;      --給變數e_name賦值
                e_age := 21;                     --給變數e_age 賦值
                EXECUTE IMMEDIATE ‘insert into Example values(seq_add_by_one.nextval,:2,:3)‘ using e_name,e_age;  --DML             END;

    3）使用動態語句賦值（select 列名 into 變數 from ……）

         DECLARE
                        temp INT;
         BEGIN
                        EXECUTE IMMEDIATE ‘SELECT COUNT(*) FROM dual‘  INTO temp; --DML

                        dbms_output.put_line(temp);
         END;

     4）傳遞並檢索值：into用在using之前。

         DECLARE
                   temp INT;
                   test VARCHAR2(10);
         BEGIN
                 test := ‘ok‘;
                 EXECUTE immediate ‘SELECT COUNT(*) FROM dual where dummy = :1 GROUP BY dummy‘ INTO temp USING test;
                 dbms_output.put_line(temp ||‘  ‘|| test);
         EXCEPTION WHEN OTHERS  THEN 
                 dbms_output.put_line(‘It has no data!‘);
         END;

三、動態語句小結

1. DDL語句只能用動態執行語句來執行，DML語句亦可用動態語句來執行。
2. 在使用select……into子句為變數賦值時，into字句必須寫在單引號外面。
3. 在同時使用select……into子句和using時，注意二者的順序：into用在using之前，且都在單引號外面。
4. 注意寫上必要的異常錯誤處理。

【轉】Oracle 執行動態語句