《存儲過程和觸發(fā)器》PPT課件

1、存儲過程、觸發(fā)器和數(shù)據(jù)完整性（ SQL的高級功能,存儲過程 觸發(fā)器 數(shù)據(jù)完整性,任務,掌握存儲過程，觸發(fā)器的概念和使用方法； 掌握運用T-SQL編寫基本的存儲過程、觸發(fā)器。（ ） 理解存儲過程、觸發(fā)器的用途； 進一步理解數(shù)據(jù)完整性的含義（） 了解數(shù)據(jù)完整性的規(guī)則、默認值等使用,5.1 存儲過程,5.1.1 存儲過程的基本概念 5.1.2 存儲過程的優(yōu)點 5.1.3 存儲過程的分類 5.1.4 存儲過程的使用方法,5.1 存儲過程,傳統(tǒng)的數(shù)據(jù)庫結(jié)構(gòu) 管理數(shù)據(jù)等共享資源， 所有的應用程序都在用戶端，都與用戶實際運行的應用程序捆綁在一起； 客戶/服務器數(shù)據(jù)庫 管理數(shù)據(jù)等共享資源 承擔一些應用邏輯，完

2、成來自客戶端的一些處理請求，在數(shù)據(jù)庫中還可以存放程序，即存儲過程,5.1.1 存儲過程的基本概念,是什么：是事先編好的、存儲在服務器端的數(shù)據(jù)庫中的程序（預編譯的SQL集合），這些程序用來完成對數(shù)據(jù)庫的指定操作。 怎么使用：這些程序可以由應用程序的調(diào)用啟動，或由數(shù)據(jù)完整性規(guī)則和觸發(fā)器調(diào)用。 怎么理解：存儲過程是用戶可以簡單地將其作為一個函數(shù)來調(diào)用，無須重復執(zhí)行存儲過程的SQL語句。它包含一組經(jīng)常執(zhí)行的、邏輯完整的SQL語句,5.1.2 存儲過程的優(yōu)點,減輕程序編寫的工作量：可以在各個程序中反復調(diào)用定義好的存儲過程。 存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度 ：因為存儲過程是預編譯的，而批處理的T- SQL

3、 語句在每次運行時都要進行編譯和優(yōu)化，因此速度相對要慢一些。 存儲過程能夠減少網(wǎng)絡流量 ： 對于同一個針對數(shù)據(jù)庫對象的操作，所涉及到的 T-SQL 語句被組織成一存儲過程，當在客戶端調(diào)用該存儲過程時，網(wǎng)絡中傳送的只是該調(diào)用語句 ，降低網(wǎng)絡負載 。 存儲過程增加安全機制：系統(tǒng)管理員通過對執(zhí)行某一存儲過程的權(quán)限進行限制，從而能夠?qū)崿F(xiàn)對相應的數(shù)據(jù)訪問權(quán)限的限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全,不使用存儲過程時，所有的數(shù)據(jù)處理都在客戶端完成；而使用存儲過程時，可以使數(shù)據(jù)處理在服務器端完成,5.1.3 儲存過程的分類,系統(tǒng)存儲過程 SQL Server本身提供了一些存儲過程，用于管理SQL

4、Server和顯示有關(guān)數(shù)據(jù)庫和用戶的信息，我們稱之為系統(tǒng)存儲過程。系統(tǒng)存儲過程都以“sp_” 開頭，存儲在master數(shù)據(jù)庫中。 用戶存儲過程 用戶也可以編寫自己的存儲過程，并把它存放在數(shù)據(jù)庫中。這樣安排的主要目的就是要充分發(fā)揮數(shù)據(jù)庫服務器的功能，盡量減少網(wǎng)絡上的堵塞,5.1.4 存儲過程的使用方法,創(chuàng)建存儲過程 執(zhí)行存儲過程 修改存儲過程 刪除存儲過程,創(chuàng)建存儲過程,CREATE PROCedure procedure_name ; number parameter data_type = default , AS sql_statement,s procedure_name：給出存儲過程名

