以下的文章主要向大家描述的是DB2 sql 存儲過程基礎，存儲過程就是stored 簡稱為procedure。要學這個我們必須要先得弄明白另外一個概念：routine，這個一般翻譯成“例程” 。

routine:存在server端，按應用程序邏輯編寫的，可以通過client或者其他routine調(diào)用的數(shù)據(jù)庫對象.

3種類型：stored procedures,UDFs(自定義function),methods.

 

stored procedures:作為客戶端的擴展但是運行在服務端；UDFs:擴展并且自定義SQL；methods:提供結構化類型的行為

 

2種形式：

 

1）sql routines:完全用sql編寫，通過create statement來注冊routine.

 

2）external routines:用C,C++,Java,OLE編寫，stored procedure還可用cobol編寫。任何語言編寫的都可以包含sql。

 

不同形式的routines可以互相調(diào)用，不管是什么語言編寫的。

 

再來看看stored procedure.

stored procedures:可以通過call statement被client或者其他routine調(diào)用；stored procedures 和它的調(diào)用程序通過create procedure statement中的參數(shù)交換數(shù)據(jù)；stored procedures還能給它的調(diào)用者返回result sets.

 

stored procedures的優(yōu)點：

 

1) 多個sql statement被調(diào)用者一次調(diào)用就能全部執(zhí)行，這能減少client和server間的數(shù)據(jù)傳輸。

 

2)將數(shù)據(jù)庫邏輯與應用程序邏輯隔離開

 

3)能返回多個result sets

 

4)如果被應用程序調(diào)用，運行起來stored procedure就像應用程序的一部分

 

缺點：

 

1)不能被sql statement調(diào)用，除了用call

 

2)返回的結果集不能直接被sql statement使用

 

3)多次調(diào)用之間不能保存調(diào)用的狀態(tài)，即調(diào)用之間是獨立的，無法傳遞信息。

 

一般的應用之處：

 

1)提供一個interface給一組sql statements。比如同時對多個表的insert操作

 

2)標準化應用程序邏輯（不理解，就是把db logic與app logic隔離嗎？）

 

開發(fā)特性：

明白了這些基本概念后再來看看開發(fā)的特性。根據(jù)以上得知開發(fā)routine的語言有很多，這篇只講sql procedure（即sql/sql pl寫的procedure）。

 

各種語言的特性

 

sql:

 

1)效率高于java routine,基本上與c/c++ routine相當

 

2)完全用sql編寫，能很快就能執(zhí)行(making them quick to implement)

 

3)DB2認為sql routine是'safe'的因為全是sql，正因如此sql routine能直接在db engine上運行，并且有很好的運行效率和應用范圍(good performance and scalability)

 

 

stored procedure feathures:   
parameter modes: 

 

 

3種類型的參數(shù)：

1)IN :傳入數(shù)據(jù)到stored procedure

2)OUT: stored procedure 返回數(shù)據(jù)

3)INOUT: 傳入的那部分數(shù)據(jù)，在執(zhí)行過程中被返回數(shù)據(jù)覆蓋

result sets:

stored procedure通過cursor來傳遞結果集給調(diào)用者。DB2 sql 存儲過程必須為每一個需要返回的結果集保留一個游標。

 

使用with return to caller/client來指定結果集返回的對象。指定為client將使得中間調(diào)用的routine不能獲得結果集，只有client才能獲得。

 

使用dynamic result sets 語句來指定返回結果集的數(shù)目，這個數(shù)目保存在syscat.routines視圖的result_sets字段。如果實際返回的結果集數(shù)目大于聲明的這個數(shù)目，將發(fā)出一個warning(sqlcode +464,sqlstate 0100E) #p#

 

sql stored procedure返回結果集的操作步驟：

 

1)declare cursor:

 

如：

declare clientcur cursor with return to caller for select * from staff; 

 

2)open the cursor:如 open clientcur;

 

3)不關閉游標退出stored procedure

 

開發(fā)：

***終于來到了真正的開發(fā)了，剛才講到sql procedure是由sql,sql pl寫的，sql就沒什么好說的了。關鍵說說sql pl (procedural language)

 

功能:控制邏輯流向，聲明和設置變量，處理警告和異常。可用于例程(routine),觸發(fā)器，動態(tài)復合語句（單個調(diào)用中的sql語句塊）

 

控制語句：declare,set,for,get diagnostics,if,iterate,leave,return,signal,while

 

sql pl不能執(zhí)行的sql：table,index,view的create和drop

 

begin atomic 開頭，end 結尾

 

declare :定義變量 和 定義出錯處理

 

 

declare sql-var-name data-type default default-values   
declare condition-name condition for sqlstate value...  

 

 這里的condition一般做“異常”解釋

 

set:聲明變量 和 給觸發(fā)器定義中的表中的列賦值

 

set pay = select salary from employee where empno = 5;//僅返回一個值

 

set pay = null;//空值

 

set pay = default;//變量定義的默認值

 

//專用寄存器的內(nèi)容

 

 

set useriduserid = userid;   
set today = current date;  

 

 

//同時給多個變量賦值

 

 

set pay =10000,bonus = 1500;   
set (pay,bonus) = (10000,1500);   
set (pay,bonus) = select (pay,bonus) from employee where empno = 5;   
>>if/then/else  

 

 

三種形式：

 

1) if then/end if 語句塊

 

2) if then/else/end if

 

3) if then/elseif /else/end if

 

可以在if/then/else 語句中使用sql運算符，如：

 

 

if (salary between 10000 and 90000) then...   
if (deptno in ('a00','b01')) then..   
if (exist (select * from employee)) then...   
if (select count(*) from employee)>0) then..   
>>while   
label:   
while condition do   
...sql pl ..   
end while lable;   

 

label可選

 

>>for:用于循環(huán)select返回結果集的行

 

格式：

 

 

label:   
for row_label as select satement do   
..sql pl..  

 

 

end for label;//label可選

 

例子：

 

 

for emp as select * from employee where bonus >1000 do   
set total_bonustotal_bonus = total_bonus +emp.bonus;   
end for;  

 

 

>>iterate:用來回到for或者while循環(huán)的開始重新執(zhí)行

 

 

check_bonus:   
for emp as select * from employee do   
if(emp.bonus>10000) then   
set total_bonustotal_bonus = total_bonus +emp.bonus;   
else   
iterate check_bonus;   
end if;   
end for check_bonus;  

 

 

>>leave:相當于java中的break，需要一個label

 

>>signal:對出現(xiàn)異常的應用程序報警

signal sqlstate value set message_text = '...';//自定義一個sqlstate,7、8、9和I~Z開頭的sqlstate

 

signal condition set message_text = '...';//自定義異常condition

 

>>get diagnostics:用在sql pl觸發(fā)器或語句塊(不是函數(shù))內(nèi)，返回update,insert,delete語句影響的記錄數(shù)。

 

get diagnostics variable = row_count; 

 

 

 以上的相關內(nèi)容就是對DB2 sql 存儲過程基礎的詳細解析的介紹，望你能有所收獲。

【編輯推薦】

1. DB2數(shù)據(jù)庫提高數(shù)據(jù)備份與恢復效率的技巧
2. DB2數(shù)據(jù)庫日志文件進行歸檔的問題分析
3. DB2數(shù)據(jù)庫的安裝目錄結構介紹
4. DB2數(shù)據(jù)庫性能調(diào)整的命令介紹
5. DB2數(shù)據(jù)庫開發(fā)常見問題解答