SQL Server數(shù)據(jù)庫(kù)基礎(chǔ)課件第八單元 數(shù)據(jù)庫(kù)編程

第八單元數(shù)據(jù)庫(kù)編程任務(wù)8.1用戶自定義函數(shù)任務(wù)8.2創(chuàng)建存儲(chǔ)過(guò)程任務(wù)8.3創(chuàng)建觸發(fā)器8.1.1情景描述8.1.2問(wèn)題分析8.1.3解決方案8.1.4知識(shí)總結(jié)8.1.5應(yīng)用實(shí)踐任務(wù)8.1用戶自定義函數(shù)學(xué)生信息管理系統(tǒng)里經(jīng)常需要做一些重復(fù)性的操作，可把這些操作創(chuàng)建為函數(shù)來(lái)使用。數(shù)據(jù)庫(kù)開(kāi)發(fā)人員需要?jiǎng)?chuàng)建一個(gè)函數(shù)，要求該函數(shù)可以根據(jù)輸入的專業(yè)名稱，返回本專業(yè)學(xué)生的學(xué)號(hào)、姓名、性別、出生日期、身份證號(hào)。8.1.1情景描述為了解決上述問(wèn)題，需要完成以下任務(wù)：1．分析專業(yè)名稱和學(xué)生信息之間的關(guān)聯(lián)，先從專業(yè)名稱，在專業(yè)表中查詢出專業(yè)代碼，再由專業(yè)代碼，在班級(jí)表中查詢出班級(jí)編號(hào)，最后根據(jù)班級(jí)編號(hào)，在學(xué)生表中查詢出學(xué)生的信息。2．根據(jù)分析結(jié)果，寫(xiě)出創(chuàng)建函數(shù)的命令。3．執(zhí)行函數(shù)以驗(yàn)證結(jié)果。8.1.2問(wèn)題分析1．打開(kāi)SQLServerManagementStudio，單擊“對(duì)象資源管理器”中的“數(shù)據(jù)庫(kù)”文件夾下的數(shù)據(jù)庫(kù)“學(xué)生管理”；2．單擊工具欄上的“新建查詢”命令，打開(kāi)“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.1.3解決方案CREATEFUNCTIONfunstudent(@majorvarchar(32))RETURNSTABLEASRETURN(SELECT學(xué)號(hào),姓名,性別,出生日期,身份證號(hào)

FROM學(xué)生

WHERE班級(jí)編號(hào)IN(SELECT班級(jí)代碼

FROM班級(jí)

WHERE專業(yè)代碼IN(SELECT專業(yè)代碼

FROM專業(yè)

WHERE專業(yè)名稱=@major)))4．單擊工具欄上的【執(zhí)行】按鈕，如圖所示。5．在“查詢編輯器”上輸入語(yǔ)句“SELECT*FROMfunstudent('軟件技術(shù)')”，單擊工具欄上的【執(zhí)行】按鈕，即可查詢軟件技術(shù)專業(yè)的學(xué)生的信息。編程基礎(chǔ)

變量與常量IF…ELSE語(yǔ)句

CASE語(yǔ)句WHILE語(yǔ)句RETURN語(yǔ)句函數(shù)標(biāo)量函數(shù)表值函數(shù)8.1.4知識(shí)總結(jié)常量表示一個(gè)特定數(shù)據(jù)值的符號(hào)，在程序運(yùn)行過(guò)程中始終保持不變，常量的格式取決于它所表示的值的數(shù)據(jù)類型，如字符常量必須用單引號(hào)括起來(lái)，由字母、數(shù)字及其它特殊字符組成；二進(jìn)制常量由0、1構(gòu)成；十進(jìn)制整型常量不帶小數(shù)點(diǎn)；日期常量也要用括號(hào)括起來(lái)等。在程序運(yùn)行過(guò)程中，值可以改變的量稱為變量，按照變量的有效作用范圍，可以分為局部變量和全局變量。局部變量的作用范圍僅限于程序內(nèi)部，局部變量名必須使用@符號(hào)開(kāi)始。全局變量的作用范圍不僅僅局限于某一程序，任何程序均可以隨時(shí)訪問(wèn)，全局變量經(jīng)常存儲(chǔ)一些SQLServer的配置設(shè)定和統(tǒng)計(jì)數(shù)據(jù)，不能由用戶的程序定義，全局變量以符號(hào)@@開(kāi)始。

變量與常量局部變量用關(guān)鍵字DECLARE聲明，語(yǔ)法格式如下：DECLARE@變量名數(shù)據(jù)類型參數(shù)說(shuō)明如下：@變量名：局部變量的名稱，必須使用@符號(hào)開(kāi)始，變量名遵守標(biāo)識(shí)符的命名規(guī)則。數(shù)據(jù)類型：用于指定局部變量的數(shù)據(jù)類型，可以是由系統(tǒng)提供的除了text、ntext、image之外的數(shù)據(jù)類型。變量聲明局部變量一旦聲明，初始值默認(rèn)為NULL，可以使用SET或SELECT命令為變量賦值，語(yǔ)法格式如下：SET@變量名=表達(dá)式或者SELECT@變量名=表達(dá)式參數(shù)說(shuō)明如下：@變量名：指定已經(jīng)聲明的要被賦值的變量名稱。表達(dá)式：合法的有效的SQLSERVER表達(dá)式。變量的賦值變量的值或常量的值可以通過(guò)SELECT或PRINT命令輸出，語(yǔ)法格式如下：SELECT局部變量|全局變量|常量PRINT局部變量|全局變量|常量參數(shù)說(shuō)明如下：@變量名：不加單引號(hào)。常量：如果是字符串要用括號(hào)括起來(lái)，如果是多個(gè)字符串的連接，需要用+號(hào)連接。變量的輸出【例8-1】定義一個(gè)字符串變量和整型變量，為其賦值，并輸出查看結(jié)果。DECLARE@coursevarchar(64),@aintSELECT@courseas'@course賦值前的值',@aas'@a賦值前的值'SELECT@course='sqlserver基礎(chǔ)',@a=1SELECT@courseas'@course賦值后的值',@aas'@a賦值后的值'SELECT'歡迎大家學(xué)習(xí)'+@course①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。SELECT@@SERVERNAMEAS'服務(wù)器名',@@VERSIONAS'版本'【例8-2】顯示當(dāng)前服務(wù)器的名稱及版本。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。條件判斷語(yǔ)句IF…ELSE用來(lái)判斷當(dāng)一個(gè)條件成立時(shí)執(zhí)行某段程序，條件不成立時(shí)執(zhí)行另外一段程序。語(yǔ)法格式如下：IF條件表達(dá)式

語(yǔ)句塊1ELSE

語(yǔ)句塊2參數(shù)說(shuō)明如下：條件表達(dá)式：關(guān)系運(yùn)算符和邏輯運(yùn)算符組成的表達(dá)式，其值決定分支的執(zhí)行路線。語(yǔ)句塊1：條件表達(dá)式成立時(shí)，執(zhí)行的語(yǔ)句塊，如果語(yǔ)句塊的語(yǔ)句多于一條，語(yǔ)句塊前用BEGIN，語(yǔ)句塊后用END。語(yǔ)句塊2：條件表達(dá)式不成立時(shí)，執(zhí)行的語(yǔ)句塊。ELSE語(yǔ)句塊2：可選項(xiàng)，最簡(jiǎn)單的IF語(yǔ)句沒(méi)有ELSE選項(xiàng)。IF…ELSE語(yǔ)句

