數(shù)據(jù)庫(kù)及其應(yīng)用課件：數(shù)據(jù)庫(kù)編程基礎(chǔ)

數(shù)據(jù)庫(kù)編程

介紹的交互式SQL語(yǔ)言是以單命令的方式操作數(shù)據(jù)庫(kù)，缺少靈活數(shù)據(jù)處理功能。

當(dāng)前，許多DBMS系統(tǒng)在標(biāo)準(zhǔn)SQL語(yǔ)言基礎(chǔ)上，引入高級(jí)語(yǔ)言程序設(shè)計(jì)的方法，擴(kuò)充程序流程控制語(yǔ)句，具有較強(qiáng)的數(shù)據(jù)操作功能，并兼顧高級(jí)語(yǔ)言的編程特點(diǎn)。

本章將介紹SQL程序設(shè)計(jì)技術(shù)，包括局部變量、全局變量、控制流語(yǔ)句、游標(biāo)等編程基礎(chǔ)知識(shí)。然后，介紹存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)和觸發(fā)器等SQL編程技術(shù)。2第一節(jié)創(chuàng)建存儲(chǔ)過(guò)程

一、存儲(chǔ)過(guò)程（StoredProcedure）是存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器上的SQL程序。存儲(chǔ)過(guò)程主要有四個(gè)優(yōu)點(diǎn):（1）可以多次調(diào)用而不必重寫程序，修改存儲(chǔ)過(guò)程不影響調(diào)

用的程序，提高程序的可移植性。（2）可以使用一些控制程序流程的語(yǔ)句，編寫復(fù)雜的業(yè)務(wù)處理程序。（3）以編譯后的形式存儲(chǔ)在數(shù)據(jù)庫(kù)中，提高系統(tǒng)執(zhí)行速度。（4）調(diào)用存儲(chǔ)過(guò)程時(shí)只需要傳遞參數(shù)、結(jié)果和少量必要的消息，從而減少網(wǎng)絡(luò)流量。3二、創(chuàng)建存儲(chǔ)過(guò)程1、基本語(yǔ)法格式：CREATEPROCEDUIRE<存儲(chǔ)過(guò)程名>([IN|OUT|INOUT]參數(shù)1數(shù)據(jù)類型1,[IN|OUT|INOUT]參數(shù)2數(shù)據(jù)類型2，…)BEGIN存儲(chǔ)過(guò)程體;END;參數(shù)分為如下3種類別：IN輸入?yún)?shù)(默認(rèn)為IN參數(shù))，該參數(shù)的值由調(diào)用程序指定。OUT 輸出參數(shù)，可將存儲(chǔ)過(guò)程計(jì)算結(jié)果返回調(diào)用程序。INOUT具有輸入和輸出兩類參數(shù)的特點(diǎn)。

42、調(diào)用存儲(chǔ)過(guò)程的SQL語(yǔ)句

CALL存儲(chǔ)過(guò)程名(參數(shù)列表);3、刪除存儲(chǔ)過(guò)程的SQL語(yǔ)句

DROPPROCEDURE<存儲(chǔ)過(guò)程名>;程序中常用方法：DROPPROCEDUREIFEXISTSPROA;

EXISTS存在量詞，判斷對(duì)象是否存在。5

DELIMITER$$DROPPROCEDUREIFEXISTSPROA;CREATEPROCEDUREPROA()BEGINSELECT姓名,工資FROM職工;END$$DELIMITER;說(shuō)明：為了編譯系統(tǒng)避免沖突，臨時(shí)換一個(gè)不常用的符號(hào)做結(jié)束符，先用“DELIMITER$$”將MySQL的結(jié)束符設(shè)置為$$，然后用“DELIMITER;”將結(jié)束符恢復(fù)成分號(hào)。4、創(chuàng)建存儲(chǔ)過(guò)程的簡(jiǎn)單示例創(chuàng)建和調(diào)用存儲(chǔ)過(guò)程示例創(chuàng)建存儲(chǔ)過(guò)程67第二節(jié)SQL編程基礎(chǔ)

一、BEGIN..END語(yǔ)句BEGIN<SQL語(yǔ)句序列>END二、注釋單行注釋符#

注釋符后直接加注釋內(nèi)容單行注釋符--注釋符后需要加一個(gè)空格多行注釋符/**/從“/*”開始到“*/”結(jié)束，可以跨越多行的注釋內(nèi)容

三、變量MySQL有四種變量類型，即局部變量、用戶變量、會(huì)話變量和全局變量，局部變量和用戶變量是根據(jù)用戶的需要，由用戶創(chuàng)建的；會(huì)話變量和全局變量屬于系統(tǒng)變量，系統(tǒng)變量在變量名前面有兩個(gè)@，系統(tǒng)變量由系統(tǒng)運(yùn)行需要?jiǎng)?chuàng)建和賦值。1、局部變量

