db2數(shù)據(jù)庫存儲(chǔ)過程

1、-1- 中國移動(dòng)通信集團(tuán)河南有限公司業(yè)務(wù)支援中中國移動(dòng)通信集團(tuán)河南有限公司業(yè)務(wù)支援中 心心 DB2DB2數(shù)據(jù)庫中的存儲(chǔ)過程數(shù)據(jù)庫中的存儲(chǔ)過程 -2- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -3- 什么是存儲(chǔ)過程？什么是存儲(chǔ)過程？(Stored Procedure)(Stored Procedure) 受受 DB2 DB2 服務(wù)器控制的一段可執(zhí)行程序服務(wù)器控制的一段可執(zhí)行程序 可以通過可以通

2、過SQLSQL的的CALLCALL語句來完成對(duì)存儲(chǔ)過程的調(diào)用語句來完成對(duì)存儲(chǔ)過程的調(diào)用 在存儲(chǔ)過程中可以包含業(yè)務(wù)邏輯在存儲(chǔ)過程中可以包含業(yè)務(wù)邏輯 存儲(chǔ)過程可以在本地或遠(yuǎn)程進(jìn)行調(diào)用存儲(chǔ)過程可以在本地或遠(yuǎn)程進(jìn)行調(diào)用 存儲(chǔ)過程可以接收或傳遞參數(shù)，生成結(jié)果集存儲(chǔ)過程可以接收或傳遞參數(shù)，生成結(jié)果集 -4- 什么時(shí)候使用存儲(chǔ)過程？什么時(shí)候使用存儲(chǔ)過程？ 使用存儲(chǔ)過程的合適時(shí)機(jī)使用存儲(chǔ)過程的合適時(shí)機(jī): : 應(yīng)用程序的應(yīng)用程序的性能性能無法滿足預(yù)期時(shí)無法滿足預(yù)期時(shí) 客戶端數(shù)量較多且應(yīng)用程序中客戶端數(shù)量較多且應(yīng)用程序中SQLSQL代碼分散時(shí)代碼分散時(shí) 應(yīng)用程序需要進(jìn)行應(yīng)用程序需要進(jìn)行繁重繁重的數(shù)據(jù)庫操作，同時(shí)這

3、些操作并不的數(shù)據(jù)庫操作，同時(shí)這些操作并不 需要進(jìn)行太多的客戶交互需要進(jìn)行太多的客戶交互 應(yīng)用程序代碼更改頻繁應(yīng)用程序代碼更改頻繁 需要對(duì)客戶應(yīng)用代碼進(jìn)行訪問控制時(shí)需要對(duì)客戶應(yīng)用代碼進(jìn)行訪問控制時(shí) 客戶應(yīng)用需要在一次操作中執(zhí)行多條客戶應(yīng)用需要在一次操作中執(zhí)行多條 SQL SQL 語句語句 -5- C/S: C/S: 宏觀交互圖宏觀交互圖 -6- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -7-

4、存儲(chǔ)過程結(jié)構(gòu)存儲(chǔ)過程結(jié)構(gòu) CREATE OR REPLACE PROCEDURE CREATE OR REPLACE PROCEDURE ( IN | OUT | INOUT ( IN | OUT | INOUT 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 默認(rèn)值默認(rèn)值 ) ) LANGUAGE SQLLANGUAGE SQL BEGINBEGIN 業(yè)務(wù)邏輯代碼業(yè)務(wù)邏輯代碼 END;END; -8- 參數(shù)類型參數(shù)類型 ININ（輸入?yún)?shù)）（輸入?yún)?shù)） 只是將實(shí)參傳遞給存儲(chǔ)過程，但在存儲(chǔ)過程中不能對(duì)其進(jìn)行修改。換句話說，只是將實(shí)參傳遞給存儲(chǔ)過程，但在存儲(chǔ)過程中不能對(duì)其進(jìn)行修改。換句話說， 對(duì)于存儲(chǔ)過程而言它

