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

1、中國挪動通訊集團(tuán)河南業(yè)務(wù)援助中心DB2數(shù)據(jù)庫中的存儲過程主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程什么是存儲過程？(Stored Procedure)受 DB2 效力器控制的一段可執(zhí)行程序可以經(jīng)過SQL的CALL語句來完成對存儲過程的調(diào)用在存儲過程中可以包含業(yè)務(wù)邏輯存儲過程可以在本地或遠(yuǎn)程進(jìn)展調(diào)用存儲過程可以接納或傳送參數(shù)，生成結(jié)果集什么時候運(yùn)用存儲過程？運(yùn)用存儲過程的適宜時機(jī):運(yùn)用程序的性能無法滿足預(yù)期時客戶端數(shù)量較多且運(yùn)用程序中SQL代碼分散時運(yùn)用程序需求進(jìn)展繁重的數(shù)據(jù)庫操作，同時這些操作并不需求進(jìn)展太多的客戶交互運(yùn)用程序

2、代碼更改頻繁需求對客戶運(yùn)用代碼進(jìn)展訪問控制時客戶運(yùn)用需求在一次操作中執(zhí)行多條 SQL 語句C/S: 宏觀交互圖主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程存儲過程構(gòu)造CREATE OR REPLACE PROCEDURE ( IN | OUT | INOUT 參數(shù)名 數(shù)據(jù)類型 默許值 )LANGUAGE SQLBEGIN 業(yè)務(wù)邏輯代碼END;參數(shù)類型IN輸入?yún)?shù)只是將實(shí)參傳送給存儲過程，但在存儲過程中不能對其進(jìn)展修正。換句話說，對于存儲過程而言它是只讀的。OUT輸出參數(shù)在存儲過程終了時向調(diào)用者前往。普通在過程中都會被賦值。INO

3、UT輸入輸出參數(shù)上述兩種參數(shù)類型的結(jié)合體。它可以協(xié)助調(diào)用者將實(shí)參傳送給進(jìn)程，另外它也可以作為輸出參數(shù)被修正和賦值。復(fù)合語句 復(fù)合語句是指包含在BEGIN和END間的語句。它普通包括如下語句類型：聲明語句賦值語句控制語句條件處置語句復(fù)合語句例如闡明：1. 復(fù)合語句可以嵌套運(yùn)用。2. BEGIN語句可以和標(biāo)簽組合運(yùn)用，這樣可以更明晰的標(biāo)識語句塊的范圍。聲明語句變量聲明DECLARE my_var INTEGER DEFAULT 6;條件聲明DECLARE not_found CONDITION FOR SQLSTATE 02000;游標(biāo)聲明DECLARE c1 CURSOR FOR select

4、* from staff;異常處置器聲明DECLARE EXIT HANDLER FOR SQLEXCEPTION ;賦值語句語法SET lv_name = expression;SET lv_name = NULL;例如(1) SET salary = salary + salary * 0.1;(2) SET init_salary = NULL;(3) SET salary = (select salary from employee where empno = lv_emp_num);注: 假設(shè) SELECT 語句前往記錄超越一行，例如 3 將會前往SQLERROR。存儲過程例子嵌套存

5、儲過程例子模塊(Module)模塊是如下幾種對象的集合：SP，UDF，global variables and cursors，types，conditions模塊的主要優(yōu)勢：構(gòu)造良好，便于組織范圍限定CALL mySchema.myModule.myProc()信息隱藏每個對象都可以是 public 或 private權(quán)限控制可以模塊為單位，而不是以模塊中的對象為單位來控制權(quán)限模塊 - 規(guī)格闡明(Module Specification)模塊可以發(fā)布type, SP, UDF以供外部運(yùn)用。CREATE OR REPLACE MODULE myMod;ALTER MODULE myMod PU