(1)聲明局部變量的語(yǔ)句格式

DECLARE變量名[,…]數(shù)據(jù)類型[DEFAULT默認(rèn)值];

例如DECLAREnameCHAR(8);DECLAREzg_salary,taxDECIMAL(8,2);

8（2)變量賦值語(yǔ)句的三種格式【格式一】SET變量名=表達(dá)式[,變量名=表達(dá)式]...;SETname='李華',zg_salary=8000,tax=zg_salary*0.05;【格式二】SELECT變量名=<表達(dá)式>;SELECTname='李華';【格式三】

SELECT字段名[,字段名,...]INTO變量名[,變量名,...]FROM表名[WHERE<條件表達(dá)式>];（3）顯示變量值的語(yǔ)句

SELECT變量名[,變量名]；SELECTname,zg_salary,tax;910在存儲(chǔ)過(guò)程中使用變量的示例調(diào)用存儲(chǔ)過(guò)程示例112、用戶變量

用戶在客戶端連接數(shù)據(jù)庫(kù)后創(chuàng)建的變量，有效期從創(chuàng)建直至數(shù)據(jù)庫(kù)斷開為止。用戶變量不用聲明，變量名前面加“@”。

用戶變量賦值的方法有三種：第一種用法：SET@num='E2';或SET@num:='E2’;第二種用法：SELECT@num:='E1’;第三種用法：SELECT@num:=字段名FROM表名WHERE<條件表達(dá)式>;SELECT姓名,工資INTO@name,@zg_salaryFROM職工WHERE職工號(hào)LIKE‘E1’;1213四、SQL的控制流語(yǔ)句順序結(jié)構(gòu)：程序從上往下依次執(zhí)行。分支結(jié)構(gòu)：程序從兩條或多條路徑中選擇一條去執(zhí)行。循環(huán)結(jié)構(gòu)：在滿足循環(huán)條件的基礎(chǔ)上，重復(fù)執(zhí)行一段代碼。1.條件分支結(jié)構(gòu)基本語(yǔ)句結(jié)構(gòu)：IF<條件表達(dá)式>THEN<語(yǔ)句序列1>;[ELSE<語(yǔ)句序列2>;]ENDIF14IF語(yǔ)句多層嵌套示例IF<條件表達(dá)式1>THEN<語(yǔ)句序列1>ELSEIF<條件表達(dá)式2>THEN<語(yǔ)句序列2>……ELSE<語(yǔ)句序列n>ENDIF;例5.8流程圖創(chuàng)建存儲(chǔ)過(guò)程15測(cè)試存儲(chǔ)過(guò)程的正確性162、CASE語(yǔ)句CASE

WHEN

<條件表達(dá)式1>

THEN

<語(yǔ)句序列1>;

WHEN

<條件表達(dá)式2>

THEN

<語(yǔ)句序列2>;

...

[ELSE

<語(yǔ)句序列N>;]ENDCASE;17例5.9的代碼1819CASE語(yǔ)句的另一種應(yīng)用方法（例5.10）3、循環(huán)語(yǔ)句