5、是只讀的。對(duì)于存儲(chǔ)過程而言它是只讀的。 OUTOUT（輸出參數(shù)）（輸出參數(shù)） 在存儲(chǔ)過程結(jié)束時(shí)向調(diào)用者返回。一般在過程中都會(huì)被賦值。在存儲(chǔ)過程結(jié)束時(shí)向調(diào)用者返回。一般在過程中都會(huì)被賦值。 INOUTINOUT（輸入輸出參數(shù)）（輸入輸出參數(shù)） 上述兩種參數(shù)類型的結(jié)合體。它可以幫助調(diào)用者將實(shí)參傳遞給進(jìn)程，另外它上述兩種參數(shù)類型的結(jié)合體。它可以幫助調(diào)用者將實(shí)參傳遞給進(jìn)程，另外它 也能夠作為輸出參數(shù)被修改和賦值。也能夠作為輸出參數(shù)被修改和賦值。 -9- 復(fù)合語句復(fù)合語句 復(fù)合語句是指包含在復(fù)合語句是指包含在BEGINBEGIN和和ENDEND間的語句。它一間的語句。它一 般包括如下語句類型：般包括如下

6、語句類型： 聲明語句聲明語句 賦值語句賦值語句 控制語句控制語句 條件處理語句條件處理語句 -10- 復(fù)合語句示例復(fù)合語句示例 說明：說明： 1. 1. 復(fù)合語句可以嵌套使用。復(fù)合語句可以嵌套使用。 2. BEGIN2. BEGIN語句可以和標(biāo)簽組合使用，這樣可以更清晰的標(biāo)識(shí)語句塊的范圍。語句可以和標(biāo)簽組合使用，這樣可以更清晰的標(biāo)識(shí)語句塊的范圍。 -11- 聲明語句聲明語句 變量聲明變量聲明 DECLARE my_var INTEGER DEFAULT 6;DECLARE my_var INTEGER DEFAULT 6; 條件聲明條件聲明 DECLARE not_found CONDITIO

7、N FOR SQLSTATE 02000;DECLARE not_found CONDITION FOR SQLSTATE 02000; 游標(biāo)聲明游標(biāo)聲明 DECLARE c1 CURSOR FOR select DECLARE c1 CURSOR FOR select * * from staff; from staff; 異常處理器聲明異常處理器聲明 DECLARE EXIT HANDLER FOR SQLEXCEPTION ;DECLARE EXIT HANDLER FOR SQLEXCEPTION ; -12- 賦值語句賦值語句 語法語法 SET lv_name = expressio

8、n;SET lv_name = expression; SET lv_name = NULL;SET lv_name = NULL; 示例示例 (1) SET salary = salary + salary (1) SET salary = salary + salary * * 0.1; 0.1; (2) SET init_salary = NULL;(2) SET init_salary = NULL; (3) SET salary = (select salary from employee where empno = lv_emp_num);(3) SET salary = (sel

9、ect salary from employee where empno = lv_emp_num); 注注: : 如果如果 SELECT SELECT 語句返回記錄超過一行，示例語句返回記錄超過一行，示例 3 3 將會(huì)返回將會(huì)返回SQLERRORSQLERROR。 -13- 存儲(chǔ)過程例子存儲(chǔ)過程例子 -14- 嵌套存儲(chǔ)過程例子嵌套存儲(chǔ)過程例子 -15- 模塊模塊(Module)(Module) 模塊是如下幾種對(duì)象的集合：模塊是如下幾種對(duì)象的集合： SPSP，UDFUDF，global variables and cursorsglobal variables and cursors，type

10、stypes，conditionsconditions 模塊的主要優(yōu)勢：模塊的主要優(yōu)勢： 結(jié)構(gòu)良好，便于組織結(jié)構(gòu)良好，便于組織 范圍限定范圍限定 CALL mySchema.myModule.myProc()CALL mySchema.myModule.myProc() 信息隱藏信息隱藏 每個(gè)對(duì)象都可以是每個(gè)對(duì)象都可以是 public public 或或 privateprivate 權(quán)限控制權(quán)限控制 可以模塊為單位，而不是以模塊中的對(duì)象為單位來控制權(quán)限可以模塊為單位，而不是以模塊中的對(duì)象為單位來控制權(quán)限 -16- 模塊模塊 - - 規(guī)格說明規(guī)格說明(Module Specification)

