DB2 SQL預存程序學習筆記

近兩期的DB2項目中，都需要在系統閑置期把暫存資料表中的資料轉到正式表中，或者在後台對某些表執行一些耗時的操作。這些都需要用到預存程序。  基本概念：
預存程序即stored procedure,一般會被簡稱procedure。要學這個先得弄明白另外一個概念：routine，這個一般翻譯成“常式”
>>routine:存在server端，按應用程式邏輯編寫的，可以通過client或者其他routine調用的資料庫物件.
>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可以互相調用，不管是什麼語言編寫的。

再來看看stored procedure.
>>stored procedures:可以通過call statement被client或者其他routine調用；stored procedures 和它的調用程式通過create procedure statement中的參數交換資料；stored procedures還能給它的調用者返回result sets.
stored procedures的優點：
1) 多個sql statement被調用者一次調用就能全部執行，這能減少client和server間的資料轉送。
2)將資料庫邏輯與應用程式邏輯隔離開
3)能返回多個result sets
4)如果被應用程式調用，運行起來stored procedure就像應用程式的一部分
缺點：
1)不能被sql statement調用，除了用call
2)返回的結果集不能直接被sql statement使用
3)多次調用之間不能儲存調用的狀態，即調用之間是獨立的，無法傳遞資訊。
一般的應用之處：
1)提供一個interface給一組sql statements。比如同時對多個表的insert操作
2)標準化應用程式邏輯（不理解，就是把db logic與app logic隔離嗎？）

開發特性：
明白了這些基本概念後再來看看開發的特性。根據以上得知開發routine的語言有很多，這篇只講sql procedure（即sql/sql pl寫的procedure）。
>>各種語言的特性
sql:
1)效率高於java routine,基本上與c/c++ routine相當
2)完全用sql編寫，能很快就能執行(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種類型的參數：1)IN :傳入資料到stored procedure 2)OUT: stored procedure 返回資料 3)INOUT: 傳入的那部分資料，在執行過程中被返回資料覆蓋

result sets:
stored procedure通過cursor來傳遞結果集給調用者。預存程序必須為每一個需要返回的結果集保留一個遊標。
>使用with return to caller/client來指定結果集返回的對象。指定為client將使得中間調用的routine不能獲得結果集，只有client才能獲得。
>使用dynamic result sets 語句來指定返回結果集的數目，這個數目儲存在syscat.routines視圖的result_sets欄位。如果實際返回的結果集數目大於聲明的這個數目，將發出一個warning(sqlcode +464,sqlstate 0100E)
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

開發：
最後終於來到了真正的開發了，剛才講到sql procedure是由sql,sql pl寫的，sql就沒什麼好說的了。關鍵說說sql pl (procedural language)
>>功能:控制邏輯流向，聲明和設定變數，處理警告和異常。可用於常式(routine),觸發器，動態複合陳述式（單個調用中的sql語句塊）
>>控制語句：declare,set,for,get diagnostics,if,iterate,leave,return,signal,while
>>sql pl不能執行的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:聲明變數 和 給觸發器定義中的表中的列賦值
set pay = select salary from employee where empno = 5;//僅返回一個值
set pay = null;//空值
set pay = default;//變數定義的預設值
//專用寄存器的內容
set userid = 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:用於迴圈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_bonus = total_bonus +emp.bonus;
end for;
>>iterate:用來回到for或者while迴圈的開始重新執行
check_bonus:
for emp as select * from employee do
if(emp.bonus>10000) then
set total_bonus = total_bonus +emp.bonus;
else
iterate check_bonus;
end if;
end for check_bonus;
>>leave:相當於java中的break，需要一個label

>>signal:對出現異常的應用程式警示
signal sqlstate value set message_text = '...';//自訂一個sqlstate,7、8、9和I~Z開頭的sqlstate
signal condition set message_text = '...';//自訂異常condition

>>get diagnostics:用在sql pl觸發器或語句塊(不是函數)內，返回update,insert,delete語句影響的記錄數。
get diagnostics variable = row_count;