《數(shù)據(jù)庫(kù)開發(fā)基礎(chǔ)》ppt課件

1、DB2開發(fā)根底知識(shí)儲(chǔ)存過(guò)程開發(fā)儲(chǔ)存過(guò)程開發(fā)內(nèi)容提要v數(shù)據(jù)類型數(shù)據(jù)類型v儲(chǔ)存過(guò)程及函數(shù)的構(gòu)造儲(chǔ)存過(guò)程及函數(shù)的構(gòu)造v參數(shù)定義參數(shù)定義v變量定義變量定義v賦值語(yǔ)句賦值語(yǔ)句v條件控制語(yǔ)句條件控制語(yǔ)句v循環(huán)語(yǔ)句循環(huán)語(yǔ)句v常用操作符常用操作符v游標(biāo)運(yùn)用游標(biāo)運(yùn)用v動(dòng)態(tài)游標(biāo)運(yùn)用動(dòng)態(tài)游標(biāo)運(yùn)用v異常處置異常處置vSESSION暫時(shí)表運(yùn)用暫時(shí)表運(yùn)用v常用函數(shù)常用函數(shù)v數(shù)據(jù)一致性和完好性數(shù)據(jù)一致性和完好性v事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別數(shù)據(jù)類型v定長(zhǎng)型字符串定長(zhǎng)型字符串CHARv變長(zhǎng)型字符串變長(zhǎng)型字符串VARCHARv整數(shù)類型整數(shù)類型SMALLINT、INTEGER、BIGINTv帶小數(shù)點(diǎn)的數(shù)字類型帶小數(shù)點(diǎn)的數(shù)字類型DEC

2、IMAL、REAL、DOUBLEv時(shí)間類型時(shí)間類型DATE、TIME、TIMESTAMPv對(duì)象類型對(duì)象類型BLOB、CLOB、DBCLOB運(yùn)用儲(chǔ)存過(guò)程優(yōu)點(diǎn)v減少客戶機(jī)與效力器之間的網(wǎng)絡(luò)運(yùn)用率。客戶機(jī)運(yùn)用減少客戶機(jī)與效力器之間的網(wǎng)絡(luò)運(yùn)用率?？蛻魴C(jī)運(yùn)用程序?qū)⒖刂茩?quán)傳送到數(shù)據(jù)庫(kù)效力器上的存儲(chǔ)過(guò)程。存程序?qū)⒖刂茩?quán)傳送到數(shù)據(jù)庫(kù)效力器上的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)效力器上執(zhí)行中間處置，而不需求在儲(chǔ)過(guò)程在數(shù)據(jù)庫(kù)效力器上執(zhí)行中間處置，而不需求在網(wǎng)絡(luò)中傳送不需求的數(shù)據(jù)。網(wǎng)絡(luò)中傳送不需求的數(shù)據(jù)。 v提高平安性。經(jīng)過(guò)使運(yùn)用靜態(tài)提高平安性。經(jīng)過(guò)使運(yùn)用靜態(tài) SQL 的存儲(chǔ)過(guò)程包含的存儲(chǔ)過(guò)程包含數(shù)據(jù)庫(kù)特權(quán)，數(shù)據(jù)庫(kù)管理員數(shù)