6、BLISH TYPE myRowTyp AS ANCHOR ROW myTab;ALTER MODULE myMod PUBLISH FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTyp;ALTER MODULE myMod PUBLISH PROCEDURE myProc(OUT param1 ANCHOR myTab.col2);模塊 - 實(shí)現(xiàn)(Module Implementation)下面的代碼是模塊的實(shí)現(xiàn)部分：ALTER MODULE myMod ADD VARIABLE pkgVar ANCHOR myTab.col1;A

7、LTER MODULE myMod ADD FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTypBEGIN DECLARE var1 myRowTyp; SELECT * INTO var1 FROM myTab WHERE col1 pkgVar;RETURN var1;ENDALTER MODULE myMod ADD PROCEDURE myProc(OUT param1 ANCHOR myTab.col2)BEGIN DECLARE varRow myRowTyp;SET param1 = varRow.col2 pkgVar

8、;END模塊 - 其他語句刪除整個模塊DROP MODULE myMod;保管規(guī)格闡明內(nèi)容，刪除實(shí)現(xiàn)ALTER MODULE myMod DROP BODY;刪除模塊中的存儲過程(SP)ALTER MODULE myMod DROP PROCEDURE myProc;將模塊的執(zhí)行權(quán)限賦給joeGRANT EXECUTE ON MODULE myMod TO joe;主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程IF語句格式：IF 條件1 THEN statement1;ELSEIF 條件2 THEN statement2;ELSE

9、 statement3; END IF;注:條件成立時為TRUE (真),不成立時為FALSE(假) 和 NULLIF語句例子IF rating = 1 THENUPDATE EMPLOYEE SET salary = salary*1.10 WHERE empno = i_num;假設(shè)滿足于.時，薪水調(diào)整1.1倍ELSEIF rating = 2 THENUPDATE EMPLOYEE SET salary = salary*1.05 WHERE empno = i_num;ELSEUPDATE EMPLOYEE SET salary = salary*1.03 WHERE empno =

10、i_num;END IF;CASE語句1 of 2簡單CASE語句CASE語句2 of 2稍加變形的CASE語句LOOP語句語法LABEL LOOP SQL-procedure-statements; END LOOP LABEL;例如fetch_loop: LOOPFETCH c1 INTO v_firstname, v_lastname; SET counter = counter + 1; IF counter = 51 THEN LEAVE fetch_loop; END IF;END LOOP fetch_loop;標(biāo)簽關(guān)鍵字FOR語句語法LABEL FOR for-loop-nam

11、e AS cursor-name CURSOR FOR select-statement DO SQL-procedure-statements; END FOR LABEL;例如DECLARE fullname CHAR(40);FOR v1 AS c1 CURSOR FOR SELECT firstnme, midinit, lastname FROM employeeDO SET fullname=lastname|,|firstnme|,|midinit; INSERT INTO tname VALUE (fullname);END FOR;其他控制語句REPEAT語句ftch_loo

12、p2:REPEAT FETCH c1 INTO v_firstname, v_midinit, v_lastname;UNTIL SQLCODE 0 AND REPEAT ftch_loop2;WHILE語句WHILE at_end = 0 DO FETCH c1 INTO v_firstname, v_midinit, v_lastname; IF SQLCODE = 100 THEN SET at_end = 1; END IF;END WHILE;LEAVE和ITERATE語句LEAVE和ITERATE語句來控制循環(huán)LEAVE語句用來跳出循環(huán)ITERATE語句用來回到for或者while

13、循環(huán)的開場重新執(zhí)行例如FETCH_LOOP1: LOOP FETCH c1 INTO v_dept, v_deptname, v_admdept; IF at_end = 1 THEN LEAVE FETCH_LOOP1; ELSEIF v_dept = D01 THEN ITERATE FETCH_LOOP1; END IF; INSERT INTO department(deptno, deptname, admdept) VALUES(NEW, v_deptname, v_admdept);END LOOP FETCH_LOOP1;GOTO語句GOTO語句用于直接跳轉(zhuǎn)到指定標(biāo)簽處。例如：

