項目4 存儲過程與觸發(fā)器管理

1、項目4：存儲過程與觸發(fā)器管理任務(wù)4-1：存儲過程的創(chuàng)建 任務(wù)4-2：存儲過程的執(zhí)行與管理任務(wù)4-3：觸發(fā)器的創(chuàng)建與使用1任務(wù)4-1：存儲過程的創(chuàng)建21、概述1）存儲過程的基本概念SQL Server的存儲過程類似于編程語言中的過程。在使用Transact-SQL語言編程的過程中，可以將某些多次調(diào)用以實現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由SQL Server服務(wù)器通過過程名調(diào)用它們，這些過程就叫做存儲過程。3存儲過程可以實現(xiàn)：接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過程或批處理返回多個值。包含用于在數(shù)據(jù)庫中執(zhí)行操作（包括調(diào)用其他過程）的編程語句。 向調(diào)用過程或批處理返回狀態(tài)

2、值，以指明成功或失敗（以及失敗的原因）。42）存儲過程的分類系統(tǒng)存儲過程：由SQL Server 2005提供，用戶可以直接使用。SQL Server 2005 中的許多管理活動都是通過一些系統(tǒng)存儲過程完成的。用戶定義的存儲過程：用戶自定義存儲過程用于實現(xiàn)用戶自己所需要實現(xiàn)的操作。53）存儲過程的優(yōu)點實現(xiàn)模塊化程序設(shè)計。存儲過程內(nèi)可引用其他存儲過程，可以簡化一系列復(fù)雜語句?？梢詼p少網(wǎng)絡(luò)通信流量。存儲過程在創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比單個 SQL 語句快。可以作為一種安全機制。一個設(shè)計良好的數(shù)據(jù)庫應(yīng)用程序通常都會用到存儲過程。62、創(chuàng)建存儲過程1）使用SQL Server Mana

3、gement Studio創(chuàng)建存儲過程教材P157。72）使用T-SQL語句創(chuàng)建存儲過程CREATE PROC EDURE procedure_name parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION ASsql_statement說明：教材P157P158。8例9.1 不帶有參數(shù)的存儲過程。 下面的存儲過程從sales數(shù)據(jù)庫的三個表的連接中返回訂單的編號、客戶名稱、銷售員和訂單日期。該存儲過程不使用任何參數(shù)。USE salesGOCREATE PROCE

4、DURE UP_OrderInfoASSELECT Orders.OrderID, Customer.CompanyName, Seller.Salename, orders.OrderDate FROM Orders INNER JOIN Seller ON Orders.SaleID = Seller.SaleIDINNER JOIN Customer ON Orders.CustomerID = Customer.CustomerIDGO9例9.2 帶有輸入?yún)?shù)的存儲過程。 下面的存儲過程從sales數(shù)據(jù)庫的三個表的聯(lián)接中返回訂單的編號、客戶名稱、銷售員和訂單日期。該存儲過程接受日期區(qū)間

5、參數(shù)：開始日期和結(jié)束日期。USE salesGOCREATE PROCEDURE UP_OrderInfoWithParam StartDate datetime, EndDate datetimeAS SELECT Orders.OrderID, Customer.CompanyName, Seller.Salename,Orders.OrderDate FROM Orders INNER JOIN Seller ON Orders.SaleID = Seller.SaleID INNER JOIN Customer ON Orders.CustomerID = Customer.Custo

6、merIDWHERE (Orders.OrderDate BETWEEN StartDate AND EndDate)GO10例9.3 帶有輸出參數(shù)的存儲過程。 該存儲過程從sales數(shù)據(jù)庫的Customer表查詢客戶的基本信息，輸入?yún)?shù)為客戶編號，輸出參數(shù)為客戶所在公司的名稱和公司地址。USE salesGOCREATE PROCEDURE UP_CustomerInfo CustId nvarchar(3), ComName nvarchar(60) OUTPUT, ComAddress nvarchar(60) OUTPUTAS SELECT ComName=CompanyName, C