11、(Module Specification) 模塊可以發(fā)布模塊可以發(fā)布type, SP, UDFtype, SP, UDF以供外部使用。以供外部使用。 CREATE OR REPLACE MODULE myMod;CREATE OR REPLACE MODULE myMod; ALTER MODULE myMod PUBLISHALTER MODULE myMod PUBLISH TYPE myRowTyp AS ANCHOR ROW myTab; TYPE myRowTyp AS ANCHOR ROW myTab; ALTER MODULE myMod PUBLISHALTER MODULE

12、 myMod PUBLISH FUNCTION myFunc(val1 ANCHOR myTab.col1) FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTyp; RETURNS myRowTyp; ALTER MODULE myMod PUBLISHALTER MODULE myMod PUBLISH PROCEDURE myProc(OUT param1 ANCHOR myTab.col2); PROCEDURE myProc(OUT param1 ANCHOR myTab.col2); -17- 模塊模塊 - - 實(shí)現(xiàn)實(shí)現(xiàn)(M

13、odule Implementation)(Module Implementation) 下面的代碼是模塊的實(shí)現(xiàn)部分：下面的代碼是模塊的實(shí)現(xiàn)部分： ALTERALTER MODULEMODULE myModmyMod ADD VARIABLEADD VARIABLE pkgVarpkgVar ANCHORANCHOR myTab.col1;myTab.col1; ALTERALTER MODULEMODULE myModmyMod ADDADD FUNCTION FUNCTION myFunc(val1myFunc(val1 ANCHORANCHOR myTab.col1) myTab.col

14、1) RETURNSRETURNS myRowTypmyRowTyp BEGINBEGIN DECLARE DECLARE var1var1 myRowTyp;myRowTyp; SELECT SELECT * * INTOINTO var1 var1 FROMFROM myTab myTab WHEREWHERE col1col1 col1 pkgVar;pkgVar; RETURNRETURN var1;var1; ENDEND ALTERALTER MODULEMODULE myModmyMod ADDADD PROCEDURE PROCEDURE myProc(myProc(OUTOU

15、T param1param1 ANCHORANCHOR myTab.col2)myTab.col2) BEGINBEGIN DECLARE DECLARE varRowvarRow myRowTyp;myRowTyp; SETSET param1param1 = = varRow.col2varRow.col2 pkgVar;pkgVar; ENDEND -18- 模塊模塊 - - 其他語句其他語句 刪除整個(gè)模塊刪除整個(gè)模塊 DROP MODULE myMod;DROP MODULE myMod; 保留規(guī)格說明內(nèi)容，刪除實(shí)現(xiàn)保留規(guī)格說明內(nèi)容，刪除實(shí)現(xiàn) ALTER MODULE myMod DR

16、OP BODY;ALTER MODULE myMod DROP BODY; 刪除模塊中的存儲(chǔ)過程刪除模塊中的存儲(chǔ)過程(SP)(SP) ALTER MODULE myMod DROP PROCEDURE myProc;ALTER MODULE myMod DROP PROCEDURE myProc; 將模塊的執(zhí)行權(quán)限賦給將模塊的執(zhí)行權(quán)限賦給joejoe GRANT EXECUTE ON MODULE myMod TO joe;GRANT EXECUTE ON MODULE myMod TO joe; -19- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存

17、儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -20- IF IF語句語句 格式：格式： IF IF 條件1 THENTHEN statement1;statement1; ELSEIFELSEIF 條件2 THENTHEN statement2;statement2; ELSEELSE statement3;statement3; END END IF;IF; 注注: :條件成立時(shí)為條件成立時(shí)為TRUE (TRUE (真真), ),不成立時(shí)為不成立時(shí)為FALSE(FALSE(假假

18、) ) 和和 NULLNULL -21- IF IF語句例子語句例子 IF IF ratingrating = = 1 1 THENTHEN UPDATEUPDATE EMPLOYEE SETEMPLOYEE SET salarysalary = = salarysalary* *1.101.10 WHEREWHERE empnoempno = = i_num;i_num;（如果滿足于（如果滿足于.時(shí)，薪水調(diào)整時(shí)，薪水調(diào)整1.11.1倍）倍） ELSEIFELSEIF ratingrating = = 2 2 THENTHEN UPDATEUPDATE EMPLOYEE SETEMPLOYEE

