存儲過程和觸發(fā)器.ppt

1、2020/7/29,1,第十章 存儲過程和觸發(fā)器,2020/7/29,2,10.1存儲過程,10.1.1 存儲過程概述,存儲過程（Stored Procedure）是一組為了完成特定功能的SQL 語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。 一般用來處理需要與數(shù)據(jù)庫進行頻繁交互的復雜的業(yè)務。因為存儲過程具有以下優(yōu)點：,存儲過程在服務器端運行，執(zhí)行速度快。 封裝業(yè)務邏輯也可以使數(shù)據(jù)庫操作人員與應用系統(tǒng)開發(fā)人員的分工更明確，支持模塊化設計。,2020/7/29,3,存儲過程執(zhí)行一次后，其執(zhí)行計劃就駐留在高速緩沖存儲器，如果需要再次調用，只需要從高速緩沖存儲器中調用，提高系統(tǒng)性能。 存儲過程可以作為安全機制。通

2、過授予用戶對存儲過程的操作權限來實現(xiàn)安全機制。,使體現(xiàn)企業(yè)規(guī)則的運算程序放入數(shù)據(jù)庫服務器中，以便集中控制。,2020/7/29,4,10.1.2 存儲過程的分類,1系統(tǒng)存儲過程 系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，以sp_開頭，可以在其他數(shù)據(jù)庫中進行調用。 如：sp_help就是取得指定對象的相關信息。 2擴展存儲過程 在SQL Server環(huán)境之外執(zhí)行動態(tài)鏈接庫稱為擴展存儲過程，以XP_開頭,用來調用操作系統(tǒng)提供的功能。 3用戶自定義的存儲過程 由用戶創(chuàng)建并能完成某一特定功能的存儲過程。包括Transact-SQL和CLR兩種類型。 （1）Transact-SQL存儲過程是指保存的Tra

3、nsact-SQL語句集合，可以接受和返回用戶提供參數(shù)。也可以從數(shù)據(jù)庫向客戶端應用程序返回數(shù)據(jù)。 （2）CLR在本書不做詳細介紹。,2020/7/29,5,10.1.3 建立存儲過程,1. 使用對象資源管理器建立存儲過程,2. 使用T-SQL命令建立存儲過程,語法形式如下： CREATE PROCEDURE procedure_name parameter data_type VARYING =defaultOUTPUT ,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement .n

4、 ,2020/7/29,6,【例題10.1】創(chuàng)建一個不帶參數(shù)的存儲過程，名稱為pro_stu，用于檢索學生的姓名、性別和家庭住址。 USE score GO -判斷pro_stu存儲過程是否存在，若存在，則刪除 If exists(select name from sysobjects where name=pro_stu and type=p) DROP PROCEDURE pro_stu Go -建立存儲過程 CREATE PROC pro_stu As SELECT student_name, sex, address FROM student,2020/7/29,7,【例題10.2】創(chuàng)

5、建一個帶參數(shù)的存儲過程，名稱為pro_class，在執(zhí)行存儲過程的時候輸入班級編號，然后檢索該班級的學生的詳細信息。 USE score GO -判斷pro_class存儲過程是否存在，若存在，則刪除 If exists(select name from sysobjects where name=pro_class and type=p) DROP PROCEDURE pro_class Go -建立存儲過程 CREATE PROC pro_class c_no char(8) As SELECT * FROM student WHERE class_id=c_no,2020/7/29,8,

6、10.1.4 存儲過程的執(zhí)行,語法形式如下： EXECUTE return_status = procedure_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE ,2020/7/29,9,1.無參存儲過程的執(zhí)行,【例題10.3】執(zhí)行在例題10.1中創(chuàng)建的存儲過程pro_stu。 EXEC pro_stu,在執(zhí)行帶參存儲過程的時候參數(shù)有以下幾種傳遞方式：,2.帶參存儲過程的執(zhí)行,（1）順序法,2020/7/29,10,CREATE PROC pro_update s_id char

