《SQLServer2005數(shù)據(jù)庫技術(shù)及應(yīng)用》課件第14章 存儲(chǔ)過程和觸發(fā)器

1、第14章 存儲(chǔ)過程和觸發(fā)器第14章 存儲(chǔ)過程和觸發(fā)器14.1 存儲(chǔ)過程的基本概念14.2 用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行14.3 用戶存儲(chǔ)過程的修改14.4 用戶存儲(chǔ)過程的刪除14.5 觸發(fā)器14.5.1 利用SQL命令創(chuàng)建觸發(fā)器14.5.2 觸發(fā)器的修改14.5.3 觸發(fā)器的刪除 14.1 存儲(chǔ)過程的基本概念存儲(chǔ)過程是一組為了完成特定功能的T-SQL語句集合，有些類似上一章提到的用戶自定義函數(shù)，所不同的是存儲(chǔ)過程的語句存儲(chǔ)在SQL Server服務(wù)器端數(shù)據(jù)庫中，而不是存儲(chǔ)在客戶計(jì)算機(jī)上，并且在使用前經(jīng)過了編譯處理設(shè)計(jì)存儲(chǔ)過程的主要目的是提高數(shù)據(jù)檢索速度，而且存儲(chǔ)過程具有傳遞參數(shù)和執(zhí)行邏輯表達(dá)式的功

2、能，使用它有助于在SQL Server中處理比較復(fù)雜的任務(wù) 存儲(chǔ)過程分類系統(tǒng)存儲(chǔ)過程 是SQL Server安裝好后系統(tǒng)自動(dòng)提供的存儲(chǔ)過程，可以作為命令執(zhí)行各種操作 用戶自定義存儲(chǔ)過程 是指用戶在數(shù)據(jù)庫中自己創(chuàng)建的存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程 在SQL Server環(huán)境之外通過對(duì)動(dòng)態(tài)鏈接庫（DLL）的調(diào)用執(zhí)行的存儲(chǔ)過程被稱為擴(kuò)展存儲(chǔ)過程 14.2 用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行創(chuàng)建存儲(chǔ)過程的語法格式：CREATE PROCEDURE owner. procedure_name parameter data_type =default OUTPUT ,n1 WITH RECOMPILE | ENCRYPTI

3、ON | RECOMPILE, ENCRYPTION AS sql_statement n2 14.2 用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行執(zhí)行存儲(chǔ)過程的語法格式： EXECUTE return_status= procedure_name | procedure_name_var parameter= value | variable OUTPUT | DEFAULT ,nWITH RECOMPILE 14.3 用戶存儲(chǔ)過程的修改語法格式：ALTER PROC EDURE procedure_name parameter data_type VARYING =default OUTPUT ,n1 WIT

4、H RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement n2 14.4 用戶存儲(chǔ)過程的刪除當(dāng)不再使用一個(gè)存儲(chǔ)過程時(shí)，就要把它從數(shù)據(jù)庫中刪除。使用DROP PROCEDURE語句可永久地刪除存儲(chǔ)過程。語法格式： DROP PROCEDURE procedure ,n 14.5 觸發(fā)器觸發(fā)器可以看作一類特殊的存儲(chǔ)過程，與一般存儲(chǔ)過程不同的就是它不是由用戶直接調(diào)用執(zhí)行的，而是事先設(shè)好觸發(fā)條件，滿足條件時(shí)觸發(fā)器就會(huì)自動(dòng)執(zhí)行。觸發(fā)器的主要作用是保護(hù)表中的數(shù)據(jù)，當(dāng)對(duì)表的操作影響到觸發(fā)器保護(hù)的數(shù)據(jù)時(shí)，觸發(fā)器自

5、動(dòng)執(zhí)行。一般情況下，對(duì)表數(shù)據(jù)的操作有：插入、修改、刪除，因而維護(hù)數(shù)據(jù)的觸發(fā)器也可分為三種類型：INSERT, UPDATE和DELETE。使用觸發(fā)器的主要目的是實(shí)現(xiàn)比較復(fù)雜的數(shù)據(jù)完整性 14.5.1 利用SQL命令創(chuàng)建觸發(fā)器 一般語法格式：CREATE TRIGGER trigger_nameON table|view WITH ENCRYPTION FOR INSERT ，DELETE，UPDATEASsql_statementn 觸發(fā)器中使用的特殊表執(zhí)行觸發(fā)器時(shí)，SQL Server統(tǒng)會(huì)自動(dòng)創(chuàng)建兩個(gè)特殊的表：inserted表和deleted表，它們是臨時(shí)的邏輯表，由系統(tǒng)維護(hù)，不允許用戶直

6、接對(duì)它們進(jìn)行修改。這兩個(gè)臨時(shí)邏輯表存放在計(jì)算機(jī)內(nèi)存中而不是數(shù)據(jù)庫里，所以觸發(fā)器工作完成后即被刪除 觸發(fā)器中使用的特殊表inserted邏輯表：當(dāng)用戶向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)自動(dòng)執(zhí)行，新記錄不僅插入到要求的實(shí)際表中，而且還同樣插入到inserted表中，也就是說實(shí)際上新數(shù)據(jù)在內(nèi)存中還有一個(gè)副本。deleted邏輯表：用于保存已從表中刪除的記錄，亦即：當(dāng)用戶從表中刪除一些記錄時(shí)，DELETE觸發(fā)器自動(dòng)執(zhí)行，要?jiǎng)h除的記錄在實(shí)際表中消失，內(nèi)存中的臨時(shí)邏輯表deleted中則添加一個(gè)和別刪除記錄相同的的記錄。如果用戶對(duì)表中數(shù)據(jù)進(jìn)行更新，系統(tǒng)的理解是：修改一條記錄等于插入一條新記錄，同時(shí)刪除舊記錄。當(dāng)對(duì)定義了UPDATE觸發(fā)器的表記錄修改時(shí)，表中原記錄移到deleted表中，修改過的記錄插入到inserted表中。deleted表和inserted表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同，注意這兩個(gè)特殊表不能被其他過程引用，這能由創(chuàng)建它們的觸發(fā)器引用。對(duì)deleted, inserted邏輯表的查詢方法與一般數(shù)據(jù)庫表的查詢方法相同 14.5.2 觸發(fā)器的修改語法格式：ALTER TRIGGER trigger_nameON table|vie