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

FROM學生

WHERE班級編號IN(SELECT班級代碼

FROM班級

WHERE專業代碼IN(SELECT專業代碼

FROM專業

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

變量與常量IF…ELSE語句

CASE語句WHILE語句RETURN語句函數標量函數表值函數8.1.4知識總結常量表示一個特定數據值的符號，在程序運行過程中始終保持不變，常量的格式取決于它所表示的值的數據類型，如字符常量必須用單引號括起來，由字母、數字及其它特殊字符組成；二進制常量由0、1構成；十進制整型常量不帶小數點；日期常量也要用括號括起來等。在程序運行過程中，值可以改變的量稱為變量，按照變量的有效作用范圍，可以分為局部變量和全局變量。局部變量的作用范圍僅限于程序內部，局部變量名必須使用@符號開始。全局變量的作用范圍不僅僅局限于某一程序，任何程序均可以隨時訪問，全局變量經常存儲一些SQLServer的配置設定和統計數據，不能由用戶的程序定義，全局變量以符號@@開始。

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

語句塊1ELSE

語句塊2參數說明如下：條件表達式：關系運算符和邏輯運算符組成的表達式，其值決定分支的執行路線。語句塊1：條件表達式成立時，執行的語句塊，如果語句塊的語句多于一條，語句塊前用BEGIN，語句塊后用END。語句塊2：條件表達式不成立時，執行的語句塊。ELSE語句塊2：可選項，最簡單的IF語句沒有ELSE選項。IF…ELSE語句

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

循環體參數說明如下：循環條件：關系運算符和邏輯運算符組成的表達式，其值循環體是否執行。循環體：循環條件為真重復執行的有效的SQL語句。如果循環體的語句多于一條，則循環體要用BEGIN…END括起來。在循環體內可以使用BREAK語句無條件終止循環體的執行；也可以使用CONTINUE語句提前結束本次循環，直接進入下一次循環條件的判斷。WHILE語句SELECT教師編號,教師姓名,CASE職稱

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

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

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

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

③在“查詢編輯器”上輸入語句“SELECTdbo.funscorenew(1,1)”，單擊工具欄上的【執行】按鈕，得出學號為1的學生選修的課程編號為1的成績。說明：函數的參數先寫參數名，再寫參數的數據類型；函數的運行在SELECT語句中，要指出函數的實際參數值，實際參數值要和形式參數一一對應。每次運行函數，輸入的實際參數不同，得出的形式參數也不同。

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

WHERE班級編號=@classid)END【例8-12】創建一個函數名為funclass，輸入班級編號，統計班級的學生人數。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，展開“對象資源管理器”窗口的“學生管理”數據庫下的“可編程性”文件夾，雙擊“函數”，展開“標量值函數”，可以看到創建的函數前面加了所有者dbo，運行結果如圖所示。

③在“查詢編輯器”上輸入語句“SELECTdbo.funclass(3)”，單擊工具欄上的【執行】按鈕，得出班級編號為3的學生人數。如果函數返回值是表（TABLE），則函數為表值函數，表值函數返回的結果是表，因此，表值函數的調用要放在SELECT語句的FROM子句調用，使用CREATEFUNCTION創建內嵌表值函數的語法格式如下：CREATEFUNCTION函數名（形式參數列表）RETURNSTABLEASRETURN(SELECT語句)參數說明如下：TABLE：指定函數返回值的類型為表。SELECT語句：單條select查詢語句，查詢語句的結果做為函數返回的表。表值函數CREATEFUNCTIONfunteacher(@deptnamevarchar(50))RETURNStableASRETURN(SELECT教師編號,教師姓名,性別,職稱,學歷,學位,專業

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

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

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

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

JOIN課程ASkcONkc.課程編號=xk.課程編號

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

FROM選課WHERE學號=5AND課程編號=1)END【例8-15】修改已經存在的函數為funscore，更改為查詢學號為5，選修的課程編號為1的成績。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。③在“查詢編輯器”上輸入語句“SELECTdbo.funscore()”，單擊工具欄上的【執行】按鈕，得出固定的學號為5的學生選修的課程編號為1的成績。說明：在修改函數的時候，可以修改函數的定義，但是如果更改函數返回值的類型，就會產生錯誤，即如果把函數返回值的類型decimal（5,2）更改為TABLE類型，則會產生錯誤。可以使用DROPFUNCTION命令刪除自定義函數，語法格式如下：DROPFUNCTION函數名刪除用戶自定義函數DROPFUNCTIONfunstuscore【例8-16】刪除函數funscore。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。

③在“查詢編輯器”上輸入語句“SELECTdbo.funscore()”，單擊工具欄上的【執行】按鈕，服務器會報錯。在銷售數據庫中，創建一個函數funproduction，根據輸入的顧客編號，查詢顧客購買的商品編號，商品名稱，數量，價格，總價，商品類別。1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數據庫”文件夾下的數據庫“銷售”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.1.5應用實踐CREATEFUNCTIONfunproduction(@customeridvarchar(50))RETURNSTABLEASRETURN(SELECTsp.商品ID,名稱,類別名稱,數量,價格,總價

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

8.2.1情景描述8.2.2問題分析8.2.3解決方案8.2.4知識總結8.2.5應用實踐任務8.2創建存儲過程根據學生信息管理系統的需求，數據庫的開發人員需要創建一個存儲過程，根據輸入的職稱，統計這類職稱的教師人數，同時返回教師的編號、姓名、性別、職稱、學歷、系部名稱的具體信息。8.2.1情景描述為了解決上述問題，需要完成以下任務：1．寫出統計指定職稱的查詢語句；2．寫出查詢指定職稱的教師信息的語句；3．寫出創建存儲過程的語句；4．調用存儲過程以驗證結果。8.2.2問題分析

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

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

數據類型”，參數之間用逗號分隔，參數可以指定默認值，格式為“參數名

數據類型=默認值”，如果是輸出參數，格式為“參數名數據類型OUTPUT”。WITHENCRYPTION：用于加密存儲過程定義語句的文本。SQL語句：合法的SQL語句，用于定義存儲過程執行的操作。如果存儲過程的定義只有一條SQL語句，那么BEGIN…END可以省略。存儲過程創建成功后，使用EXECUTE命令執行存儲過程，EXECUTE可以省略為EXEC。CREATEPROCEDUREprocscoreASBEGINSELECT成績

FROM選課WHERE學號=6AND課程編號=2END【例8-17】創建存儲過程procscore，查詢學號為6、選修的課程編號為2的成績。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。③在“查詢編輯器”上輸入語句“EXECprocscore”，單擊工具欄上的【執行】按鈕，查詢固定的學號為6的學生選修的課程編號為2的成績。說明：存儲過程的定義只有一條SQL語句，此例中BEGIN…END可以省略。EXEC是關鍵字EXECUTE的簡寫。執行存儲過程的時候，如果一次只執行一條語句，那么EXEC也可以省略。

CREATEPROCprocscorenew@stuidint,@courseidintASSELECT成績

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

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

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

WHERE系部名稱=@deptname【例8-20】創建一個存儲過程名為procteacher，輸入系部名稱，查詢指定系部的教師編號、教師姓名、性別、職稱、學歷、學位、專業。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。③在“查詢編輯器”上輸入語句“procteacher'計算機系'”，單擊工具欄上的【執行】按鈕，顯示計算機系的老師信息。

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

WHERE課程編號=@courseid

SELECT學生.學號,姓名,性別,身份證號FROM學生JOIN選課ON學生.學號=選課.學號WHERE成績<@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】創建一個存儲過程名為procscorecourse，輸入課程編號，查詢指定課程編號的課程的最高成績，最低成績，平均成績，并查詢成績低于指定課程平均分的學生的學號，姓名，性別，身份證號。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。③在“查詢編輯器”上輸入以下語句之后，單擊工具欄上的【執行】按鈕，在結果欄的“消息”選項卡內返回查詢結果影響的行數，及最高成績，最低成績，平均成績。在結果選項卡中返回的小于平均成績的學生的信息。當存儲過程的定義需要修改的時候，使用ALTERPROCEDURE命令，修改存儲過程的語法與創建存儲過程的語法一樣，只需要將CREATE換成ALTER即可。修改存儲過程ALTERPROCEDUREprocscoreASBEGINSELECT成績

FROM選課WHERE學號=5AND課程編號=1END【例8-23】修改已經存在的存儲過程名為procscore，更改為查詢學號為5，選修的課程編號為1的成績。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕。③在“查詢編輯器”上輸入語句“procscore”，單擊工具欄上的【執行】按鈕，查詢固定的學號為5的學生選修的課程編號為1的成績。可以使用DROPPROCEDURE命令刪除自定義存儲過程，語法格式為：刪除用戶自定義存儲過程DROPPROCEDURE存儲過程名DROPPROCEDUREprocscore【例8-24】刪除存儲過程procscore。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕。③在“查詢編輯器”上輸入語句“procscore”，單擊工具欄上的【執行】按鈕，則會提示找不到存儲過程procscore。可以使用系統存儲過程sp_help查看存儲過程的基本信息；用sp_helptext存儲過程的定義信息，用法和查看視圖信息相同。查看存儲過程信息sp_helpprocscorecourse【例8-25】用sp_help查看存儲過程procscorecourse的基本信息。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。sp_helptextprocscorecourse【例8-26】用sp_helptext查看存儲過程procscorecourse的定義文本。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。sp_helptextprocteacher【例8-27】用sp_helptext查看加密存儲過程procteacher的定義文本。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。可以使用sp_rename來對存儲過程進行改名，語法格式如下：重命名存儲過程sp_rename存儲過程原名,存儲過程新名稱參數說明如下：存儲過程原名：指定數據庫中存在的存儲過程的名稱。存儲過程新名稱：指定存儲過程更改名稱后的名稱。sp_renameprocteacher,procteachernew【例8-28】用sp_rename更改存儲過程procteacher為procteachernew。①打開SQLServerManagementStudio，在工具欄上單擊“新建查詢”按鈕，打開SQL編輯器，編寫如下代碼：②單擊工具欄上的【執行】按鈕，運行結果如圖所示。在銷售數據庫中，創建一個存儲過程，根據輸入的供應商名稱，統計從該供應商進貨的商品數量，并查詢從該供應商進貨的商品的編號，名稱，價格，保質期。1．打開SQLServerManagementStudio，單擊“對象資源管理器”中的“數據庫”文件夾下的數據庫“銷售”；2．單擊工具欄上的“新建查詢”命令，打開“查詢編輯器”；3．在“查詢編輯器”上輸入以下代碼：8.2.5應用實踐CREATEPROCprocproduct@supplynamevarchar(50),@cintOUTPUTASBEGINSELECT@c=COUNT(*)FROM進貨

WHERE供應商ID=(SELECT供應商IDFROM供應商

WHERE名稱=@supplyname)SELECT商品.商品ID,商品.名稱,商品.價格,商品.保質期

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