DB2存儲過程培訓(xùn)ppt課件

1、DB2存儲過程根底培訓(xùn)2021年7月28日內(nèi)容提要數(shù)據(jù)類型運用存儲過程的優(yōu)點儲存過程的構(gòu)造參數(shù)定義變量定義賦值語句條件控制語句循環(huán)語句常用操作符異常處置游標(biāo)運用動態(tài)游標(biāo)運用SESSION暫時表運用2數(shù)據(jù)類型定長型字符串CHAR變長型字符串VARCHAR整數(shù)類型SMALLINT、INTEGER、BIGINT帶小數(shù)點的數(shù)字類型DECIMAL、REAL、DOUBLE時間類型DATE、TIME、TIMESTAMP對象類型BLOB、CLOB、DBCLOB3運用儲存過程優(yōu)點減少客戶機與效力器之間的網(wǎng)絡(luò)運用率?？蛻魴C運用程序?qū)⒖刂茩?quán)傳送到數(shù)據(jù)庫效力器上的存儲過程。存儲過程在數(shù)據(jù)庫效力器上執(zhí)行中間處置，而不需

2、求在網(wǎng)絡(luò)中傳送不需求的數(shù)據(jù)。 提高平安性。經(jīng)過使運用靜態(tài) SQL 的存儲過程包含數(shù)據(jù)庫特權(quán)，數(shù)據(jù)庫管理員DBA可以提高平安性。調(diào)用存儲過程的客戶機運用程序的用戶不需求數(shù)據(jù)庫特權(quán)。 提高可靠性。在數(shù)據(jù)庫運用程序環(huán)境中，許多義務(wù)是反復(fù)的。經(jīng)過重用一個公共過程，存儲過程就可以高效地處理這些反復(fù)情況。4存儲過程構(gòu)造存儲過程構(gòu)造如下：CREATE PROCEDURE SP_STAFF (IN SAL INT ) DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE cur1 CURSOR WITH RETURN FOR SELEC

3、T name, dept, job,salary FROM staff WHERE salary SAL; OPEN cur1; END;5參數(shù)定義1DB2儲存過程的參數(shù)分為兩部分：輸入、輸出參數(shù)和性能相關(guān)參數(shù)。輸入、輸出參數(shù)表示方式:輸入?yún)?shù)用IN開頭輸出參數(shù)用OUT開頭既是輸入又是輸出參數(shù)用INOUT開頭舉例闡明：create procedure sp_sample (in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20)6參數(shù)定義2創(chuàng)建存儲過程語句CREATE PROCEDURE可以包含很多參數(shù)，雖然從語法角度講它們

4、不是必需的，但是在創(chuàng)建存儲過程時提供它們可以提高執(zhí)行效率。下面是一些常用的參數(shù) 允許 SQL allowedSQL 允許 SQL allowedSQL子句的值指定了存儲過程能否會運用 SQL 語句，假設(shè)運用，其類型如何。它的能夠值如下所示：7參數(shù)定義3NO SQL： 表示存儲過程不可以執(zhí)行任何 SQL 語句。 CONTAINS SQL： 表示存儲過程可以執(zhí)行 SQL 語句，但不會讀取 SQL 數(shù)據(jù)，也不會修正 SQL 數(shù)據(jù)。 READS SQL DATA： 表示在存儲過程中包含不會修正 SQL 數(shù)據(jù)的 SQL 語句。也就是說該儲存過程只從數(shù)據(jù)庫中讀取數(shù)據(jù)。 MODIFIES SQL DATA：

5、 表示存儲過程可以執(zhí)行任何 SQL 語句。即可以對數(shù)據(jù)庫中的數(shù)據(jù)進展添加、刪除和修正。假設(shè)沒有明確聲明 allowed-SQL，其默許值是 MODIFIES SQL DATA。不同類型的存儲過程執(zhí)行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。假設(shè)存儲過程只是讀取數(shù)據(jù)，但是由于沒有聲明 allowed-SQL 使其被當(dāng)作對數(shù)據(jù)進展修正的存儲過程來執(zhí)行，這顯然會降低程序的執(zhí)行效率。因此創(chuàng)建存儲過程時，該當(dāng)明確聲明其 allowed-SQL。8參數(shù)定義4前往結(jié)果集個數(shù)DYNAMIC RESULT SETS n 存儲過程可以前往 0 個或者多個結(jié)果集。為了從