WHILE、REPEAT和LOOP結(jié)構(gòu)20三種循環(huán)語(yǔ)句的格式21例5.11使用WHILE語(yǔ)句實(shí)現(xiàn)22例5.11使用REPEAT語(yǔ)句實(shí)現(xiàn)23例5.11使用LOOP語(yǔ)句實(shí)現(xiàn)24第三節(jié)存儲(chǔ)過(guò)程的應(yīng)用示例例5.12已知：商品(商品編碼,品名,顏色,花型,規(guī)格,在庫(kù)數(shù)量,最高庫(kù)存,最低庫(kù)存,單價(jià)）關(guān)系。創(chuàng)建一個(gè)存儲(chǔ)過(guò)程PROF，能夠按照商品編碼查詢庫(kù)存狀況。如果商品的庫(kù)存數(shù)量大于等于最高庫(kù)存，則顯示商品編號(hào)、品名、在庫(kù)數(shù)量、最高庫(kù)存和積壓數(shù)量；如果在庫(kù)數(shù)量小于等于最低數(shù)量，則顯示商品編號(hào)、品名、在庫(kù)數(shù)量、最低庫(kù)存和缺貨數(shù)量。25例5.12的源代碼26庫(kù)存積壓測(cè)試27庫(kù)存缺貨測(cè)試28第四節(jié)創(chuàng)建存儲(chǔ)函數(shù)1、創(chuàng)建存儲(chǔ)函數(shù)的SQL語(yǔ)句CREATEFUNCTION<函數(shù)名>([參數(shù)數(shù)據(jù)類型[,…]])RETURNS返回值數(shù)據(jù)類型

DETERMINISTIC聲明為確定性函數(shù)BEGINSQL語(yǔ)句序列

(必須有RETURN變量或值)END;說(shuō)明：函數(shù)可有多個(gè)參數(shù)，必須說(shuō)明參數(shù)和參數(shù)的類型。RETURNS必須說(shuō)明返回值數(shù)據(jù)類型。DETERMINISTIC聲明函數(shù)是確定性。SQL語(yǔ)句序列是函數(shù)的主體，其中必須包括RETURN

返回函數(shù)值，否則編譯系統(tǒng)提示出錯(cuò)信息。29創(chuàng)建存儲(chǔ)函數(shù)示例30第五節(jié)游標(biāo)及游標(biāo)的應(yīng)用游標(biāo)（CURSOR）相當(dāng)于一個(gè)臨時(shí)表，在表中存放查詢的結(jié)果，為了逐個(gè)地取出這個(gè)表中的元組，設(shè)置一個(gè)指針，指示可取元組的位置；每取一個(gè)元組，指針向前推進(jìn)一個(gè)位置。利用游標(biāo)（CURSOR）機(jī)制，可以將集合操作轉(zhuǎn)換成單記錄的處理方式。31二、游標(biāo)的用法例5.14創(chuàng)建的存儲(chǔ)過(guò)程PROG，批量查詢庫(kù)存狀況。利用游標(biāo)技術(shù)，實(shí)現(xiàn)處理多行數(shù)據(jù)。3233第六節(jié)數(shù)據(jù)庫(kù)觸發(fā)器觸發(fā)器的概念

觸發(fā)器（Trigger）是由事件觸發(fā)的某個(gè)操作。它是一種實(shí)現(xiàn)復(fù)雜完整性約束的特殊存儲(chǔ)過(guò)程，能夠在符合條件時(shí)自動(dòng)觸發(fā)的SQL程序。觸發(fā)器的特點(diǎn)（1）觸發(fā)器是數(shù)據(jù)庫(kù)的一個(gè)對(duì)象，必須創(chuàng)建在一個(gè)特定的表上，并存儲(chǔ)在數(shù)據(jù)庫(kù)中。（2）如果對(duì)一個(gè)表上的某種操作（如插入、更新或刪除）定義了觸發(fā)器，則該操作發(fā)生時(shí)觸發(fā)器將自動(dòng)觸發(fā)。（3）觸發(fā)器不能被直接調(diào)用，也不能傳遞或接受參數(shù)。（4）觸發(fā)器和激活它的SQL語(yǔ)句構(gòu)成一個(gè)事務(wù)，可以在觸發(fā)器中包含ROLLBACKTRANSACTION語(yǔ)句，根據(jù)觸發(fā)器運(yùn)行的狀態(tài)回滾事務(wù)，撤消所有操作。（5）觸發(fā)器是實(shí)現(xiàn)數(shù)據(jù)安全性控制的一種手段。35(1)利用觸發(fā)器能夠?qū)崿F(xiàn)相關(guān)表的級(jí)聯(lián)操作。(2)具有更強(qiáng)大和更復(fù)雜的完整性約束定義功能。(3)利用觸發(fā)器可以比較數(shù)據(jù)修改前后的狀態(tài)，并可根據(jù)差異而采取不同的對(duì)策。(4)應(yīng)用觸發(fā)器能夠簡(jiǎn)化復(fù)雜業(yè)務(wù)的實(shí)現(xiàn)方法，用簡(jiǎn)單的方法定義復(fù)雜的業(yè)務(wù)規(guī)則和完整性約束條件。(5)由于觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它具備存儲(chǔ)過(guò)程的優(yōu)點(diǎn)。2、觸發(fā)器的優(yōu)點(diǎn)二、創(chuàng)建觸發(fā)器CREATETRIGGER<觸發(fā)器名稱>--創(chuàng)建觸發(fā)器的名稱BEFORE|AFTER--設(shè)置事件發(fā)生前或后執(zhí)行INSERT|UPDATE|DELETE|

--設(shè)定觸發(fā)的事件ON<表名>--創(chuàng)建觸發(fā)器的表FOREACHROW

--對(duì)每一行執(zhí)行一次<SQL語(yǔ)句序列>--觸發(fā)的語(yǔ)句系列36NEW將要（BEFORE）或已經(jīng)（AFTER）變動(dòng)的新數(shù)據(jù)OLD將要（BEFORE）或已經(jīng)（AFTER）變動(dòng)的舊數(shù)據(jù)

使用方法：NEW.字段名

（為相應(yīng)表某一列名的新值）OLD.字段名