14、IF v_DEPT = D11 GOTO bye;bye:RETURN語句RETURN語句用于向調(diào)用前往。IF v_DEPT = D11 RETURN 1;主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程游標(biāo)的聲明下面是游標(biāo)聲明的幾個例子：DECLARE c1 CURSOR FOR select * from staff;DECLARE關(guān)鍵字，cl游標(biāo)稱號， CURSOR是必需有的，；指經(jīng)過c1的游標(biāo)來操作staff里一切的數(shù)據(jù)最常用的最普通的。2.DECLARE c1 CURSOR WITH HOLD FOR select * f

15、orm staff;3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form staff;4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * form staff;游標(biāo)的相關(guān)操作翻開游標(biāo)OPEN 提取游標(biāo)FETCH INTO 封鎖游標(biāo)CLOSE 游標(biāo)的遍歷DECLARE at_end INT DEFAULT 0; 聲明了at_end的變量，默許值是0DECLARE PIID INTEGER DEFAULT 0;DECLARE PINT INTEGER DEFAULT 0;DE

16、CLARE not_found CONDITIONFORSQLSTATE02000; DECLARE c1 CURSOR FOR SELECT IID FROM YH; 聲明了一個游標(biāo)，把IID的目的拿出來DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; OPENc1; 進(jìn)展循環(huán)SET PCOUNT = 0; ins_loop: LOOP FETCH c1 INTO PIID; IF at_end 0 THEN LEAVE ins_loop; LEAVE跳出循環(huán) END IF; SET PCOUNT = PCOUNT + 1; 表示提

17、取了多少條記錄END LOOP;刪除游標(biāo)對應(yīng)的數(shù)據(jù)行DECLARE cursor1 CURSOR FOR SELECT DEPTNO, DEPTNAME, LOCATION FROM DB2ADMIN.ORG FOR UPDATE;聲明一個cursor1的游標(biāo)，從一個表時提出部門稱號，.，位置OPEN cursor1;翻開游標(biāo)FETCH FROM cursor1 INTO v_DEPTNO, V_DEPTNAME, v_LOCATION;DELETE FROM DB2ADMIN.ORG WHERE CURRENT OF cursor1;刪除DB2ADMIN.ORG的記錄； CURRENT OF

18、 cursor1這是的游標(biāo)是指向某一個位置；刪除游標(biāo)指向的當(dāng)前行。CLOSE cursor1;封鎖游標(biāo)，也可做一個循環(huán)，刪除一切的內(nèi)容更新游標(biāo)對應(yīng)的數(shù)據(jù)行DECLARE cursor1 CURSOR FOR SELECT DEPTNO, DEPTNAME, LOCATION FROM DB2ADMIN.ORG FOR UPDATE;OPEN cursor1;FETCH FROM cursor1 INTO v_DEPTNO, v_DEPTNAME, v_LOCATION;UPDATE DB2ADMIN.ORG SET DEPTNAME = NEW NAMEWHERE CURRENT OF cur

19、sor1;CLOSE cursor1;運(yùn)用游標(biāo)前往多個結(jié)果集動態(tài)的結(jié)果集兩個處于翻開的形狀？主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程異常處置器的聲明DECLARE handler-type HANDLER FOR condition handler-action; 語法構(gòu)造異常處置器是需求預(yù)先聲明的異常處置器的類型handler-type異常處置器的類型有如下幾種：CONTINUE繼續(xù)在異常處置器操作完成之后，會繼續(xù)執(zhí)行產(chǎn)生這個異常語句之后的下一條語句。 EXIT記錄完后就退出，不再繼續(xù)執(zhí)行在異常處置器操作完成之后，存儲過程