19、 SET salarysalary = = salarysalary* *1.051.05 WHEREWHERE empnoempno = = i_num;i_num; ELSEELSE UPDATEUPDATE EMPLOYEE SETEMPLOYEE SET salarysalary = = salarysalary* *1.031.03 WHEREWHERE empnoempno = = i_num;i_num; ENDEND IF;IF; -22- CASECASE語句（語句（1 of 21 of 2） 簡單簡單CASECASE語句語句 -23- CASECASE語句（語句（2 of

20、22 of 2） 稍加變形的稍加變形的CASECASE語句語句 -24- LOOPLOOP語句語句 語法語法 LABEL LOOPLABEL LOOP SQL-procedure-statements; SQL-procedure-statements; END END LOOP LABEL;LOOP LABEL; 示例示例 fetch_loop: LOOPfetch_loop: LOOP FETCH c1 INTO v_firstname, v_lastname;c1 INTO v_firstname, v_lastname; SET counter = counter + 1; SET c

21、ounter = counter + 1; IF counter = 51 THEN IF counter = 51 THEN LEAVE fetch_loop; LEAVE fetch_loop; END IF; END IF; ENDEND LOOP fetch_loop;LOOP fetch_loop; 標(biāo)簽標(biāo)簽 關(guān)鍵字關(guān)鍵字 -25- FORFOR語句語句 語法語法 LABEL FOR for-loop-name AS cursor-name CURSOR FORLABEL FOR for-loop-name AS cursor-name CURSOR FOR select-state

22、ment select-statement DODO SQL-procedure-statements; SQL-procedure-statements; END END FOR LABEL; LABEL; 示例示例 DECLARE fullname CHAR(40);DECLARE fullname CHAR(40); FOR v1 AS c1 CURSOR FOR FOR v1 AS c1 CURSOR FOR SELECT firstnme, midinit, lastname FROM employeeSELECT firstnme, midinit, lastname FROM e

23、mployee DODO SET fullname=lastname|,|firstnme|,|midinit; SET fullname=lastname|,|firstnme|,|midinit; INSERT INTO tname VALUE (fullname); INSERT INTO tname VALUE (fullname); END FOR;END FOR; -26- 其他控制語句其他控制語句 REPEATREPEAT語句語句 ftch_loop2:ftch_loop2: REPEATREPEAT FETCH c1 INTO v_firstname, v_midinit, v

24、_lastname; FETCH c1 INTO v_firstname, v_midinit, v_lastname; UNTIL SQLCODE 0 AND REPEAT ftch_loop2;UNTIL SQLCODE 0 AND REPEAT ftch_loop2; WHILEWHILE語句語句 WHILE at_end = 0 DOWHILE at_end = 0 DO FETCH c1 INTO v_firstname, v_midinit, v_lastname; FETCH c1 INTO v_firstname, v_midinit, v_lastname; IF SQLCO

25、DE = 100 THEN IF SQLCODE = 100 THEN SET at_end = 1; SET at_end = 1; END IF; END IF; END WHILE;END WHILE; -27- LEAVELEAVE和和ITERATEITERATE語句語句 LEAVELEAVE和和ITERATEITERATE語句來控制循環(huán)語句來控制循環(huán) LEAVELEAVE語句用來跳出循環(huán)語句用來跳出循環(huán) ITERATEITERATE語句用來回到語句用來回到forfor或者或者whilewhile循環(huán)的開始重新執(zhí)行循環(huán)的開始重新執(zhí)行 示例示例 FETCH_LOOP1: LOOPFETC