DECLARE@pricemoneySET@price=66IF@price>50PRINT'價(jià)格過(guò)高'ELSEPRINT'價(jià)格比較合適'【例8-3】IF…ELSE語(yǔ)句的用法，IF語(yǔ)句成立執(zhí)行的語(yǔ)句只有一條。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。DECLARE@scorefloatSET@score=50IF@score<60BEGINSET@score=@score+20PRINT@scoreENDELSEBEGINSET@score=@score+10PRINT@scoreEND【例8-4】IF…ELSE語(yǔ)句的用法，IF語(yǔ)句成立時(shí)執(zhí)行的語(yǔ)句多于一條。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。當(dāng)條件表達(dá)式的分支多于兩條的時(shí)候，可以用CASE語(yǔ)句對(duì)每一種結(jié)果處理。語(yǔ)法格式如下：CASE條件表達(dá)式WHEN條件表達(dá)式結(jié)果1THEN語(yǔ)句1WHEN條件表達(dá)式結(jié)果2THEN語(yǔ)句2……WHEN條件表達(dá)式結(jié)果nTHEN語(yǔ)句nELSE語(yǔ)句n+1END參數(shù)說(shuō)明如下：條件表達(dá)式：關(guān)系運(yùn)算符和邏輯運(yùn)算符組成的表達(dá)式，其值決定分支的執(zhí)行路線。條件表達(dá)式結(jié)果：要與條件表達(dá)式的數(shù)據(jù)類型相同，二者如果相同，則執(zhí)行對(duì)應(yīng)THEN后面的語(yǔ)句。ELSE：與上面的條件表達(dá)式結(jié)果都不相同的時(shí)候，執(zhí)行ELSE后面的語(yǔ)句。CASE語(yǔ)句CASE語(yǔ)句執(zhí)行的步驟如下：①計(jì)算條件表達(dá)式的值，然后按照指定順序?qū)γ總€(gè)WHEN子句的條件表達(dá)式結(jié)果進(jìn)行比較。②一旦發(fā)現(xiàn)條件表達(dá)式和條件表達(dá)式結(jié)果相同，則返回對(duì)應(yīng)THEN后面的語(yǔ)句的執(zhí)行結(jié)果。③如果條件表達(dá)式和條件表達(dá)式結(jié)果都不能匹配，則返回ELSE后面的語(yǔ)句的執(zhí)行結(jié)果。DECLARE@scoreintSET@score=85SELECTCASE@score/10when10then'滿分'when9then'優(yōu)秀'when8then'良好'when7then'中等'when6then'及格'else'不及格'ENDAS'五級(jí)制成績(jī)'【例8-5】定義一個(gè)變量，賦值百分制成績(jī)，改為五級(jí)制成績(jī)輸出。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。WHILE語(yǔ)句可以根據(jù)某一條件重復(fù)執(zhí)行一段代碼，直到不滿足特定條件為止，WHILE語(yǔ)句有兩個(gè)關(guān)鍵部分，循環(huán)條件和循環(huán)語(yǔ)句，當(dāng)循環(huán)條件為真，就執(zhí)行循環(huán)體，循環(huán)體執(zhí)行結(jié)束就去判斷循環(huán)條件，如果循環(huán)條件繼續(xù)為真，則重復(fù)執(zhí)行循環(huán)體，一直到循環(huán)條件為假為止。語(yǔ)法格式如下：WHILE循環(huán)條件

循環(huán)體參數(shù)說(shuō)明如下：循環(huán)條件：關(guān)系運(yùn)算符和邏輯運(yùn)算符組成的表達(dá)式，其值循環(huán)體是否執(zhí)行。循環(huán)體：循環(huán)條件為真重復(fù)執(zhí)行的有效的SQL語(yǔ)句。如果循環(huán)體的語(yǔ)句多于一條，則循環(huán)體要用BEGIN…END括起來(lái)。在循環(huán)體內(nèi)可以使用BREAK語(yǔ)句無(wú)條件終止循環(huán)體的執(zhí)行；也可以使用CONTINUE語(yǔ)句提前結(jié)束本次循環(huán)，直接進(jìn)入下一次循環(huán)條件的判斷。WHILE語(yǔ)句SELECT教師編號(hào),教師姓名,CASE職稱

WHEN'教授'THEN'高級(jí)'WHEN'副教授'THEN'高級(jí)'WHEN'講師'THEN'中級(jí)'WHEN'助教'THEN'初級(jí)'ENDAS'教師職稱'FROM教師【例8-6】在“學(xué)生管理”數(shù)據(jù)庫(kù)里查詢教師編號(hào)、教師姓名、教師職稱，其中教師職稱用高級(jí)、中級(jí)、初級(jí)表示。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT'循環(huán)結(jié)束后，變量@i的值是：'+str(@i)PRINT'循環(huán)結(jié)束后，變量@sum的值是：'+str(@sum)【例8-7】計(jì)算1到100的和。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。DECLARE@iint,@aint,@bint,@cintSET@i=101WHILE@i<=999BEGINSET@a=@i/100SET@b=@i/10%10SET@c=@i%10IF@a*@a*@a+@b*@b*@b+@c*@c*@c=@iBEGINPRINT'找到的第一個(gè)水仙花數(shù):'+str(@i)breakENDSET@i=@i+1END【例8-8】水仙花數(shù)是一個(gè)三位數(shù)，滿足：個(gè)位數(shù)的立方、十位數(shù)的立方及百位數(shù)的立方三者之和等于此三位數(shù)本身。求從101開(kāi)始的第一個(gè)水仙花數(shù)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE@i<100BEGINSET@i=@i+1IF@i%2=0continueSET@sum=@sum+@iENDPRINT'1到的奇數(shù)之和是：'+str(@sum)【例8-9】求1到100的奇數(shù)之和。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。RETURN語(yǔ)句主要用于無(wú)條件的終止當(dāng)前SQL語(yǔ)句的的執(zhí)行，可用于存儲(chǔ)過(guò)程、函數(shù)等語(yǔ)句中。RETURN語(yǔ)句函數(shù)可以完成一個(gè)特定功能，常用系統(tǒng)函數(shù)的用法在前面已經(jīng)學(xué)習(xí)過(guò)，它們只能解決特定問(wèn)題，無(wú)法根據(jù)實(shí)際需要進(jìn)行調(diào)整，用戶可以根據(jù)需要?jiǎng)?chuàng)建自定義函數(shù)，以實(shí)現(xiàn)特殊的功能。在SQLServer中，根據(jù)函數(shù)的返回值的形式將用戶函數(shù)分為兩大類，分別是標(biāo)量函數(shù)和表值函數(shù)，其中表值函數(shù)又被分為內(nèi)嵌表值函數(shù)和多語(yǔ)句表值函數(shù)。函數(shù)如果函數(shù)返回值是標(biāo)量數(shù)據(jù)類型，則函數(shù)為標(biāo)量函數(shù)，標(biāo)量函數(shù)創(chuàng)建完成后，可以像使用系統(tǒng)函數(shù)一樣去調(diào)用。使用CREATEFUNCTION創(chuàng)建自定義標(biāo)量函數(shù)的語(yǔ)法格式如下：CREATEFUNCTION函數(shù)名（形式參數(shù)列表）RETURNS返回值類型ASBEGIN函數(shù)體END參數(shù)說(shuō)明如下：函數(shù)名：指定自定義函數(shù)的名稱，遵守標(biāo)識(shí)符的命名規(guī)則。形式參數(shù)列表：格式為“變量名

