熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

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

2013-11-13 22:15:09  來源: Oracle 

  . 靜態SQLSQL與動態SQL
  
  Oracle編譯PL/SQL程序塊分為兩個種其一為前期聯編(early binding)即SQL語句在程序編譯期間就已經確定大多數的編譯情況屬於這種類型另外一種是後期聯編(late binding)即SQL語句只有在運行階段才能建立例如當查詢條件為用戶輸入時那麼Oracle的SQL引擎就無法在編譯期對該程序語句進行確定只能在用戶輸入一定的查詢條件後才能提交給SQL引擎進行處理通常靜態SQL采用前一種編譯方式而動態SQL采用後一種編譯方式
  
  本文主要就動態SQL的開發進行討論並在最後給出一些實際開發的技巧
  
  . 動態SQL程序開發
  
  理解了動態SQL編譯的原理也就掌握了其基本的開發思想動態SQL既然是一種不確定的SQL那其執行就有其相應的特點Oracle中提供了Execute immediate語句來執行動態SQL語法如下
  
  Excute immediate 動態SQL語句 using 綁定參數列表 returning into 輸出參數列表;
  
  對這一語句作如下說明
  
  ) 動態SQL是指DDL和不確定的DML(即帶參數的DML)
  
  ) 綁定參數列表為輸入參數列表即其類型為in類型在運行時刻與動態SQL語句中的參數(實際上占位符可以理解為函數裡面的形式參數)進行綁定
  
  ) 輸出參數列表為動態SQL語句執行後返回的參數列表
  
  ) 由於動態SQL是在運行時刻進行確定的所以相對於靜態而言其更多的會損失一些系統性能來換取其靈活性
  
  為了更好的說明其開發的過程下面列舉一個實例
  
  設數據庫的emp表其數據為如下
  
  ID NAME SALARY
   Jacky
   Rose
   John
  
  要求
  
  .創建該表並輸入相應的數據
  
  .根據特定ID可以查詢到其姓名和薪水的信息
  
  .根據大於特定的薪水的查詢相應的員工信息
  
  根據前面的要求可以分別創建三個過程(均使用動態SQL)來實現
  
  過程一
  
  create or replace procedure create_table as
  begin
  execute immediate
  create table emp(id number
  name varchar()
  salary number; ); 動態SQL為DDL語句
  insert into emp
  values (jacky);
  insert into emp
  values (rose);
  insert into emp
  values (john);
  end create_table;
  
  過程二
  
  create or replace procedure find_info(p_id number) as
  v_name varchar();
  v_salary number;
  begin
  execute immediate
  select namesalary from emp
  where id=:
  using p_id
  returning into v_namev_salary; 動態SQL為查詢語句
  dbms_outputput_line(v_name ||的收入為||to_char(v_salary))
  exception
  when others then
  dbms_outputput_line(找不到相應數據)
  end find_info;
  
  過程三
  
  create or replace procedure find_emp(p_salary number) as
  r_emp emp%rowtype;
  type c_type is ref cursor;
  c c_type;
  begin
  open c for
  select * from emp
  where salary >:
  using p_salary;
  loop
  fetch c into r_emp;
  exit when c%notfound;
  dbms_outputput_line(薪水大於||to_char(p_salary)||的員工為);
  dbms_outputput_line(ID為to_char(r_emp)|| 其姓名為||r_empname);
  end loop;
  close c;
  end create_table;
  
  注意在過程二中的動態SQL語句使用了占位符:其實它相當於函數的形式參數使用作為前綴然後使用using語句將p_id在運行時刻將:給替換掉這裡p_id相當於函數裡的實參另外過程三中打開的游標為動態游標它也屬於動態SQL的范疇其整個編譯和開發的過程與 execute immediate執行的過程很類似這裡就不在贅述了
  
  . 動態SQL語句開發技巧
  
  前面分析到了動態SQL的執行是以損失系統性能來換取其靈活性的所以對它進行一定程度的優化也是必要的筆者根據實際開發經驗給出一些開發的技巧需要指出的是這裡很多經驗不僅局限於動態SQL有些也適用於靜態SQL在描述中會給予標注
  
  技巧一盡量使用類似的SQL語句這樣Oracle本身通過SGA中的共享池來直接對該SQL語句進行緩存那麼在下一次執行類似語句時就直接調用緩存中已解析過的語句以此來提高執行效率
  
  技巧二當涉及到集合單元的時候盡量使用批聯編比如需要對id為的員工的薪水加薪一般情況下應該為如下形式
  
  declare
  type num_list is varray() of number;
  v_id num_list :=num_list();
  begin
  
  for i in v_idfirst v_idlast loop
  
  execute immediate update emp
  set =salary*
  where id=:
  using v_id(i);
  end loop;
  end;
  
  對於上面的處理當數據量大的時候就會顯得比較慢那麼如果采用批聯編的話則整個集合首先一次性的傳入到SQL引擎中進行處理這樣比單獨處理效率要高的多進行批聯編處理的代碼如下
  
  declare
  type num_list is varray() of number;
  v_id num_list :=num_list();
  begin
  
  forall i in v_idfirst v_idlast loop
  
  execute immediate update emp
  set =salary*
  where id=:
  using v_id(i);
  end loop;
  end;
  
  這裡是使用forall來進行批聯編這裡將批聯編處理的情形作一個小結
  
  ) 如果一個循環內執行了insertdeleteupdate等語句引用了集合元素那麼可以將其移 動到一個forall語句中
  
  ) 如果select intofetch into 或returning into 子句引用了一個集合應該使用bulk collect 子句進行合並
  
  ) 如有可能應該使用主機數組來實現在程序和數據庫服務器之間傳遞參數
  
  技巧三使用NOCOPY編譯器來提高PL/SQL性能缺省情況下out類型和in out類型的參數是由值傳遞的方式進行的但是對於大的對象類型或者集合類型的參數傳遞而言其希望損耗將是很大的為了減少損耗可以采用引用傳遞的方式即在進行參數聲明的時候引用NOCOPY關鍵字來說明即可到達這樣的效果比如創建一個過程
  
  create or replace procedure test(p_object in nocopy square)
  
  end;
  
  其中square為一個大的對象類型這樣只是傳遞一個地址而不是傳遞整個對象了顯然這樣的處理也是提高了效率
  
  . 小結
  
  本文對動態SQL的編譯原理開發過程以及開發技巧的討論通過本文的介紹後相信讀者對動態SQL程序開發有了一個總體的認識為今後深入的工作打下一個良好的基礎
  
  前面代碼部分已經在下列環境中調試成功
  
  服務器端UNIX+ORACLE
  
  客戶端WINDOWS PRO+TOAD
From:http://tw.wingwit.com/Article/program/Oracle/201311/18603.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.