5、； s number：對同名的存儲過程指定一個序號； s parameter：給出參數(shù)名； s data_type：指出參數(shù)的數(shù)據(jù)類型； s = default：給出參數(shù)的默認值； s sql_statement：存儲過程所要執(zhí)行的SQL語句，它可以是一組SQL語句，可以包含流程控制語句等,例：創(chuàng)建一個最簡單的存儲過程（無參數(shù)調(diào)用,CREATE PROCedure sp_getemp;1 AS SELECT * FROM 職工,說明：創(chuàng)建存儲過程sp_getemp;1，要求查詢職工信息,例：帶參數(shù)的存儲過程,CREATE PROCedure sp_getemp;2 (salary int) A

6、S SELECT * FROM 職工 WHERE 工資 salary,說明：創(chuàng)建存儲過程sp_getemp;2，要求查詢工資值大于給定值的職工信息,說明：存儲過程一般用來完成數(shù)據(jù)查詢和數(shù)據(jù)處理操作，所以在存儲過程中不可以使用創(chuàng)建數(shù)據(jù)庫對象的語句，即在存儲過程中一般不能含有以下語句： CREATE TABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE,執(zhí)行存儲過程,EXECute = =,例：執(zhí)行帶參數(shù)的sp_getemp;2存儲過程,Execute sp_getemp;2 1240,說明：執(zhí)行存

7、儲過程sp_getemp;2，要求查詢工資值大于1240元的職工信息,存儲過程的返回值和狀態(tài)信息,無論什么時候執(zhí)行存儲過程，總要返回一個結(jié)果碼，用以指示存儲過程的執(zhí)行狀態(tài)。如果存儲過程執(zhí)行成功，返回的結(jié)果碼是0；如果存儲過程執(zhí)行失敗，返回的結(jié)果碼一般是一個負數(shù)，它和失敗的類型有關(guān)。我們在創(chuàng)建存儲過程時，也可以定義自己的狀態(tài)碼和錯誤信息,常用的存儲過程返回狀態(tài)表,0 過程成功執(zhí)行 1 對象丟失 2 發(fā)生數(shù)據(jù)類型錯誤 3 處理過程被死鎖 4 發(fā)生權(quán)限錯誤 5 發(fā)生語法錯誤 6 發(fā)生惡意用戶錯誤 7 發(fā)生資源錯誤 8 遭遇非致命的內(nèi)部錯誤 9 遭遇系統(tǒng)限制 10 發(fā)生致命的內(nèi)部不穩(wěn)定性 12 表或索

8、引被破壞 13 數(shù)據(jù)庫被破壞 14 發(fā)生硬盤錯誤 通常用全局變量ERROR 返回最后執(zhí)行的SQL 語句的錯誤代碼,CREATE PROCedure sp_getemp;3 (salary int =NULL) AS IF salary IS NULL BEGIN PRINT 必須提供一個數(shù)值作參數(shù)！ RETURN 13 END IF NOT EXISTS (SELECT * FROM 職工 WHERE 工資 salary) BEGIN PRINT 沒有滿足條件的記錄！ RETURN -103 END SELECT * FROM 職工 WHERE 工資 salary RETURN 0,例：帶參數(shù)

9、和返回狀態(tài)值的存儲過程,利用全局變量修改剛才的例子,CREATE PROCedure sp_getemp;3 (salary int =NULL) AS SELECT * FROM 職工 WHERE 工資 salary IF ERROR = 0 RETURN 0 ELSE RETURN -1,例：執(zhí)行以上存儲過程,DECLARE status int EXECUTE status=sp_getemp;3 salary 1200 print status,存儲過程的修改和刪除,修改存儲過程的語句是（一般格式）： ALTER PROCedure procedure_name ; number pa

10、rameter data_type = default , AS sql_statement 刪除存儲過程的語句是： DROP PROCedure procedure_name 注意：刪除存儲過程的語句中不能指定序號。也就是說，該語句將同時刪除同名的所有存儲過程,小結(jié)：要用好存儲過程,存儲過程是客戶/服務器機制的一個重要組成部分，如果使用客戶/服務器機制的數(shù)據(jù)庫管理系統(tǒng)，但是不理解存儲過程或沒有充分利用存儲過程，那將使客戶/服務器機制的功能大打折扣，使系統(tǒng)的整體性能可能降低很多,5.2 觸發(fā)器,5.2.1 觸發(fā)器的基本概念 5.2.2 觸發(fā)器的用途 5.2.3 觸發(fā)器與存儲過程的比較 5.2.