數(shù)據(jù)類型”，參數(shù)之間用逗號(hào)分隔。返回值類型：函數(shù)運(yùn)行結(jié)束時(shí)使用RETURN語(yǔ)句返回值的類型，可以是除了text、ntext、image和timestamp之外的基本數(shù)據(jù)類型。函數(shù)體：合法的SQL語(yǔ)句，必須包含RETURN語(yǔ)句，RETURN語(yǔ)句返回值的數(shù)據(jù)類型和RETURNS子句指定的返回值類型要一致。標(biāo)量函數(shù)CREATEFUNCTIONfunscore()RETURNSdecimal(5,2)ASBEGINRETURN(SELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=6AND課程編號(hào)=2)END【例8-10】創(chuàng)建一個(gè)函數(shù)名為funscore，求出學(xué)號(hào)為6、選修的課程編號(hào)為2的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“SELECTdbo.funscore（）”，單擊工具欄上的【執(zhí)行】按鈕，得出成績(jī)信息。說(shuō)明：函數(shù)如果沒(méi)有參數(shù)，函數(shù)名后面的小括號(hào)也不能省略；在第二行用RETURNS關(guān)鍵字指出函數(shù)運(yùn)行結(jié)束后返回的值的數(shù)據(jù)類型是decimal(5,2)，那么在函數(shù)體內(nèi)需要用RETURN語(yǔ)句返回一個(gè)此類型的值；標(biāo)量函數(shù)的調(diào)用方法和系統(tǒng)函數(shù)相同；此函數(shù)沒(méi)有參數(shù)，功能限制為只能返回學(xué)號(hào)為6的學(xué)生選修課程編號(hào)為2的成績(jī)，其它的學(xué)生的成績(jī)不能用此函數(shù)，可以通過(guò)創(chuàng)建有參數(shù)的函數(shù)來(lái)解決。CREATEFUNCTIONfunscorenew(@stuidint,@courseidint)RETURNSdecimal(5,2)ASBEGINRETURN(SELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=@stuidAND課程編號(hào)=@courseid)END【例8-11】創(chuàng)建一個(gè)函數(shù)名為funscorenew，輸入學(xué)號(hào)和課程編號(hào)，返回指定學(xué)號(hào)和課程編號(hào)的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“函數(shù)”，展開(kāi)“標(biāo)量值函數(shù)”，可以看到創(chuàng)建的函數(shù)前面加了所有者dbo，運(yùn)行結(jié)果如圖所示。

③在“查詢編輯器”上輸入語(yǔ)句“SELECTdbo.funscorenew(1,1)”，單擊工具欄上的【執(zhí)行】按鈕，得出學(xué)號(hào)為1的學(xué)生選修的課程編號(hào)為1的成績(jī)。說(shuō)明：函數(shù)的參數(shù)先寫(xiě)參數(shù)名，再寫(xiě)參數(shù)的數(shù)據(jù)類型；函數(shù)的運(yùn)行在SELECT語(yǔ)句中，要指出函數(shù)的實(shí)際參數(shù)值，實(shí)際參數(shù)值要和形式參數(shù)一一對(duì)應(yīng)。每次運(yùn)行函數(shù)，輸入的實(shí)際參數(shù)不同，得出的形式參數(shù)也不同。

CREATEFUNCTIONfunclass(@classidint)RETURNSintASBEGINRETURN(SELECTCOUNT(*)FROM學(xué)生

WHERE班級(jí)編號(hào)=@classid)END【例8-12】創(chuàng)建一個(gè)函數(shù)名為funclass，輸入班級(jí)編號(hào)，統(tǒng)計(jì)班級(jí)的學(xué)生人數(shù)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“函數(shù)”，展開(kāi)“標(biāo)量值函數(shù)”，可以看到創(chuàng)建的函數(shù)前面加了所有者dbo，運(yùn)行結(jié)果如圖所示。

③在“查詢編輯器”上輸入語(yǔ)句“SELECTdbo.funclass(3)”，單擊工具欄上的【執(zhí)行】按鈕，得出班級(jí)編號(hào)為3的學(xué)生人數(shù)。如果函數(shù)返回值是表（TABLE），則函數(shù)為表值函數(shù)，表值函數(shù)返回的結(jié)果是表，因此，表值函數(shù)的調(diào)用要放在SELECT語(yǔ)句的FROM子句調(diào)用，使用CREATEFUNCTION創(chuàng)建內(nèi)嵌表值函數(shù)的語(yǔ)法格式如下：CREATEFUNCTION函數(shù)名（形式參數(shù)列表）RETURNSTABLEASRETURN(SELECT語(yǔ)句)參數(shù)說(shuō)明如下：TABLE：指定函數(shù)返回值的類型為表。SELECT語(yǔ)句：?jiǎn)螚lselect查詢語(yǔ)句，查詢語(yǔ)句的結(jié)果做為函數(shù)返回的表。表值函數(shù)CREATEFUNCTIONfunteacher(@deptnamevarchar(50))RETURNStableASRETURN(SELECT教師編號(hào),教師姓名,性別,職稱,學(xué)歷,學(xué)位,專業(yè)

FROM教師ASaJOIN系部ASbONa.系部代碼=b.系部代碼

WHERE系部名稱=@deptname)【例8-13】創(chuàng)建一個(gè)函數(shù)名為funteacher，輸入系部名稱，查詢指定系部的教師的編號(hào)、姓名、性別、職稱、學(xué)歷、學(xué)位、專業(yè)信息。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“函數(shù)”，展開(kāi)“表值函數(shù)”，可以看到創(chuàng)建的函數(shù)前面加了所有者dbo，運(yùn)行結(jié)果如圖所示。

③在“查詢編輯器”上輸入語(yǔ)句“SELECT*FROMfunteacher('電子系')”，單擊工具欄上的【執(zhí)行】按鈕，查詢電子系的教師的信息。說(shuō)明：函數(shù)的參數(shù)的數(shù)據(jù)類型要和數(shù)據(jù)庫(kù)中系部表的字段“系部名稱”的數(shù)據(jù)類型相同；返回的結(jié)果是表，SELECT語(yǔ)句查詢的結(jié)果就是表的格式，當(dāng)作函數(shù)的結(jié)果。