3、據(jù)庫(kù)特權(quán)，數(shù)據(jù)庫(kù)管理員DBA可以提高平安性?？梢蕴岣咂桨残浴Ｕ{(diào)用存儲(chǔ)過(guò)程的客戶機(jī)運(yùn)用程序的用戶不需求數(shù)據(jù)庫(kù)調(diào)用存儲(chǔ)過(guò)程的客戶機(jī)運(yùn)用程序的用戶不需求數(shù)據(jù)庫(kù)特權(quán)。特權(quán)。 v提高可靠性。在數(shù)據(jù)庫(kù)運(yùn)用程序環(huán)境中，許多義務(wù)是提高可靠性。在數(shù)據(jù)庫(kù)運(yùn)用程序環(huán)境中，許多義務(wù)是反復(fù)的。經(jīng)過(guò)重用一個(gè)公共過(guò)程，存儲(chǔ)過(guò)程就可以高反復(fù)的。經(jīng)過(guò)重用一個(gè)公共過(guò)程，存儲(chǔ)過(guò)程就可以高效地處理這些反復(fù)情況。效地處理這些反復(fù)情況。 存儲(chǔ)過(guò)程構(gòu)造存儲(chǔ)過(guò)程構(gòu)造如下：存儲(chǔ)過(guò)程構(gòu)造如下：CREATE PROCEDURE sqlsamp ( IN dpt varchar(4) ) SPECIFIC sqlsamp RESULT SETS 1

4、 LANGUAGE SQL - - SQL Stored Procedure sqlsamp - P1: BEGIN - Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT DEPARTMENT.DEPTNO, DEPARTMENT.DEPTNAME, EMPLOYEE.FIRSTNME, EMPLOYEE.MIDINIT, EMPLOYEE.LASTNAMEFROM DEPARTMENT, EMPLOYEE WHERE ( ( EMPLOYEE.EMPNO = DEPARTMENT.MGRNO ) AND ( ( DEPA

5、RTMENT.DEPTNO = dpt ) ) ); - Cursor left open for client application OPEN cursor1; END P1 參數(shù)定義DB2儲(chǔ)存過(guò)程的參數(shù)分為兩部分：儲(chǔ)存過(guò)程的參數(shù)分為兩部分：輸入和輸出參數(shù)。輸入和輸出參數(shù)。參數(shù)表示方式參數(shù)表示方式:輸入?yún)?shù)用輸入?yún)?shù)用IN開頭開頭輸出參數(shù)用輸出參數(shù)用OUT開頭開頭既是輸入又是輸出參數(shù)用既是輸入又是輸出參數(shù)用INOUT開開頭頭舉例闡明：舉例闡明：create procedure sp_sample (in var0 varchar(10),out var1 varchar(20),inout

6、 var2 varchar(20)變量定義存儲(chǔ)過(guò)程中可以運(yùn)用關(guān)鍵字存儲(chǔ)過(guò)程中可以運(yùn)用關(guān)鍵字DECLARE定義變量，然后在后續(xù)定義變量，然后在后續(xù)程序過(guò)程中運(yùn)用變量來(lái)處置邏輯。程序過(guò)程中運(yùn)用變量來(lái)處置邏輯。定義變量時(shí)可以指定一個(gè)初始值。定義變量時(shí)可以指定一個(gè)初始值。舉例闡明：舉例闡明：CREATE PROCEDURE P2(INOUT a VARCHAR(8),OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5) DEFAULT a | bc;- other SQL sta

7、tements END 賦值語(yǔ)句存儲(chǔ)過(guò)程運(yùn)用關(guān)鍵字存儲(chǔ)過(guò)程運(yùn)用關(guān)鍵字SET給變量賦給變量賦值。值。舉例闡明舉例闡明:CREATE PROCEDURE P2(INOUT a VARCHAR(8), OUT b INTEGER) LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0; DECLARE var2 VARCHAR(5) DEFAULT a | bc; SET var1 = 0; SET var1 = var1 + 1; SET var2 = var2 | def; SET a = var1; SET b = var2; END 條件控制語(yǔ)句

8、1條件控制語(yǔ)句包括以下幾種：條件控制語(yǔ)句包括以下幾種：IFEND IF舉例闡明舉例闡明:IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salar

9、y * 1.03, bonus = 0 WHERE empno = employee_number; END IF ; 條件控制語(yǔ)句2vCASEWHENv舉例闡明：舉例闡明：vCASEvWHEN v_workdept = A00 vTHEN UPDATE department vSET deptname = DATA ACCESS 1;v WHEN v_workdept = B01 vTHEN UPDATE department vSET deptname = DATA ACCESS 2; vELSE UPDATE department vSET deptname = DATA ACCESS

10、3;v END CASE 循環(huán)語(yǔ)句1循環(huán)語(yǔ)句包括以下幾種：循環(huán)語(yǔ)句包括以下幾種：WHILE舉例闡明：舉例闡明：WHILE v_counter (v_numRecords / 2 + 1) DO SET v_salary1 = v_salary2; SET v_counter = v_counter + 1;END WHILE;循環(huán)語(yǔ)句循環(huán)語(yǔ)句2 2vLOOPv舉例闡明舉例闡明:vLOOPvFETCH c1 INTO v_firstnme, v_midinit, v_lastname;v - Use a local variable for the iterator variablev - be

11、cause SQL procedures only allow you to assign v - values to an OUT parameterv SET v_counter = v_counter + 1;v IF v_midinit = THENv LEAVE fetch_loop;v END IF;vEND LOOP fetch_loop;常用操作符常用操作符有以下幾種：常用操作符有以下幾種：關(guān)系運(yùn)算符關(guān)系運(yùn)算符關(guān)系運(yùn)算符有六種：小于、小于等于、大于、關(guān)系運(yùn)算符有六種：小于、小于等于、大于、大于等于、等于、不等于大于等于、等于、不等于邏輯運(yùn)算符邏輯運(yùn)算符邏輯運(yùn)算符有三種：邏輯運(yùn)算

12、符有三種：AND、OR、NOT游標(biāo)運(yùn)用1游標(biāo)有兩種類型：靜態(tài)的和動(dòng)態(tài)的。運(yùn)用游標(biāo)前要先定義，然游標(biāo)有兩種類型：靜態(tài)的和動(dòng)態(tài)的。運(yùn)用游標(biāo)前要先定義，然后可以運(yùn)用循環(huán)語(yǔ)句操作游標(biāo)。后可以運(yùn)用循環(huán)語(yǔ)句操作游標(biāo)。舉例闡明：舉例闡明：CREATE PROCEDURE leave_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

13、_counter SMALLINT DEFAULT 0; DECLARE at_end SMALLINT DEFAULT 0; DECLARE not_found CONDITION for SQLSTATE 02000;游標(biāo)運(yùn)用2lDECLARE c1 CURSOR FOR l SELECT firstnme, midinit, lastname l FROM employee;l DECLARE CONTINUE HANDLER for not_found l SET at_end = 1;l - initialize OUT parameterl SET counter = 0;l OP

14、EN c1;l fetch_loop:l LOOPl FETCH c1 INTO l v_firstnme, v_midinit, v_lastname;l IF at_end 0 THEN LEAVE fetch_loop;l END IF;游標(biāo)運(yùn)用3l- Use a local variable for the iterator variablel - because SQL procedures only allow you to assign l - values to an OUT parameterl SET v_counter = v_counter + 1;l END LOOP

15、 fetch_loop;l CLOSE c1;l- Now assign the value of the locall - variable to the OUT parameterl SET counter = v_counter;lEND 動(dòng)態(tài)游標(biāo)運(yùn)用動(dòng)態(tài)游標(biāo)運(yùn)用起來(lái)比較方便靈敏，在存儲(chǔ)過(guò)程中會(huì)經(jīng)常動(dòng)態(tài)游標(biāo)運(yùn)用起來(lái)比較方便靈敏，在存儲(chǔ)過(guò)程中會(huì)經(jīng)常用到，也引薦運(yùn)用動(dòng)態(tài)游標(biāo)處置邏輯。用到，也引薦運(yùn)用動(dòng)態(tài)游標(biāo)處置邏輯。舉例闡明：舉例闡明：DECLARE c1 CURSOR FOR s0;SET temp_sql=SELECT colname FROM SYSCAT.COLUMNS WHERE

16、TABSCHEMA=|tgtschema | and TABNAME =|tgttabname| and colnameLSBH ORDER BY colno; PREPARE s0 FROM temp_sql；OPEN c1;異常處置存儲(chǔ)過(guò)程中要有可以處置程序發(fā)生異常時(shí)的處置流程。存儲(chǔ)過(guò)程中要有可以處置程序發(fā)生異常時(shí)的處置流程。舉例闡明：舉例闡明：-自定義異常處置自定義異常處置DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERR_MSG = RTRIM(CHAR(CURRENT TIMESTAMP)| (P_GENERATESQL)系統(tǒng)錯(cuò)誤：系統(tǒng)

17、錯(cuò)誤：SQLCODE=|RTRIM(CHAR(SQLCODE)|,SQLSTATE=|SQLSTATE|; 儲(chǔ)存過(guò)程中間處置流程儲(chǔ)存過(guò)程中間處置流程-執(zhí)行過(guò)程中發(fā)生異常前往出錯(cuò)標(biāo)志和出錯(cuò)信息執(zhí)行過(guò)程中發(fā)生異常前往出錯(cuò)標(biāo)志和出錯(cuò)信息IF ERR_MSG THEN SET RUNSTATUS=1;SET MESSAGE=MESSAGE|ERR_MSG;END IF;SESSION暫時(shí)表運(yùn)用v暫時(shí)表只能建立在用戶暫時(shí)表空間上，而不能建立在暫時(shí)表只能建立在用戶暫時(shí)表空間上，而不能建立在系統(tǒng)暫時(shí)表空間上。系統(tǒng)暫時(shí)表空間上。v暫時(shí)表是在一個(gè)暫時(shí)表是在一個(gè)SESSION內(nèi)有效的。假設(shè)程序有多內(nèi)有效的。假設(shè)程

18、序有多線程，最好不要運(yùn)用暫時(shí)表，由于比較難控制。線程，最好不要運(yùn)用暫時(shí)表，由于比較難控制。v建立暫時(shí)表最好加上建立暫時(shí)表最好加上with replace選項(xiàng)，這樣可以不選項(xiàng)，這樣可以不顯示地顯示地drop暫時(shí)表。暫時(shí)表。v舉例闡明：舉例闡明：vDECLARE GLOBAL TEMPORARY TABLE ETL_TEMPSQL (TGTSCHEMA VARCHAR(128),TGTTABLE VARCHAR(128),XH INTEGER,TYPE CHARACTER(1),SQL VARCHAR(3000) NOT LOGGED WITH REPLACE ;常用函數(shù)1DB2常用函數(shù)有以下一些

19、：常用函數(shù)有以下一些：類似類似oracle中中decode的判別操作的判別操作例如：例如：select case a1 when 1 then n1 when 2 then n2 else n3 end as aa1 from 表名表名 類似類似charindex查找字符在字符串中的位置查找字符在字符串中的位置例如：例如：Locate(y,dfdasfay)查找字符查找字符y在字符串在字符串dfdasfay中的位置中的位置常用函數(shù)常用函數(shù)2v類似類似datedif計(jì)算兩個(gè)日期的相差天數(shù)函數(shù)計(jì)算兩個(gè)日期的相差天數(shù)函數(shù)DAYSv例如：例如：days(date(2001-06-05) days(da

20、te(2001-04-01) v預(yù)防空字段的處置函數(shù)預(yù)防空字段的處置函數(shù)COALESCEv例如：處置字符型例如：處置字符型COALESCE(NAME ,)v處置數(shù)字型處置數(shù)字型COALESCE(BOX_NUM ,0)v 數(shù)據(jù)一致性和完好性數(shù)據(jù)一致性和完好性事務(wù)在多用戶環(huán)境中并發(fā)的幾種情況：事務(wù)在多用戶環(huán)境中并發(fā)的幾種情況：臟讀臟讀這種情況發(fā)生在一個(gè)事務(wù)讀取還未提交的數(shù)據(jù)時(shí)。例如：事務(wù)這種情況發(fā)生在一個(gè)事務(wù)讀取還未提交的數(shù)據(jù)時(shí)。例如：事務(wù)1改動(dòng)了一行改動(dòng)了一行數(shù)據(jù)，而在事務(wù)數(shù)據(jù)，而在事務(wù)1提交修正之前事務(wù)提交修正之前事務(wù)2讀取了事務(wù)讀取了事務(wù)1所改動(dòng)的行的數(shù)據(jù)。所改動(dòng)的行的數(shù)據(jù)。假設(shè)事務(wù)假設(shè)事務(wù)

21、1回滾了修正，那么事務(wù)回滾了修正，那么事務(wù)2就讀取了實(shí)踐上并不存在的數(shù)據(jù)。就讀取了實(shí)踐上并不存在的數(shù)據(jù)。不可反復(fù)讀不可反復(fù)讀這種情況發(fā)生在事務(wù)兩次讀取同一行中的數(shù)據(jù)卻得到不同的數(shù)值時(shí)。例如：這種情況發(fā)生在事務(wù)兩次讀取同一行中的數(shù)據(jù)卻得到不同的數(shù)值時(shí)。例如：事務(wù)事務(wù)1讀取了一行數(shù)據(jù)，而事務(wù)讀取了一行數(shù)據(jù)，而事務(wù)2改動(dòng)或刪除了那些行并提交了修正。假改動(dòng)或刪除了那些行并提交了修正。假設(shè)事務(wù)設(shè)事務(wù)1再次讀取了那一行，那么，事務(wù)再次讀取了那一行，那么，事務(wù)1就得到了不同的值假設(shè)那一就得到了不同的值假設(shè)那一行被更新或發(fā)現(xiàn)那一行已不存在假設(shè)那一行被刪除行被更新或發(fā)現(xiàn)那一行已不存在假設(shè)那一行被刪除幻影數(shù)據(jù)幻影

22、數(shù)據(jù)這種情況發(fā)生在一行數(shù)據(jù)滿足搜索規(guī)那么，卻在開場(chǎng)沒(méi)有被看到時(shí)。例如：這種情況發(fā)生在一行數(shù)據(jù)滿足搜索規(guī)那么，卻在開場(chǎng)沒(méi)有被看到時(shí)。例如：事務(wù)事務(wù)1讀取了一系列滿足搜索規(guī)那么的行，而事務(wù)讀取了一系列滿足搜索規(guī)那么的行，而事務(wù)2插入了一個(gè)滿足事務(wù)插入了一個(gè)滿足事務(wù)1搜索規(guī)那么的行。假設(shè)事務(wù)搜索規(guī)那么的行。假設(shè)事務(wù)1再次查詢語(yǔ)句，就會(huì)得到不同的一系列行再次查詢語(yǔ)句，就會(huì)得到不同的一系列行。事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別1DB2運(yùn)用四個(gè)不同的事務(wù)隔離級(jí)別來(lái)實(shí)現(xiàn)并發(fā)。運(yùn)用四個(gè)不同的事務(wù)隔離級(jí)別來(lái)實(shí)現(xiàn)并發(fā)?？煞磸?fù)讀可反復(fù)讀可反復(fù)讀可反復(fù)讀(RR)鎖定運(yùn)用程序在一個(gè)事務(wù)中援用的一切行，可以防止幻影行鎖定運(yùn)用程序在一個(gè)事務(wù)中援用的一切行，可以防止幻影行。例如假設(shè)掃描。例如假設(shè)掃描10000行數(shù)據(jù)并對(duì)它們進(jìn)展過(guò)濾，雖然只需行數(shù)據(jù)并對(duì)它們進(jìn)展過(guò)濾，雖然只需10行滿足條行滿足條件，但仍會(huì)鎖定全部的件，但仍會(huì)鎖定全部的10000行數(shù)據(jù)。在可反復(fù)讀隔離級(jí)別下，其他事行數(shù)據(jù)。在可反復(fù)讀隔離級(jí)別下，其他事務(wù)不能更新、刪除、插入數(shù)據(jù)，執(zhí)行的事務(wù)看不到其他事務(wù)的未提交務(wù)不能更新、刪除、插入數(shù)據(jù)，執(zhí)行的事務(wù)看不到其他事務(wù)的未提交修正?？煞磸?fù)讀能夠會(huì)持有大量鎖定最終導(dǎo)致行鎖定晉級(jí)到表鎖定。修正。可反復(fù)讀能夠會(huì)持有大量鎖定最終導(dǎo)致行鎖定晉級(jí)到表鎖定。讀穩(wěn)定性讀穩(wěn)定性讀穩(wěn)定性讀穩(wěn)定性(RS)只鎖定