7、omAddress=AddressFROM Customer WHERE CustomerID=CustIdGO11例9.4 帶有返回值的存儲過程。 該存儲過程向Category表中插入數(shù)據(jù)，如果插入成功返回1，插入失敗返回0，輸入?yún)?shù)為商品種類編號、種類名稱和種類表述信息。USE salesGOCREATE PROCEDURE UP_InsertCate CategoryID int, CategoryName nvarchar(15), Description nvarchar(200)AS SET nocount on IF (NOT EXISTS(SELECT * FROM Categ

8、ory WHERE CategoryID=CategoryID) BEGIN INSERT INTO Category(CategoryID,CategoryName,Description) VALUES(CategoryID,CategoryName,Description) RETURN 1-添加數(shù)據(jù)成功返回1 END ELSE RETURN 0-添加數(shù)據(jù)失敗返回0GO12任務(wù)4-2：存儲過程的執(zhí)行與管理131、執(zhí)行存儲過程存儲過程可以通過EXECUTE語句來執(zhí)行，其語法格式如下： EXEC UTE return_status = procedure_name | procedure_n

9、ame_var parameter = value | OUTPUT | DEFAULT ,.n WITH RECOMPILE 說明：教材P159P160。141）不帶參數(shù)的存儲過程調(diào)用 調(diào)用例9.1中的存儲過程。USE salesEXECUTE UP_OrderInfo-或EXEC UP_OrderInfo-或UP_OrderInfo152）帶輸入?yún)?shù)的存儲過程的調(diào)用 調(diào)用例9.2中的存儲過程。USE salesEXECUTE UP_OrderInfoWithParam 2008-7-1, 2008-7-9-或EXECUTE UP_OrderInfoWithParam StartDate=2

10、008-7-1, EndDate=2008-7-9-或EXECUTE UP_OrderInfoWithParam EndDate=2008-7-9, StartDate=2008-7-1163）帶輸出參數(shù)的存儲過程的調(diào)用 調(diào)用例9.3中的存儲過程。USE salesGODECLARE Name nvarchar(60), Address nvarchar(60)EXECUTE UP_CustomerInfo C01, Name OUTPUT, Address OUTPUT SELECT Name, Address174）帶有返回值的存儲過程的調(diào)用 調(diào)用例9.4中的存儲過程。USE salesD

11、ECLARE return_value intEXECUTE return_value = UP_InsertCate 5, 香煙, 中華、熊貓和玉溪IF return_value=1 PRINT 添加數(shù)據(jù)成功ELSE PRINT 數(shù)據(jù)已存在，不能添加185）一個存儲過程調(diào)用另一存儲過程-創(chuàng)建存儲過程USE salesGOCREATE PROCEDURE UP_CallInsertCateAS DECLARE return_value int EXECUTE return_value = UP_InsertCate 5,香煙,中華、熊貓和玉溪 IF return_value=1 PRINT 添

12、加數(shù)據(jù)成功 ELSE PRINT 數(shù)據(jù)已存在，不能添加GO-執(zhí)行存儲過程USE salesEXECUTE UP_CallInsertCate192、管理存儲過程1）查看存儲過程創(chuàng)建存儲過程之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用系統(tǒng)存儲過程sp_help、sp_helptext、sp_depends來查看用戶自定義存儲過程。202）修改存儲過程ALTER PROC EDURE procedure_name parameter data_type = default OUTPUT ,.n WITH RECOMPILE | ENC

13、RYPTION | RECOMPILE , ENCRYPTION ASsql_statement注：修改存儲過程的語法格式與創(chuàng)建存儲過程的語法格式相似。213）重命名存儲過程 sp_rename procedure_name , new_procedure_name示例：USE salesGOsp_rename UP_OrderInfoWithParam, UP_OrderInfoDateGO224）刪除名存儲過程 DROP PROCEDURE procedure ,.n 示例：USE salesGODROP PRODEDURE UP_OrderInfoGO23課堂練習（1）創(chuàng)建一個存儲過程【

14、proc_1】，其功能是用來查找所有女銷售員的清單。然后執(zhí)行該存儲過程。（2）創(chuàng)建一個帶有輸入?yún)?shù)的存儲過程【proc_2】，其功能是通過一個給定的銷售員編號，顯示出該銷售員的詳細信息。然后執(zhí)行該存儲過程。（3）創(chuàng)建一個帶有輸入?yún)?shù)和輸出參數(shù)的存儲過程【proc_3】，其功能是通過一個給定的產(chǎn)品編號，查詢出該產(chǎn)品的價格和庫存量，并通過輸出參數(shù)進行返回。然后執(zhí)行該存儲過程。24任務(wù)4-3：觸發(fā)器的創(chuàng)建和使用251、概述觸發(fā)器（trigger）是一種特殊的存儲過程，它不同于一般的存儲過程。一般的存儲過程通過過程名被直接調(diào)用，而觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行。觸發(fā)器是一個功能強大的工具，與表緊