CREATEFUNCTIONfunstuscore(@stuidint=1)RETURNSTABLEASRETURN(SELECTxs.學(xué)號(hào),姓名,性別,xk.課程編號(hào),課程名稱,成績(jī)FROM學(xué)生ASxsJOIN選課ASxkONxs.學(xué)號(hào)=xk.學(xué)號(hào)

JOIN課程ASkcONkc.課程編號(hào)=xk.課程編號(hào)

WHERExs.學(xué)號(hào)=@stuid)【例8-14】創(chuàng)建一個(gè)函數(shù)名為funstuscore，輸入學(xué)號(hào)，查詢指定學(xué)生的選課信息。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“函數(shù)”，展開(kāi)“表值函數(shù)”，可以看到表值函數(shù)dbo.funstuscore，查看其參數(shù)，@stuid有默認(rèn)值，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“SELECT*FROMfunstuscore(DEFAULT)”，單擊工具欄上的【執(zhí)行】按鈕，可查詢學(xué)號(hào)為默認(rèn)值1的選課信息，參數(shù)DEFAULT也可以換成指定的學(xué)號(hào)。說(shuō)明：在創(chuàng)建函數(shù)的時(shí)候，可以指定默認(rèn)值。當(dāng)函數(shù)定義需要修改的時(shí)候，使用ALTERFUNCTION命令，修改函數(shù)的的語(yǔ)法與創(chuàng)建函數(shù)的語(yǔ)法一樣，只需要將CREATE換成ALTER即可，但是不能修改自定義函數(shù)的類型，即不能將標(biāo)量函數(shù)更改為內(nèi)聯(lián)表值函數(shù)或者多語(yǔ)句表值函數(shù)。修改函數(shù)ALTERFUNCTIONfunscore()RETURNSdecimal(5,2)ASBEGINRETURN(SELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=5AND課程編號(hào)=1)END【例8-15】修改已經(jīng)存在的函數(shù)為funscore，更改為查詢學(xué)號(hào)為5，選修的課程編號(hào)為1的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“SELECTdbo.funscore()”，單擊工具欄上的【執(zhí)行】按鈕，得出固定的學(xué)號(hào)為5的學(xué)生選修的課程編號(hào)為1的成績(jī)。說(shuō)明：在修改函數(shù)的時(shí)候，可以修改函數(shù)的定義，但是如果更改函數(shù)返回值的類型，就會(huì)產(chǎn)生錯(cuò)誤，即如果把函數(shù)返回值的類型decimal（5,2）更改為T(mén)ABLE類型，則會(huì)產(chǎn)生錯(cuò)誤?？梢允褂肈ROPFUNCTION命令刪除自定義函數(shù)，語(yǔ)法格式如下：DROPFUNCTION函數(shù)名刪除用戶自定義函數(shù)DROPFUNCTIONfunstuscore【例8-16】刪除函數(shù)funscore。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。

③在“查詢編輯器”上輸入語(yǔ)句“SELECTdbo.funscore()”，單擊工具欄上的【執(zhí)行】按鈕，服務(wù)器會(huì)報(bào)錯(cuò)。在銷(xiāo)售數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)函數(shù)funproduction，根據(jù)輸入的顧客編號(hào)，查詢顧客購(gòu)買(mǎi)的商品編號(hào)，商品名稱，數(shù)量，價(jià)格，總價(jià)，商品類別。1．打開(kāi)SQLServerManagementStudio，單擊“對(duì)象資源管理器”中的“數(shù)據(jù)庫(kù)”文件夾下的數(shù)據(jù)庫(kù)“銷(xiāo)售”；2．單擊工具欄上的“新建查詢”命令，打開(kāi)“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.1.5應(yīng)用實(shí)踐CREATEFUNCTIONfunproduction(@customeridvarchar(50))RETURNSTABLEASRETURN(SELECTsp.商品ID,名稱,類別名稱,數(shù)量,價(jià)格,總價(jià)

FROM商品ASspJOIN商品類型ASsplxONsp.類別ID=splx.類別IDJOIN銷(xiāo)售ASxsONxs.商品ID=sp.商品IDWHERE顧客ID=@customerid)4．單擊工具欄上的【執(zhí)行】按鈕，如圖所示。5．在“查詢編輯器”上輸入語(yǔ)句“SELECT*FROMfunproduction(2)”，單擊工具欄上的【執(zhí)行】按鈕，查詢顧客編號(hào)為2的顧客購(gòu)買(mǎi)的商品信息。

8.2.1情景描述8.2.2問(wèn)題分析8.2.3解決方案8.2.4知識(shí)總結(jié)8.2.5應(yīng)用實(shí)踐任務(wù)8.2創(chuàng)建存儲(chǔ)過(guò)程根據(jù)學(xué)生信息管理系統(tǒng)的需求，數(shù)據(jù)庫(kù)的開(kāi)發(fā)人員需要?jiǎng)?chuàng)建一個(gè)存儲(chǔ)過(guò)程，根據(jù)輸入的職稱，統(tǒng)計(jì)這類職稱的教師人數(shù)，同時(shí)返回教師的編號(hào)、姓名、性別、職稱、學(xué)歷、系部名稱的具體信息。8.2.1情景描述為了解決上述問(wèn)題，需要完成以下任務(wù)：1．寫(xiě)出統(tǒng)計(jì)指定職稱的查詢語(yǔ)句；2．寫(xiě)出查詢指定職稱的教師信息的語(yǔ)句；3．寫(xiě)出創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句；4．調(diào)用存儲(chǔ)過(guò)程以驗(yàn)證結(jié)果。8.2.2問(wèn)題分析

8.2.3解決方案CREATEPROCprocteachercount@titlevarchar(20),@cintOUTPUTASBEGINSELECT@c=COUNT(*)FROM教師WHERE職稱=@titleSELECT教師編號(hào),教師姓名,性別,職稱,學(xué)歷,系部名稱FROM教師JOIN系部ON教師.系部代碼=系部.系部代碼WHERE職稱=@titleEND1．打開(kāi)SQLServerManagementStudio，單擊“對(duì)象資源管理器”中的“數(shù)據(jù)庫(kù)”文件夾下的數(shù)據(jù)庫(kù)“學(xué)生管理”；2．單擊工具欄上的“新建查詢”命令，打開(kāi)“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：4．單擊工具欄上的【執(zhí)行】按鈕，如圖所示。5．在“查詢編輯器”輸入以下語(yǔ)句之后，單擊工具欄上的【執(zhí)行】按鈕，在結(jié)果欄的“消息”選項(xiàng)卡內(nèi)返回查詢結(jié)果影響的行數(shù)，及統(tǒng)計(jì)的指定職稱的人數(shù)。在結(jié)果選項(xiàng)卡中返回指定職稱的教師信息。DECLARE@countintEXECprocteachercount'副教授',@countoutputPRINT@count存儲(chǔ)過(guò)程的概念