20、會終止，并將控制前往給調(diào)用者。 UNDO撤銷所做的記錄，退出整個程序在異常處置器操作執(zhí)行之前，DB2會回滾存儲過程中執(zhí)行的SQL操作。在異常處置器操作完成之后，存儲過程會終止，并將控制前往給調(diào)用者。 異常處置器和SQLSTATE 異常處置器可以處置基于特定SQLSTATE值的定制異常，或者處置預(yù)定義異常的類。預(yù)定義的3種異常如下所示： NOT FOUND標(biāo)識導(dǎo)致SQLCODE值為100或者SQLSATE值為02000的異常。這個異常通常在SELECT沒有前往行的時候出現(xiàn)。 SQLEXCEPTION標(biāo)識導(dǎo)致SQLCODE值為負(fù)的異常。 SQLWARNING標(biāo)識導(dǎo)致警告異?；蛘邔?dǎo)致100以外的SQ

21、LCODE正值的異常。 注:假設(shè)產(chǎn)生了NOT FOUND 或者SQLWARNING異常，并且沒有為這個異常定義異常處置器，那么就會忽略這個異常，并且將控制流轉(zhuǎn)向下一個語句。假設(shè)產(chǎn)生了SQLEXCEPTION異常，并且沒有為這個異常定義異常處置器，那么存儲過程就會失敗，并且會將控制流前往調(diào)用者。 異常處置器例如 如下例如聲明了兩個異常處置器。 EXIT處置器會在出現(xiàn)SQLEXCEPTION 或者SQLWARNING異常的時候被調(diào)用。EXIT處置器會在終止SQL程序之前，將名為stmt的變量設(shè)為“ABORTED,并且將控制流前往給調(diào)用者。UNDO處置器會將控制流前往給調(diào)用者之前，回滾存儲過程體中曾

22、經(jīng)完成的SQL操作。 DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = ABORTED; DECLARE UNDO HANDLER FOR NOT FOUND; 異常處置器定制 假設(shè)預(yù)定義異常集不能滿足需求，就可以為特定的SQLSTATE值聲明定制異常，然后再為這個定制異常聲明處置器。語法如下： DECLARE unique-name CONDITION FOR SQLSATE sqlstate 更為復(fù)雜的異常處置器例如1 of 2 - Generic Handler DECLARE CONTINUE HANDLER FO

23、R SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN NOT ATOMIC - Capture SQLCODE & SQLSTATE SELECT SQLCODE, SQLSTATE INTO hSqlcode, hSqlstate FROM SYSIBM.SYSDUMMY1; - Use the poGenStatus variable to tell the procedure - what type of error occurred CASE hSqlstate WHEN 02000 THEN SET poGenStatus=5000; WHEN 42

24、724 THEN SET poGenStatus=3; ELSE IF (hSqlCode 0) THEN SET poGenStatus=hSqlCode; END IF; END 更為復(fù)雜的異常處置器例如2 of 2 上面的異常處置器會在出現(xiàn)SQLEXCEPTION, SQLWARNING, NOT FOUND異常的時候觸發(fā)。異常處置器會取出當(dāng)前的SQLCODE, SQLSTATE，然后根據(jù)它們的值來設(shè)置輸出參數(shù)poGenStatus的值。 主要內(nèi)容：1、存儲過程引見2、存儲過程根底構(gòu)造3、控制語句4、游標(biāo)和結(jié)果集5、異常處置器6、編寫和調(diào)試存儲過程運(yùn)用ODS開發(fā)存儲過程的根本步驟創(chuàng)建存儲過程將存儲過程部署到本地或遠(yuǎn)程DB2數(shù)據(jù)庫修正并重新部署存儲過程對存儲過程進(jìn)展測試和Debug啟動ODSODS任務(wù)臺創(chuàng)建數(shù)據(jù)庫銜接 (1 of 2)創(chuàng)建數(shù)據(jù)庫銜接 (2 of 2)創(chuàng)建一個新的工程 (1 of 3)創(chuàng)建一個新的工程 (2 of 3)創(chuàng)建一個新的工程 (3 of 3)新建一個存儲過程指定運(yùn)用言語類型設(shè)定存儲過程運(yùn)用的SQL語句設(shè)定SQL語句的類型和構(gòu)建方式SQL語句構(gòu)造器選擇用于構(gòu)造SQL語句的對象調(diào)整SQL語句運(yùn)轉(zhuǎn)調(diào)整后的SQL語句