15、密連接，可以看作是表格定義的一部分。當用戶修改（INSERT、UPDATE或DELETE）指定表或視圖中的數(shù)據(jù)時，該表中的相應(yīng)的觸發(fā)器就會自動執(zhí)行。觸發(fā)器基于一個表創(chuàng)建，但可以操作多個表。觸發(fā)器常用來實現(xiàn)復(fù)雜的商業(yè)規(guī)則。但是，不管觸發(fā)器所進行的操作多復(fù)雜，觸發(fā)器都只作為一個獨立的單元被執(zhí)行，被看作一個事務(wù)。如果在執(zhí)行觸發(fā)器的過程中發(fā)生了錯誤，則整個事務(wù)將會自動回滾。262、觸發(fā)器的分類DML 觸發(fā)器 AFTER 觸發(fā)器 INSTEAD OF 觸發(fā)器 CLR 觸發(fā)器DDL 觸發(fā)器271） DML 觸發(fā)器 當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (DML) 事件時將調(diào)用 DML 觸發(fā)器。DML 事件包括在指定

16、表或視圖中修改數(shù)據(jù)的 INSERT 語句、UPDATE 語句或 DELETE 語句。DML 觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的 Transact-SQL 語句。28 AFTER 觸發(fā)器 又稱后觸發(fā)器（After Trigger），這種類型的觸發(fā)器將在執(zhí)行了相應(yīng)的DML語句操作之后才被觸發(fā)?？梢詫ψ儎拥臄?shù)據(jù)進行檢查，如果發(fā)現(xiàn)錯誤，將拒絕接受或回滾變動的數(shù)據(jù)。指定AFTER與指定FOR相同，AFTER 觸發(fā)器只能在表上定義。在同一個數(shù)據(jù)表中可以創(chuàng)建多個AFTER觸發(fā)器。29 INSTEAD OF 觸發(fā)器 又稱前觸發(fā)器（Inserted Of Trigger）INSTEAD OF觸發(fā)器在數(shù)據(jù)變動

17、以前被觸發(fā)，并取代變動數(shù)據(jù)的操作（UPDATE、INSERT和DELETE操作），而去執(zhí)行觸發(fā)器定義的操作。INSTEAD OF觸發(fā)器可以在表或視圖上定義。在表或視圖上，每個UPDATE、INSERT和DELETE語句最多可以定義一個INSTEAD OF觸發(fā)器。30 CLR 觸發(fā)器 CLR 觸發(fā)器可以是AFTER觸發(fā)器或INSTEAD OF 觸發(fā)器。CLR 觸發(fā)器還可以是DDL 觸發(fā)器。CLR 觸發(fā)器將執(zhí)行在托管代碼（在 .NET Framework 中創(chuàng)建并在 SQL Server 中上載的程序集的成員）編寫的方法，而不用執(zhí)行Transact-SQL語句。31說明： DML 觸發(fā)器語句使用兩

18、種特殊的表：刪除的表（deleted 表）和插入的表（inserted 表）。SQL Server 2005 會自動創(chuàng)建和管理這兩種表。可以使用這兩種駐留內(nèi)存的臨時表來測試特定數(shù)據(jù)修改的影響以及設(shè)置 DML 觸發(fā)器操作條件。 刪除的表deleted 用于存儲 DELETE 和 UPDATE 語句所影響的行的副本。在執(zhí)行 DELETE 或 UPDATE 語句的過程中，行從觸發(fā)器的基表中刪除，并傳輸?shù)絛eleted表中。deleted表和觸發(fā)器的基表通常沒有相同的行。 插入的表inserted用于存儲 INSERT 和 UPDATE 語句所影響的行的副本。在插入或更新事務(wù)期間，新行將同時被添加到i

