數(shù)據(jù)庫(kù)原理與應(yīng)用第2版

1、數(shù)據(jù)庫(kù)原理與應(yīng)用（第2版）,高等院校計(jì)算機(jī)教材系列,第12章 存儲(chǔ)過程和觸發(fā)器,12.1 變量及流程控制語(yǔ)句12.2 存儲(chǔ)過程,12.1 變量及流程控制語(yǔ)句,12.1.1 變量1.變量的種類2. 變量的聲明與賦值12.1.2 流程控制語(yǔ)句1. BEGIN … END語(yǔ)句2. IF … ELSE 語(yǔ)句3. WHILE語(yǔ)句,1.變量的種類,變量是被賦予一定的值的語(yǔ)言元素。在T-SQL中，變量分為全局變量和局部變量:全局變量

2、：@@開始的變量局部變量：以@開始的變量。全局變量是由系統(tǒng)提供且預(yù)先聲明的變量，用戶一般只能查看不能修改全局變量的值。局部變量是用戶用以保存特定類型的單個(gè)數(shù)據(jù)值的對(duì)象，它局部于一個(gè)語(yǔ)句批。,2. 變量的聲明,在SQL Server中，局部變量必須先聲明，再使用。聲明變量的語(yǔ)句格式： DECLARE @局部變量名 數(shù)據(jù)類型變量名最多可以包含128個(gè)字符。局部變量的數(shù)據(jù)類型可以是系統(tǒng)數(shù)據(jù)類型，也可以是用戶自己定義的數(shù)據(jù)類

3、型，但不能是text或image類型。使用DECLARE語(yǔ)句聲明一個(gè)局部變量后，變量的值將被初始化為NULL。,2.變量的賦值,變量的賦值語(yǔ)句為：SET @局部變量名 = 值 | 表達(dá)式 SELECT @局部變量名 = 值 | 表達(dá)式SET語(yǔ)句是對(duì)局部變量賦值的首選方法。說明：變量只能出現(xiàn)在使用常數(shù)的位置上。在標(biāo)準(zhǔn)的SQL語(yǔ)句中，變量不能用在表、字段或其他數(shù)據(jù)庫(kù)對(duì)象的名稱的位置上，也不能用在關(guān)鍵字的位置上。,示例,聲明

4、三個(gè)整型變量：@x、@y和@z，并給@x、@y變量分別賦予一個(gè)初值，然后將這兩個(gè)變量的和值賦給@z，并顯示變量@z的結(jié)果。DECLARE @x int, @y int, @z intSET @x = 10SET @y = 20SET @z = @x + @yPrint @z,關(guān)于PRINT語(yǔ)句,作用：將信息顯示在顯示器上。語(yǔ)法格式： PRINT 字符串常量 | @局部變量名 | 字符串表達(dá)式@局部變量名：是

5、任意有效的字符類型的變量，此變量必須是char（或nchar）或varchar（或nvarchar）型的變量。字符串表達(dá)式：返回字符串的表達(dá)式?？砂?lián)的字面值和變量。消息字符串最多可有8000個(gè)字符，超過8000個(gè)字節(jié)的任何字符均被截?cái)唷?12.1.2 流程控制語(yǔ)句,用于控制程序的流程，一般分為三類：順序分支循環(huán)SQL Server 2005也提供對(duì)這三種流程控制的支持。,T-SQL提供的主要流程控制語(yǔ)句,1. BEGIN

6、 … END語(yǔ)句塊,BEGIN 語(yǔ)句1 語(yǔ)句2 …END,BEGIN … END語(yǔ)句塊通常是與流程控制語(yǔ)句IF … ELSE或WHILE一起使用的,2. IF … ELSE 語(yǔ)句,“布爾表達(dá)式”表示一個(gè)測(cè)試條件，取值為True或False如果布爾表達(dá)式中包含SELECT語(yǔ)句，則必須將其用圓括號(hào)擴(kuò)起來。,IF 布爾表達(dá)式 語(yǔ)句塊1 [ ELSE 語(yǔ)句塊2 ],處理過程為： 如果布爾表達(dá)式為True，則執(zhí)行語(yǔ)句塊

7、1； 如果布爾表達(dá)式為False，則執(zhí)行語(yǔ)句塊2，如果有的話。,3. WHILE語(yǔ)句,用于設(shè)置重復(fù)執(zhí)行的一個(gè)語(yǔ)句塊。,WHILE 布爾表達(dá)式 語(yǔ)句塊,當(dāng)布爾表達(dá)式為真時(shí)，重復(fù)執(zhí)行語(yǔ)句塊（稱為循環(huán)體）； 當(dāng)布爾表達(dá)式為假時(shí)退出循環(huán)。,示例,例：計(jì)算1+2+3+…+100的和。DECLARE @i int, @sum intSET @i = 1SET @sum = 0WHILE @i <= 100BEGIN SE

8、T @sum = @sum + @i SET @i = @i + 1ENDPRINT @sum,12.2 存儲(chǔ)過程,12.2.1 存儲(chǔ)過程概念12.2.2 創(chuàng)建和執(zhí)行存儲(chǔ)過程12.2.3 查看和修改存儲(chǔ)過程12.2.4 刪除存儲(chǔ)過程,12.2.1 存儲(chǔ)過程概念,使用 T-SQL語(yǔ)言編寫代碼時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行代碼。在客戶端存儲(chǔ)代碼，然后創(chuàng)建向 SQL Server 發(fā)送SQL命令（或SQL語(yǔ)句）并處理返回結(jié)果的應(yīng)用

9、程序；將發(fā)送的SQL語(yǔ)句存儲(chǔ)在服務(wù)器端的數(shù)據(jù)庫(kù)中，然后由客戶端應(yīng)用程序調(diào)用執(zhí)行這些代碼。這些存儲(chǔ)在服務(wù)器端數(shù)據(jù)庫(kù)中供客戶端調(diào)用執(zhí)行的SQL語(yǔ)句就是存儲(chǔ)過程。,存儲(chǔ)過程功能,接受輸入?yún)?shù)并以輸出參數(shù)的形式將多個(gè)值返回給調(diào)用過程。包含執(zhí)行數(shù)據(jù)庫(kù)操作（包括調(diào)用其它過程）的編程語(yǔ)句。 向調(diào)用過程返回狀態(tài)值，以表明成功或失?。ㄒ约笆≡颍?。,使用存儲(chǔ)過程好處,允許模塊化程序設(shè)計(jì)改善性能減少網(wǎng)絡(luò)流量可作為安全機(jī)制使用,創(chuàng)建存儲(chǔ)過程,C

10、REATE PROC[ EDURE ] 存儲(chǔ)過程名 [ { @參數(shù)名 數(shù)據(jù)類型 } [ = default ] [OUTPUT] ] [ , ... n ] AS SQL語(yǔ)句 [ ... n ]default：表示數(shù)的默認(rèn)值。如果定義了默認(rèn)值，則在執(zhí)行存儲(chǔ)過程時(shí)，可以不必指定該參數(shù)的值。OUTPUT：表明參數(shù)是輸出參數(shù)。使用 OUTPUT 參數(shù)可將信息返回給調(diào)用者。,執(zhí)行存儲(chǔ)過程,[ EXEC [ UTE ]

11、] 存儲(chǔ)過程名 [實(shí)參 [, OUTPUT] [, … n] ],示例1,例1．帶有復(fù)雜 SELECT 語(yǔ)句的存儲(chǔ)過程：查詢計(jì)算機(jī)系學(xué)生的考試情況，列出學(xué)生的姓名、課程名和考試成績(jī)。CREATE PROCEDURE p_grade1AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOI

12、N course c ON c.cno = sc.cno WHERE Sdept = '計(jì)算機(jī)系',示例2,例2．帶有輸入?yún)?shù)的存儲(chǔ)過程：查詢某個(gè)指定系學(xué)生的考試情況，列出學(xué)生的姓名、所在系、課程名和考試成績(jī)。CREATE PROCEDURE p_grade2 @dept char(20) AS SELECT Sname, Sdept, Cname, Grade F

13、ROM Student s INNER JOIN sc ON s.sno = sc.sno JOIN course c ON c.cno = sc.cno WHERE Sdept = @dept,執(zhí)行例2存儲(chǔ)過程,執(zhí)行例2定義的存儲(chǔ)過程，查詢信息系學(xué)生的修課情況：EXEC p_grade2 '信息系',示例3,例3．含多個(gè)輸入?yún)?shù)并有默認(rèn)值的存儲(chǔ)過程：查詢某個(gè)學(xué)生某門課程的考試成績(jī)

14、，若沒有指定課程，則默認(rèn)課程為“數(shù)據(jù)庫(kù)基礎(chǔ)”CREATE PROCEDURE p_grade3 @student_name char(10), @course_name char(20) = '數(shù)據(jù)庫(kù)基礎(chǔ)'AS SELECT Sname, Cname, Grade FROM Student s INNER JOIN sc ON s.sno = sc.sno INNER JOIN

15、 course c ON c.cno = sc.cno WHERE sname = @student_name AND cname = @course_name,參數(shù)的傳遞方式,按參數(shù)位置傳遞值EXEC p_grade3 '劉晨', 'VB'按參數(shù)名傳遞值EXEC p_grade3 @student_name = '劉晨',@course_name =

16、 'VB',對(duì)默認(rèn)值的調(diào)用,如果在定義存儲(chǔ)過程時(shí)為參數(shù)指定了默認(rèn)值，則在執(zhí)行存儲(chǔ)過程時(shí)可以不為有默認(rèn)值的參數(shù)提供值。例如：EXEC p_grade3 '吳賓'相當(dāng)于執(zhí)行：EXEC p_grade3 '吳賓','數(shù)據(jù)庫(kù)基礎(chǔ)',示例4,例4．含多個(gè)輸入?yún)?shù)并均指定默認(rèn)值的存儲(chǔ)過程。查詢指定系、指定性別的學(xué)生中年齡大于等于指定年齡的學(xué)生詳細(xì)信息。系的默認(rèn)值為“計(jì)算機(jī)系”

17、，性別默認(rèn)值為“男”，年齡默認(rèn)值為20。CREATE PROCEDURE P_Student @dept char(20) = '計(jì)算機(jī)系', @sex char(2) = '男', @age int = 20AS SELECT * FROM Student WHERE Sdept = @dept AND Ssex = @sex AND Sage >=

18、@age,,執(zhí)行1：不提供任何參數(shù)值。EXEC P_Student執(zhí)行2：提供全部參數(shù)值。 EXEC P_Student '信息系', '女', 19執(zhí)行3：只為第二個(gè)參數(shù)提供值。 EXEC P_Student @sex = '女'執(zhí)行4：只為第一個(gè)和第三個(gè)參數(shù)提供值。 EXEC P_Student @sex = '女', @age=19,示例5,例5

19、．含輸出參數(shù)的存儲(chǔ)過程。計(jì)算兩個(gè)數(shù)的乘積，將計(jì)算結(jié)果用輸出參數(shù)返回給調(diào)用者。CREATE PROCEDURE p_Sum@var1 int, @var2 int, @var3 int outputAs Set @var3 = @var1 * @var2 執(zhí)行此存儲(chǔ)過程的示例：Declare @res intEXECUTE p_Sum 5,7,@res outputPrint @res,示例6,例6．含輸入?yún)?shù)和

20、一個(gè)輸出參數(shù)的存儲(chǔ)過程。統(tǒng)計(jì)指定課程（課程名）的平均成績(jī)，并將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROC p_AvgGrade @cn char(20), @avg_grade int outputAS SELECT @avg_grade = AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = @cn,執(zhí)行例6存儲(chǔ)過程示例,

21、查詢VB課程的考試平均成績(jī)：DECLARE @Avg_Grade intEXEC p_AvgGrade 'VB', @Avg_Grade outputPRINT @Avg_Grade,示例7,例7．含輸入?yún)?shù)和多個(gè)輸出參數(shù)的存儲(chǔ)過程。統(tǒng)計(jì)指定課程的平均成績(jī)和選課人數(shù)，將統(tǒng)計(jì)的結(jié)果用輸出參數(shù)返回。CREATE PROC p_AvgCount @cn char(20), @avg_grade int out

22、put, @total int outputAS SELECT @avg_grade = AVG(Grade), @total = COUNT(*) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = @cn,執(zhí)行例7存儲(chǔ)過程示例,查詢VB課程的考試平均成績(jī)和選課人數(shù)：DECLARE @avg int, @count intEXEC p_AvgCount

23、'VB', @avg output, @count outputSELECT @avg AS 平均成績(jī), @count AS 選課人數(shù),示例8,例8．將指定課程的學(xué)分增加2分。 CREATE PROC p_UpdateCredit1 @cno char(6) AS UPDATE Course SET Credit = Credit + 2 WHERE Cno

24、= @cno,示例9,例9．將指定課程的學(xué)分改為指定值，要求指定值必須在1~10之間，否則不予修改。CREATE PROC p_UpdateCredit2 @cno char(6),@credit intAS IF @credit BETWEEN 1 AND 20 UPDATE Course SET Credit = @credit WHERE Cno = @cno,示例10,例10．刪除指定學(xué)生（學(xué)號(hào)

25、）的成績(jī)不及格的修課記錄。CREATE PROC p_DeleteSC @sno char(7)AS DELETE FROM SC WHERE Sno = @sno AND Grade < 60,12.2.3 查看和修改存儲(chǔ)過程,1．查看已定義的存儲(chǔ)過程展開要查看存儲(chǔ)過程的數(shù)據(jù)庫(kù)，然后順序展開“可編程性”?“存儲(chǔ)過程”。在某個(gè)存儲(chǔ)過程上右擊鼠標(biāo)，在彈出的菜單中選擇“修改”命令，可以查看定義該存儲(chǔ)過程的代碼。

26、,2．修改存儲(chǔ)過程,修改存儲(chǔ)過程的語(yǔ)句為：ALTER PROC [ EDURE ] 存儲(chǔ)過程名 [ { @參數(shù)名 數(shù)據(jù)類型 } [ = default ] [OUTPUT] ] [ , ... n ] AS SQL語(yǔ)句 [ ... n ],示例,例11．修改p_grade2存儲(chǔ)過程，使其能查詢指定系中考試成績(jī)大于等于80分的學(xué)生姓名、所在系、課程名和考試成績(jī)。ALTER PROCEDURE p_grad

27、e2 @dept char(20)AS SELECT Sname, Sdept, Cname, Grade FROM Student s INNER JOIN SC ON s.Sno = SC.Sno INNER JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = @dept AND Grade >= 80,12.2.4 刪除存儲(chǔ)過程,用圖形化方

28、法刪除：在要?jiǎng)h除的存儲(chǔ)過程上右擊鼠標(biāo)，在彈出菜單中選擇“刪除”命令;用T-SQL語(yǔ)句刪除:DROP { PROC | PROCEDURE } { procedure } [ ,...n ],示例,例12．刪除p_grade2存儲(chǔ)過程。DROP PROC p_grade2例13．同時(shí)刪除p_Student和p_Sum存儲(chǔ)過程。DROP PROC p_Student, p_Sum,12.3 觸發(fā)器,12.3.1 創(chuàng)建

29、觸發(fā)器12.3.2 后觸發(fā)型觸發(fā)器12.3.3 前觸發(fā)型觸發(fā)器12.3.4 查看和更改觸發(fā)器12.3.5 刪除觸發(fā)器,觸發(fā)器概述,觸發(fā)器是一種特殊的存儲(chǔ)過程，當(dāng)用戶對(duì)表中的數(shù)據(jù)進(jìn)行UPDATE、INSERT或DELETE操作時(shí)自動(dòng)觸發(fā)執(zhí)行。通常用于保證業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性，使用戶可以用編程的方法來實(shí)現(xiàn)復(fù)雜的處理邏輯和商業(yè)規(guī)則，增強(qiáng)了數(shù)據(jù)完整性約束的功能。,12.3.1創(chuàng)建觸發(fā)器,CREATE TRIGGER 觸發(fā)器名稱O

30、N {表名 | 視圖名}{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ DELETE ] [ , ] [UPDATE ] }AS SQL 語(yǔ)句,注意,在一個(gè)表上可以建立多個(gè)觸發(fā)器，對(duì)于AFTER型的觸發(fā)器，可以在同一種操作上建立多個(gè)觸發(fā)器；對(duì)于INSTEAD OF型的觸發(fā)器，在同一種操作上只能建立一個(gè)觸發(fā)器。不允許在觸發(fā)器中創(chuàng)建和更改數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)對(duì)象的語(yǔ)句

31、、以及所有的DROP語(yǔ)句。,兩個(gè)臨時(shí)表,INSERTED和DELETED表：結(jié)構(gòu)同建立觸發(fā)器的表，而且只能用在觸發(fā)器代碼中。INSERTED表保存了INSERT操作中新插入的數(shù)據(jù)和UPDATE操作中更新后的數(shù)據(jù)；DELETED保存了DELETE操作刪除的數(shù)據(jù)和UPDATE操作中更新前的數(shù)據(jù)。,12.3.2 后觸發(fā)型觸發(fā)器,使用FOR或AFTER選項(xiàng)定義的觸發(fā)器；只在引發(fā)觸發(fā)器執(zhí)行的語(yǔ)句中指定的操作都已成功執(zhí)行，并且所有的約束檢查也

32、成功完成后，才執(zhí)行觸發(fā)器。,示例,例1．限制職工的基本工資必須在相應(yīng)工作的最低工資和最高工資之間。CREATE Trigger tri_BasePay ON 職工表 AFTER INSERT, UPDATEAS IF EXISTS( SELECT * FROM 職工表 a JOIN 工作表 b ON a.工作號(hào)= b.工作號(hào) WHERE 基本工資 NOT BETWEEN 最低工資 AND 最高工資

33、) ROLLBACK -- 撤消已執(zhí)行的操作,說明,觸發(fā)器與引發(fā)觸發(fā)器執(zhí)行的操作共同構(gòu)成了一個(gè)事務(wù)。 事務(wù)的開始是引發(fā)觸發(fā)器執(zhí)行的操作，事務(wù)的結(jié)束是觸發(fā)器的結(jié)束。,示例,例2．限制職工表中“浮動(dòng)工資”列的取值必須在“基本工資”的0～30％范圍內(nèi)。CREATE Trigger tri_FloatPay ON 職工表 AFTER INSERT, UPDATEAS IF EXISTS( SELECT *

34、FROM INSERTED WHERE 浮動(dòng)工資 NOT BETWEEN 0 AND 基本工資 * 0.3) ROLLBACK,示例,例3．限制不能將不及格學(xué)生的成績(jī)改為及格，如果違反約束，給出提示信息：“不能將不及格成績(jī)改為及格”。CREATE Trigger tri_Grade ON SC AFTER UPDATEAS IF EXISTS( SELECT * FRO

35、M INSERTED a JOIN DELETED b ON a.Sno=b.Sno AND a.Cno=b.Cno WHERE b.Grade=60) BEGIN ROLLBACK PRINT '不能將不及格成績(jī)改為及格' END,12.3.3 前觸發(fā)型觸發(fā)器,使用INSTEAD OF選項(xiàng)定義的觸發(fā)器。在這種模式的觸發(fā)器中，指定執(zhí)行觸發(fā)器而不是執(zhí)行引發(fā)觸發(fā)器執(zhí)行的SQ

36、L語(yǔ)句，從而替代引發(fā)語(yǔ)句的操作。,示例,例4. 用前觸發(fā)器實(shí)現(xiàn)例1限制職工基本工資必須在相應(yīng)工作的最低工資和最高工資之間。CREATE Trigger tri_Salary ON 職工表 INSTEAD OF INSERTAS IF NOT EXISTS( SELECT * FROM 職工表 a JOIN 工作表 b ON a.工作號(hào) = b.工作號(hào) WHERE 基本工資 NOT BETWEE

37、N 最低工資 AND 最高工資) INSERT INTO 職工表 SELECT * FROM INSERTED,示例,例5．限制不能將“浮動(dòng)工資”列的值改為超過該職工基本工資的30％，如果違反約束給出提示信息：“浮動(dòng)工資不能高于基本工資的30%”。CREATE Trigger tri_FloatPay ON 職工表INSTEAD OF UPDATEAS IF NOT EXISTS(SELECT * FROM INSE

38、RTED WHERE 浮動(dòng)工資 > 基本工資 * 0.3) UPDATE 職工表 SET 浮動(dòng)工資 = ( SELECT 浮動(dòng)工資 FROM INSERTED i WHERE 職工表.職工號(hào) = i.職工號(hào)) ELSE PRINT '浮動(dòng)工資不能高于基本工資的30%',12.3.4 查看和更改觸發(fā)器,1.查看觸發(fā)器在對(duì)象資源

39、管理器中，通過展開某個(gè)表下的“觸發(fā)器”節(jié)點(diǎn)，可以看到定義在該表上的全部觸發(fā)器。,2.修改觸發(fā)器,在某觸發(fā)器上右擊鼠標(biāo)，在彈出的菜單中選擇“修改”命令，可打開定義觸發(fā)器的代碼?？蓪?duì)此代碼直接修改；或使用ALTER TRIGGER語(yǔ)句修改。,12.3.5 刪除觸發(fā)器,使用SSMS圖形化刪除：在要?jiǎng)h除的觸發(fā)器上右擊鼠標(biāo)，然后在彈出的菜單中選擇“刪除”命令。用DROP TRIGGER語(yǔ)句刪除，語(yǔ)法格式為： DROP TRIGGER 觸