引入存儲(chǔ)過(guò)程的好處存儲(chǔ)過(guò)程的創(chuàng)建和調(diào)用修改存儲(chǔ)過(guò)程刪除用戶自定義存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程信息重命名存儲(chǔ)過(guò)程8.2.4知識(shí)總結(jié)數(shù)據(jù)庫(kù)中保存的預(yù)先編譯好的獨(dú)立的數(shù)據(jù)庫(kù)對(duì)象，駐留在數(shù)據(jù)庫(kù)中，可以被應(yīng)用程序調(diào)用，并允許數(shù)據(jù)以參數(shù)的形式在過(guò)程與應(yīng)用程序之間傳遞。能接收輸入?yún)?shù)的值，存儲(chǔ)過(guò)程的定義都包含對(duì)數(shù)據(jù)庫(kù)進(jìn)行查詢修改的SQL語(yǔ)句，有返回值；它的返回值只是指明執(zhí)行知否成功，不能返回用戶需要的結(jié)果；存儲(chǔ)過(guò)程不能直接在表達(dá)式中使用，可以帶多個(gè)輸出參數(shù)。存儲(chǔ)過(guò)程主要分為三類，系統(tǒng)存儲(chǔ)過(guò)程，擴(kuò)展存儲(chǔ)過(guò)程和用戶自定義的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程的概念存儲(chǔ)過(guò)程在服務(wù)器端運(yùn)行，執(zhí)行速度快，存儲(chǔ)過(guò)程創(chuàng)建好后被編譯成可執(zhí)行的系統(tǒng)代碼保留在服務(wù)器中，一般用戶只需要提供存儲(chǔ)過(guò)程所需的參數(shù)，執(zhí)行存儲(chǔ)過(guò)程，就能得到所需的查詢結(jié)果，而不用管具體的實(shí)現(xiàn)過(guò)程；存儲(chǔ)過(guò)程存儲(chǔ)在服務(wù)器上并在服務(wù)器上執(zhí)行，網(wǎng)絡(luò)上只傳送存儲(chǔ)過(guò)程執(zhí)行的最終數(shù)據(jù)，可以減少網(wǎng)絡(luò)流量；存儲(chǔ)過(guò)程一旦創(chuàng)建，可以多次被用戶調(diào)用，而不必重新編寫(xiě)SQL語(yǔ)句，實(shí)現(xiàn)了模塊化程序設(shè)計(jì)的思想；存儲(chǔ)過(guò)程如果需要修改，在修改之后，所有調(diào)用該存儲(chǔ)過(guò)程的程序得到的結(jié)果都會(huì)隨之改變，提高了程序的可移植性；用戶可以被授予權(quán)限執(zhí)行存儲(chǔ)過(guò)程，而不必?fù)碛性L問(wèn)存儲(chǔ)過(guò)程中引用的表的權(quán)限，即當(dāng)用戶需要訪問(wèn)表中數(shù)據(jù)但是沒(méi)有權(quán)限的時(shí)候，可以設(shè)計(jì)一個(gè)存儲(chǔ)過(guò)程來(lái)存取表中的數(shù)據(jù)，提供給用戶，存儲(chǔ)過(guò)程只作為一個(gè)存取通道，保護(hù)了數(shù)據(jù)的安全性。引入存儲(chǔ)過(guò)程的好處存儲(chǔ)過(guò)程和表、視圖等數(shù)據(jù)庫(kù)對(duì)象一樣，在使用前要先創(chuàng)建，使用CREATEPROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法格式如下：存儲(chǔ)過(guò)程的創(chuàng)建和調(diào)用CREATEPROCEDURE存儲(chǔ)過(guò)程名參數(shù)列表WITHENCRYPTIONASBEGINSQL語(yǔ)句END參數(shù)說(shuō)明如下：存儲(chǔ)過(guò)程名：指定存儲(chǔ)過(guò)程的名稱，遵守標(biāo)識(shí)符的命名規(guī)則，建議前綴加proc。參數(shù)列表：可以省略，格式為“參數(shù)名

數(shù)據(jù)類型”，參數(shù)之間用逗號(hào)分隔，參數(shù)可以指定默認(rèn)值，格式為“參數(shù)名

數(shù)據(jù)類型=默認(rèn)值”，如果是輸出參數(shù)，格式為“參數(shù)名數(shù)據(jù)類型OUTPUT”。WITHENCRYPTION：用于加密存儲(chǔ)過(guò)程定義語(yǔ)句的文本。SQL語(yǔ)句：合法的SQL語(yǔ)句，用于定義存儲(chǔ)過(guò)程執(zhí)行的操作。如果存儲(chǔ)過(guò)程的定義只有一條SQL語(yǔ)句，那么BEGIN…END可以省略。存儲(chǔ)過(guò)程創(chuàng)建成功后，使用EXECUTE命令執(zhí)行存儲(chǔ)過(guò)程，EXECUTE可以省略為EXEC。CREATEPROCEDUREprocscoreASBEGINSELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=6AND課程編號(hào)=2END【例8-17】創(chuàng)建存儲(chǔ)過(guò)程procscore，查詢學(xué)號(hào)為6、選修的課程編號(hào)為2的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“EXECprocscore”，單擊工具欄上的【執(zhí)行】按鈕，查詢固定的學(xué)號(hào)為6的學(xué)生選修的課程編號(hào)為2的成績(jī)。說(shuō)明：存儲(chǔ)過(guò)程的定義只有一條SQL語(yǔ)句，此例中BEGIN…END可以省略。EXEC是關(guān)鍵字EXECUTE的簡(jiǎn)寫(xiě)。執(zhí)行存儲(chǔ)過(guò)程的時(shí)候，如果一次只執(zhí)行一條語(yǔ)句，那么EXEC也可以省略。

CREATEPROCprocscorenew@stuidint,@courseidintASSELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=@stuidAND課程編號(hào)=@courseid【例8-18】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程名為procscorenew，輸入學(xué)號(hào)和課程編號(hào)，查詢指定學(xué)號(hào)和課程編號(hào)的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“存儲(chǔ)過(guò)程”，可以看到創(chuàng)建的存儲(chǔ)過(guò)程前面加了所有者dbo，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“procscorenew1,1”，單擊工具欄上的【執(zhí)行】按鈕，得出學(xué)號(hào)為1的學(xué)生選修的課程編號(hào)為1的成績(jī)。說(shuō)明：PROC是關(guān)鍵字PROCEDURE的簡(jiǎn)寫(xiě)形式；存儲(chǔ)過(guò)程只用了一條SQL語(yǔ)句，BEGIN…END可以省略；存儲(chǔ)過(guò)程的執(zhí)行只有一條語(yǔ)句，EXEC可以省略；實(shí)際參數(shù)和存儲(chǔ)過(guò)程定義指定的參數(shù)要一一對(duì)應(yīng)。CREATEPROCprocclass@classidint=1ASSELECTCOUNT(*)FROM學(xué)生

WHERE班級(jí)編號(hào)=@classid【例8-19】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程名為procclass，輸入班級(jí)編號(hào)，查詢指定班級(jí)的學(xué)生個(gè)數(shù)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，展開(kāi)“對(duì)象資源管理器”窗口的“學(xué)生管理”數(shù)據(jù)庫(kù)下的“可編程性”文件夾，雙擊“存儲(chǔ)過(guò)程”，可以看到創(chuàng)建的存儲(chǔ)過(guò)程前面加了所有者dbo，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“procclass”，單擊工具欄上的【執(zhí)行】按鈕，顯示班級(jí)編號(hào)為默認(rèn)值1的學(xué)生個(gè)數(shù)。說(shuō)明：PROC是關(guān)鍵字PROCEDURE的簡(jiǎn)寫(xiě)形式；存儲(chǔ)過(guò)程的執(zhí)行也可以用EXECprocclass；存儲(chǔ)過(guò)程的定義有一個(gè)參數(shù)，在執(zhí)行的過(guò)程中如果沒(méi)有指定參數(shù)值，則用默認(rèn)的參數(shù)值，也可以指定班級(jí)編號(hào)。CREATEPROCEDUREprocteacher@deptnamevarchar(50)WITHENCRYPTIONASSELECT教師編號(hào),教師姓名,性別,職稱,學(xué)歷,學(xué)位,專業(yè)

FROM教師ASaJOIN系部ASbONa.系部代碼=b.系部代碼