11、4 觸發(fā)器的使用方法,5.2.1 觸發(fā)器的基本概念,觸發(fā)器可以看作是一類特殊的存儲過程，它在滿足某個特定條件時自動觸發(fā)執(zhí)行。 觸發(fā)器是為表上的更新、插入、刪除操作定義的，也就是說當表上發(fā)生更新、插入或刪除操作時觸發(fā)器將執(zhí)行。 因此，相應的，觸發(fā)器根據(jù)觸發(fā)類型分為insert，delete，update觸發(fā)器,5.2.2 觸發(fā)器的用途,觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復雜的參照完整性和數(shù)據(jù)的一致性。 除此之外，觸發(fā)器還有以下的功能： 觸發(fā)器可以通過級聯(lián)的方式對相關(guān)的表進行修改。比如，對父表的修改，可以引起對子孫表的一系列修改，從而保證數(shù)據(jù)的一致性和完整性。 觸發(fā)器可以禁止或

12、撤消違反參照完整性的修改。 觸發(fā)器可以強制比用CHECK約束定義更加復雜的限制,5.2.3 觸發(fā)器與存儲過程的比較,聯(lián)系： 1、觸發(fā)器也是存儲過程。 2、它們都是提高數(shù)據(jù)庫服務器性能的工具。 區(qū)別： 1、執(zhí)行方法不同。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，存儲過程可以通過存儲過程名字而被直接調(diào)用。 2、建立方法不同,觸發(fā)器是依附于表的數(shù)據(jù)庫對象,CREATE TRIGGER 語句必須是批處理的第一個語句 表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限，表的所有者不能把該權(quán)限傳給其它用戶。 觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合命名規(guī)則。 盡管在觸發(fā)器的SQL 語句中可以參照其它數(shù)據(jù)庫中的對象，但是觸發(fā)器

13、只能創(chuàng)建在當前數(shù)據(jù)庫中。 雖然觸發(fā)器可以參照視圖或臨時表，但不能在視圖或臨時表上創(chuàng)建觸發(fā)器，而只能在基表或在創(chuàng)建視圖的表上創(chuàng)建觸發(fā)器。 一個觸發(fā)器只能對應一個表，這是由觸發(fā)器的機制決定的,觸發(fā)器是依附于表的數(shù)據(jù)庫對象,一個觸發(fā)器和三部分內(nèi)容有關(guān)： 定義觸發(fā)器的表 激活觸發(fā)器的數(shù)據(jù)操作語句 觸發(fā)器要采取的動作,不能在觸發(fā)器中使用的sql語句,Create database和create table 所有drop語句 數(shù)據(jù)庫修改語句alter table,alter database 對象權(quán)限語句grant和revoke,5.2.4 觸發(fā)器的使用方法,創(chuàng)建觸發(fā)器的語句 觸發(fā)器的使用原理-理解兩個視

14、圖并掌握它們的使用 插入類觸發(fā)器 刪除類觸發(fā)器 更新類觸發(fā)器 觸發(fā)器的相關(guān)操作 修改刪除觸發(fā)器,建立觸發(fā)器的語句,CREATE TRIGGER trigger_name ON table FOR INSERT | UPDATE | DELETE AS IF UPDATE(column) AND | OR UPDATE(column) sql_statement,strigger_name：給出了觸發(fā)器的名稱； stable：說明了定義觸發(fā)器的表或視圖； sFOR INSERT | UPDATE | DELETE ：說明了激活觸發(fā)器的數(shù)據(jù)操作語句； sIF UPDATE(column)：對應于U

15、PDATE類觸發(fā)器，說明如果更新某（些）列則做如何處理； ssql_statement：觸發(fā)器所要執(zhí)行的SQL語句，它可以是一組SQL語句，可以包含流程控制語句等,例：建立一個簡單的觸發(fā)器,CREATE TRIGGER wh_trigger ON 倉庫 FOR INSERT AS PRINT 插入了一個倉庫元組,說明：當在“倉庫”表中插入一行元組，則輸出“插入了一個倉庫元組,觸發(fā)器的原理 兩個特殊的視圖,每個觸發(fā)器有兩個特殊的視圖：插入視圖（inserted）和刪除視圖（deleted） 。 它們是邏輯表且是由系統(tǒng)管理的，存儲在內(nèi)存中，不允許用戶直接對其修改，結(jié)構(gòu)與原表有相同的表結(jié)構(gòu)。 當觸發(fā)