7、(10), c_id char(5), result int -參數(shù) AS UPDATE score SET grade=result WHERE student_id=s_id AND course_id=c_id / *順序法執(zhí)行存儲過程，把0801101號學生1001號課程的成績修改為85分*/ EXEC pro_update 0801101, 1001, 85,【例題10.4】建立一個存儲過程pro_update，通過指定學號和課程編號來把成績表中的成績修改為指定的某個值，然后執(zhí)行該存儲過程。,2020/7/29,11,EXEC pro_update s_id=0801101, res

8、ult=85,c_id=1001 說明：提示法中的參數(shù)順序可以與建立存儲過程時的參數(shù)順序不一致，而順序法必須保持一致。,（2）提示法,【例題10.5】通過提示法來執(zhí)行存儲過程pro_update。,2020/7/29,12,建立存儲過程時，可以指定參數(shù)的默認值，默認值必須為常量或者為NULL。,（3）使用默認參數(shù),【例題10.6】建立一個存儲過程pro_insert，用來完成向課程表中插入記錄的功能，然后執(zhí)行該存儲過程并檢查執(zhí)行結果。 CREATE PROC pro_insert c_id char(5), c_name char(20), period int=64, c_type char

9、(10)=NULL AS INSERT course VALUES( c_id ,c_name,period,c_type ) GO -執(zhí)行存儲過程pro_insert EXEC pro_insert 1006, c+程序設計 GO -查看插入結果 SELECT * FROM course,2020/7/29,13,在建立存儲過程時，通過定義輸出參數(shù)，可以從存儲過程中返回一個或多個值。在執(zhí)行存儲過程時，可以將結果返回給返回參數(shù)。定義輸出參數(shù)需要在參數(shù)定義的數(shù)據(jù)類型后使用關鍵字OUTPUT，或省寫為OUT。,（4）使用帶OUTPUT的返回參數(shù),【例題10.7】建立一個帶參數(shù)的存儲過程，用于求兩個

10、整數(shù)的和，其中和定義為OUTPUT參數(shù)，然后執(zhí)行存儲過程并輸出和。 CREATE PROC pro_sum a int ,b int, sum int output AS SET sum=a+b GO -執(zhí)行存儲過程 DECLARE s int EXEC pro_sum 20 ,15,s output PRINT s,2020/7/29,14,10.1.5 存儲過程的返回值,存儲過程被執(zhí)行以后可以返回一個整數(shù)類型的代碼值，該值用來記錄存儲過程的執(zhí)行情況。若返回值為0，表示執(zhí)行成功；否則表示執(zhí)行不成功。,1）用一個變量來捕捉存儲過程執(zhí)行后返回值。 2）使用RETURN語句為存儲過程指定一個確定的

11、返回值，以用來反映存儲過程執(zhí)行的結果情況。,捕捉存儲過程返回值的方法,2020/7/29,15,【例題10.9】用變量來捕捉存儲過程執(zhí)行后返回值。建立一個存儲過程，完成查詢指定學生指定科目的成績。然后執(zhí)行該存儲過程并捕捉執(zhí)行是否成功。 CREATE PROC pro_grade s_id char(10), c_id char(5) AS SELECT grade FROM score WHERE student_id=s_id AND course_id=c_id GO DECLARE status int -聲明變量,用來存放執(zhí)行結果的狀態(tài) EXEC status=pro_grade 08

12、01101,1001 SELECT status -輸出狀態(tài)值,2020/7/29,16,【例題10.10】使用RETURN語句為存儲過程指定一個確定的返回值。建立一個存儲過程，完成查詢指定學生指定科目的成績，如果成績大于60分，返回1；如果等于60分，返回0；小于60分，則返回-1。然后執(zhí)行該存儲過程并輸出返回值。 CREATE PROC pro_return s_id char(10), c_id char(5) AS DECLARE result int SELECT result=grade FROM score WHERE student_id=s_id AND course_id=

13、c_id IF result60 RETURN 1 IF result=60 RETURN 0 IF result60 RETURN -1 GO DECLARE status int -聲明變量,用來存放執(zhí)行結果的狀態(tài) EXEC status=pro_return 0801101,1001 SELECT 狀態(tài)=status -輸出狀態(tài)值,2020/7/29,17,10.1.6 查看、修改和刪除存儲過程,1.修改存儲過程,語法形式如下： ALTER PROCEDURE procedure_name parameter data_type VARYING =defaultOUTPUT ,.n WI