WHERE系部名稱=@deptname【例8-20】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程名為procteacher，輸入系部名稱，查詢指定系部的教師編號(hào)、教師姓名、性別、職稱、學(xué)歷、學(xué)位、專業(yè)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入語(yǔ)句“procteacher'計(jì)算機(jī)系'”，單擊工具欄上的【執(zhí)行】按鈕，顯示計(jì)算機(jī)系的老師信息。

CREATEPROCprocsum@nint,@sumintOUTPUTASBEGINDECLARE@iintSET@i=1SET@sum=0WHILE@i<=@nBEGINSET@sum=@sum+@iSET@i=@i+1ENDENDDECLARE@sumintEXECprocsum100,@sumoutputPRINT@sum【例8-21】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程名為procsum，輸入一個(gè)數(shù)n，計(jì)算從1到輸入的數(shù)n的和。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入以下語(yǔ)句之后，單擊工具欄上的【執(zhí)行】按鈕，計(jì)算出從1到輸入?yún)?shù)100的和。CREATEPROCprocscorecourse@courseidint,@maxscorenumeric(5,2)OUTPUT,@minscorenumeric(5,2)OUTPUT,@avgscorenumeric(5,2)OUTPUTASBEGINSELECT@maxscore=MAX(成績(jī)),@minscore=MIN(成績(jī)),@avgscore=AVG(成績(jī))FROM選課

WHERE課程編號(hào)=@courseid

SELECT學(xué)生.學(xué)號(hào),姓名,性別,身份證號(hào)FROM學(xué)生JOIN選課ON學(xué)生.學(xué)號(hào)=選課.學(xué)號(hào)WHERE成績(jī)<@avgscoreENDDECLARE@maxnumeric(5,2),@minnumeric(5,2),@avgnumeric(5,2)EXECprocscorecourse1,@maxoutput,@minoutput,@avgoutputPRINT'最高分：'+CONVERT(VARCHAR(5),@max)PRINT'最低分：'+CONVERT(VARCHAR(5),@min)PRINT'平均分：'+CONVERT(VARCHAR(5),@avg)【例8-22】創(chuàng)建一個(gè)存儲(chǔ)過(guò)程名為procscorecourse，輸入課程編號(hào)，查詢指定課程編號(hào)的課程的最高成績(jī)，最低成績(jī)，平均成績(jī)，并查詢成績(jī)低于指定課程平均分的學(xué)生的學(xué)號(hào)，姓名，性別，身份證號(hào)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。③在“查詢編輯器”上輸入以下語(yǔ)句之后，單擊工具欄上的【執(zhí)行】按鈕，在結(jié)果欄的“消息”選項(xiàng)卡內(nèi)返回查詢結(jié)果影響的行數(shù)，及最高成績(jī)，最低成績(jī)，平均成績(jī)。在結(jié)果選項(xiàng)卡中返回的小于平均成績(jī)的學(xué)生的信息。當(dāng)存儲(chǔ)過(guò)程的定義需要修改的時(shí)候，使用ALTERPROCEDURE命令，修改存儲(chǔ)過(guò)程的語(yǔ)法與創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法一樣，只需要將CREATE換成ALTER即可。修改存儲(chǔ)過(guò)程ALTERPROCEDUREprocscoreASBEGINSELECT成績(jī)

FROM選課WHERE學(xué)號(hào)=5AND課程編號(hào)=1END【例8-23】修改已經(jīng)存在的存儲(chǔ)過(guò)程名為procscore，更改為查詢學(xué)號(hào)為5，選修的課程編號(hào)為1的成績(jī)。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕。③在“查詢編輯器”上輸入語(yǔ)句“procscore”，單擊工具欄上的【執(zhí)行】按鈕，查詢固定的學(xué)號(hào)為5的學(xué)生選修的課程編號(hào)為1的成績(jī)?？梢允褂肈ROPPROCEDURE命令刪除自定義存儲(chǔ)過(guò)程，語(yǔ)法格式為：刪除用戶自定義存儲(chǔ)過(guò)程DROPPROCEDURE存儲(chǔ)過(guò)程名DROPPROCEDUREprocscore【例8-24】刪除存儲(chǔ)過(guò)程procscore。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕。③在“查詢編輯器”上輸入語(yǔ)句“procscore”，單擊工具欄上的【執(zhí)行】按鈕，則會(huì)提示找不到存儲(chǔ)過(guò)程procscore?？梢允褂孟到y(tǒng)存儲(chǔ)過(guò)程sp_help查看存儲(chǔ)過(guò)程的基本信息；用sp_helptext存儲(chǔ)過(guò)程的定義信息，用法和查看視圖信息相同。查看存儲(chǔ)過(guò)程信息sp_helpprocscorecourse【例8-25】用sp_help查看存儲(chǔ)過(guò)程procscorecourse的基本信息。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。sp_helptextprocscorecourse【例8-26】用sp_helptext查看存儲(chǔ)過(guò)程procscorecourse的定義文本。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。sp_helptextprocteacher【例8-27】用sp_helptext查看加密存儲(chǔ)過(guò)程procteacher的定義文本。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示?？梢允褂胹p_rename來(lái)對(duì)存儲(chǔ)過(guò)程進(jìn)行改名，語(yǔ)法格式如下：重命名存儲(chǔ)過(guò)程sp_rename存儲(chǔ)過(guò)程原名,存儲(chǔ)過(guò)程新名稱參數(shù)說(shuō)明如下：存儲(chǔ)過(guò)程原名：指定數(shù)據(jù)庫(kù)中存在的存儲(chǔ)過(guò)程的名稱。存儲(chǔ)過(guò)程新名稱：指定存儲(chǔ)過(guò)程更改名稱后的名稱。sp_renameprocteacher,procteachernew【例8-28】用sp_rename更改存儲(chǔ)過(guò)程procteacher為procteachernew。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，運(yùn)行結(jié)果如圖所示。在銷(xiāo)售數(shù)據(jù)庫(kù)中，創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，根據(jù)輸入的供應(yīng)商名稱，統(tǒng)計(jì)從該供應(yīng)商進(jìn)貨的商品數(shù)量，并查詢從該供應(yīng)商進(jìn)貨的商品的編號(hào)，名稱，價(jià)格，保質(zhì)期。1．打開(kāi)SQLServerManagementStudio，單擊“對(duì)象資源管理器”中的“數(shù)據(jù)庫(kù)”文件夾下的數(shù)據(jù)庫(kù)“銷(xiāo)售”；2．單擊工具欄上的“新建查詢”命令，打開(kāi)“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.2.5應(yīng)用實(shí)踐CREATEPROCprocproduct@supplynamevarchar(50),@cintOUTPUTASBEGINSELECT@c=COUNT(*)FROM進(jìn)貨

WHERE供應(yīng)商ID=(SELECT供應(yīng)商IDFROM供應(yīng)商

WHERE名稱=@supplyname)SELECT商品.商品ID,商品.名稱,商品.價(jià)格,商品.保質(zhì)期

