|
|
Posted on 2010-05-09 18:32 Prayer 閱讀(257) 評論(0) 編輯 收藏 引用 所屬分類: DB2
DB2 sql存儲過程基礎 原帖發于：http://eyejava.javaeye.com/blog/32263
基本概念：
存儲過程即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;
|