16、器工作完成，這兩個視圖也被刪除。 它們主要保存因用戶操作（存放剛插入的新記錄和存放剛刪除的舊記錄）而被影響到的原數(shù)據(jù)值或新數(shù)據(jù)值。 它們是只讀的，即用戶不能向這兩個表寫入內(nèi)容,但可以引用表中的數(shù)據(jù),觸發(fā)器的原理這兩個視圖與數(shù)據(jù)操作的關(guān)系,一旦對表執(zhí)行了插入操作，插入視圖就是用來存儲向原表插入的內(nèi)容。 一旦對表執(zhí)行了刪除操作，則將所有的刪除行存放至刪除視圖中。 更新操作包括兩個部分即先將更新的內(nèi)容去掉然后將新值插入，因此對一個定義了更新類型觸發(fā)器的表來講，在刪除視圖中存放了舊值，然后在插入視圖中存放新值,插入類觸發(fā)器,插入類觸發(fā)器就是當表上發(fā)生插入操作時所觸發(fā)執(zhí)行的程序,例：對職工表的插入操作定

17、義一個觸發(fā)器，使得當插入職工記錄時，檢查相應的倉庫元組是否存在，如果不存在則撤消所做的插入操作,CREATE TRIGGER e_ins_trigger ON 職工 FOR INSERT AS,如果 插入的職工元組的倉庫號在倉庫表中存在 ,那么插入成功，職工表增加一行元組。 如果 插入的職工元組的倉庫號在倉庫表中不存在,則插入操作不成功，給出相應的提示，并且事務回滾到插入操作之前,IF (SELECT COUNT(*) FROM 倉庫 w , inserted i WHERE w.倉庫號 = i.倉庫號) = 0 BEGIN RAISERROR (非法倉庫號！,1,1) ROLLBACK TR

18、ANSACTION END,思考：如果上題要求改為在職工表的插入操作定義一個觸發(fā)器，使得當插入職工記錄時，檢查實體完整性，如果不滿足實體完整性則撤消所做的插入操作,CREATE TRIGGER e_ins_trigger2 ON 職工 FOR INSERT AS,如果 插入的職工元組的職工號在原職工表中不存在,那么插入成功，職工表增加一行元組。 如果 插入的職工元組的職工號在原職工表中已存在,則插入操作不成功，給出相應的提示，并且事務回滾到插入操作之前,IF (SELECT COUNT(*) FROM 職工 w , inserted i WHERE w.職工號 = i.職工號) 0 BEGIN

