【轉】Oracle 提高動态SQL效率

原文連結：http://blog.itpub.net/23237282/viewspace-1116208

1．靜态SQL與動态SQL 

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

過程一： 

代碼如下:

create or replace procedure create_table as 

begin 

execute immediate ' 

create table emp(id number, 

name varchar2(10), 

salary number )'; --動态SQL為DDL語句 

insert into emp 

values (100,'jacky',5600); 

insert into emp 

values (101,'rose',3000); 

insert into emp 

values (102,'john',4500); 

end create_table; 

過程二： 

代碼如下:

create or replace procedure find_info(p_id number) as 

v_name varchar2(10); 

v_salary number; 

begin 

execute immediate ' 

select name,salary from emp 

where id=:1' 

using p_id 

returning into v_name,v_salary; --動态SQL為查詢語句 

dbms_output.put_line(v_name ||'的收入為：'||to_char(v_salary))； 

exception 

when others then 

dbms_output.put_line('找不到相應資料')； 

end find_info; 

過程三： 

代碼如下:

create or replace procedure find_emp(p_salary number) as 

r_emp emp%rowtype; 

type c_type is ref cursor; 

c1 c_type; 

begin 

open c1 for ' 

select * from emp 

where salary >:1' 

using p_salary; 

loop 

fetch c1 into r_emp; 

exit when c1%notfound; 

dbms_output.put_line('薪水大于‘||to_char(p_salary)||'的員工為：‘); 

dbms_output.put_line('ID為'to_char(r_emp)||' 其姓名為：'||r_emp.name); 

end loop; 

close c1; 

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％，一般情況下應該為如下形式： 

代碼如下:

declare 

type num_list is varray(20) of number; 

v_id num_list :=num_list(100,101); 

begin 

... 

for i in v_id.first .. v_id.last loop 

... 

execute immediate 'update emp 

set =salary*1.2 

where id=:1 ' 

using v_id(i); 

end loop; 

end; 

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

代碼如下:

declare 

type num_list is varray(20) of number; 

v_id num_list :=num_list(100,101); 

begin 

... 

forall i in v_id.first .. v_id.last loop 

... 

execute immediate 'update emp 

set =salary*1.2 

where id=:1 ' 

using v_id(i); 

end loop; 

end; 

這裡是使用forall來進行批聯編，這裡将批聯編處理的情形作一個小結： 

1) 如果一個循環内執行了insert，delete，update等語句引用了集合元素，那麼可以将其移動到一個forall語句中。 

2) 如果select into，fetch into 或returning into 子句引用了一個集合，應該使用bulk collect 子句進行合并。 

3) 如有可能，應該使用主機數組來實作在程式和資料庫伺服器之間傳遞參數。 

技巧三：使用NOCOPY編譯器來提高PL/SQL性能。預設情況下，out類型和in類型的參數是由值傳遞的方式進行的。但是對于大的對象類型或者集合類型的參數傳遞而言，其希望損耗将是很大的，為了減少損耗，可以采用引用傳遞的方式，即在進行參數聲明的時候引用NOCOPY關鍵字來說明即可到達這樣的效果。比如建立一個過程： 

代碼如下:

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

其中square為一個大的對象類型。這樣隻是傳遞一個位址，而不是傳遞整個對象了。顯然這樣的處理也是提高了效率。<!-- 正文結束 -->

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23237282/viewspace-1116208/，如需轉載，請注明出處，否則将追究法律責任。