《數(shù)據(jù)庫系統(tǒng)原理與應(yīng)用（第二版）》課件第7章

第七章SQLServer2005程序設(shè)計(jì)7.1Transact-SQL程序設(shè)計(jì)基礎(chǔ)7.1.1Transact-SQL語法格式約定大寫字母：代表Transact-SQL保留的關(guān)鍵字.小字字母：表示對(duì)象標(biāo)識(shí)符和表達(dá)式等.大括號(hào){}或尖括號(hào)<>：大括號(hào)或尖括號(hào)中的內(nèi)容為必選參數(shù)，其中可包含多個(gè)選項(xiàng)，各選項(xiàng)之間用豎線分隔，用戶必須從這些選項(xiàng)中選擇一項(xiàng).方括號(hào)[]：它所列出的項(xiàng)目列表為可選項(xiàng)，用戶可根據(jù)需要選擇使用.豎線｜：表示參數(shù)之間是“或”的關(guān)系，可以從中選擇任意一個(gè)使用.[,…n]：表示重復(fù)前面的語法單元，各項(xiàng)之間用逗號(hào)分隔。[…n]：表示重復(fù)前面的語法單元，各項(xiàng)之間用空格分隔。注釋：注釋為Transact-SQL腳本程序中的說明信息，SQLServer不執(zhí)行這部分內(nèi)容。

單行注釋：使用兩個(gè)連字符（--）作為注釋的開始標(biāo)志。從它到本行行尾的所有內(nèi)容均為注釋信息。塊注釋：塊注釋的格式為/*…*/，其間的所有內(nèi)容均為注釋信息。塊注釋與單行注釋的不同之處是它可以跨越多行，并且可以插入在程序代碼中的任何地方。7.1.1Transact-SQL語法格式約定(續(xù))7.1.2標(biāo)識(shí)符1．常規(guī)標(biāo)識(shí)符

常規(guī)標(biāo)識(shí)符遵守以下的命名規(guī)則：標(biāo)識(shí)符長(zhǎng)度可以為1～128個(gè)字符。標(biāo)識(shí)符的首字符必須為Unicode2.0標(biāo)準(zhǔn)所定義的字母或_、＠、＃符號(hào)。標(biāo)識(shí)符第一個(gè)字符后面的字符可以為UnicodeStandard2.0所定義的字符、數(shù)字或＠、#、$、_符號(hào)。標(biāo)識(shí)符內(nèi)不能嵌入空格和特殊字符。標(biāo)識(shí)符不能與SQLServer中的保留關(guān)鍵字同名。2．定界標(biāo)識(shí)符定界標(biāo)識(shí)符允許在標(biāo)識(shí)符中使用SQLServer保留關(guān)鍵字或常規(guī)標(biāo)識(shí)符中不允許使用的一些特殊字符，但必須由雙引號(hào)或方括號(hào)定界符進(jìn)行分隔.7.1.3運(yùn)算符1．算術(shù)運(yùn)算符+：加。-：減。*：乘。/：除。%：取模。2．位運(yùn)算符對(duì)整數(shù)或二進(jìn)制數(shù)據(jù)進(jìn)行按位與（&）、或（|）、異或（^）、求反（～）等邏輯運(yùn)算。3．比較運(yùn)算符>：大于。=：等于。<：小于。>=：大于等于。<=：小于等于。<>：不等于。!=：不等于。!>：不大于。!<：不小于。4．邏輯運(yùn)算符AND。OR。NOT。[NOT]BETWEEN….AND。[NOT]LIKE。[NOT]IN。IS[NOT]NULL。ALL、SOME、ANY。[NOT]EXISTS。6．賦值運(yùn)算符SQLServer中的賦值運(yùn)算符為等號(hào)（=），它將表達(dá)式的值賦給一個(gè)變量.5．字符串連接符“+”可以實(shí)現(xiàn)字符串之間的連接操作