FROM商品JOIN進(jìn)貨ON商品.商品ID=進(jìn)貨.商品IDJOIN供應(yīng)商O(píng)N供應(yīng)商.供應(yīng)商ID=進(jìn)貨.供應(yīng)商IDAND供應(yīng)商.名稱=@supplynameEND4．單擊工具欄上的【執(zhí)行】按鈕，如圖所示。DECLARE@countintEXECprocproduct'重慶渝州服裝廠',@countoutputPRINT@count5．在“查詢編輯器”上輸入以下語(yǔ)句之后，單擊工具欄上的【執(zhí)行】按鈕，在結(jié)果欄的“消息”選項(xiàng)卡內(nèi)返回查詢結(jié)果影響的行數(shù)，及從指定供應(yīng)商進(jìn)貨的商品的個(gè)數(shù)。在結(jié)果選項(xiàng)卡中返回從指定供應(yīng)商的進(jìn)貨的商品信息。8.3.1情景描述8.3.2問(wèn)題分析8.3.3解決方案8.3.4知識(shí)總結(jié)8.3.5應(yīng)用實(shí)踐任務(wù)8.3創(chuàng)建觸發(fā)器在班級(jí)表中，用專業(yè)代碼存儲(chǔ)班級(jí)所在的專業(yè)信息，但是專業(yè)代碼在班級(jí)表中并沒(méi)有被設(shè)置外鍵，以關(guān)聯(lián)專業(yè)表的專業(yè)代碼字段。這樣在班級(jí)表中插入記錄的時(shí)候，很容易保存一條不存在的專業(yè)代碼的班級(jí)信息。數(shù)據(jù)庫(kù)開(kāi)發(fā)人員經(jīng)過(guò)分析得出兩種方案，一是需要修改專業(yè)表，增加專業(yè)代碼的外鍵關(guān)聯(lián)；二是在專業(yè)表中創(chuàng)建一個(gè)觸發(fā)器來(lái)完成。經(jīng)過(guò)考慮，數(shù)據(jù)庫(kù)開(kāi)發(fā)人員采用第二種方案。8.3.1情景描述為了解決上述問(wèn)題，需要完成以下任務(wù)：1．在班級(jí)表中創(chuàng)建一個(gè)插入觸發(fā)器；2．在班級(jí)表中執(zhí)行插入操作，激活觸發(fā)器，驗(yàn)證觸發(fā)器的工作。8.3.2問(wèn)題分析1．打開(kāi)SQLServerManagementStudio，單擊“對(duì)象資源管理器”中的“數(shù)據(jù)庫(kù)”文件夾下的數(shù)據(jù)庫(kù)“學(xué)生管理”；2．單擊工具欄上的“新建查詢”命令，打開(kāi)“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.3.3解決方案CREATETRIGGERtriclassinsertON班級(jí)AFTERINSERTASBEGINIF(select專業(yè)代碼FROMinserted)NOTIN(SELECT專業(yè)代碼FROM專業(yè))BEGINPRINT'你要插入的班級(jí)信息的專業(yè)代碼在專業(yè)表中不存在!'ROLLBACKENDEND4．單擊工具欄上的【執(zhí)行】按鈕，提示“命令成功完成”，在對(duì)象資源管理器窗口的“班級(jí)”表下的“觸發(fā)器”文件夾，可以看到創(chuàng)建的觸發(fā)器。5．在“查詢編輯器”輸入以下語(yǔ)句之后，單擊工具欄上的【執(zhí)行】按鈕，激活觸發(fā)器，執(zhí)行結(jié)果如圖所示。INSERTINTO班級(jí)VALUES(8,’計(jì)算機(jī)1403’,8,’2014級(jí)’,’張靜’,’互聯(lián)網(wǎng)’)6．在“查詢編輯器”輸入語(yǔ)句“SELECT*FROM班級(jí)WHERE班級(jí)代碼=8”之后，單擊工具欄上的【執(zhí)行】按鈕，查詢結(jié)果為空，則剛才的插入操作被撤銷(xiāo)執(zhí)行。觸發(fā)器的概念觸發(fā)器的分類觸發(fā)器的作用DDL觸發(fā)器的創(chuàng)建與管理DML觸發(fā)器的創(chuàng)建與管理8.3.4知識(shí)總結(jié)觸發(fā)器和存儲(chǔ)過(guò)程一樣，是一組T-SQL語(yǔ)句的集合，是一種特殊的存儲(chǔ)過(guò)程，作為表的一部分被創(chuàng)建，當(dāng)向表中插入、更新或刪除記錄的時(shí)候自動(dòng)執(zhí)行，不能像存儲(chǔ)過(guò)程一樣由用戶調(diào)用執(zhí)行，只要觸發(fā)器觸發(fā)的條件滿足，就會(huì)自動(dòng)觸發(fā)執(zhí)行。觸發(fā)器一旦運(yùn)行，就會(huì)產(chǎn)生兩個(gè)臨時(shí)表，即已插入表（inserted）和已刪除表（deleted），這兩個(gè)臨時(shí)表存儲(chǔ)在內(nèi)存中，由系統(tǒng)管理，用戶不能執(zhí)行插入、更新和刪除操作，只能執(zhí)行查詢操作；表的結(jié)構(gòu)與該觸發(fā)器所在的表是相同的，具有相同的列名和列的定義。當(dāng)觸發(fā)器工作完成后，這兩張臨時(shí)表也會(huì)被刪除。觸發(fā)器的概念根據(jù)觸發(fā)器觸發(fā)的事件的不同，可以把觸發(fā)器分為兩大類型，即DML（數(shù)據(jù)修改語(yǔ)言）觸發(fā)器和DDL觸發(fā)器（數(shù)據(jù)定義語(yǔ)言）。DML觸發(fā)器根據(jù)具體觸發(fā)的語(yǔ)句，又分為INSERT觸發(fā)器，UPDATE觸發(fā)器和DELETE觸發(fā)器。根據(jù)觸發(fā)器觸發(fā)的方式不同，可以把觸發(fā)器分為后觸發(fā)器（AFTER觸發(fā)器）和替代觸發(fā)器（INSTEADOF觸發(fā)器）。觸發(fā)器的分類保持?jǐn)?shù)據(jù)同步能夠?qū)Ρ碇械臄?shù)據(jù)進(jìn)行級(jí)聯(lián)修改觸發(fā)器可以實(shí)現(xiàn)比CHECK約束更為復(fù)雜的約束防止非法修改數(shù)據(jù)觸發(fā)器的作用創(chuàng)建DDL觸發(fā)器修改DDL觸發(fā)器刪除DDL觸發(fā)器DDL觸發(fā)器的創(chuàng)建與管理使用CREATETRIGGER命令創(chuàng)建DDL觸發(fā)器的語(yǔ)法規(guī)則如下：創(chuàng)建DDL觸發(fā)器CREATETRIGGER觸發(fā)器名ONALLSERVER|DATABASEAFTER|FOR操作ASBEGINSQL語(yǔ)句END參數(shù)說(shuō)明如下：觸發(fā)器名：指定定義的觸發(fā)器名稱，遵守標(biāo)識(shí)符的命名規(guī)則，但不能以#或##開(kāi)頭，建議前綴加tri。ALLSERVER|DATABASE：指定觸發(fā)器的作用域，ALLSERVER指觸發(fā)器應(yīng)用于整個(gè)服務(wù)器，DATABASE指觸發(fā)器作用于當(dāng)前數(shù)據(jù)庫(kù)，兩者選一個(gè)。AFTER|FOR：指定觸發(fā)器指定的操作成功執(zhí)行后被觸發(fā)，兩者選一個(gè)，實(shí)現(xiàn)的功能相同。操作：指定觸發(fā)器觸發(fā)的操作，DDL觸發(fā)器觸發(fā)的事件。SQL語(yǔ)句：觸發(fā)器實(shí)現(xiàn)的操作。CREATETRIGGERtriddlONDATABASEFORALTER_TABLEASBEGINPRINT'不能修改表'ROLLBACKENDALTERTABLE學(xué)生ADD健康狀況varchar(6)【例8-29】在學(xué)生管理數(shù)據(jù)庫(kù)中，創(chuàng)建觸發(fā)器triddl，不允許對(duì)數(shù)據(jù)庫(kù)中的表作任何修改。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】，如圖所示，測(cè)試觸發(fā)器的功能。DDL觸發(fā)器創(chuàng)建完成后，如果需要修改定義，則把CREATE改為ALTER即可。修改DDL觸發(fā)器ALTERTRIGGERtriddlONDATABASEFORDROP_TABLEASBEGINPRINT'不能刪除表'ROLLBACKEND--觸發(fā)器觸發(fā)的事件DROPTABLE學(xué)生備份【例8-30】在學(xué)生管理數(shù)據(jù)庫(kù)中，修改觸發(fā)器triddl，不允許對(duì)數(shù)據(jù)庫(kù)中的表進(jìn)行刪除操作。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】，如圖所示，測(cè)試觸發(fā)器的功能。使用DROPTRIGGER命令刪除DDL觸發(fā)器的語(yǔ)法規(guī)則如下：刪除DDL觸發(fā)器DROPTRIGGER觸發(fā)器名ONALLSERVER|DATABASE參數(shù)說(shuō)明如下：觸發(fā)器名：指定要?jiǎng)h除的觸發(fā)器的名稱，如果是多個(gè)觸發(fā)器，名稱之間用逗號(hào)分隔。ALLSERVER|DATABASE：指定刪除的觸發(fā)器的作用域。DROPTRIGGERtriddlONDATABASE--觸發(fā)器觸發(fā)的事件DROPTABLE學(xué)生備份【例8-31】在學(xué)生管理數(shù)據(jù)庫(kù)中，刪除觸發(fā)器triddl。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】按鈕，由于限制刪除表的觸發(fā)器已經(jīng)不存在了，則會(huì)刪除成功。創(chuàng)建DML觸發(fā)器修改DML觸發(fā)器刪除DML觸發(fā)器查看DML觸發(fā)器禁用和啟用DML觸發(fā)器DML觸發(fā)器的創(chuàng)建與管理使用CREATETRIGGER命令創(chuàng)建DML觸發(fā)器的語(yǔ)法規(guī)則如下：創(chuàng)建DML觸發(fā)器CREATETRIGGER觸發(fā)器名ON表名|視圖名AFTER|FOR|INSTEADOF[INSERT][,][UPDATE][,][DELETE]ASBEGINSQL語(yǔ)句END參數(shù)說(shuō)明如下：觸發(fā)器名：指定定義的觸發(fā)器名稱，遵守標(biāo)識(shí)符的命名規(guī)則，但不能以#或##開(kāi)頭，建議前綴加tri。表名|視圖名：指定觸發(fā)器所在的表名或視圖名，兩者選一個(gè)，視圖只能被INSTEADOF觸發(fā)器引用。AFTER|FOR|INSTEADOF：AFTER或FOR指定DML觸發(fā)器僅在觸發(fā)SQL語(yǔ)句中指定的所有操作都已成功執(zhí)行時(shí)才觸發(fā)，INSTEADOF替代類型觸發(fā)器，執(zhí)行觸發(fā)器的操作來(lái)替代觸發(fā)的SQL語(yǔ)句的執(zhí)行。對(duì)于每一個(gè)INSERT、UPDATE或DELETE語(yǔ)句只能定義一個(gè)INSTEADOF觸發(fā)器。[INSERT][,][UPDATE][,][DELETE]：激活觸發(fā)器的操作，這里可以選取任意組合，中間用逗號(hào)隔開(kāi)。SQL語(yǔ)句：觸發(fā)器實(shí)現(xiàn)的操作。CREATETRIGGERtriinsertstuON系部AFTERINSERTASPRINT'數(shù)據(jù)插入成功！'INSERTINTO系部VALUES(6,'會(huì)計(jì)系','61232123','潘剛')【例8-32】在“系部”表中，創(chuàng)建插入語(yǔ)觸發(fā)器triinsertstu，一旦數(shù)據(jù)插入成功，打印一個(gè)提示消息“數(shù)據(jù)插入成功”。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】，如圖所示，在表中執(zhí)行插入操作，觸發(fā)器被激活，提示“數(shù)據(jù)插入成功！”。③在“查詢編輯器”輸入“SELECT*FROM系部WHERE系部代碼=6”，單擊工具欄上的【執(zhí)行】，可以查看到剛增加的記錄。CREATETRIGGERtrimajorON專業(yè)FORUPDATEASBEGINIFUPDATE(專業(yè)代碼)BEGINPRINT'專業(yè)代碼是主鍵，不允許更新！'ROLLBACKENDENDUPDATE專業(yè)SET專業(yè)代碼=9WHERE專業(yè)名稱='信息管理技術(shù)'【例8-33】在“專業(yè)”表中，創(chuàng)建一個(gè)觸發(fā)器trimajor，禁止更新專業(yè)代碼字段。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】，如圖所示，在表中執(zhí)行更新專業(yè)代碼字段，觸發(fā)器被激活，提示“專業(yè)代碼是主鍵，不允許更新！”。③在“查詢編輯器”輸入“SELECT*FROM專業(yè)WHERE專業(yè)名稱='信息管理技術(shù)'”，單擊工具欄上的【執(zhí)行】，可以查看專業(yè)代碼還是原來(lái)的4。CREATETRIGGERtricourseON課程INSTEADOFDELETEASBEGINIFEXISTS(SELECT*FROMdeletedWHERE課程性質(zhì)LIKE'%必修%')BEGINPRINT'不能刪除必修課程，包括專業(yè)必修課和公共必修課'ROLLBACKENDENDDELETE課程WHERE課程性質(zhì)='專業(yè)必修課'【例8-34】在課程表中，創(chuàng)建一個(gè)觸發(fā)器tricourse，禁止刪除必修課程。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器創(chuàng)建成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄上的【執(zhí)行】，如圖所示，在表中刪除專業(yè)必修課，觸發(fā)器被激活，提示“不能刪除必修課程，包括專業(yè)必修課和公共必修課！”。DML觸發(fā)器創(chuàng)建完成后，如果需要修改定義，則把CREATE改為ALTER即可。修改DML觸發(fā)器【例8-35】在課程表中，修改觸發(fā)器tricourse，提示用戶正在修改必修課程，并取消操作。①打開(kāi)SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開(kāi)SQL編輯器，編寫(xiě)如下代碼：ALTERTRIGGERtricourseON課程INSTEADOFUPDATEASBEGINIFEXISTS(SELECT*FROMINSERTEDWHERE課程性質(zhì)LIKE'%必修%')BEGINPRINT'您正在修改被保護(hù)的必修課程，請(qǐng)取消您的操作'ROLLBACKENDEND②單擊工具欄上的【執(zhí)行】按鈕，觸發(fā)器修改成功后，再在“查詢編輯器”輸入觸發(fā)器觸發(fā)的操作，單擊工具欄