26、H_LOOP1: LOOP FETCH c1 INTO v_dept, v_deptname, v_admdept; FETCH c1 INTO v_dept, v_deptname, v_admdept; IF at_end = 1 THEN IF at_end = 1 THEN LEAVE FETCH_LOOP1; LEAVE FETCH_LOOP1; ELSEIF v_dept = D01 THEN ELSEIF v_dept = D01 THEN ITERATE FETCH_LOOP1; ITERATE FETCH_LOOP1; END IF; END IF; INSERT INTO

27、department(deptno, deptname, admdept) INSERT INTO department(deptno, deptname, admdept) VALUES(NEW, v_deptname, v_admdept); VALUES(NEW, v_deptname, v_admdept); END LOOP FETCH_LOOP1;END LOOP FETCH_LOOP1; -28- GOTOGOTO語句語句 GOTOGOTO語句用于直接跳轉(zhuǎn)到指定標(biāo)簽處。例如：語句用于直接跳轉(zhuǎn)到指定標(biāo)簽處。例如： IF v_DEPT = D11IF v_DEPT = D11 GOT

28、O bye; GOTO bye; bye:bye: -29- RETURNRETURN語句語句 RETURNRETURN語句用于向調(diào)用返回。語句用于向調(diào)用返回。 IF v_DEPT = D11IF v_DEPT = D11 RETURN 1; RETURN 1; -30- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -31- 游標(biāo)的聲明游標(biāo)的聲明 下面是游標(biāo)聲明的幾個(gè)例子：下面是游標(biāo)聲明的幾個(gè)例

29、子： DECLARE c1 CURSOR FOR select DECLARE c1 CURSOR FOR select * * from staff; from staff; （DECLAREDECLARE關(guān)鍵字，關(guān)鍵字，clcl游標(biāo)名稱，游標(biāo)名稱， CURSORCURSOR是必須有的，；指通過是必須有的，；指通過c1c1的游標(biāo)來操作的游標(biāo)來操作staffstaff里所有的數(shù)據(jù)）最常里所有的數(shù)據(jù)）最常 用的最普通的。用的最普通的。 2.DECLARE c1 CURSOR WITH HOLD FOR select 2.DECLARE c1 CURSOR WITH HOLD FOR select

30、 * * form form staff;staff; 3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR 3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select select * * form staff; form staff; 1.1.4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR 4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select select * * form staff; form staff;

31、-32- 游標(biāo)的相關(guān)操作游標(biāo)的相關(guān)操作 打開游標(biāo)打開游標(biāo) OPEN OPEN 提取游標(biāo)提取游標(biāo) FETCH FETCH INTO INTO 關(guān)閉游標(biāo)關(guān)閉游標(biāo) CLOSE CLOSE -33- 游標(biāo)的遍歷游標(biāo)的遍歷 DECLARE at_end INT DEFAULT 0; DECLARE at_end INT DEFAULT 0; （聲明了（聲明了at_endat_end的變量，默認(rèn)值是的變量，默認(rèn)值是0 0） DECLARE PIID INTEGER DEFAULT 0;DECLARE PIID INTEGER DEFAULT 0; DECLARE PINT INTEGER DEFAULT

32、0;DECLARE PINT INTEGER DEFAULT 0; DECLARE DECLARE not_found not_found CONDITION CONDITIONFORFORSQLSTATESQLSTATE02000; 02000; DECLARE c1 CURSOR FOR SELECT IID FROM YH; DECLARE c1 CURSOR FOR SELECT IID FROM YH; （聲明了一個(gè)游標(biāo)，把聲明了一個(gè)游標(biāo)，把IIDIID的指標(biāo)拿出來）的指標(biāo)拿出來） DECLARE CONTINUE HANDLER FOR not_found SET at_end =

33、 1; DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; OPENOPENc1; c1; （進(jìn)行循環(huán)）（進(jìn)行循環(huán)） SET PCOUNT = 0; SET PCOUNT = 0; ins_loop: LOOPins_loop: LOOP FETCH c1 INTO PIID; FETCH c1 INTO PIID; IF IF at_endat_end 0 THEN 0 THEN LEAVE ins_loop; LEAVE ins_loop; （ LEAVELEAVE跳出循環(huán)）跳出循環(huán)） END IF; END IF; SET PCO