7．1．4．變量DECLARE語句的語法格式為：DECLARE＠variable_namedate_type[,@variable_namedata_type…]1．變量聲明2．變量賦值SET語句的語法格式為:SET@variable_name=expressionSELECT語句為變量賦值的語法格式為：SELECT@variable_name=expression[FROM<表名>WHERE<條件>]7.1.5流程控制語句1．BEGIN…END語句BEGIN{SQL語句｜語句塊}END2．條件語句條件語句的語法格式為：IF

<布爾表達(dá)式>{SQL語句｜語句塊}[ELSE{SQL語句｜語句塊}]7.1.5流程控制語句(續(xù))3．轉(zhuǎn)移語句GOTO

<標(biāo)號(hào)>4．循環(huán)語句循環(huán)語句的語法格式為：

WHILE<布爾表達(dá)式> {SQL語句｜語句塊} [BREAK] {SQL語句｜語句塊} [CONTINUE] [SQL語句｜語句塊]7.1.5流程控制語句(續(xù))5．等待語句WAITFOR{DELAY'interval'｜TIME'time'}6．返回語句RETURN

[整數(shù)表達(dá)式]簡(jiǎn)單CASE函數(shù)語法：CASEinput_expression

WHENwhen_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression

]END7．CASE語句CASE搜索函數(shù)語法：CASE

WHENBoolean_expressionTHENresult_expression

[...n]

[

ELSEelse_result_expression

]END7.1.6．異常處理1．TRY...CATCH語句TRY...CATCH語句是在SQLSERVER2005數(shù)據(jù)庫中新加入的，用于實(shí)現(xiàn)異常的處理。TRY...CATCH語法結(jié)構(gòu)如下：BEGINTRY

{語句｜語句塊}ENDTRYBEGINCATCH

{語句｜語句塊}ENDCATCH2．與異常有關(guān)的函數(shù)ERROR_NUMBER()：返回錯(cuò)誤號(hào)。ERROR_SEVERITY()：返回錯(cuò)誤的嚴(yán)重級(jí)別。ERROR_STATE()：返回錯(cuò)誤狀態(tài)號(hào)。ERROR_PROCEDURE()：返回發(fā)生錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。ERROR_LINE()：返回發(fā)生錯(cuò)誤的行號(hào)。ERROR_MESSAGE()：返回錯(cuò)誤的消息文本。3．拋出錯(cuò)誤語句RAISERROR(message|msg_id,,severity,state

[,argument[,...n]])[WITHoption[,...n]]7.1.7游標(biāo)（1）游標(biāo)的定義DECLARE<游標(biāo)名>[INSENSITIVE][SCROLL]CURSORFOR<查詢語句>[FOR<READONLY|UPDATE[OFcolumn_list]>（2）打開游標(biāo)OPEN<游標(biāo)名>（3）從一個(gè)打開的游標(biāo)中提取數(shù)據(jù)行

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]<游標(biāo)名>[INTO<變量名列表>]（4）關(guān)閉游標(biāo)CLOSE<游標(biāo)名>（5）釋放游標(biāo)

