【正文】 SQL Server 2022 數(shù)據(jù)庫管理與開發(fā)教程 第 8章 存儲過程與觸發(fā)器 本章主要介紹如何創(chuàng)建存儲過程與使用觸發(fā)器，包括存儲過程簡介、創(chuàng)建存儲過程、執(zhí)行存儲過程、修改和刪除存儲過程、觸發(fā)器簡介、創(chuàng)建觸發(fā)器、修改觸發(fā)器和刪除觸發(fā)器。 通過本章的學(xué)習(xí)，讀者可以掌握使用管理控制器和 TransactSQL創(chuàng)建存儲過程或觸發(fā)器，并應(yīng)用存儲過程或觸發(fā)器編寫 SQL語句從而優(yōu)化查詢和提高數(shù)據(jù)訪問速度。 存儲過程簡介 創(chuàng)建存儲過程 執(zhí)行存儲過程 查看和修改存儲過程 刪除存儲過程 觸發(fā)器簡介 創(chuàng)建觸發(fā)器 修改觸發(fā)器 刪除觸發(fā)器 存儲過程簡介 在使用 TransactSQL語言編程的過程中，可以將某些需要多次調(diào)用的實現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由SQL Server服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲過程。 存儲過程在創(chuàng)建時就被編譯和優(yōu)化 ，調(diào)用一次以后 ， 相關(guān)信息就保存在內(nèi)存中 ， 下次調(diào)用時可以直接執(zhí)行 。 存儲過程有以下特點： 存儲過程中可以包含一條或多條TransactSQL語句 。 存儲過程可以接受輸入?yún)?shù)并可以返回輸出值 。 在一個存儲過程中可以調(diào)用另一個存儲過程 。 存儲過程可以返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序 。 存儲過程可以提高應(yīng)用程序的處理能力，降低編寫數(shù)據(jù)庫應(yīng)用程序的難度，同時還可以提高應(yīng)用程序的效率。 存儲過程的處理非常靈活，允許用戶使用聲明的變量，還可以有輸入輸出參數(shù)，返回單個或多個結(jié)果集以及處理后的結(jié)果值。 存儲過程的優(yōu)點 （ 1）存儲過程可以嵌套使用，支持代碼重用。 （ 2）存儲過程可以接受并使用參數(shù)動態(tài)執(zhí)行其中的 SQL語句。 （ 3）存儲過程比一般的 SQL語句執(zhí)行速度快。 （ 4）存儲過程具有安全特性（例如權(quán)限）和所有權(quán)鏈接，以及可以附加到它們的證書。 （ 5）存儲過程允許模塊化程序設(shè)計。 （ 6）存儲過程可以減少網(wǎng)絡(luò)通信流量。 （ 7）存儲過程可以強制應(yīng)用程序的安全性。 存儲過程的類別 （ 1）系統(tǒng)存儲過程： 由系統(tǒng)自動創(chuàng)建，系統(tǒng)存儲過程出現(xiàn)在每個系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫的 sys構(gòu)架中。在 SQL Server 2022中，可將GRANT、 DENY和 REVOKE權(quán)限應(yīng)用于系統(tǒng)存儲過程。系統(tǒng)存儲過程主要存儲在 master數(shù)據(jù)庫中并以 sp_為前綴。系統(tǒng)存儲過程主要是從系統(tǒng)表中獲取信息，從而為數(shù)據(jù)庫系統(tǒng)管理員管理 SQL Server提供支持。 （ 2）用戶自定義存儲過程： 是指封裝了可重用代碼的模塊或例程，由用戶創(chuàng)建，能完成某一特定的功能?？梢越邮茌斎?yún)?shù)，返回輸出參數(shù)。 （ 3）擴(kuò)展存儲過程 創(chuàng)建存儲過程 在 SQL Server 2022中創(chuàng)建存儲過程有兩種方法：一種方法是使用管理控制器創(chuàng)建存儲過程；另一種方法是使用 TransactSQL語言創(chuàng)建存儲過程。 使用企業(yè)管理器創(chuàng)建存儲過程 【 例 81】 創(chuàng)建存儲過程。 在 SQL Server管理控制臺中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的“可編程性”文件夾，右擊其中的“存儲過程”，在彈出的快捷菜單中選擇“新建存儲過程 ? ”選項。 圖 用對象資源管理器創(chuàng)建存儲過程 圖 在 Management Studio中編寫存儲過程 圖 創(chuàng)建 loving1存儲過程 使用 TransactSQL語言創(chuàng)建存 儲過程 CREATE PROCEDURE語句用于在服務(wù)器上創(chuàng)建存儲過程。 CREATE PROCEDURE語句不能與其他 SQL語句在單個批處理中組合使用 。 必須具有數(shù)據(jù)庫的 CREATE PROCEDURE權(quán)限 。 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程 。 不要創(chuàng)建任何使用 sp_作為前綴的存儲過程 。 CREATE PROCEDURE的語法形式如下： CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ ,...n ] [ WITH ENCRYPTION ] [ RECOMPILE ] AS { sql_statement [。][ ...n ] }[。] sql_statement ::= { [ BEGIN ] statements [ END ] } 其中 ， 各參數(shù)的意義如下： schema_name： 過程所屬架構(gòu)的名稱 。 procedure_name： 新存儲過程的名稱 。 @ parameter： 過程中的參數(shù) 。 [ type_schema_name. ] data_type： 參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型 。 VARYING： 指定作為輸出參數(shù)支持的結(jié)果集 。僅適用于 cursor參數(shù) 。 default： 參數(shù)的默認(rèn)值 。 OUTPUT： 指示參數(shù)是輸出參數(shù) 。 ENCRYPTION： 將 CREATE PROCEDURE語句的原始文本加密 。 RECOMPILE： 指示數(shù)據(jù)庫引擎不緩存該存儲過程的計劃 ， 該過程在運行時編譯 。 sql_statement： 要包含在過程中的一個或多個 TransactSQL語句 。 使用參數(shù) 存儲過程通過參數(shù)來與調(diào)用它的程序通信。在程序調(diào)用存儲過程時，可以通過輸入?yún)?shù)將數(shù)據(jù)傳給存儲過程，存儲過程可以通過輸出參數(shù)和返回值將數(shù)據(jù)返回給調(diào)用它的程序。 執(zhí)行帶輸入?yún)?shù)的存儲過程時， SQL Server提供了以下兩種傳遞參數(shù)的方式。 如果存儲過程中有輸入?yún)?shù)，在執(zhí)行存儲過程時沒有給出參數(shù)，則系統(tǒng)會顯示錯誤提示。 （ 1）按位置傳遞。 （ 2）通過參數(shù)名傳遞 通過定義輸出參數(shù)，可以從存儲過程中返回一個或多個值。定義輸出參數(shù)需要在參數(shù)定義的數(shù)據(jù)類型后使用關(guān)鍵字OUTPUT，或簡寫為 OUT。 【 例 82】 使用 TransactSQL語言創(chuàng)建存儲過程。 圖 創(chuàng)建 loving存儲過程 例：創(chuàng)建一個帶有 SELECT語句的簡單過程，該存儲過程返回所有員工姓名， Email地址，電話等。該存儲過程不使用任何參數(shù) 程序清單如下： USE adventureworks GO CREATE PROCEDURE au_infor_all AS SELECT lastname, firstname, address, phone FROM GO 例：創(chuàng)建一個存儲過程，以簡化對 sc表的數(shù)據(jù)添加工作，使得在執(zhí)行該存儲過程時，其參數(shù)值作為數(shù)據(jù)添加到表中。 程序清單如下： CREATE PROCEDURE [dbo].[ pr1_sc_ins] @Param1 char(10),@Param2 char(2),@Param3 real AS BEGIN insert into sc(sno,o,score) values(@Param1,@Param2,@Param3) END 例：下面的存儲過程從表工姓名及其電話。該存儲過程對傳遞的參數(shù)進(jìn)行模式匹配。如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值（姓氏以字母 D開頭） 程序清單如下： GO USE AdventureWorks。 GO CREATE PROCEDURE au_infor2 @lastname varchar(40) = 3