6、存儲過程中前往結(jié)果集，需求執(zhí)行如下步驟： 在 CREATE PROCEDURE 語句的 DYNAMIC RESULT SETS 子句中聲明存儲過程將要前往的結(jié)果集的數(shù)量number-of-result-sets。假設(shè)這里聲明的前往結(jié)果集的數(shù)量小于存儲過程中實踐前往的結(jié)果集數(shù)量，在執(zhí)行該存儲過程的時候，DB2 會前往一個警告。 如下存儲過程就會前往警告：9參數(shù)定義5CREATE PROCEDURE RESULT_SET ( ) DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE cur1 CURSOR WITH RETU

7、RN FOR SELECT name, dept, job,salary FROM staff WHERE salary 20000; DECLARE cur2 CURSOR WITH RETURN FOR SELECT name, dept, job,salary FROM staff WHERE salary 20000; OPEN cur1; OPEN cur2;END;10變量定義存儲過程中可以運用關(guān)鍵字DECLARE定義變量，然后在后續(xù)程序過程中運用變量來處置邏輯。定義變量時可以指定一個初始值。舉例闡明：DECLARE temp1 SMALLINT DEFAULT 0;DECLARE

8、 temp2 INTEGER DEFAULT 10;DECLARE temp3 DECIMAL(10,2) DEFAULT 100.10;DECLARE temp4 REAL DEFAULT 10.1;DECLARE temp5 DOUBLE DEFAULT 10000.1001;DECLARE temp6 BIGINT DEFAULT 10000;DECLARE temp7 CHAR(10) DEFAULT yes;DECLARE temp8 VARCHAR(10) DEFAULT hello;DECLARE temp9 DATE DEFAULT 1998-12-25;DECLARE tem

9、p10 TIME DEFAULT 1:50 PM;DECLARE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;DECLARE temp12 CLOB(2G);DECLARE temp13 BLOB(2G);11賦值語句存儲過程運用關(guān)鍵字SET給變量賦值。舉例闡明:SET total = 100;VALUES(100,200,200+1) INTO var1,var2,var3; /*并行賦值，效率高*/SET total = NULL;SET total = (select sum(c1) from T1);SET sch = CURRENT SC

10、HEMA;12條件控制語句2CASEWHEN舉例闡明：CASEWHEN v_workdept = A00 THEN UPDATE department SET deptname = DATA ACCESS 1; WHEN v_workdept = B01 THEN UPDATE department SET deptname = DATA ACCESS 2; ELSE UPDATE department SET deptname = DATA ACCESS 3; END CASE 13循環(huán)語句1循環(huán)語句包括以下幾種：WHILE舉例闡明：WHILE v_counter (v_numRecords

11、 / 2 + 1) DO SET v_salary1 = v_salary2; SET v_counter = v_counter + 1;END WHILE;14循環(huán)語句2LOOP舉例闡明:LOOPFETCH c1 INTO v_firstnme, v_midinit, v_lastname; - Use a local variable for the iterator variable - because SQL procedures only allow you to assign - values to an OUT parameter SET v_counter = v_count

12、er + 1; IF v_midinit = THEN LEAVE fetch_loop; END IF;END LOOP fetch_loop;15循環(huán)語句3FOR舉例闡明：CREATE PROCEDURE Concat_names()LANGUAGE SQLBEGIN - Note: implicit cursor manipulation DECLARE fullname CHAR(140); FOR v1 AS SELECT firstnme, midinit, lastname FROM employee DO SET fullname = v1.lastname | , | v1.

13、firstnme | | v1.midinit; INSERT INTO tname VALUES (fullname); END FOR;END16常用操作符常用操作符有以下幾種：關(guān)系運算符關(guān)系運算符有六種：小于、小于等于、大于、大于等于、等于、不等于邏輯運算符邏輯運算符有三種：AND、OR、NOT17異常處置1任何SQL語句執(zhí)行假設(shè)發(fā)生SQLSTATE00000的情況都能夠喚起condition，可以是通用的conditions：SQLWARNING, SQLEXCEPTION, NOT FOUND，如： DECLARE not_found CONDITION FOR NOT FOUND;

14、也可以是指定SQLSTATE的conditions，如： DECLARE trunc CONDITION FOR SQLSTATE 01004;留意： 為防止未預(yù)見的出錯情況出現(xiàn)后被忽略，應(yīng)盡量防止定義SQLEXCEPTION CONDITION，而應(yīng)針對詳細(xì)的SQLSTATE定義CONDITION。18異常處置2CONDITION HANDLE的定義： BEGIN DECLARE HANDLER FOR 喚醒conditionsCONTINUE點statement_1;statement_2;EXIT或UNDO點statement_3; END定義出錯處置動作：CONTINUE，EXIT或U