DEALLOCATE<游標(biāo)名>（6）游標(biāo)應(yīng)用舉例在圖書管理信息系統(tǒng)（BookSys）中，有一個(gè)名為Book的圖書信息表，其中有一個(gè)名為Price的圖書單價(jià)字段?？紤]到圖書維護(hù)成本的不斷增長(zhǎng)，再考慮按如下規(guī)則對(duì)圖書單價(jià)進(jìn)行提價(jià)：30元以下的，提價(jià)10%；60元以下的，提價(jià)20%；60元以上的，提價(jià)30%。參考教材中的代碼.7.2存儲(chǔ)過程7.2.1存儲(chǔ)過程概述存儲(chǔ)過程分為三類：系統(tǒng)提供的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程和擴(kuò)展存儲(chǔ)過程.(2)自定義的存儲(chǔ)過程：是由用戶為完成某一特定功能而編寫的存儲(chǔ)過程。在SQLSERVER2005中，按編寫的語言，又分為兩種類型：T-SQL和CLR。（1）系統(tǒng)提供的存儲(chǔ)過程：在安裝SQLServer時(shí)，系統(tǒng)創(chuàng)建了很多系統(tǒng)存儲(chǔ)過程（3）擴(kuò)展存儲(chǔ)過程：是對(duì)動(dòng)態(tài)鏈接庫（DLL）函數(shù)的調(diào)用7.2.2存儲(chǔ)過程的優(yōu)點(diǎn)（1）減少網(wǎng)絡(luò)流量（2）增強(qiáng)代碼的重用性和共享性（3）加快系統(tǒng)運(yùn)行速度（4）加強(qiáng)安全性7.2.3．用T-SQL語句創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程的語法為：CREATEPROCEDURE<procedure_name>[WITHENCRYPTION][<@parameterdata_type>[=default][OUTPUT]][,...n]ASsql_statement[...n]WITHENCRYPTION：加密存儲(chǔ)過程代碼，保護(hù)作者知識(shí)產(chǎn)權(quán)。procedure_name：存儲(chǔ)過程的名稱。@parameter：參數(shù)名稱，注意名稱前必須有“@”符號(hào)。data_type：參數(shù)的數(shù)據(jù)類型。default：輸入?yún)?shù)的缺省值。OUTPUT：表明該參數(shù)是輸出參數(shù)。sql_statement：SQL語句，這是存儲(chǔ)過程的重點(diǎn)構(gòu)造部分CREATEPROCEDUREprocReader1 --創(chuàng)建存儲(chǔ)過程@CardIDCHAR(10)ASSELECTBORROW.CARDID,READER.[NAME],BORROW.BOOKID,BOOK.BOOKNAME,BDATE,SDATEFROMBORROW,BOOK,READERWHEREBORROW.BOOKID=BOOK.BOOKIDANDBORROW.CARDID=READER.CARDIDANDREADER.CARDID=@CardID例:創(chuàng)建一存儲(chǔ)過程，根據(jù)傳入的讀者卡號(hào)，檢索該讀者的借書信息，包括卡號(hào)、姓名、書號(hào)、書名、借書時(shí)間和還書時(shí)間。該存儲(chǔ)過程帶一輸入?yún)?shù)：@CardID，即傳入一個(gè)讀者卡號(hào)7．2．4．執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程的完整語法如下：[EXEC][@return_value=]procedure_name[Value_List][@return_value=]用于接收存儲(chǔ)過程的返回值。procedure_name：要執(zhí)行的存儲(chǔ)過程的名稱。Value_List：輸入?yún)?shù)值。參數(shù)之間用逗號(hào)分隔，輸出參數(shù)一定要傳變量7.2.5刪除存儲(chǔ)過程刪除存儲(chǔ)過程是指刪除由用戶創(chuàng)建的存儲(chǔ)過程。格式：DROPPROCEDURE存儲(chǔ)過程名。7.2.6．在ManagementStudio中修改存儲(chǔ)過程

在“對(duì)象資源管理器”中,展開用戶數(shù)據(jù)庫（如BOOKSYS）、“可編程性”、“存儲(chǔ)過程”，在“存儲(chǔ)過程”節(jié)點(diǎn)下可以看到用戶創(chuàng)建的所有存儲(chǔ)過程，右擊要修改的存儲(chǔ)過程,在彈出的菜單中選擇“修改”命令。代碼編輯器中顯示該存儲(chǔ)過程的代碼如圖所示7.2.7使用ManagementStudio中模板新建存儲(chǔ)過程直接在“存儲(chǔ)過程”節(jié)點(diǎn)上右鍵單擊，在彈出菜單中選擇“新建存儲(chǔ)過程”命令，系統(tǒng)會(huì)打開代碼編輯器，并在代碼編輯器中顯示創(chuàng)建存儲(chǔ)過程的模板。用戶可以修改模板中的參數(shù)并添加相關(guān)T-SQL代碼.7.3函數(shù)7.3.1函數(shù)類型（1）內(nèi)連標(biāo)量函數(shù)：是指返加類型為RETURN子句中定義的數(shù)據(jù)類型的單個(gè)值,內(nèi)連標(biāo)量函數(shù)的函數(shù)體是單個(gè)T-SQL語句（2）多語句標(biāo)量函數(shù)：是指返加類型為RETURN子句中定義的數(shù)據(jù)類型的單個(gè)值。函數(shù)體是包含在BEGIN...END之間的一組T-SQL語句。