34、UNT = PCOUNT + 1; SET PCOUNT = PCOUNT + 1; （表示提取了多少條記錄）（表示提取了多少條記錄） END LOOP;END LOOP; -34- 刪除游標(biāo)對(duì)應(yīng)的數(shù)據(jù)行刪除游標(biāo)對(duì)應(yīng)的數(shù)據(jù)行 DECLAREDECLARE cursor1cursor1 CURSORCURSOR FOR FOR SELECTSELECT DEPTNO,DEPTNO, DEPTNAME,DEPTNAME, LOCATIONLOCATION FROMFROM DB2ADMIN.ORG FORDB2ADMIN.ORG FOR UPDATE;UPDATE;（聲明一個(gè)聲明一個(gè)cursor1

35、cursor1 的游標(biāo)，從一個(gè)表時(shí)提出部門名稱，的游標(biāo)，從一個(gè)表時(shí)提出部門名稱，.，位置），位置） OPENOPEN cursor1;cursor1;（打開游標(biāo)）（打開游標(biāo)） FETCHFETCH FROMFROM cursor1cursor1 INTOINTO v_DEPTNO,v_DEPTNO, V_DEPTNAME, v_LOCATION;V_DEPTNAME, v_LOCATION; DELETE FROM DB2ADMIN.ORG WHERE CURRENT OF cursor1;DELETE FROM DB2ADMIN.ORG WHERE CURRENT OF cursor1;（刪

36、除刪除DB2ADMIN.ORGDB2ADMIN.ORG的記錄；的記錄； CURRENT OF cursor1CURRENT OF cursor1這是的游標(biāo)是指向某一個(gè)位置；刪除游標(biāo)指向的當(dāng)前行。）這是的游標(biāo)是指向某一個(gè)位置；刪除游標(biāo)指向的當(dāng)前行。） CLOSE cursor1;CLOSE cursor1;（關(guān)閉游標(biāo)，也可做一個(gè)循環(huán)，刪除所有的內(nèi)容關(guān)閉游標(biāo)，也可做一個(gè)循環(huán)，刪除所有的內(nèi)容） -35- 更新游標(biāo)對(duì)應(yīng)的數(shù)據(jù)行更新游標(biāo)對(duì)應(yīng)的數(shù)據(jù)行 DECLAREDECLARE cursor1cursor1 CURSORCURSOR FOR FOR SELECTSELECT DEPTNO,DEPTNO,

37、 DEPTNAME,DEPTNAME, LOCATIONLOCATION FROMFROM DB2ADMIN.ORG DB2ADMIN.ORG FORFOR UPDATE;UPDATE; OPENOPEN cursor1;cursor1; FETCHFETCH FROMFROM cursor1cursor1 INTOINTO v_DEPTNO,v_DEPTNO, v_DEPTNAME, v_LOCATION;v_DEPTNAME, v_LOCATION; UPDATE DB2ADMIN.ORG SET DEPTNAME = NEW NAMEWHERE CURRENT OF UPDATE DB2

38、ADMIN.ORG SET DEPTNAME = NEW NAMEWHERE CURRENT OF cursor1;cursor1; CLOSE cursor1;CLOSE cursor1; -36- 使用游標(biāo)返回多個(gè)結(jié)果集使用游標(biāo)返回多個(gè)結(jié)果集 動(dòng)態(tài)的結(jié)果集兩個(gè)動(dòng)態(tài)的結(jié)果集兩個(gè) 處于打開的狀態(tài)？處于打開的狀態(tài)？ -37- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -38- 異常處理器的聲明異