19、nserted表和觸發(fā)器基表。inserted表中的行是觸發(fā)器基表中新行的副本。 更新操作類似于在刪除操作之后執(zhí)行插入操作；首先，舊行被復(fù)制到deleted表中，然后，新行被復(fù)制到觸發(fā)器表和inserted表中。322）DDL 觸發(fā)器 DDL 觸發(fā)器是一種特殊的觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語言 (DDL)主要包括CREATE、ALTER和DROP 語句時觸發(fā)。它們可以用于數(shù)據(jù)庫中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫操作。333、創(chuàng)建觸發(fā)器1）使用SQL Server Management Studio創(chuàng)建觸發(fā)器教材P166。342）使用T-SQL語句創(chuàng)建觸發(fā)器 創(chuàng)建DML觸發(fā)器創(chuàng)建觸發(fā)器時需指定

20、： 觸發(fā)器名稱。在其上定義觸發(fā)器的表或視圖。觸發(fā)器將何時激發(fā)。激活觸發(fā)器的數(shù)據(jù)修改語句。有效選項為 INSERT、UPDATE 或 DELETE。多個數(shù)據(jù)修改語句可激活同一個觸發(fā)器。例如，觸發(fā)器可由 INSERT 或 UPDATE 語句激活。觸發(fā)操作主體。35創(chuàng)建DML觸發(fā)器的語句格式如下：CREATE TRIGGER schema_name.trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE AS dml_sql_statement 說明：教材P167。3

21、6例10.1 創(chuàng)建一個后觸發(fā)器，在Seller表中插入數(shù)據(jù)后，顯示友好的提示信息。USE salesGOCREATE TRIGGER tr_notify ON SellerAFTER INSERTAS BEGIN PRINT (剛剛在Seller表中增加了一條記錄！)ENDGO向Seller表中添加一條記錄來驗證觸發(fā)器： INSERT INTO Seller(SaleID,SaleName)VALUES(s23,趙明明)37例10.2 創(chuàng)建Customer表的刪除觸發(fā)器tr_CustomerDelete。USE salesGOCREATE TRIGGER tr_CustomerDeleteON

22、 CustomerFOR DELETEAS BEGIN DECLARE com varchar(60) SELECT com= CompanyName FROM DELETED PRINT rtrim(com)+ 客戶信息已經(jīng)被刪除！ENDGO刪除Customer表中的一條記錄來驗證觸發(fā)器： DELETE FROM Customer WHERE CustomerID=c0838 創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的語句格式如下： CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE FOR | AFTERAS ddl_sql_statement

23、說明：教材P16839例10.3 創(chuàng)建DDL觸發(fā)器，當修改sales數(shù)據(jù)庫中的表結(jié)構(gòu)時，彈出提示信息“數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改！”。USE salesGOCREATE TRIGGER tr_alterTableON DATABASEFOR ALTER_TABLEASBEGIN PRINT 數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改！ENDGO向Customer表中增加一個字段來驗證觸發(fā)器： ALTER TABLE Customer ADD Memo varchar(200)404、管理觸發(fā)器1）修改觸發(fā)器使用ALTER TRIGGER命令修改觸發(fā)器，修改觸發(fā)器與創(chuàng)建觸發(fā)器的語法基本相同，只是將創(chuàng)建觸發(fā)器的CREATE關(guān)

24、鍵字換成了ALTER關(guān)鍵字。412）查看觸發(fā)器可以使用系統(tǒng)存儲過程sp_help、sp_helptext、sp_depends和sp_helptrigger分別查看觸發(fā)器的不同信息。其中，系統(tǒng)存儲過程sp_helptrigger，返回對當前數(shù)據(jù)庫的指定表上定義的DML 觸發(fā)器的類型，語法格式如下：sp_helptrigger table_name423）禁用/啟用觸發(fā)器當用戶想暫停觸發(fā)器的使用，但又不想刪除它，這時可以禁用觸發(fā)器，使其無效。當需要時可以再次啟用。 禁用/啟用DML觸發(fā)器使用DISABLE TRIGGER命令禁用觸發(fā)器，語法格式為： DISABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name 說明：教材P170使用ENABLE TRIGGER命令禁用觸發(fā)器，語法格式為： ENABLE TRIGGER schema_name trigger_name ,.n | ALL ON object_name例10.7 啟用Customer表上的tr_CustomerDelete觸發(fā)器。 ENABLE TRIGGER tr_Cust