（3）內(nèi)連表值函數(shù)：返回TABLE數(shù)據(jù)類型，它沒有函數(shù)體，返回的表是單個(gè)SELECT語句執(zhí)行后的返回結(jié)果集

7.3.1函數(shù)類型(續(xù))（4）多語句表值函數(shù)：返回TABLE數(shù)據(jù)類型，函數(shù)體中包含一組T-SQL語句，這些語句可以生成行，并插入到返回表中。（5）內(nèi)置函數(shù)：也稱系統(tǒng)函數(shù)，是SQLSERVER2005提供的返回標(biāo)量數(shù)據(jù)類型或TABLE數(shù)據(jù)類型的函數(shù)。內(nèi)置函數(shù)不能修改。7．3．2．函數(shù)的優(yōu)點(diǎn)函數(shù)和存儲(chǔ)過程類似，具有執(zhí)行速度快、減少網(wǎng)絡(luò)流量、增強(qiáng)代碼的重用性和共享性等優(yōu)點(diǎn)7.3.3函數(shù)與存儲(chǔ)過程如果存儲(chǔ)過程返回單個(gè)標(biāo)量值，則使用標(biāo)量函數(shù)更有優(yōu)勢(shì)；如果存儲(chǔ)過程返回單個(gè)結(jié)果集，則可以使用表值函數(shù)來替代。7.3.4用T-SQL語句創(chuàng)建函數(shù)1．創(chuàng)建標(biāo)量函數(shù)CREATEFUNCTION<函數(shù)名>([{參數(shù)名參數(shù)數(shù)據(jù)類型

[=默認(rèn)值]}

[,...n]

])RETURNS返回的數(shù)據(jù)類型

[WITHENCRYPTION]

[AS]

BEGIN

函數(shù)體

RETURN<表達(dá)式>

END例:創(chuàng)建內(nèi)連標(biāo)量函數(shù)，返回給定日期的月信息。CREATEFUNCTIONgetMonth--函數(shù)名(@datedatetime)---參數(shù)RETURNSint--返回類型

AS

BEGIN

RETURNDATEPART(MM,@date)---返回值

END2．創(chuàng)建內(nèi)連表值函數(shù)CREATEFUNCTION<函數(shù)名>([{參數(shù)名參數(shù)數(shù)據(jù)類型

[=默認(rèn)值]}

[,...n]

])RETURNSTABLE

[WITHENCRYPTION]

[AS]

RETURNSELECT語句例7-15用函數(shù)返回指定讀者的所有借書（未還）記錄

CREATEFUNCTIONgetBorrow(@cardidchar(14))RETURNSTABLEASRETURNSELECT*FROMborrowWHEREcardid=@cardid----返回結(jié)果3．創(chuàng)建多語句表值函數(shù)CREATEFUNCTION<函數(shù)名>([{參數(shù)名參數(shù)數(shù)據(jù)類型

[=默認(rèn)值]}

[,...n]

])RETURNS@return_variableTABLE<table_type_definition>

[WITHENCRYPTION]

[AS]

BEGIN

函數(shù)體

RETURN

END7.3.5刪除函數(shù)格式：DROPFUNCTION<函數(shù)名>7.3.6在SManagementStudio中修改函數(shù)在“對(duì)象資源管理器”中,依次展開“數(shù)據(jù)庫”節(jié)點(diǎn)、用戶數(shù)據(jù)庫（本例為BOOKSYS）、“可編程性”、“函數(shù)”，根據(jù)函數(shù)類型選擇“表值函數(shù)”或“標(biāo)量函數(shù)”結(jié)點(diǎn)，在該節(jié)點(diǎn)下可以看到用戶創(chuàng)建的所有相關(guān)函數(shù)，選擇要修改的函數(shù).右擊函數(shù)名，在彈出的菜單中選擇“修改”命令.7.3.7函數(shù)的調(diào)用1．標(biāo)量函數(shù)調(diào)用SQLSERVER2005中，標(biāo)量函數(shù)可以直接調(diào)用，凡是可以出現(xiàn)表達(dá)式的地方都可以調(diào)用標(biāo)量函數(shù)。標(biāo)量函數(shù)的調(diào)用語法為：<架構(gòu)名>.函數(shù)名2．表值函數(shù)調(diào)用SQLSERVER2005中，表值函數(shù)返回的是結(jié)果集，在T-SQL中可以象訪問表或視圖一樣調(diào)用表值函數(shù)。7.3.8常用內(nèi)置函數(shù)1．日期與時(shí)間函數(shù)（1）GETDATE函數(shù)：GETDATE（）沒有輸入?yún)?shù)，返回當(dāng)前系統(tǒng)日期和時(shí)間（2）DATEPART函數(shù)：DATEPART（datepart,date），返回date參數(shù)指定的日期中的datepart參數(shù)指定的日期部份的整數(shù).（3）DATEDIFF函數(shù)：DATEDIFF(datepart,startdate,enddate)，返回enddate和startdate表示的兩個(gè)日期之差，結(jié)果由參數(shù)datepart決定

1．日期與時(shí)間函數(shù)(續(xù))（4）DATEADD函數(shù)：DATEADD(datepart,number,date)，返回參數(shù)date指定的日期上增加number后得到的新的日期

（5）YEAR、MONTH、DAY函數(shù)：它們均接收一個(gè)日期參數(shù)，返回給定日期中年、月、日

（6）DATENAME函數(shù)：DATENAME(datepart,date)，返回指定日期的指定日期部份的字符串。2．字符串函數(shù)ASCIINCHARSOUNDEXCHARPATINDEXSPACECHARINDEXQUOTENAMESTRDIFFERENCEREPLACESTUFFLEFTREPLICATESUBSTRINGLENREVERSEUNICODELOWERRIGHTUPPERLTRIMRTRIM

3．?dāng)?shù)學(xué)函數(shù)SQLServer2005提供了大量的數(shù)學(xué)函數(shù)，參考教材表7-5。7.4觸發(fā)器

觸發(fā)器是一種特殊的存儲(chǔ)過程，當(dāng)在指定的數(shù)據(jù)表進(jìn)行行插入、修改或刪除操作時(shí)被自動(dòng)調(diào)用

7．4．1．觸發(fā)器概述1．觸發(fā)器的概念觸發(fā)器（Trigger）是用戶對(duì)某一表中的數(shù)據(jù)做插入、更新和刪除操作時(shí)被觸發(fā)執(zhí)行的一段程序.通常我們使用觸發(fā)器來檢查用戶對(duì)表的操作是否符合整個(gè)應(yīng)用系統(tǒng)的需求及是否符合商業(yè)規(guī)則，以維持表內(nèi)數(shù)據(jù)的完整性和正確性。

2．與觸發(fā)器有關(guān)的兩個(gè)特殊表SQLServer為每個(gè)觸發(fā)器創(chuàng)建了兩個(gè)專用臨時(shí)表：INSERTED表和DELETED表,它們存放于內(nèi)存中，而不是存放在數(shù)據(jù)庫中。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同

（1）INSERTED表：存放由于INSERT或UPDATE語句的執(zhí)行而導(dǎo)致要加到該觸發(fā)器作用的表中去的所有新行

