程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 基於Oracle的高性能動態SQL程序開發

基於Oracle的高性能動態SQL程序開發

編輯:Oracle數據庫基礎
摘要:對動態SQL的程序開發進行了總結,並結合筆者實際開發經驗給出若干開發技巧。

  關鍵詞:動態SQL,PL/SQL,高性能

  1. 靜態SQLSQL與動態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表,其數據為如下:

IDNAMESALARY100Jacky 5600101Rose 3000102 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的程序開發進行了總結,並結合筆者實際開發經驗給出若干開發技巧。

  關鍵詞:動態SQL,PL/SQL,高性能

  1. 靜態SQLSQL與動態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表,其數據為如下:

IDNAMESALARY100Jacky 5600101Rose 3000102 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 out類型的參數是由值傳遞的方式進行的。但是對於大的對象類型或者集合類型的參數傳遞而言,其希望損耗將是很大的,為了減少損耗,可以采用引用傳遞的方式,即在進行參數聲明的時候引用NOCOPY關鍵字來說明即可到達這樣的效果。比如創建一個過程:

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

  其中square為一個大的對象類型。這樣只是傳遞一個地址,而不是傳遞整個對象了。顯然這樣的處理也是提高了效率。

  4. 小結

  本文對動態SQL的編譯原理、開發過程以及開發技巧的討論,通過本文的介紹後,相信讀者對動態SQL程序開發有了一個總體的認識,為今後深入的工作打下一個良好的基礎。

  前面代碼部分已經在下列環境中調試成功:

  服務器端:UNIX+Oracle9.2

  客戶端:Windows2000 PRO+TOAD

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved