数据库原理与应用（SQL Server）笔记 第九章 存储过程和触发器
目錄
- 前言
- 一、存儲過程
- （一）存儲過程的定義
- （二）存儲過程的特點
- （三）存儲過程的分類
- 1、用戶存儲過程
- 2、系統(tǒng)存儲過程
- 3、擴(kuò)展存儲過程
- （四）存儲過程的創(chuàng)建
- （五）存儲過程的執(zhí)行
- （六）帶參數(shù)的存儲過程的使用
- 1、帶默認(rèn)參數(shù)的存儲過程
- 2、帶輸入?yún)?shù)的存儲過程
- 3、帶輸出參數(shù)的存儲過程
- 4、帶返回值的存儲過程
- （七）存儲過程的修改
- （八）存儲過程的刪除
- 二、觸發(fā)器
- （一）觸發(fā)器的定義
- （二）觸發(fā)器的特點和分類
- 1、觸發(fā)器的特點
- 2、觸發(fā)器的分類
- （三）觸發(fā)器中使用的特殊臨時表
- 1、INSERTED臨時表
- 2、DELETED臨時表
- （四）DML觸發(fā)器
- 1、AFTER觸發(fā)器
- 2、INSTEAD OF觸發(fā)器
- （五）DDL觸發(fā)器
- （六）創(chuàng)建觸發(fā)器
- （七）使用觸發(fā)器
- 1、使用AFTER觸發(fā)器
- 2、使用INSTEAD OF觸發(fā)器
- （八）修改觸發(fā)器
- （九）刪除觸發(fā)器
- （十）觸發(fā)器的啟用和禁用
- 結(jié)語
前言
本章內(nèi)容將介紹數(shù)據(jù)庫中的存儲過程和觸發(fā)器，并將通過定義以及使用和驗證其功能。
注：以下皆通過T-SQL語句進(jìn)行操作，另一種圖形界面自行翻閱書籍或網(wǎng)上搜索。
一、存儲過程
（一）存儲過程的定義
簡單的來說，存儲過程就是一串T-SQL語句集合，通過定義后，可以使用該存儲過程。
（二）存儲過程的特點
它有以下特點：
1、可以快速執(zhí)行。相較于批處理語句，存儲過程可以更快的執(zhí)行，省去了重新分析、重新編譯、重新優(yōu)化等步驟。
2、具有安全特性。
3、可以減少網(wǎng)絡(luò)通信流量。
4、可-被重復(fù)調(diào)用任意次，且可以保證功能的一致性。
（三）存儲過程的分類
可以分為三類，即用戶存儲過程、系統(tǒng)存儲過程、擴(kuò)展存儲過程，這里簡單介紹這三種類型：
1、用戶存儲過程
用戶存儲過程即用戶自己創(chuàng)建的存儲過程，其中包括T-SQL存儲過程和CLR存儲過程（CLR存儲過程這里不重點講述），T-SQL存儲過程即使用T-SQL語句創(chuàng)建的語句集合，通過接收和返回用戶輸入的參數(shù)執(zhí)行該存儲過程。
2、系統(tǒng)存儲過程
系統(tǒng)存儲過程是由系統(tǒng)本身所定義的存儲過程，可以作為命令來執(zhí)行，系統(tǒng)存儲過程一般定義在系統(tǒng)數(shù)據(jù)庫master中，都是以前綴sp_開頭的，如下圖。
3、擴(kuò)展存儲過程
擴(kuò)展存儲過程是指允許使用編程語言來創(chuàng)建自己的外部例程，添加至系統(tǒng)后也是按照存儲過程的方法執(zhí)行。
（四）存儲過程的創(chuàng)建
語句格式如下：
CREATE PROC/PROCEDURE <存儲過程的名稱> （@參數(shù)的名稱 類型...） /*定義參數(shù)的類型*/ AS SQL語句...上面的定義參數(shù)，會在后面的帶參數(shù)的存儲過程的使用中詳細(xì)講解。
例1、根據(jù)以下所給信息，在數(shù)據(jù)庫Sales中，創(chuàng)建一個存儲過程proc_1：其功能是顯示商品信息表中出庫存量最少的5條商品信息。
sql語句：
USE Sales GO CREATE PROC proc_1 AS SELECT TOP 5* FROM Product ORDER BY stocks ASC
可以發(fā)現(xiàn)命令完成后，在數(shù)據(jù)庫Sales的可編編程性中的存儲過程中找到所創(chuàng)建的存儲過程dbo.proc_1。
注：當(dāng)我們創(chuàng)建一個存儲過程執(zhí)行后，再點擊執(zhí)行會無法執(zhí)行報錯“已存在名為…的對象”，若我們不想再通過代碼來修改存儲過程的內(nèi)容，可以在左側(cè)對象資源管理器中找到相應(yīng)的存儲過程名右鍵刪除，此時再點執(zhí)行，執(zhí)行已修改的存儲過程。
（五）存儲過程的執(zhí)行
通過使用EXEC或EXECUTE命令來執(zhí)行所創(chuàng)建的存儲過程。
語句格式如下：
例2、執(zhí)行例1中創(chuàng)建的存儲過程proc_1。
sql語句：USE Sales GO EXEC proc_1
（六）帶參數(shù)的存儲過程的使用
存儲過程帶參數(shù)可以在存儲過程和用戶之間交換數(shù)據(jù)，有兩種傳遞參數(shù)的方式，一是按位置傳遞參數(shù)，即采用實參列表方式，使傳遞參數(shù)和定義時的參數(shù)順序一致；二是通過參數(shù)名稱來傳遞參數(shù)，即參數(shù)=值的方式來傳遞，這鐘方式的每個參數(shù)的順序可以任意排列。
1、帶默認(rèn)參數(shù)的存儲過程
在創(chuàng)建存儲過程時，可以為帶參數(shù)的存儲過程設(shè)置為默認(rèn)值，默認(rèn)值必須為常量或NULL。當(dāng)用戶調(diào)用存儲過程時，如果未指定對應(yīng)的實參值，則自動使用對應(yīng)的默認(rèn)值進(jìn)行替代。
2、帶輸入?yún)?shù)的存儲過程
要定義帶輸入?yún)?shù)的存儲過程，就必須要在創(chuàng)建語句中聲明一個或多個變量及其類型。
語句格式如下：
例3、根據(jù)以下所給信息，在數(shù)據(jù)庫Sales中，創(chuàng)建一個帶默認(rèn)參數(shù)的存儲過程proc_2，其功能是：輸入銷售員的工號，輸出指定銷售員工號的銷售員信息，指定默認(rèn)工號為“S01”。
這里把創(chuàng)建存儲過程和執(zhí)行存儲過程放在一起，為了方便執(zhí)行，同時在執(zhí)行存儲過程中輸入的參數(shù)采用的是按位置傳遞參數(shù)方式。
首先我們查詢工號為“S02”的員工信息，，結(jié)果如下：
sql語句：
USE Sales GO CREATE PROC proc_2 @SaleID char(3)='S01' AS SELECT * FROM Seller WHERE SaleID=@SaleID GO EXEC proc_2 S02
因為事先已指定默認(rèn)參數(shù)為“S01”，若我們不指定輸入值，輸出結(jié)果則會為工號為“S01”的員工，這里也是在執(zhí)行存儲過程中輸入的參數(shù)采用的是按位置傳遞參數(shù)方式，結(jié)果如下：
sql語句：
USE Sales GO CREATE PROC proc_2 @SaleID char(3)='S01' AS SELECT * FROM Seller WHERE SaleID=@SaleID GO EXEC proc_2
例4、根據(jù)以下所給信息，在數(shù)據(jù)庫Sales中，創(chuàng)建一個帶默認(rèn)參數(shù)的存儲過程proc_3，其功能是：輸入銷售員的工號和性別，輸出指定銷售員工號的信息，指定默認(rèn)工號為“S01”、默認(rèn)性別為“男”。
> 這里我們定義了兩個參數(shù)，分別是@SaleID和@Sex，在執(zhí)行存儲過程中輸入的參數(shù)采用的是通過參數(shù)名傳遞參數(shù)方式，指定工號未“S08”，而未指定性別，所以采用的是默認(rèn)性別。
sql語句：
USE Sales GO CREATE PROC proc_3 @SaleID char(3)='S01', @Sex char(2)='男' AS SELECT * FROM Seller WHERE SaleID=@SaleID AND Sex=@Sex GO EXEC proc_3 @SaleID='S08'
而當(dāng)我們要查詢“工號為“S03”的女銷售員李芳時，由于默認(rèn)指定性別為男，所以沒有結(jié)果。
3、帶輸出參數(shù)的存儲過程
定義輸出參數(shù)可以從存儲過程中返回一個或多個值到用戶，這里要注意，在定義帶輸出參數(shù)的存儲過程的時，在創(chuàng)建語句和執(zhí)行語句中必須要使用OUTPUT關(guān)鍵字。
語句格式如下：
例5、根據(jù)以下所給信息，在數(shù)據(jù)庫Sales中，創(chuàng)建一個帶有輸入?yún)?shù)和輸出參數(shù)的存儲過程proc_4：其功能是通過一個給定的銷售員工號，查詢出該銷售員銷售的商品總量及銷售總金額，并通過輸出參數(shù)進(jìn)行返回。
首先為了得到查詢出該銷售員銷售的商品總量及總金額，要定義了三個參數(shù)，分別是一個輸入?yún)?shù)：代表銷售員的工號、兩個輸出參數(shù)：分別代表銷售商品總量及銷售總金額。同時在SELECT語句中使用SUM()函數(shù)對多個銷售商品數(shù)量和銷售金額進(jìn)行合計。然后在執(zhí)行語句中通過DECLARE語句定義輸出參數(shù)，并輸出。
sql語句：
USE Sales GO CREATE PROC proc_4 @saleID char(3), @quantity int OUTPUT, @total money OUTPUT AS SELECT @quantity=SUM(quantity),@total=SUM(total) FROM orders,OrderDetail WHERE Orders.OrderID=OrderDetail.OrderID AND Orders.SaleID=@saleID GO DECLARE @quantity int DECLARE @total money EXEC proc_4 S03,@quantity output,@total output PRINT '該銷售員銷售總量為：'+ str(@quantity)+ '，總金額為：'+str(@total)
4、帶返回值的存儲過程
存儲過程執(zhí)行后會返回整型狀態(tài)，若返回代碼為0，表示成功執(zhí)行；若返回代碼為-1～-99之間的整數(shù)，表示沒有成功執(zhí)行，可使用RETURN關(guān)鍵字進(jìn)行返回。
例6、根據(jù)以下所給信息，在數(shù)據(jù)庫Sales中，創(chuàng)建一個帶有返回值的存儲過程proc_5：其功能是通過一個給定的商品編號，查詢該商品的庫存情況，庫存量大于等于500返回1，小于500返回0。
sql語句：
USE Sales GO CREATE PROC proc_5 @ProductID char(6) AS declare @x int select @x=stocks FROM Product WHERE ProductID=@ProductID IF @x>=500 return 1 ELSE return 0 GO DECLARE @s int EXEC @s = proc_5 'P01001' PRINT str(@s)
（七）存儲過程的修改
使用ALTER PROC語句修改已存在的存儲過程，其與創(chuàng)建存儲過程相同，這里不再累述。
（八）存儲過程的刪除
使用DROP PROC語句可以刪除創(chuàng)建的存儲過程。
語句格式如下：
二、觸發(fā)器
（一）觸發(fā)器的定義
簡單的來說，觸發(fā)器就是一個特殊類型的存儲過程，其特殊性就在于其在修改表數(shù)據(jù)時自動觸發(fā)執(zhí)行所定義的語句。它和存儲過程的區(qū)別是：存儲過程是通過其名稱直接調(diào)用，而觸發(fā)器是通過事件進(jìn)行觸發(fā)從而自動執(zhí)行。
（二）觸發(fā)器的特點和分類
1、觸發(fā)器的特點
觸發(fā)器較于存儲過程首先是其自動執(zhí)行，另外它是建立在表或視圖上的，而存儲過程是建立在數(shù)據(jù)庫上的。此外觸發(fā)器可以實現(xiàn)比約束還更嚴(yán)謹(jǐn)?shù)南拗?#xff0c;從而防止被修改。
觸發(fā)器：
存儲過程：
2、觸發(fā)器的分類
觸發(fā)器分為DML觸發(fā)器和DDL觸發(fā)器，這里的DML和DDL的意義分別是數(shù)據(jù)操作語言和數(shù)據(jù)定義語言。
（三）觸發(fā)器中使用的特殊臨時表
這里我們要知道在執(zhí)行觸發(fā)器時，系統(tǒng)創(chuàng)建了兩個特殊的臨時表，分別是INSERTED表和DELETED表，它們在觸發(fā)器執(zhí)行時被創(chuàng)建，執(zhí)行完后消失。
1、INSERTED臨時表
當(dāng)向數(shù)據(jù)表或視圖中插入數(shù)據(jù)時，INSERT觸發(fā)器執(zhí)行，將新元組插入INSERTED臨時表及基本數(shù)據(jù)表中，也就是說INSERTED表是包含了已插入元組的一個副本。
2、DELETED臨時表
（1）當(dāng)向數(shù)據(jù)表或視圖中執(zhí)行刪除操作時，DELETE觸發(fā)器執(zhí)行， 將刪除的元組存放到DELETED表中。
（2）而當(dāng)向數(shù)據(jù)表或視圖中執(zhí)行更新操作時，UPDATE觸發(fā)器執(zhí)行，執(zhí)行先刪除后插入的命令，即先將數(shù)據(jù)表或視圖中的舊元組移至DELETED表中，然后將修改后的新元組插入到INSERTED表中。
（四）DML觸發(fā)器
當(dāng)數(shù)據(jù)庫有數(shù)據(jù)操作語言事件時，將調(diào)用DML觸發(fā)器，這里包括修改表或視圖數(shù)據(jù)的INSERT語句、UPDATE語句、DELETE語句，即執(zhí)行增刪改操作。如果檢測到錯誤，DML觸發(fā)器還可以實現(xiàn)整個事務(wù)的自動回滾。DML觸發(fā)器分為AFTER觸發(fā)器和INSTEAD OF觸發(fā)器，它們觸發(fā)條件差不多只不過執(zhí)行的方式不同。
1、AFTER觸發(fā)器
后觸發(fā)型觸發(fā)器，當(dāng)用戶對表或視圖執(zhí)行INSERT語句、UPDATE語句、DELETE語句之后，觸發(fā)器被觸發(fā)。
2、INSTEAD OF觸發(fā)器
前觸發(fā)型觸發(fā)器，當(dāng)用戶對表或視圖執(zhí)行INSERT語句、UPDATE語句、DELETE語句之后，系統(tǒng)不立即執(zhí)行這些操作，而是將操作內(nèi)容交給觸發(fā)器來檢查操作是否正確，若正確才繼續(xù)執(zhí)行。
（五）DDL觸發(fā)器
當(dāng)有對數(shù)據(jù)定義事件的修改時執(zhí)行，例CREATE語句、ALTER語句、DROP語句時才被觸發(fā)，一般用于執(zhí)行數(shù)據(jù)庫中的管理任務(wù)，防止數(shù)據(jù)表結(jié)構(gòu)被修改等等。
（六）創(chuàng)建觸發(fā)器
要注意CREATE TRIGGER必須是批處理中的第一條語句，即在USE <表或視圖> 后面要加GO批處理，且觸發(fā)器只能應(yīng)用到一個表中。
語句格式如下：
（七）使用觸發(fā)器
1、使用AFTER觸發(fā)器
例7、創(chuàng)建Product表的插入觸發(fā)器tri_1：其功能是當(dāng)向該表中插入一條新商品記錄后，則提示“向Product表中添加了XXX商品記錄”。并驗證該觸發(fā)器。
sql語句：
USE Sales GO CREATE TRIGGER trigger_1 ON Product AFTER INSERT AS DECLARE @ProductName varchar(30) select @ProductName=ProductName FROM INSERTED PRINT '向Product表中添加了'+@ProductName+'商品記錄' GO INSERT Product(ProductID,ProductName,CategoryID,Price,stocks) VALUES('P05001','美汁源2000ml','3','13.5','2699')
例8、創(chuàng)建Category表的UPDATE觸發(fā)器trigger_2：其功能是防止用戶修改Category表中的分類序號ID。并驗證該觸發(fā)器。
這里的ROLLBACK TRANSACTION即為事務(wù)回滾，由于進(jìn)行了事務(wù)回滾，回滾到更新操作之前的狀態(tài)，所以沒有修改CategoryID為1的日用品的CategoryName列的修改。
sql語句：
USE Sales GO CREATE TRIGGER trigger_2 ON Category AFTER UPDATE AS IF UPDATE(CategoryName) BEGIN PRINT '不能修改分類名稱' ROLLBACK TRANSACTION END GO --驗證該觸發(fā)器 USE Sales UPDATE Category SET CategoryName='水果' WHERE CategoryID='1' GO
2、使用INSTEAD OF觸發(fā)器
與AFTER觸發(fā)器不同，INSTEAD OF觸發(fā)器在觸發(fā)時只執(zhí)行觸發(fā)器內(nèi)部的T-SQL語句，而不執(zhí)行激活該觸發(fā)器的T-SQL語句。
例9、創(chuàng)建Category表的INSTEAD OF觸發(fā)器trigger_3：其功能是防止用戶對該表的任何數(shù)據(jù)進(jìn)行刪除。并驗證該觸發(fā)器。
這里由于定義了INSTEAD OF觸發(fā)器，所以并未刪除Category表的記錄。
sql語句：
USE Sales GO CREATE TRIGGER trigger_3 ON Category INSTEAD OF DELETE AS PRINT '不能刪除該表的數(shù)據(jù)！' GO --驗證該觸發(fā)器 USE Sales DELETE Category WHERE CategoryID='1' GO
（八）修改觸發(fā)器
修改觸發(fā)器使用ALTER TRIGGER語句進(jìn)行修改，與創(chuàng)建觸發(fā)器相同，只不過修改DML、DDL觸發(fā)器不一樣。
（九）刪除觸發(fā)器
刪除觸發(fā)器使用DROP TRIGGER語句進(jìn)行刪除，語句格式如下：
DROP TRIGGER <觸發(fā)器名稱>（十）觸發(fā)器的啟用和禁用
若暫時不需要使用某個觸發(fā)器，我們可以禁用該觸發(fā)器，使用的T-SQL語句是ENABLE/DISABLE TRIGGER語句。
語句格式如下：
結(jié)語
以上就是本次數(shù)據(jù)庫原理與應(yīng)用的全部內(nèi)容，篇幅較長，感謝您的閱讀和支持，若有表述或代碼中有不當(dāng)之處，望指出！您的指出和建議能給作者帶來很大的動力！！！
總結(jié)
以上是生活随笔為你收集整理的数据库原理与应用（SQL Server）笔记 第九章 存储过程和触发器的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 数据库原理与应用（SQL Server）
- 下一篇： 数据库原理与应用（SQL Server）