39、常處理器的聲明 DECLARE handler-type HANDLER FOR condition handler-action;DECLARE handler-type HANDLER FOR condition handler-action; （語法結(jié)構(gòu)）（異常處理器是需要預(yù)先聲明的）（語法結(jié)構(gòu)）（異常處理器是需要預(yù)先聲明的） -39- 異常處理器的類型（異常處理器的類型（handler-typehandler-type） 異常處理器的類型有如下幾種：異常處理器的類型有如下幾種： CONTINUECONTINUE（繼續(xù)繼續(xù)） 在異常處理器操作完成之后，會(huì)繼續(xù)執(zhí)行產(chǎn)生這個(gè)異常語句之后的下一

40、條語句。在異常處理器操作完成之后，會(huì)繼續(xù)執(zhí)行產(chǎn)生這個(gè)異常語句之后的下一條語句。 EXITEXIT（記錄完后就退出，不再繼續(xù)執(zhí)行記錄完后就退出，不再繼續(xù)執(zhí)行） 在異常處理器操作完成之后，存儲(chǔ)過程會(huì)終止，并將控制返回給調(diào)用者。在異常處理器操作完成之后，存儲(chǔ)過程會(huì)終止，并將控制返回給調(diào)用者。 UNDOUNDO（撤銷所做的記錄，退出整個(gè)程序撤銷所做的記錄，退出整個(gè)程序） 在異常處理器操作執(zhí)行之前，在異常處理器操作執(zhí)行之前，DB2DB2會(huì)回滾存儲(chǔ)過程中執(zhí)行的會(huì)回滾存儲(chǔ)過程中執(zhí)行的SQLSQL操作。在異常處理器操作。在異常處理器 操作完成之后，存儲(chǔ)過程會(huì)終止，并將控制返回給調(diào)用者。操作完成之后，存儲(chǔ)過程會(huì)

41、終止，并將控制返回給調(diào)用者。 -40- 異常處理器和異常處理器和SQLSTATESQLSTATE 異常處理器可以處理基于特定異常處理器可以處理基于特定SQLSTATESQLSTATE值的定制異常，或者處理預(yù)定值的定制異常，或者處理預(yù)定 義異常的類。預(yù)定義的義異常的類。預(yù)定義的3 3種異常如下所示：種異常如下所示： NOT FOUNDNOT FOUND 標(biāo)識(shí)導(dǎo)致標(biāo)識(shí)導(dǎo)致SQLCODESQLCODE值為值為100100或者或者SQLSATESQLSATE值為值為0200002000的異常。這個(gè)異常通的異常。這個(gè)異常通 常在常在SELECTSELECT沒有返回行的時(shí)候出現(xiàn)。沒有返回行的時(shí)候出現(xiàn)。 S

42、QLEXCEPTIONSQLEXCEPTION 標(biāo)識(shí)導(dǎo)致標(biāo)識(shí)導(dǎo)致SQLCODESQLCODE值為負(fù)的異常。值為負(fù)的異常。 SQLWARNINGSQLWARNING 標(biāo)識(shí)導(dǎo)致警告異?；蛘邔?dǎo)致標(biāo)識(shí)導(dǎo)致警告異?；蛘邔?dǎo)致100100以外的以外的SQLCODESQLCODE正值的異常。正值的異常。 注注: :如果產(chǎn)生了如果產(chǎn)生了NOT FOUND NOT FOUND 或者或者SQLWARNINGSQLWARNING異常，并且沒有為這個(gè)異常定義異常處理器，異常，并且沒有為這個(gè)異常定義異常處理器， 那么就會(huì)忽略這個(gè)異常，并且將控制流轉(zhuǎn)向下一個(gè)語句。如果產(chǎn)生了那么就會(huì)忽略這個(gè)異常，并且將控制流轉(zhuǎn)向下一個(gè)語句。

43、如果產(chǎn)生了SQLEXCEPTIONSQLEXCEPTION異常，異常， 并且沒有為這個(gè)異常定義異常處理器，那么存儲(chǔ)過程就會(huì)失敗，并且會(huì)將控制流返回調(diào)用并且沒有為這個(gè)異常定義異常處理器，那么存儲(chǔ)過程就會(huì)失敗，并且會(huì)將控制流返回調(diào)用 者。者。 -41- 異常處理器示例異常處理器示例 如下示例聲明了兩個(gè)異常處理器。如下示例聲明了兩個(gè)異常處理器。 EXITEXIT處理器會(huì)在出現(xiàn)處理器會(huì)在出現(xiàn)SQLEXCEPTION SQLEXCEPTION 或者或者 SQLWARNINGSQLWARNING異常的時(shí)候被調(diào)用。異常的時(shí)候被調(diào)用。EXITEXIT處理器會(huì)在終止處理器會(huì)在終止SQLSQL程序之前，將名為程序