15、NDO。為曾經(jīng)定義的condition名或是直接的通用conditions，可以是多個是一條或多條語句，可以包含控制語句19異常處置3例子：DECLARE CONTINUE HANDLER FOR not_found, SQLEXCEPTION SET at_end = 1;DECLARE CONTINUE HANDLER FOR trunc BEGIN SET truncated = 1; SET msg=message; END;留意： 假設(shè)SQL PROCEDURE語句執(zhí)行后SQLSTATE=02000或SQLSTATE=01xxx,引起SQLWARNING或NOT FOUND條件，且定

16、義了相應(yīng)條件的handler，那么DB2將控制交給相應(yīng)handler；假設(shè)未定義handler，那么DB2設(shè)SQLSTATE及SQLCODE值并繼續(xù)運轉(zhuǎn)。20異常處置4假設(shè)SQL PROCEDURE語句執(zhí)行后出錯，引起SQLEXCEPTION條件，且定義了相應(yīng)條件的handler，那么DB2將控制交給相應(yīng)handler，假設(shè)handler運轉(zhuǎn)勝利，那么SQLCODE及SQLSTATE重置為0及00000；假設(shè)未定義handler，那么DB2中止PROCEDURE并前往CLIENT。需求留意的是，任何語句的勝利執(zhí)行都會將SQLCODE、SQLSTATE重置為0,00000。假設(shè)需求截獲出錯代碼，

17、獨一的方法是在handler的第一條語句將其中的一個值保管在變量中，如： DECLARE CONTINUE HANDLER for SQLEXCEPTION SET Saved_SQLCODE = SQLCODE;假設(shè)PROCEDURE中需求向客戶端前往用戶錯誤信息，可運用SIGNAL： SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=找不到用戶記錄MESSAGE_TEXT也可以是一個字符串變量，此功能可以用來調(diào)試存儲過程。21游標(biāo)運用1游標(biāo)有兩種類型：靜態(tài)的和動態(tài)的。運用游標(biāo)前要先定義，然后可以運用循環(huán)語句操作游標(biāo)。舉例闡明：CREATE PROCEDURE l

18、eave_loop(OUT counter INT)LANGUAGE SQLBEGIN DECLARE SQLSTATE CHAR(5); DECLARE v_firstnme VARCHAR(12); DECLARE v_midinit CHAR(1); DECLARE v_lastname VARCHAR(15); DECLARE v_counter SMALLINT DEFAULT 0; DECLARE at_end SMALLINT DEFAULT 0; DECLARE not_found CONDITION for SQLSTATE 02000;22游標(biāo)運用2DECLARE c1 C

19、URSOR FOR SELECT firstnme, midinit, lastname FROM employee; DECLARE CONTINUE HANDLER for not_found SET at_end = 1; - initialize OUT parameter SET counter = 0; OPEN c1; fetch_loop: LOOP FETCH c1 INTO v_firstnme, v_midinit, v_lastname; IF at_end 0 THEN LEAVE fetch_loop; END IF;23游標(biāo)運用3- Use a local var

20、iable for the iterator variable- because SQL procedures only allow you to assign - values to an OUT parameter SET v_counter = v_counter + 1; END LOOP fetch_loop; CLOSE c1;- Now assign the value of the local - variable to the OUT parameter SET counter = v_counter;END 24動態(tài)游標(biāo)運用動態(tài)游標(biāo)運用起來比較方便靈敏，在存儲過程中會經(jīng)常用

21、到，也引薦運用動態(tài)游標(biāo)處置邏輯。舉例闡明：CREATE PROCEDURE Dynamic_Cursor (IN SAL INT) DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE stmt varchar(255); DECLARE st STATEMENT; DECLARE cur1 CURSOR WITH RETURN FOR st; SET stmt = SELECT name, dept, job,salary FROM staff WHERE salary ?; PREPARE st FROM stmt;

22、 OPEN cur1 USING SAL; END;25SESSION暫時表運用1暫時表只能建立在用戶暫時表空間上，而不能建立在系統(tǒng)暫時表空間上。暫時表是在一個SESSION內(nèi)有效的。假設(shè)程序有多線程，最好不要運用暫時表，由于比較難控制。建立暫時表最好加上with replace選項，這樣可以不顯示地drop暫時表。舉例闡明：CREATE PROCEDURE DB2ADMIN.TEP_TABLE_TEST ( ) DYNAMIC RESULT SETS 1 LANGUAGE SQL MODIFIES SQL DATA BEGIN -定義錯誤代碼 DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT 00000; DECLARE not_found CONDITION FOR SQLSTATE 02000; DECLARE at_end INTEG