19、 RAISERROR (重復的職工號！,1,1) ROLLBACK TRANSACTION END,刪除類觸發(fā)器,刪除類觸發(fā)器就是當表上發(fā)生刪除操作時所觸發(fā)執(zhí)行的程序,例：定義一個觸發(fā)器，使得當刪除倉庫記錄時，同時將所屬所有職工記錄的倉庫號字段值置為空值NULL,CREATE TRIGGER w_del_trigger ON 倉庫 FOR DELETE AS UPDATE 職工 SET 倉庫號=NULL WHERE 倉庫號 = (SELECT 倉庫號 FROM deleted,思考：若改為定義一個觸發(fā)器，使得當刪除倉庫記錄時，同時將所屬所有職工記錄刪除,CREATE TRIGGER w_del

20、_trigger2 ON 倉庫 FOR DELETE AS DELETE FROM 職工 FROM deleted WHERE 職工.倉庫號 = deleted.倉庫號,更新類觸發(fā)器,更新類觸發(fā)器就是當表上發(fā)生更新操作時所觸發(fā)執(zhí)行的程序,例：對職工表的更新操作定義一個觸發(fā)器，使得當職工變換所屬倉庫時，檢查相應的倉庫元組是否存在，如果不存在則撤消所做的更新操作，如果新的倉庫號是WH2則將工資提高10,定義語句（注意表名，哪一類的觸發(fā)器） 當改變職工所屬的倉庫號時（倉庫號a-倉庫號b，不確定，怎么寫語句？） 如果倉庫號b在倉庫表中不存在，事務回滾，撤銷更新職工表的倉庫號字段，維持原來的元組。 如果

21、倉庫號b在倉庫表中存在，則進行下面的工作 如果更新的倉庫號b為WH2，則給這個職工增加10%工資 如果更新的倉庫號b不是WH2，而是其它合法的倉庫號，則只是修改該職工所屬的倉庫號（即用戶的更新操作成功,WH3,1331,WH5,CREATE TRIGGER e_upd_trigger ON 職工 FOR UPDATE AS DECLARE wh_no CHAR(4) IF UPDATE(倉庫號) BEGIN IF (SELECT COUNT(*) FROM 倉庫 w , inserted i WHERE w.倉庫號 = i.倉庫號) = 0 BEGIN RAISERROR (非法倉庫號！, 1

22、6, 1) ROLLBACK TRANSACTION END ELSE BEGIN SELECT wh_no = 倉庫號 FROM inserted IF wh_no = WH2 UPDATE 職工 SET 工資 = 工資*1.10 WHERE 職工號 = (SELECT 職工號 FROM inserted) END END,定義語句,當更新操作，生成兩張視圖,如果倉庫號b在倉庫表中不存在，不符合參照完整性，事務回滾,如果倉庫號b在倉庫表中存在，且為WH2則增加工資,使用系統(tǒng)存儲過程查看觸發(fā)器,系統(tǒng)存儲過程sp_help， sp_helptext 和sp_depends 分別提供有關(guān)觸發(fā)器的不

23、同信息。 sp_help，通過該系統(tǒng)過程可以了解觸發(fā)器的一般信息如觸發(fā)器的名字屬性類型創(chuàng)建時間 使用sp_help 系統(tǒng)過程的命令格式是 sp_help 觸發(fā)器名字,sp_helptext 通過sp_helptext 能夠查看觸發(fā)器的正文信息，其語法格式為 sp_helptext 觸發(fā)器名 sp_depends 通過sp_depends 能夠查看指定觸發(fā)器所引用的表或指定的表涉及到的所有觸發(fā)器，其語法形式如下 sp_depends 觸發(fā)器名字 sp_depends 表名,修改刪除觸發(fā)器,可以修改觸發(fā)器的名字和正文 使用sp_rename 命令修改觸發(fā)器的名字，其語法格式為 sp_rename

24、oldname,newname 用Alert trigger 命令修改觸發(fā)器正文 刪除已創(chuàng)建的觸發(fā)器有兩種方法： 用系統(tǒng)命令DROP TRIGGER 刪除指定的觸發(fā)器，其語法形式如下 DROP TRIGGER 觸發(fā)器名字 刪除觸發(fā)器所在的表時， 將自動刪除與該表相關(guān)的觸發(fā)器,5.3 數(shù)據(jù)完整性,在第3章已經(jīng)介紹了在關(guān)系數(shù)據(jù)模型上數(shù)據(jù)完整性的概念和規(guī)則；在前一章介紹了CREATE TABLE語句中可以實現(xiàn)的一些完整性約束。這里介紹與數(shù)據(jù)完整性有關(guān)的其他一些內(nèi)容。,5.3.1 規(guī)則,在CREATE TABLE語句中可以使用CHECK子句實現(xiàn)一些用戶定義完整性或域完整性約束。另外還可以通過“規(guī)則”（

25、RULE）來實現(xiàn)用戶定義完整性或域完整性。 CHECK約束固定在一個表的一個列上，它只在指定的列上起作用。如果在不同的列上有相同的約束條件，則可以使用規(guī)則，一個規(guī)則可以綁定在多個列上。 規(guī)則是一種獨立的數(shù)據(jù)庫對象，它可以綁定到一個列上來約束該列的取值范圍等,規(guī)則的用法,定義規(guī)則 綁定到相應的列上,建立規(guī)則的命令是,CREATE RULE rule AS condition_expression rule：給出新建規(guī)則的名稱； condition_expression：定義規(guī)則的條件，可以是任何有效的表達式，并且可以包含諸如算術(shù)運算符、關(guān)系運算符和謂詞（如IN、LIKE、BETWEEN）之類的元

26、素。 注意： 規(guī)則不能引用列或其它數(shù)據(jù)庫對象，規(guī)則可以包含不引用數(shù)據(jù)庫對象的內(nèi)置函數(shù)； condition_expression需要包含一個變量，變量的前面有一個前綴； 該表達式引用通過 UPDATE或INSERT語句輸入或傳遞的字段值,例如，規(guī)定某類數(shù)值對象的取值范圍是10003000，則可以定義規(guī)則,CREATE RULE range_rule AS range=1000 AND range=3000,規(guī)則的綁定,規(guī)則是獨立的數(shù)據(jù)庫對象，要通過系統(tǒng)存儲過程sp_bindrule把規(guī)則綁定到數(shù)據(jù)列上，該系統(tǒng)存儲過程的格式是： sp_bindrule rulename , objname ,

27、futureonly rulename是用CREATE RULE命令建立的規(guī)則名； objname指出要綁定的表和列或用戶定義的數(shù)據(jù)類型； futureonly，當綁定規(guī)則到用戶定義的數(shù)據(jù)類型時可以選用此項，該選項是禁止已經(jīng)存在的、用用戶定義數(shù)據(jù)類型定義的列遵循新的規(guī)則,例如，將規(guī)則range_rule綁定到職工表的工資列上,sp_bindrule range_rule,職工.工資 使用CREATE RULE命令創(chuàng)建的規(guī)則對象，可以綁定到多個數(shù)據(jù)列上，即一個規(guī)則可以反復使用,綁定的消除和規(guī)則的刪除,綁定到數(shù)據(jù)列上的規(guī)則可以去除，相應的系統(tǒng)存儲過程是sp_unbindrule。例如，取消綁定在職

28、工表工資列上的規(guī)則可以使用如下語句： sp_unbindrule 職工.工資 規(guī)則可以刪除，刪除規(guī)則的命令是DROP RULE，但是刪除規(guī)則之前，必須首先解除所有的綁定,5.3.2 默認值,在CREATE TABLE命令中可以使用DEFAULT約束為數(shù)據(jù)列定義默認值。這里介紹另外一種方法：使用CREATE DEFAULT命令創(chuàng)建默認值對象,CREATE DEFAULT命令的格式,CREATE DEFAULT default AS constant_expression default：是建立的默認值對象名； constant_expression：定義默認值的常量表達式,例如，定義一個值為“北

29、京”的默認值對象val_bj,CREATE DEFAULT val_bj AS 北京,綁定默認值,默認值是獨立的數(shù)據(jù)庫對象，它要作用于某個數(shù)據(jù)對象，則也和綁定規(guī)則一樣，需要用類似的系統(tǒng)存儲過程把默認值綁定到列，綁定默認值的系統(tǒng)存儲過程是sp_bindefault，具體格式是： sp_bindefault defname , objname , futureonly defname：是用CREATE DEFAULT命令建立的默認值對象名； objname：指出要綁定的表和列或用戶定義的數(shù)據(jù)類型； futureonly，當綁定默認值到用戶定義的數(shù)據(jù)類型時可以選用此項，該選項是禁止已經(jīng)存在的、用用戶

30、定義數(shù)據(jù)類型定義的列遵循新的默認值約定,例如，將定義的默認值對象val_bj綁定到倉庫關(guān)系的城市列上和供應商關(guān)系的地址列上,sp_bindefault val_bj,倉庫.城市 sp_bindefault val_bj,供應商.地址,綁定的去除和默認值的刪除,綁定到數(shù)據(jù)列上的默認值可以去除，相應的系統(tǒng)存儲過程是sp_unbindefault。 例如，取消綁定在供應商表地址列上的默認值可以使用如下語句： sp_unbindefault 供應商.地址 默認值也可以刪除，刪除默認值的命令是DROP DEFAULT，但是刪除默認值之前，必須首先解除所有的綁定,5.3.3 用戶定義數(shù)據(jù)完整性,除了實體完整性約束和參照完整性約束