14、TH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n ,2020/7/29,18,【例題10.11】修改存儲過程pro_sum，使其功能更改為輸出兩個數(shù)相加的和，不使用返回參數(shù)。,USE score GO ALTER PROC pro_sum a int ,b int AS DECLARE sum int SET sum=a+b PRINT sum,2020/7/29,19,2.查看存儲過程,（1）使用對象資源管理器查看,（2）使用T-SQL命令查看,1）查看存儲過程的參數(shù)及其數(shù)據(jù)類型

15、格式：sp_help name 2）查看存儲過程的源代碼 格式：sp_helptext name 3）查看和存儲過程相關的數(shù)據(jù)庫對象 格式：sp_depends name,2020/7/29,20,【例題10.12】查看存儲過程pro_return的定義文本。 EXEC sp_helptext pro_return,例題10.13】查看存儲過程pro_return的所有者、類型及參數(shù)。 EXEC sp_help pro_return,【例題10.14】查看存儲過程pro_return的相關性。 EXEC sp_depends pro_return,2020/7/29,21,3.刪除存儲過程,（

16、1）使用對象資源管理器刪除存儲過程,（2）使用T-SQL語句刪除存儲過程,語法格式： DROP PROCEDURE procedure_name ,n,【例題10.15】刪除存儲過程pro_return。 DROP PROC pro_return,2020/7/29,22,10.2 觸發(fā)器,10.2.1 觸發(fā)器的概述,Microsoft SQL Server 2005提供了兩種主要機制來強制執(zhí)行業(yè)務規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。,觸發(fā)器的主要作用就是其能夠實現(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據(jù)的一致性。具體表現(xiàn)為： 觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行級聯(lián)更改。 觸發(fā)器可以防止惡意

17、或錯誤的UPDATE、 INSERT、 DELETE 、CREATE和ALTER操作，這些限制比用 CHECK 約束所定義的更復雜。 觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。 一個表可以有多個同類觸發(fā)器允許采取多個不同的操作來響應同一個修改語句。,2020/7/29,23,10.2.2 觸發(fā)器的類型,1.DML觸發(fā)器,DML觸發(fā)器是當數(shù)據(jù)庫服務器中發(fā)生數(shù)據(jù)操作語言事件時所執(zhí)行的操作 .,DML觸發(fā)器有三種類型：AFTER觸發(fā)器、INSTEAD OF觸發(fā)器和CLR觸發(fā)器。,兩大類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。,2020/7/29,24,指定用觸發(fā)器中的操作代替觸發(fā)語句的操

18、作，也就是該觸發(fā)器并不執(zhí)行所定義的操作（INSERT、 DELETE、 UPDATE），而是執(zhí)行觸發(fā)器本身的SQL語句?？梢詾榛谝粋€或多個表的視圖定義INSTEAD OF觸發(fā)器，而這些觸發(fā)器可能夠擴展視圖可支持的數(shù)據(jù)類型。,（2）INSTEAD OF觸發(fā)器,表示只有在執(zhí)行了指定的操作（INSERT、 DELETE、 UPDATE）之后觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的SQL 語句。若只指定FOR， 則默認為AFTER 觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建。,（1）AFTER觸發(fā)器,2020/7/29,25,DDL觸發(fā)器是一種特殊的觸發(fā)器，當服務器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL，CREATE、

19、DROP、ALTER等）事件時將激活這些觸發(fā)器。他們可以用于在數(shù)據(jù)庫中執(zhí)行管理任務，譬如，審核以及規(guī)范數(shù)據(jù)庫操作。DDL觸發(fā)器無法作為INSTEAD OF觸發(fā)器使用。,2.DDL觸發(fā)器,如果要執(zhí)行以下操作，可以考慮使用DDL觸發(fā)器。 防止他人對數(shù)據(jù)庫架構進行修改。 希望數(shù)據(jù)庫發(fā)生某種情況以響應數(shù)據(jù)庫架構中的更改。 要記錄數(shù)據(jù)庫架構中的更改或事件。,2020/7/29,26,(1) 使用對象資源管理器建立DML觸發(fā)器,10.2.3 DML觸發(fā)器,1. 建立DML觸發(fā)器,(2) 使用T-SQL語句建立DML觸發(fā)器 語法格式： CREATE TRIGGER trigger_name ON table

20、|view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS sql_statement.n ,2020/7/29,27,【例題10.16】在student表中創(chuàng)建了一個觸發(fā)器，當往該表中成功插入記錄時，輸出提示信息。然后執(zhí)行插入記錄操作，檢驗觸發(fā)器。 -判斷insert_stu觸發(fā)器是否存在，若存在，則刪除 IF EXISTS (SELECT name FROM sysobjects WHERE name = insert_stu AND type = TR) DROP TRIGGER insert_st

21、u GO -建立觸發(fā)器 CREATE TRIGGER insert_stu ON student FOR INSERT AS PRINT 插入記錄成功 -插入記錄，激活觸發(fā)器 INSERT student(student_id,student_name,sex,class_id) VALUES(0801109,吳盼盼,男,08011),2020/7/29,28,（1）inserted表和deleted表,2DML觸發(fā)器的應用,在執(zhí)行觸發(fā)器時，SQL Server 會為觸發(fā)器建立兩個臨時表：deleted 表和 inserted，它們的結構和觸發(fā)器所在的表的結構相同,Inserted表存儲INS

22、ERT和UPDATE語句所影響的行的副本。在一個插入或更新事務處理中，新建行被同時添加到inserted表和觸發(fā)器表中。Inserted表中的行是觸發(fā)器表中新行的副本。 Deleted表存儲DELETE和UPDATE語句所影響的行的復本。在執(zhí)行DELETE或UPDATE語句時，記錄從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。Deleted表和觸發(fā)器表通常沒有相同的行。,2020/7/29,29,注意一：執(zhí)行 UPDATE 語句時，會被視為插入或刪除事務，舊的行值會保留一份副本在deleted表中，而新的行值的副本則保留在觸發(fā)器表與inserted表。 注意二：inserted和deleted

23、表中的值只限于在觸發(fā)器中使用。一旦觸發(fā)器完成就無法再使用。,2020/7/29,30,【例題10.17】建立一個觸發(fā)器，在修改數(shù)據(jù)時激活該觸發(fā)器。然后執(zhí)行修改操作，查看inserted和deleted表中的數(shù)據(jù)。 -建立觸發(fā)器 CREATE TRIGGER update_stu ON student FOR insert, update,delete AS SELECT * FROM inserted SELECT * FROM deleted -修改數(shù)據(jù)，激活觸發(fā)器 UPDATE student SET sex=男 WHERE student_id=0801101,2020/7/29,31,

24、當往表中插入記錄時，INSERT觸發(fā)器被執(zhí)行。一般情況下，INSERT觸發(fā)器被用來更新時間標記字段，或者驗證被觸發(fā)器監(jiān)控的字段中的數(shù)據(jù)是否滿足要求，以確保數(shù)據(jù)完整性。,（2）INSERT觸發(fā)器,2020/7/29,32,【例題10.18】建立一個觸發(fā)器，當向成績表中插入一條記錄時，檢查被插入的成績是否大于等于0和小于等于100。若不滿足該條件，不允許插入記錄。 CREATE TRIGGER check_insert ON score AFTER insert AS DECLARE 成績 int SELECT 成績=grade FROM inserted IF 成績=0 AND 成績=100 P

25、RINT 插入成功 ELSE BEGIN PRINT 成績值超出范圍,不允許插入 ROLLBACK TRANSACTION END -插入記錄，檢驗insert觸發(fā)器的作用 INSERT grade(student_id,course_id,grade) VALUES(0801107,1001,-10),2020/7/29,33,(3)UPDATE觸發(fā)器 修改觸發(fā)器和插入觸發(fā)器的工作過程基本上一致，修改一條記錄等于插入了一條新的記錄并且刪除一條舊的記錄。,2020/7/29,34,【例題10.19】假設成績信息被成功錄入到表中以后將允許修改，建立一個觸發(fā)器，禁止其修改成績。 CREATE TR

26、IGGER check_update ON score AFTER update AS IF update(grade) BEGIN PRINT 修改無效,成績被錄入后,不允許修改 ROLLBACK TRANSACTION END -修改成績值，檢查update觸發(fā)器的作用 UPDATE score set grade=80 WHERE student_id=0801101 AND course_id=1001,2020/7/29,35,(4)DELETE觸發(fā)器 DELETE觸發(fā)器通常用于兩種情況，第一種情況是為了防止那些確實需要刪除但會引起數(shù)據(jù)一致性問題的記錄的刪除。 第二種情況是執(zhí)行可刪除

27、主記錄的子記錄的級聯(lián)刪除操作。可以使用這樣的觸發(fā)器從主銷售記錄中刪除所有的定單項。,2020/7/29,36,【例題10.20】在學生表中建立一個觸發(fā)器，若刪除學生記錄，則把該學生在成績表中的成績記錄一并刪除。 CREATE TRIGGER check_delete ON student AFTER delete AS DELETE score WHERE student_id in (SELECT student_id FROM deleted) -刪除學生記錄，檢查delete觸發(fā)器的作用 DELETE student WHERE student_id=0801103 -查看成績表grad

28、e中0801103號學生的成績是否被刪除 SELECT * FROM grade,2020/7/29,37,10.2.4 DDL觸發(fā)器 1、建立DDL觸發(fā)器 CREATE TRIGGER trigger_name ON ALL SERVER|DATABASE WITH ENCRYPTION FOR | AFTER event_type|event_group,n AS sql_statement.n ,2020/7/29,38,參數(shù)說明： ALL SERVER 指明觸發(fā)器的作用域為當前服務器。如果指定了此參數(shù)，則只要當前服務器中的任何位置出現(xiàn)event_type或event_group，就會激

29、活觸發(fā)器。 DATABASE 指明觸發(fā)器的作用域為當前數(shù)據(jù)庫。如果指定了此參數(shù)，則只要當前數(shù)據(jù)庫中的任何位置出現(xiàn)event_type或event_group，就會激活觸發(fā)器。 event_type 將導致激活DDL觸發(fā)器的T-SQL語言的名稱。event_type選項有：CREATE_TABLE、CREATE_ DATABASE 、ALTER_TABLE等等。 event_group 預定義的T-SQL語言事件分組的名稱。執(zhí)行任何屬于event_group的T-SQL語言事件，都將激活DDL觸發(fā)器。 event_group有：DDL_SERVER_SECURITY_EVENTS代表所有以服務器

30、為目標的各類DDL語法語法事件，而 DDL_TABLE_VIEW_EVENTS代表了針對數(shù)據(jù)表、視圖表、索引與統(tǒng)計的DDL事件。,2020/7/29,39,【例題10.21】使用 DDL 觸發(fā)器來防止數(shù)據(jù)庫score中的表被任意修改或刪除。 USE score GO CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS BEGIN PRINT 禁止修改或刪除表 ROLLBACK END -修改表，檢驗safety觸發(fā)器的功能 ALTER TABLE student ADD f_name char(8),2020/7/2

31、9,40,【例題10.22】建立 DDL 觸發(fā)器來防止在當前服務器下建立數(shù)據(jù)庫。 CREATE TRIGGER forbid_create ON ALL SERVER FOR CREATE_DATABASE AS BEGIN PRINT 禁止建立數(shù)據(jù)庫 ROLLBACK END -建立數(shù)據(jù)庫，檢驗forbid_create觸發(fā)器的功能 CREATE DATABASE book,2020/7/29,41,10.2.5查看、修改和刪除觸發(fā)器,1. 查看觸發(fā)器 （）使用對象資源管理器查看觸發(fā)器信息 （）使用系統(tǒng)存儲過程查看觸發(fā)器,2020/7/29,42,（1）sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。 格式：sp_help 觸發(fā)器名稱 （2） sp_helptext：用于查看觸發(fā)器的正文信息 格式：s