44、之前，將名為 stmtstmt的變量設(shè)為的變量設(shè)為“ABORTED”,“ABORTED”,并且將控制流返回給調(diào)用者。并且將控制流返回給調(diào)用者。UNDOUNDO處理器會(huì)將處理器會(huì)將 控制流返回給調(diào)用者之前，回滾存儲(chǔ)過程體中已經(jīng)完成的控制流返回給調(diào)用者之前，回滾存儲(chǔ)過程體中已經(jīng)完成的SQLSQL操作。操作。 DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = ABORTED; SET stmt = ABORTED; DECLARE

45、 UNDO HANDLER FOR NOT FOUND; DECLARE UNDO HANDLER FOR NOT FOUND; -42- 異常處理器定制異常處理器定制 如果預(yù)定義異常集不能滿足需求，就可以為特定的如果預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATESQLSTATE值聲明定值聲明定 制異常，然后再為這個(gè)定制異常聲明處理器。語法如下：制異常，然后再為這個(gè)定制異常聲明處理器。語法如下： DECLARE unique-name CONDITION FOR SQLSATE sqlstate DECLARE unique-name CONDITION FOR SQLSATE sq

46、lstate -43- 更為復(fù)雜的異常處理器示例（更為復(fù)雜的異常處理器示例（1 of 21 of 2） - Generic Handler - Generic Handler DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN NOT ATOMIC BEGIN NOT ATOMIC - Capture SQLCODE FROM SYSIBM.SYSDUMMY1; - Use t

47、he poGenStatus variable to tell the procedure - what type of error occurred - Use the poGenStatus variable to tell the procedure - what type of error occurred CASE hSqlstate CASE hSqlstate WHEN 02000 THEN WHEN 02000 THEN SET poGenStatus=5000; SET poGenStatus=5000; WHEN 42724 THEN WHEN 42724 THEN SET

48、 poGenStatus=3; SET poGenStatus=3; ELSE IF (hSqlCode 0) THEN ELSE IF (hSqlCode 0) THEN SET poGenStatus=hSqlCode; SET poGenStatus=hSqlCode; END IF; END IF; END END -44- 更為復(fù)雜的異常處理器示例（更為復(fù)雜的異常處理器示例（2 of 22 of 2） 上面的異常處理器會(huì)在出現(xiàn)上面的異常處理器會(huì)在出現(xiàn)SQLEXCEPTION, SQLWARNING, NOT SQLEXCEPTION, SQLWARNING, NOT FOUNDFOUND異常的時(shí)候觸發(fā)。異常處理器會(huì)取出當(dāng)前的異常的時(shí)候觸發(fā)。異常處理器會(huì)取出當(dāng)前的SQLCODE, SQLCODE, SQLSTATESQLSTATE，然后根據(jù)它們的值來設(shè)置輸出參數(shù)（，然后根據(jù)它們的值來設(shè)置輸出參數(shù)（poGenStatuspoGenStatus）的值。）的值。 -45- 主要內(nèi)容：主要內(nèi)容： 1 1、存儲(chǔ)過程介紹、存儲(chǔ)過程介紹 2 2、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu)、存儲(chǔ)過程基礎(chǔ)結(jié)構(gòu) 3 3、控制語句、控制語句 4 4、游標(biāo)和結(jié)果集、游標(biāo)和結(jié)果集 5 5、異常處理器、異常處理器 6 6、編寫和調(diào)試存儲(chǔ)過程、編寫和調(diào)試存儲(chǔ)過程 -46- 使用使用ODSODS開發(fā)存儲(chǔ)過程的基本步驟開發(fā)存儲(chǔ)過