（2）DELETED表：存放由于DELETE或UPDATE語句的執(zhí)行而導(dǎo)致要從被該觸發(fā)器作用的表中刪除的所有行。即把被作用表中要?jiǎng)h除或要更新的舊值移到DELETED表中3．觸發(fā)器的分類（1）DML觸發(fā)器：DML觸發(fā)器是當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時(shí)要執(zhí)行的操作

DML觸發(fā)器包括兩種類型，AFTER觸發(fā)器和INSTEADOF觸發(fā)器.

（2）DDL觸發(fā)器：DDL觸發(fā)器是SQLServer2005新增的功能。它是一種特殊的觸發(fā)器，在響應(yīng)數(shù)據(jù)定義語言（DDL）時(shí)觸發(fā)

4．創(chuàng)建觸發(fā)器的T-SQL語CREATETRIGGER<觸發(fā)器名>ON<表名|視圖名>[WITHENCRYPTION]<FOR|AFTER|INSTEADOF><[INSERT][,][UPDATE][,][DELETE]>AS[IFUPDATE(列名)]<SQL語句[...n]>WITHENCRYPTION選項(xiàng)加密觸發(fā)器代碼，保護(hù)作者的知識(shí)產(chǎn)權(quán)UPDATE(列名)是一個(gè)函數(shù)，用于測(cè)試是否對(duì)表或視圖的指定列進(jìn)行了INSERT或UPDATE操作7.4.2AFTER觸發(fā)器AFTER觸發(fā)器是在執(zhí)行INSERT、UPDATE、DELETE任一操作之后被觸發(fā)。AFTER觸發(fā)器只能在表上定義AFTER觸發(fā)器是在執(zhí)行INSERT、UPDATE、DELETE任一操作之后被觸發(fā)。AFTER觸發(fā)器只能在表上定義1．INSERT觸發(fā)器INSERT觸發(fā)器由INSERT語句觸發(fā)，即用戶在表中插入一條記錄且插入成功時(shí)，觸發(fā)INSERT觸發(fā)器例7-17創(chuàng)建一觸發(fā)器以實(shí)現(xiàn)如下功能：當(dāng)往BORROW表中插入一條記錄時(shí)，如果書號(hào)或卡號(hào)不存在，則撤消插入INSERT觸發(fā)器的工作過程如下：（1）用戶或系統(tǒng)運(yùn)行INSERT語句。（2）如果記錄不違反限制，則將記錄插入到臨時(shí)表INSERTED中。（3）觸發(fā)觸發(fā)器。（4）如果觸發(fā)器執(zhí)行完畢而無錯(cuò)誤，則INSERTED表被刪除，插入操作完成。

2．DELETE觸發(fā)器DELETE觸發(fā)器由DELETE語句觸發(fā)，即用戶在表中刪除一條記錄時(shí)，觸發(fā)DELETE觸發(fā)器.例7-18創(chuàng)建一觸發(fā)器以實(shí)現(xiàn)如下功能：當(dāng)試圖刪除BORROW表中的一條記錄時(shí)，若還書日期為空或還書日期距今還不到半年，則撤消事務(wù).DELETE觸發(fā)器的工作過程如下：（1）用戶或系統(tǒng)運(yùn)行DELETE語句。（2）如果記錄不違反外部關(guān)鍵字限制，則刪除表中的記錄并將其插入到臨時(shí)表DELETED中。（3）觸發(fā)觸發(fā)器。（4）如果觸發(fā)器執(zhí)行完畢而無錯(cuò)誤，則DELETED表被刪除，刪除操作完成。

3．UPDATE觸發(fā)器更新操作可以看成一個(gè)刪除加一個(gè)插入：刪除舊值和插入新值。例7-19創(chuàng)建一觸發(fā)器以實(shí)現(xiàn)如下功能：如果要更改READER表中的CARDID，則先檢查BORROW表中是否有記錄引用了該CARDID