第7章 存儲(chǔ)過程和觸發(fā)器管理

1、1 存儲(chǔ)過程存儲(chǔ)過程7.1 觸發(fā)器觸發(fā)器7.2 2 n存儲(chǔ)過程概述存儲(chǔ)過程概述 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 n執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程 n存儲(chǔ)過程的參數(shù)存儲(chǔ)過程的參數(shù) n存儲(chǔ)過程的返回值存儲(chǔ)過程的返回值 n查看和修改存儲(chǔ)過程查看和修改存儲(chǔ)過程 n刪除存儲(chǔ)過程刪除存儲(chǔ)過程 n觸發(fā)器概述觸發(fā)器概述 ninserted和和deleted表表 n事務(wù)的概念及應(yīng)用事務(wù)的概念及應(yīng)用 n創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 n修改和重命名觸發(fā)器修改和重命名觸發(fā)器 n刪除觸發(fā)器刪除觸發(fā)器 3 在創(chuàng)建在創(chuàng)建SQL Server數(shù)據(jù)庫應(yīng)用程序時(shí)，數(shù)據(jù)庫應(yīng)用程序時(shí)，Transact-SQL語語 言是應(yīng)用程序和言是應(yīng)用程序和SQL

2、Server數(shù)據(jù)庫之間的主要編程接口。數(shù)據(jù)庫之間的主要編程接口。 可用如下兩種方法存儲(chǔ)和執(zhí)行可用如下兩種方法存儲(chǔ)和執(zhí)行Transact-SQL語句。語句。 (1) 將將Transact-SQL程序保存在本地，創(chuàng)建向程序保存在本地，創(chuàng)建向SQL Server 發(fā)送命令并處理結(jié)果的應(yīng)用程序。發(fā)送命令并處理結(jié)果的應(yīng)用程序。 (2) 可以將可以將Transact-SQL程序保存在程序保存在SQL Server中，即存中，即存 儲(chǔ)過程，在本地創(chuàng)建執(zhí)行存儲(chǔ)過程及處理結(jié)果的應(yīng)用程序。儲(chǔ)過程，在本地創(chuàng)建執(zhí)行存儲(chǔ)過程及處理結(jié)果的應(yīng)用程序。 任何一組任何一組Transact-SQL語句構(gòu)成的代碼塊，都可以作為語句

3、構(gòu)成的代碼塊，都可以作為 存儲(chǔ)過程保存起來。它在服務(wù)器端對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行存儲(chǔ)過程保存起來。它在服務(wù)器端對(duì)數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行 處理，并將結(jié)果返回到客戶端。這樣就避免了從客戶端多處理，并將結(jié)果返回到客戶端。這樣就避免了從客戶端多 次連接并訪問數(shù)據(jù)庫的操作，減少了網(wǎng)絡(luò)上的傳輸量，同次連接并訪問數(shù)據(jù)庫的操作，減少了網(wǎng)絡(luò)上的傳輸量，同 時(shí)也提高了客戶端的工作效率（因?yàn)檫@些操作都是在服務(wù)時(shí)也提高了客戶端的工作效率（因?yàn)檫@些操作都是在服務(wù) 器端完成的）。器端完成的）。 4 存儲(chǔ)過程是集中存儲(chǔ)在存儲(chǔ)過程是集中存儲(chǔ)在SQL Server中的中的SQL語語 句和流程控制語句的預(yù)編譯集合，用以實(shí)現(xiàn)某種句和流程控

4、制語句的預(yù)編譯集合，用以實(shí)現(xiàn)某種 任務(wù)（如查詢）。這些語句在一個(gè)名稱下存儲(chǔ)并任務(wù)（如查詢）。這些語句在一個(gè)名稱下存儲(chǔ)并 作為一個(gè)單元進(jìn)行處理。作為一個(gè)單元進(jìn)行處理。 5 n可以減少客戶端代碼的重復(fù)。只需創(chuàng)建存儲(chǔ)過程一次并可以減少客戶端代碼的重復(fù)。只需創(chuàng)建存儲(chǔ)過程一次并 將其存儲(chǔ)在數(shù)據(jù)庫中，以后即可在客戶端程序中多次調(diào)將其存儲(chǔ)在數(shù)據(jù)庫中，以后即可在客戶端程序中多次調(diào) 用該存儲(chǔ)過程。存儲(chǔ)過程可由在數(shù)據(jù)庫編程方面有專長(zhǎng)用該存儲(chǔ)過程。存儲(chǔ)過程可由在數(shù)據(jù)庫編程方面有專長(zhǎng) 的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。 n允許更快地執(zhí)行。如果某操作需要大量的允許更

5、快地執(zhí)行。如果某操作需要大量的Transact-SQL 代碼或需要重復(fù)執(zhí)行，使用存儲(chǔ)過程將比在客戶端執(zhí)行代碼或需要重復(fù)執(zhí)行，使用存儲(chǔ)過程將比在客戶端執(zhí)行 Transact-SQL批代碼的執(zhí)行速度要快。存儲(chǔ)過程在服務(wù)批代碼的執(zhí)行速度要快。存儲(chǔ)過程在服務(wù) 器端經(jīng)過預(yù)編譯，生成查詢計(jì)劃，可以直接執(zhí)行，而在器端經(jīng)過預(yù)編譯，生成查詢計(jì)劃，可以直接執(zhí)行，而在 客戶端每次運(yùn)行客戶端每次運(yùn)行Transact-SQL語句時(shí)，都要從客戶端重語句時(shí)，都要從客戶端重 復(fù)發(fā)送，并且在復(fù)發(fā)送，并且在SQL Server每次執(zhí)行這些語句時(shí)，都要每次執(zhí)行這些語句時(shí)，都要 對(duì)其進(jìn)行編譯和優(yōu)化。因此，存儲(chǔ)過程執(zhí)行速度更快。對(duì)其進(jìn)

6、行編譯和優(yōu)化。因此，存儲(chǔ)過程執(zhí)行速度更快。 6 n減少網(wǎng)絡(luò)流量。例如，一個(gè)需要數(shù)百行減少網(wǎng)絡(luò)流量。例如，一個(gè)需要數(shù)百行 Transact-SQL代碼的操作由一條執(zhí)行過程代碼代碼的操作由一條執(zhí)行過程代碼 的單獨(dú)語句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送的單獨(dú)語句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送 數(shù)百行代碼。數(shù)百行代碼。 n可以簡(jiǎn)化數(shù)據(jù)庫管理。例如，要修改某種查詢，可以簡(jiǎn)化數(shù)據(jù)庫管理。例如，要修改某種查詢， 如果查詢重復(fù)存放在客戶機(jī)上，則要在所有客如果查詢重復(fù)存放在客戶機(jī)上，則要在所有客 戶機(jī)上修改查詢，而使用存儲(chǔ)過程可以集中修戶機(jī)上修改查詢，而使用存儲(chǔ)過程可以集中修 改。改。 n可作為安全機(jī)制使用。例如，

7、即使對(duì)于沒有直可作為安全機(jī)制使用。例如，即使對(duì)于沒有直 接執(zhí)行存儲(chǔ)過程中語句的權(quán)限的用戶，也可授接執(zhí)行存儲(chǔ)過程中語句的權(quán)限的用戶，也可授 予他們執(zhí)行該存儲(chǔ)過程的權(quán)限。予他們執(zhí)行該存儲(chǔ)過程的權(quán)限。 7 n系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程: SQL Server內(nèi)置的存儲(chǔ)過程，存內(nèi)置的存儲(chǔ)過程，存 儲(chǔ)在儲(chǔ)在master庫中，主要用途是執(zhí)行庫中，主要用途是執(zhí)行SQL Server的的 某些管理功能、顯示有關(guān)數(shù)據(jù)庫和用戶的信息。某些管理功能、顯示有關(guān)數(shù)據(jù)庫和用戶的信息。 系統(tǒng)存儲(chǔ)過程名以系統(tǒng)存儲(chǔ)過程名以SP_開頭，可以在任何數(shù)據(jù)庫開頭，可以在任何數(shù)據(jù)庫 中執(zhí)行系統(tǒng)存儲(chǔ)過程。中執(zhí)行系統(tǒng)存儲(chǔ)過程。 n用戶存儲(chǔ)過程

8、：用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù)用戶存儲(chǔ)過程：用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù) 庫中的存儲(chǔ)過程。庫中的存儲(chǔ)過程。 n臨時(shí)存儲(chǔ)過程：分為局部臨時(shí)存儲(chǔ)過程和全局臨臨時(shí)存儲(chǔ)過程：分為局部臨時(shí)存儲(chǔ)過程和全局臨 時(shí)存儲(chǔ)過程。時(shí)存儲(chǔ)過程。 8 局部臨時(shí)存儲(chǔ)過程名稱以局部臨時(shí)存儲(chǔ)過程名稱以#開頭，存放在開頭，存放在 tempdb數(shù)據(jù)庫中，只由創(chuàng)建并連接的用戶使數(shù)據(jù)庫中，只由創(chuàng)建并連接的用戶使 用，當(dāng)該用戶斷開連接時(shí)將自動(dòng)刪除局部臨時(shí)用，當(dāng)該用戶斷開連接時(shí)將自動(dòng)刪除局部臨時(shí) 存儲(chǔ)過程。存儲(chǔ)過程。 全局臨時(shí)存儲(chǔ)過程名稱以全局臨時(shí)存儲(chǔ)過程名稱以#開頭，存放在開頭，存放在 tempdb數(shù)據(jù)庫中，允許所有連接的用戶使用，數(shù)

9、據(jù)庫中，允許所有連接的用戶使用， 在所有用戶斷開連接時(shí)自動(dòng)被刪除。在所有用戶斷開連接時(shí)自動(dòng)被刪除。 n遠(yuǎn)程存儲(chǔ)過程：位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程。遠(yuǎn)程存儲(chǔ)過程：位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過程。 n擴(kuò)展存儲(chǔ)過程：利用外部語言（如擴(kuò)展存儲(chǔ)過程：利用外部語言（如C）編寫的存）編寫的存 儲(chǔ)過程，以彌補(bǔ)儲(chǔ)過程，以彌補(bǔ)SQL Server的不足之處，擴(kuò)展新的不足之處，擴(kuò)展新 的功能的功能 9 n使用使用CREATE PROCEDURE語句語句 n使用企業(yè)管理器中的菜單命令使用企業(yè)管理器中的菜單命令 10 CREATE PROC EDURE 存儲(chǔ)過程名存儲(chǔ)過程名;編號(hào)編號(hào) 參數(shù)參數(shù) 數(shù)據(jù)類型數(shù)據(jù)類型 VARYIN

10、G = 默認(rèn)值默認(rèn)值 OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS SQL語句語句 .n 11 n存儲(chǔ)過程名必須符合標(biāo)識(shí)符的命名規(guī)則，且對(duì)存儲(chǔ)過程名必須符合標(biāo)識(shí)符的命名規(guī)則，且對(duì) 于數(shù)據(jù)庫及其所有者必須是惟一的。要?jiǎng)?chuàng)建局于數(shù)據(jù)庫及其所有者必須是惟一的。要?jiǎng)?chuàng)建局 部臨時(shí)存儲(chǔ)過程，可以在存儲(chǔ)過程名前面加一部臨時(shí)存儲(chǔ)過程，可以在存儲(chǔ)過程名前面加一 個(gè)個(gè)#號(hào)，要?jiǎng)?chuàng)建全局臨時(shí)過程，可以在存儲(chǔ)過號(hào)，要?jiǎng)?chuàng)建全局臨時(shí)過程，可以在存儲(chǔ)過 程名前面加兩個(gè)程名前面加兩個(gè)#號(hào)。完整的名稱（包括號(hào)。完整的名稱（包括 # 或或 #）不能超

11、過）不能超過128個(gè)字符。個(gè)字符。 n編號(hào)：可選整數(shù)，用來對(duì)同名的存儲(chǔ)過程分組，編號(hào)：可選整數(shù)，用來對(duì)同名的存儲(chǔ)過程分組， 以便用一條以便用一條DROP PROCEDURE語句即可將同語句即可將同 組的存儲(chǔ)過程一起刪除。組的存儲(chǔ)過程一起刪除。 12 n參數(shù)：過程中的參數(shù)。在參數(shù)：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個(gè)或多個(gè)參數(shù)。語句中可以聲明一個(gè)或多個(gè)參數(shù)。 用戶必須在執(zhí)行存儲(chǔ)過程時(shí)提供每個(gè)所聲明參用戶必須在執(zhí)行存儲(chǔ)過程時(shí)提供每個(gè)所聲明參 數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。參數(shù)數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。參數(shù) 名稱前需要使用名稱前需要使用符號(hào)。參數(shù)名稱必須

12、符合標(biāo)符號(hào)。參數(shù)名稱必須符合標(biāo) 識(shí)符的命名規(guī)則。每個(gè)過程的參數(shù)僅用于該過識(shí)符的命名規(guī)則。每個(gè)過程的參數(shù)僅用于該過 程本身。在其他過程中可以使用相同的參數(shù)名程本身。在其他過程中可以使用相同的參數(shù)名 稱。默認(rèn)情況下，參數(shù)只能代替常量，而不能稱。默認(rèn)情況下，參數(shù)只能代替常量，而不能 用于代替表名、列名或其他數(shù)據(jù)庫對(duì)象的名稱。用于代替表名、列名或其他數(shù)據(jù)庫對(duì)象的名稱。 13 n數(shù)據(jù)類型：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型數(shù)據(jù)類型：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型 （包括（包括text、ntext和和image）均可以用作存儲(chǔ)過）均可以用作存儲(chǔ)過 程的參數(shù)。不過，程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于數(shù)據(jù)類

13、型只能用于 OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為參數(shù)。如果指定的數(shù)據(jù)類型為cursor， 也必須同時(shí)指定也必須同時(shí)指定VARYING和和OUTPUT關(guān)鍵字。關(guān)鍵字。 nVARYING：指定作為輸出參數(shù)支持的結(jié)果集：指定作為輸出參數(shù)支持的結(jié)果集 （由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅（由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅 適用于游標(biāo)參數(shù)。適用于游標(biāo)參數(shù)。 14 n默認(rèn)值：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，默認(rèn)值：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值， 不必指定該參數(shù)的值即可執(zhí)行存儲(chǔ)過程。默認(rèn)不必指定該參數(shù)的值即可執(zhí)行存儲(chǔ)過程。默認(rèn) 值必須是常量或值必須是常量或NULL。如果要在存儲(chǔ)過程中。如果要在

14、存儲(chǔ)過程中 對(duì)該參數(shù)使用對(duì)該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可關(guān)鍵字，那么默認(rèn)值中可 以包含通配符（以包含通配符（%、_、和和）。）。 nOUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值 可以返回給可以返回給EXECUTE。使用。使用OUTPUT參數(shù)參數(shù) 可將信息返回給調(diào)用過程。可將信息返回給調(diào)用過程。text、ntext和和image 參數(shù)可用作參數(shù)可用作OUTPUT參數(shù)。使用參數(shù)。使用OUTPUT關(guān)鍵關(guān)鍵 字的輸出參數(shù)可以是游標(biāo)占位符。字的輸出參數(shù)可以是游標(biāo)占位符。 15 nRECOMPILE：表明不保存該存儲(chǔ)過程的執(zhí)行：表明不保存該存儲(chǔ)過程的執(zhí)行 計(jì)劃，該

15、存儲(chǔ)過程將在運(yùn)行時(shí)重新編譯。計(jì)劃，該存儲(chǔ)過程將在運(yùn)行時(shí)重新編譯。 nENCRYPTION：指定：指定SQL Server對(duì)對(duì) syscomments表中包含本表中包含本CREATE PROCEDURE語句文本的條目進(jìn)行加密語句文本的條目進(jìn)行加密 nAS：用于指定該存儲(chǔ)過程要執(zhí)行的操作。：用于指定該存儲(chǔ)過程要執(zhí)行的操作。 nSQL語句：存儲(chǔ)過程中要包含的語句：存儲(chǔ)過程中要包含的Transact-SQL 語句。語句。 16 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程“增加成績(jī)?cè)黾映煽?jī)”，將表，將表“學(xué)生學(xué)生”中中 所有學(xué)生的入學(xué)成績(jī)?cè)黾铀袑W(xué)生的入學(xué)成績(jī)?cè)黾?0%。 USE 學(xué)生管理學(xué)生管理 GO CREATE

16、PROCEDURE 增加成績(jī)?cè)黾映煽?jī) AS UPDATE 學(xué)生學(xué)生 SET 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) = 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) + 10 GO 17 n在創(chuàng)建一個(gè)存儲(chǔ)過程時(shí)，如果已經(jīng)存在同名的存儲(chǔ)過在創(chuàng)建一個(gè)存儲(chǔ)過程時(shí)，如果已經(jīng)存在同名的存儲(chǔ)過 程，則不允許創(chuàng)建新的存儲(chǔ)過程?？梢詫⒁陨洗a改程，則不允許創(chuàng)建新的存儲(chǔ)過程。可以將以上代碼改 寫為：寫為： USE 學(xué)生管理學(xué)生管理 -如果存在名稱為如果存在名稱為“增加成績(jī)?cè)黾映煽?jī)”的存儲(chǔ)過程，則將其刪的存儲(chǔ)過程，則將其刪 除除 IF EXISTS (SELECT name FROM sysobjects WHERE name = 增加成績(jī)?cè)黾映煽?jī) AND ty

17、pe = P) DROP PROCEDURE 增加成績(jī)?cè)黾映煽?jī) GO CREATE PROCEDURE 增加成績(jī)?cè)黾映煽?jī) AS UPDATE 學(xué)生學(xué)生 SET 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) = 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) + 10 GO 18 n在企業(yè)管理器中，展開要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)在企業(yè)管理器中，展開要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù) 庫庫,用鼠標(biāo)右擊用鼠標(biāo)右擊“存儲(chǔ)過程存儲(chǔ)過程”，在彈出菜單中單，在彈出菜單中單 擊擊“新建存儲(chǔ)過程新建存儲(chǔ)過程”命令，打開新建存儲(chǔ)過程命令，打開新建存儲(chǔ)過程 對(duì)話框，如下圖：對(duì)話框，如下圖： 19 在該對(duì)話框的文本框中有以下的默認(rèn)語句：在該對(duì)話框的文本框中有以下的默認(rèn)語句： CREATE PR

18、OCEDURE OWNER.PROCEDURE NAME AS 這是這是CREATE PROCEDURE語句的開始部分，語句的開始部分， 存儲(chǔ)過程的具體文本需要用戶自己輸入。例如，存儲(chǔ)過程的具體文本需要用戶自己輸入。例如， 將以上默認(rèn)語句修改成：將以上默認(rèn)語句修改成： CREATE PROCEDURE 增加成績(jī)?cè)黾映煽?jī) AS UPDATE 學(xué)生學(xué)生 SET 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) = 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) + 10 GO 20 EXEC UTE 返回狀態(tài)返回狀態(tài) = 存儲(chǔ)過程名存儲(chǔ)過程名 | 存儲(chǔ)過程名變量存儲(chǔ)過程名變量 參數(shù)名稱參數(shù)名稱 = 值值 | 變量變量 OUTPUT | DEFAULT ,.n

19、 WITH RECOMPILE 21 n返回狀態(tài)：是一個(gè)可選的整型變量，保存存儲(chǔ)過程返回狀態(tài)：是一個(gè)可選的整型變量，保存存儲(chǔ)過程 的返回狀態(tài)。這個(gè)變量在用于的返回狀態(tài)。這個(gè)變量在用于EXECUTE語句前，語句前， 必須在批處理、存儲(chǔ)過程或函數(shù)中聲明過。必須在批處理、存儲(chǔ)過程或函數(shù)中聲明過。 n存儲(chǔ)過程名：要調(diào)用的存儲(chǔ)過程的名稱。存儲(chǔ)過程名：要調(diào)用的存儲(chǔ)過程的名稱。 n存儲(chǔ)過程名變量：局部變量名，代表存儲(chǔ)過程的存儲(chǔ)過程名變量：局部變量名，代表存儲(chǔ)過程的 名稱。名稱。 n參數(shù)名稱：存儲(chǔ)過程的參數(shù)，在參數(shù)名稱：存儲(chǔ)過程的參數(shù)，在CREATE PROCEDURE語句中定義。參數(shù)名稱前必須加上符語句中定

20、義。參數(shù)名稱前必須加上符 號(hào)號(hào)。在使用格式。在使用格式“參數(shù)參數(shù)= 值值”時(shí)，參數(shù)名稱和時(shí)，參數(shù)名稱和 常量不一定按照常量不一定按照CREATE PROCEDURE語句中定語句中定 義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用“參參 數(shù)數(shù)= 值值”格式，則其他所有參數(shù)都必須使用這種格格式，則其他所有參數(shù)都必須使用這種格 式。式。 22 n值：過程中參數(shù)的值。如果沒有指定參數(shù)名稱，參值：過程中參數(shù)的值。如果沒有指定參數(shù)名稱，參 數(shù)值必須以數(shù)值必須以CREATE PROCEDURE語句中定義的語句中定義的 順序給出。順序給出。 如果在如果在CREATE PROCED

21、URE語句中定義了默語句中定義了默 認(rèn)值，用戶執(zhí)行該存儲(chǔ)過程時(shí)可以不必指定對(duì)應(yīng)認(rèn)值，用戶執(zhí)行該存儲(chǔ)過程時(shí)可以不必指定對(duì)應(yīng) 的參數(shù)。如果該存儲(chǔ)過程使用了帶的參數(shù)。如果該存儲(chǔ)過程使用了帶LIKE關(guān)鍵字關(guān)鍵字 的參數(shù)名稱，則默認(rèn)值必須是常量，并且可以包的參數(shù)名稱，則默認(rèn)值必須是常量，并且可以包 含含 %、_、 及及 通配符。通配符。 默認(rèn)值也可以為默認(rèn)值也可以為NULL。通常，定義存儲(chǔ)過程時(shí)。通常，定義存儲(chǔ)過程時(shí) 會(huì)指定當(dāng)參數(shù)值為會(huì)指定當(dāng)參數(shù)值為NULL時(shí)應(yīng)該執(zhí)行的操作。時(shí)應(yīng)該執(zhí)行的操作。 n變量：是用來保存參數(shù)或者返回參數(shù)的變量。變量：是用來保存參數(shù)或者返回參數(shù)的變量。 23 nOUTPUT：指定

22、存儲(chǔ)過程必須返回一個(gè)參數(shù)。該存：指定存儲(chǔ)過程必須返回一個(gè)參數(shù)。該存 儲(chǔ)過程的匹配參數(shù)也必須由關(guān)鍵字儲(chǔ)過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。創(chuàng)建。 使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。 nDEFAULT：根據(jù)存儲(chǔ)過程的定義，提供參數(shù)的默：根據(jù)存儲(chǔ)過程的定義，提供參數(shù)的默 認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好的默認(rèn)認(rèn)值。當(dāng)過程需要的參數(shù)值沒有事先定義好的默認(rèn) 值，或缺少參數(shù)，或指定了值，或缺少參數(shù)，或指定了DEFAULT關(guān)鍵字時(shí)，關(guān)鍵字時(shí)， 就會(huì)出錯(cuò)。就會(huì)出錯(cuò)。 nWITH RECOMPILE：強(qiáng)制編譯新的計(jì)劃。如果所：強(qiáng)制編譯新的計(jì)劃。如果所 提供的參數(shù)

23、為非典型參數(shù)或者數(shù)據(jù)有很大的改變，提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變， 使用該選項(xiàng)。在以后的程序執(zhí)行中使用更改過的計(jì)使用該選項(xiàng)。在以后的程序執(zhí)行中使用更改過的計(jì) 劃。該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過程。建議盡量少使劃。該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過程。建議盡量少使 用該選項(xiàng)，因?yàn)樗妮^多系統(tǒng)資源。用該選項(xiàng)，因?yàn)樗妮^多系統(tǒng)資源。 24 n執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程“增加成績(jī)?cè)黾映煽?jī)”，并查看執(zhí)行結(jié)果。，并查看執(zhí)行結(jié)果。 USE 學(xué)生管理學(xué)生管理 GO EXEC 增加成績(jī)?cè)黾映煽?jī) SELECT * FROM 學(xué)生學(xué)生 GO 執(zhí)行結(jié)果執(zhí)行結(jié)果: 25 n為了提高存儲(chǔ)過程的靈活性，為了提高存儲(chǔ)過程的靈活

24、性，SQL Server 2000支支 持在存儲(chǔ)過程中使用參數(shù)。持在存儲(chǔ)過程中使用參數(shù)。 n存儲(chǔ)過程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)兩種類存儲(chǔ)過程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)兩種類 型，輸入?yún)?shù)用于向存儲(chǔ)過程中帶入數(shù)據(jù)，而輸型，輸入?yún)?shù)用于向存儲(chǔ)過程中帶入數(shù)據(jù)，而輸 出參數(shù)則能將存儲(chǔ)過程中的數(shù)據(jù)返回到調(diào)用程序。出參數(shù)則能將存儲(chǔ)過程中的數(shù)據(jù)返回到調(diào)用程序。 n在定義存儲(chǔ)過程時(shí)在定義存儲(chǔ)過程時(shí),可以同時(shí)指定參數(shù)可以同時(shí)指定參數(shù),格式如下：格式如下： 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 =默認(rèn)值默認(rèn)值 OUTPUT, . n n如果參數(shù)后面使用如果參數(shù)后面使用OUTPUT關(guān)鍵字，則表明它是關(guān)鍵字，則表明它是

25、 輸出參數(shù)。輸出參數(shù)。 26 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程add_proc，用于計(jì)算兩個(gè)參數(shù)之和，用于計(jì)算兩個(gè)參數(shù)之和 并將其輸出。并將其輸出。 CREATE PROCEDURE add_proc num1 INT = 0, num2 INT = 0 AS DECLARE num3 INT SET num3 = num1 + num2 PRINT num3 n該存儲(chǔ)過程定義了兩個(gè)參數(shù)該存儲(chǔ)過程定義了兩個(gè)參數(shù)num1和和num2， 它們都是輸入?yún)?shù)它們都是輸入?yún)?shù),參數(shù)類型為參數(shù)類型為INT,默認(rèn)值為默認(rèn)值為0。 27 n不帶參數(shù)執(zhí)行：不帶參數(shù)執(zhí)行： EXEC add_proc 運(yùn)行結(jié)果為：運(yùn)行結(jié)

26、果為： 0 n帶參數(shù)執(zhí)行：帶參數(shù)執(zhí)行： EXEC add_proc 13, 25 運(yùn)行結(jié)果為：運(yùn)行結(jié)果為： 38 有時(shí)需要將存儲(chǔ)過程中的計(jì)算結(jié)果返回到調(diào)用程有時(shí)需要將存儲(chǔ)過程中的計(jì)算結(jié)果返回到調(diào)用程 序中，以便進(jìn)行進(jìn)一步的處理，此時(shí)就需要在存儲(chǔ)序中，以便進(jìn)行進(jìn)一步的處理，此時(shí)就需要在存儲(chǔ) 過程中使用輸出參數(shù)。過程中使用輸出參數(shù)。 28 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程add_proc1，用于計(jì)算兩個(gè)參數(shù)之，用于計(jì)算兩個(gè)參數(shù)之 和，并使用輸出參數(shù)返回結(jié)果。和，并使用輸出參數(shù)返回結(jié)果。 CREATE PROCEDURE add_proc1 num1 INT = 0, num2 INT = 0, num3

27、 INT OUTPUT -num3為輸出參數(shù)為輸出參數(shù) AS SET num3 = num1 + num2 29 DECLARE num AS INT EXEC add_proc1 12, 23, num OUTPUT PRINT num 運(yùn)行結(jié)果為：運(yùn)行結(jié)果為： 38 30 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程AvgScore，用于根據(jù)給定的院系，用于根據(jù)給定的院系 和班級(jí)名稱計(jì)算平均成績(jī)，并使用輸出參數(shù)返和班級(jí)名稱計(jì)算平均成績(jī)，并使用輸出參數(shù)返 回結(jié)果?；亟Y(jié)果。 CREATE PROCEDURE AvgScore org varchar(100), -院系名稱，輸入?yún)?shù)院系名稱，輸入?yún)?shù) class

28、 varchar(50), -班級(jí)名稱，輸入?yún)?shù)班級(jí)名稱，輸入?yún)?shù) score float OUTPUT - 成績(jī)，輸出參數(shù)成績(jī)，輸出參數(shù) AS DECLARE orgid int SET orgid = 0 31 - 根據(jù)參數(shù)中指定的院系名稱根據(jù)參數(shù)中指定的院系名稱org, 獲取院系編號(hào)獲取院系編號(hào) SELECT orgid = 記錄編號(hào)記錄編號(hào) FROM 院系院系 WHERE 院系名稱院系名稱=org IF orgid = 0 BEGIN SET score = 0 PRINT 指定的院系記錄不存在指定的院系記錄不存在 END ELSE BEGIN SELECT score = AVG(入

29、學(xué)成績(jī)?nèi)雽W(xué)成績(jī)) FROM 學(xué)生學(xué)生 WHERE 所屬院系所屬院系=orgid AND 班級(jí)班級(jí)=class GROUP BY 所屬院系所屬院系, 班級(jí)班級(jí) END 32 DECLARE score float EXEC AvgScore 軟件系軟件系, 二班二班, score OUTPUT PRINT score 運(yùn)行結(jié)果為：運(yùn)行結(jié)果為： 584 33 可以在存儲(chǔ)過程中使用可以在存儲(chǔ)過程中使用RETURN語句返回一個(gè)語句返回一個(gè) 狀態(tài)值，返回值只能是整數(shù)。狀態(tài)值，返回值只能是整數(shù)。 34 n創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程AvgScore1，根據(jù)給定的院系和班，根據(jù)給定的院系和班 級(jí)名稱計(jì)算平均成績(jī)

30、，并將結(jié)果使用輸出參數(shù)返級(jí)名稱計(jì)算平均成績(jī)，并將結(jié)果使用輸出參數(shù)返 回。如果指定的院系存在，則返回回。如果指定的院系存在，則返回1，否則返回，否則返回0。 CREATE PROCEDURE AvgScore1 org varchar(100), class varchar(50), score float OUTPUT AS DECLARE orgid int SET orgid = 0 35 - 根據(jù)參數(shù)中指定的院系名稱根據(jù)參數(shù)中指定的院系名稱org, 獲取院系編號(hào)獲取院系編號(hào) SELECT orgid = 記錄編號(hào)記錄編號(hào) FROM 院系院系 WHERE 院系名稱院系名稱=org IF o

31、rgid = 0 RETURN 0 ELSE BEGIN SELECT score = AVG(入學(xué)成績(jī)?nèi)雽W(xué)成績(jī)) FROM 學(xué)生學(xué)生 WHERE 所屬院系所屬院系=orgid AND 班級(jí)班級(jí)=class GROUP BY 所屬院系所屬院系, 班級(jí)班級(jí) RETURN 1 END GO 36 DECLARE score float DECLARE result int EXEC result = AvgScore1 軟件系軟件系, 二班二班, score OUTPUT - 檢查返回值檢查返回值 IF result = 1 PRINT score ELSE PRINT 沒有對(duì)應(yīng)的記錄沒有對(duì)應(yīng)的記

32、錄 37 n使用企業(yè)管理器查看和修改存儲(chǔ)過程使用企業(yè)管理器查看和修改存儲(chǔ)過程 n使用查詢分析器查看和修改存儲(chǔ)過程使用查詢分析器查看和修改存儲(chǔ)過程 n使用企業(yè)管理器重命名存儲(chǔ)過程使用企業(yè)管理器重命名存儲(chǔ)過程 n使用使用ALTER PROCEDURE語句修改存儲(chǔ)過程語句修改存儲(chǔ)過程 n使用使用sp_rename重命名存儲(chǔ)過程重命名存儲(chǔ)過程 38 n在企業(yè)管理器中展開指定的數(shù)據(jù)庫，選中在企業(yè)管理器中展開指定的數(shù)據(jù)庫，選中“存儲(chǔ)存儲(chǔ) 過程過程”項(xiàng)，可以查看指定數(shù)據(jù)庫中定義的所有存項(xiàng)，可以查看指定數(shù)據(jù)庫中定義的所有存 儲(chǔ)過程。儲(chǔ)過程。 39 n雙擊指定的存儲(chǔ)過程，可以打開存儲(chǔ)過程屬性對(duì)雙擊指定的存儲(chǔ)過程

33、，可以打開存儲(chǔ)過程屬性對(duì) 話框，查看存儲(chǔ)過程的定義情況。話框，查看存儲(chǔ)過程的定義情況。 用戶可以在存儲(chǔ)過用戶可以在存儲(chǔ)過 程屬性對(duì)話框中修程屬性對(duì)話框中修 改存儲(chǔ)過程的代碼。改存儲(chǔ)過程的代碼。 40 n右鍵單擊要編輯的存儲(chǔ)過程，在彈出菜單中選擇右鍵單擊要編輯的存儲(chǔ)過程，在彈出菜單中選擇 “編輯編輯”，可以在右鍵的窗格中顯示指定存儲(chǔ)過，可以在右鍵的窗格中顯示指定存儲(chǔ)過 程的代碼。程的代碼。 41 n在企業(yè)管理器，展開在企業(yè)管理器，展開“數(shù)據(jù)庫數(shù)據(jù)庫”文件夾，選擇存文件夾，選擇存 儲(chǔ)過程所在的數(shù)據(jù)庫，單擊儲(chǔ)過程所在的數(shù)據(jù)庫，單擊“存儲(chǔ)過程存儲(chǔ)過程”文件夾。文件夾。 在右側(cè)的窗口中，列出了選擇數(shù)據(jù)庫

34、的所有存儲(chǔ)在右側(cè)的窗口中，列出了選擇數(shù)據(jù)庫的所有存儲(chǔ) 過程。過程。 n右擊要重命名的存儲(chǔ)過程，選擇右擊要重命名的存儲(chǔ)過程，選擇“重命名重命名”菜單菜單 項(xiàng)，就可以在當(dāng)前位置上修改存儲(chǔ)過程的名字。項(xiàng)，就可以在當(dāng)前位置上修改存儲(chǔ)過程的名字。 42 ALTER PROC EDURE 存儲(chǔ)過程名存儲(chǔ)過程名;編號(hào)編號(hào) 參數(shù)名參數(shù)名 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING = 默認(rèn)值默認(rèn)值 OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION AS SQL語句語句 .n 各參數(shù)含義與各參數(shù)含義與CREATE PROCEDURE語句相同語句相同

35、 43 n使用使用ALTER PROCEDURE語句修改存儲(chǔ)過程語句修改存儲(chǔ)過程 “增加成績(jī)?cè)黾映煽?jī)”，對(duì)其進(jìn)行加密處理。，對(duì)其進(jìn)行加密處理。 USE 學(xué)生管理學(xué)生管理 GO ALTER PROCEDURE 增加成績(jī)?cè)黾映煽?jī) WITH ENCRYPTION AS UPDATE 學(xué)生學(xué)生 SET 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) = 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī) + 10 指定以上語句后，在企業(yè)管理器中查看指定以上語句后，在企業(yè)管理器中查看“增加成增加成 績(jī)績(jī)”存儲(chǔ)過程，將彈出一個(gè)對(duì)話框，提示用戶存存儲(chǔ)過程，將彈出一個(gè)對(duì)話框，提示用戶存 儲(chǔ)過程已經(jīng)加密，不能夠查看。儲(chǔ)過程已經(jīng)加密，不能夠查看。 44 n系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)

36、過程sp_rename的功能是更改當(dāng)前數(shù)據(jù)的功能是更改當(dāng)前數(shù)據(jù) 庫中用戶創(chuàng)建對(duì)象（如表、列或用戶定義數(shù)據(jù)類庫中用戶創(chuàng)建對(duì)象（如表、列或用戶定義數(shù)據(jù)類 型）的名稱。使用型）的名稱。使用sp_rename重命名存儲(chǔ)過程的重命名存儲(chǔ)過程的 語法結(jié)構(gòu)如下：語法結(jié)構(gòu)如下： sp_rename objname = 對(duì)象名對(duì)象名, newname = 新對(duì)象名新對(duì)象名 參數(shù)說明：參數(shù)說明： objname = 對(duì)象名對(duì)象名：指定存儲(chǔ)過程的當(dāng)前：指定存儲(chǔ)過程的當(dāng)前 名稱。名稱。 newname = 新對(duì)象名新對(duì)象名：指定存儲(chǔ)過程的：指定存儲(chǔ)過程的 新名稱。新名稱。 45 n將存儲(chǔ)過程將存儲(chǔ)過程add_proc

37、重命名為重命名為add_proc2，則可，則可 以使用以下命令：以使用以下命令： EXEC sp_rename add_proc, add_proc2 運(yùn)行結(jié)果為：運(yùn)行結(jié)果為： 注意注意: 更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過程。 object 已重命名為已重命名為 add_proc2。 46 n使用企業(yè)管理器使用企業(yè)管理器 n使用使用DROP PROCEDURE 語句語句 47 在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過程，選在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過程，選 擇擇“刪除刪除”命令，并確認(rèn)刪除。命令，并確認(rèn)刪除。 48 DROP PROCED

38、URE 存儲(chǔ)過程名存儲(chǔ)過程名 ,.n 49 n刪除存儲(chǔ)過程刪除存儲(chǔ)過程add_proc，可以使用以下命令：，可以使用以下命令： DROP PROCEDURE add_proc 50 n設(shè)某設(shè)某“職工職工”數(shù)據(jù)庫中有一個(gè)數(shù)據(jù)庫中有一個(gè)“職工工資職工工資”表，該表包含表，該表包含 有有“基本工資基本工資”、“獎(jiǎng)金獎(jiǎng)金”和和“實(shí)發(fā)工資實(shí)發(fā)工資”等列。創(chuàng)建一等列。創(chuàng)建一 組存儲(chǔ)過程，求組存儲(chǔ)過程，求“職工工資職工工資”表的平均基本工資、平均獎(jiǎng)表的平均基本工資、平均獎(jiǎng) 金和平均實(shí)發(fā)工資。金和平均實(shí)發(fā)工資。 USE 職工職工 GO CREATE PROC AveSalary;1 AS SELECT AVG

39、(基本工資基本工資) FROM 職工工資職工工資 GO CREATE PROC AveSalary;2 AS SELECT AVG(獎(jiǎng)金獎(jiǎng)金) FROM 職工工資職工工資 GO CREATE PROC AveSalary;3 AS SELECT AVG(實(shí)發(fā)工資實(shí)發(fā)工資) FROM 職工工資職工工資 GO 51 使用以下語句執(zhí)行以上存儲(chǔ)過程。使用以下語句執(zhí)行以上存儲(chǔ)過程。 EXEC AveSalary;1 EXEC AveSalary;2 EXEC AveSalary;3 要?jiǎng)h除以上創(chuàng)建的三個(gè)存儲(chǔ)過程，可以使用語句：要?jiǎng)h除以上創(chuàng)建的三個(gè)存儲(chǔ)過程，可以使用語句： DROP PROCEDURE A

40、veSalary 注意，不能寫成：注意，不能寫成： DROP PROCEDURE AveSalary;1 DROP PROCEDURE AveSalary;2 DROP PROCEDURE AveSalary;3 52 n觸發(fā)器概述觸發(fā)器概述 ninserted和和deleted表表 n事務(wù)的概念及應(yīng)用事務(wù)的概念及應(yīng)用 n創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器 n修改和重命名觸發(fā)器修改和重命名觸發(fā)器 n刪除觸發(fā)器刪除觸發(fā)器 53 n觸發(fā)器與普通存儲(chǔ)過程的區(qū)別：觸發(fā)器的執(zhí)行是觸發(fā)器與普通存儲(chǔ)過程的區(qū)別：觸發(fā)器的執(zhí)行是 由事件觸發(fā)的，而普通存儲(chǔ)過程是由命令調(diào)用執(zhí)由事件觸發(fā)的，而普通存儲(chǔ)過程是由命令調(diào)用執(zhí) 行的。行的

41、。 n使用觸發(fā)器有助于強(qiáng)制保持?jǐn)?shù)據(jù)庫的數(shù)據(jù)完整性。使用觸發(fā)器有助于強(qiáng)制保持?jǐn)?shù)據(jù)庫的數(shù)據(jù)完整性。 例如，在觸發(fā)器中可以完成如下功能：例如，在觸發(fā)器中可以完成如下功能： 不允許刪除或更新特定的記錄。不允許刪除或更新特定的記錄。 不允許插入不符合邏輯關(guān)系的記錄。不允許插入不符合邏輯關(guān)系的記錄。 在刪除一條記錄的同時(shí)刪除其他表中與其相關(guān)在刪除一條記錄的同時(shí)刪除其他表中與其相關(guān) 的記錄。的記錄。 在修改一條記錄的同時(shí)修改其他表中與其相關(guān)在修改一條記錄的同時(shí)修改其他表中與其相關(guān) 的記錄。的記錄。 54 nINSTEAD OF觸發(fā)器：觸發(fā)器：INSTEAD OF觸發(fā)器在指觸發(fā)器在指 定的操作（定的操作（IN

42、SERT、UPDATE或或DELETE語句）語句） 之前被執(zhí)行，它的功能是不執(zhí)行指定的操作，而之前被執(zhí)行，它的功能是不執(zhí)行指定的操作，而 是執(zhí)行是執(zhí)行INSTEAD OF觸發(fā)器中定義的操作?？梢杂|發(fā)器中定義的操作?？梢?在表和視圖上定義在表和視圖上定義INSTEAD OF觸發(fā)器。觸發(fā)器。 nAFTER觸發(fā)器。在執(zhí)行了觸發(fā)器。在執(zhí)行了INSERT、UPDATE或或 DELETE語句操作之后執(zhí)行語句操作之后執(zhí)行AFTER觸發(fā)器。觸發(fā)器。 AFTER觸發(fā)器只能在表上指定。觸發(fā)器只能在表上指定。 55 n觸發(fā)器是自動(dòng)執(zhí)行的，不需要管理員手動(dòng)維護(hù)數(shù)觸發(fā)器是自動(dòng)執(zhí)行的，不需要管理員手動(dòng)維護(hù)數(shù) 據(jù)庫的數(shù)據(jù)完

43、整性。據(jù)庫的數(shù)據(jù)完整性。 n觸發(fā)器可以對(duì)數(shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。觸發(fā)器可以對(duì)數(shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。 例如，可以在表例如，可以在表“院系院系”中定義觸發(fā)器，當(dāng)用戶中定義觸發(fā)器，當(dāng)用戶 刪除表刪除表“院系院系”中的記錄時(shí)，觸發(fā)器將刪除表中的記錄時(shí)，觸發(fā)器將刪除表 “學(xué)生學(xué)生”中對(duì)應(yīng)院系的記錄。中對(duì)應(yīng)院系的記錄。 n觸發(fā)器可以限制向表中插入無效的數(shù)據(jù)，這一點(diǎn)觸發(fā)器可以限制向表中插入無效的數(shù)據(jù)，這一點(diǎn) 與與CHECK約束的功能相似。但在約束的功能相似。但在CHECK約束中約束中 不能使用到其他表中的字段，而在觸發(fā)器中則沒不能使用到其他表中的字段，而在觸發(fā)器中則沒 有此限制。例如，可以在表

44、有此限制。例如，可以在表“學(xué)生學(xué)生”中定義觸發(fā)中定義觸發(fā) 器，限制插入的記錄其器，限制插入的記錄其“院系編號(hào)院系編號(hào)”字段值必須字段值必須 在表在表“院系院系”中存在對(duì)應(yīng)的記錄。中存在對(duì)應(yīng)的記錄。 56 ndeleted表用于存儲(chǔ)表用于存儲(chǔ)DELETE和和UPDATE語句所語句所 影響的行的復(fù)本。在執(zhí)行影響的行的復(fù)本。在執(zhí)行DELETE或或UPDATE 語句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)秸Z句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)?deleted表中。表中。deleted表和觸發(fā)器表通常沒有相表和觸發(fā)器表通常沒有相 同的行。同的行。 ninserted表用于存儲(chǔ)表用于存儲(chǔ)INSERT和和UPDATE

45、語句所語句所 影響的行的副本。在一個(gè)插入或更新事務(wù)處理影響的行的副本。在一個(gè)插入或更新事務(wù)處理 中，新建行被同時(shí)添加到中，新建行被同時(shí)添加到inserted表和觸發(fā)器表表和觸發(fā)器表 中。中。inserted表中的行是觸發(fā)器表中新行的副本。表中的行是觸發(fā)器表中新行的副本。 57 n在設(shè)置觸發(fā)器條件時(shí)，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操在設(shè)置觸發(fā)器條件時(shí)，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操 作恰當(dāng)使用作恰當(dāng)使用inserted和和deleted表。表。 n通常在插入數(shù)據(jù)時(shí)，可以從通常在插入數(shù)據(jù)時(shí)，可以從insert表中讀取新插表中讀取新插 入的值，此時(shí)入的值，此時(shí)delete表不會(huì)發(fā)生變化。表不會(huì)發(fā)生變化。 n在刪除數(shù)據(jù)時(shí)，

46、可以從在刪除數(shù)據(jù)時(shí)，可以從delete表中讀取已經(jīng)刪除表中讀取已經(jīng)刪除 或修改的值，而或修改的值，而insert表不會(huì)發(fā)生變化。表不會(huì)發(fā)生變化。 n在更新數(shù)據(jù)時(shí)，在更新數(shù)據(jù)時(shí)，insert表和表和delete表都發(fā)生變化。表都發(fā)生變化。 可以從可以從delete表中讀取原有的值，從表中讀取原有的值，從insert表中表中 讀取修改后的值。讀取修改后的值。 58 在觸發(fā)器中經(jīng)常會(huì)取消用戶先前進(jìn)行的操作，在觸發(fā)器中經(jīng)常會(huì)取消用戶先前進(jìn)行的操作， 例如不允許插入不符合條件的數(shù)據(jù)。例如不允許插入不符合條件的數(shù)據(jù)。SQL Server 提供了一種叫做事務(wù)的機(jī)制，它可以保證指定的提供了一種叫做事務(wù)的機(jī)制，

47、它可以保證指定的 對(duì)數(shù)據(jù)庫的一系列操作作為一個(gè)整體被執(zhí)行，在對(duì)數(shù)據(jù)庫的一系列操作作為一個(gè)整體被執(zhí)行，在 最終提交操作之間，用戶可以隨時(shí)取消前面的操最終提交操作之間，用戶可以隨時(shí)取消前面的操 作，將數(shù)據(jù)庫還原到?jīng)]有執(zhí)行操作前的狀態(tài)。作，將數(shù)據(jù)庫還原到?jīng)]有執(zhí)行操作前的狀態(tài)。 59 n原子性原子性: 事務(wù)必須是原子工作單元。它對(duì)數(shù)據(jù)庫所進(jìn)行事務(wù)必須是原子工作單元。它對(duì)數(shù)據(jù)庫所進(jìn)行 的操作，要么全都執(zhí)行，要么全都不執(zhí)行。的操作，要么全都執(zhí)行，要么全都不執(zhí)行。 n一致性一致性: 事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致 狀態(tài)。在相關(guān)數(shù)據(jù)庫中，所有規(guī)則都必須應(yīng)用于事

48、務(wù)的狀態(tài)。在相關(guān)數(shù)據(jù)庫中，所有規(guī)則都必須應(yīng)用于事務(wù)的 修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時(shí)，所有的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時(shí)，所有的 內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。 n隔離性隔離性: 由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事 務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)， 要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事 務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)

49、。 n持久性持久性: 事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。 該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。 60 n用戶在網(wǎng)上商場(chǎng)選擇商品，然后向商家提交購用戶在網(wǎng)上商場(chǎng)選擇商品，然后向商家提交購 物請(qǐng)求，并進(jìn)行網(wǎng)上支付。此時(shí)，交易狀態(tài)為物請(qǐng)求，并進(jìn)行網(wǎng)上支付。此時(shí)，交易狀態(tài)為 提交。提交。 n商家獲得用戶提交的購物請(qǐng)求，在確認(rèn)收到網(wǎng)商家獲得用戶提交的購物請(qǐng)求，在確認(rèn)收到網(wǎng) 上支付的金額后向用戶郵寄商品。此時(shí)，交易上支付的金額后向用戶郵寄商品。此時(shí)，交易 狀態(tài)為已處理。狀態(tài)為已處理。 n用戶在收到商品并確認(rèn)商品無質(zhì)量后，

50、在網(wǎng)上用戶在收到商品并確認(rèn)商品無質(zhì)量后，在網(wǎng)上 商場(chǎng)確認(rèn)已收到商品。此時(shí)，交易狀態(tài)為成功。商場(chǎng)確認(rèn)已收到商品。此時(shí)，交易狀態(tài)為成功。 定義一個(gè)事務(wù)需要定義一個(gè)事務(wù)需要3種操作，即啟動(dòng)事務(wù)、回滾事務(wù)和提交事務(wù)。種操作，即啟動(dòng)事務(wù)、回滾事務(wù)和提交事務(wù)。 啟動(dòng)事務(wù)相當(dāng)于用戶提交購物請(qǐng)求之前的狀態(tài)，回滾事務(wù)相當(dāng)于用啟動(dòng)事務(wù)相當(dāng)于用戶提交購物請(qǐng)求之前的狀態(tài)，回滾事務(wù)相當(dāng)于用 戶取消當(dāng)前交易，提交事務(wù)相當(dāng)于用戶確認(rèn)交易成功。戶取消當(dāng)前交易，提交事務(wù)相當(dāng)于用戶確認(rèn)交易成功。 61 SQL Server中包括中包括2種啟動(dòng)事務(wù)的模式，即顯式種啟動(dòng)事務(wù)的模式，即顯式 事務(wù)和隱式事務(wù)。事務(wù)和隱式事務(wù)。 n顯式事務(wù)

51、：通過顯式事務(wù)：通過BEGIN TRANSACTION語句顯語句顯 式啟動(dòng)事務(wù)。式啟動(dòng)事務(wù)。BEGIN TRANSACTION語句的基語句的基 本語法如下：本語法如下： BEGIN TRANSACTION 事務(wù)名事務(wù)名 在顯式事務(wù)中，事務(wù)名是可選項(xiàng)。事務(wù)名必須符在顯式事務(wù)中，事務(wù)名是可選項(xiàng)。事務(wù)名必須符 合標(biāo)識(shí)符命名規(guī)則。合標(biāo)識(shí)符命名規(guī)則。 n隱式事務(wù)：當(dāng)用戶沒有顯式地定義事務(wù)時(shí)，隱式事務(wù)：當(dāng)用戶沒有顯式地定義事務(wù)時(shí)，SQL Server按其默認(rèn)的規(guī)定自動(dòng)劃分事務(wù)。按其默認(rèn)的規(guī)定自動(dòng)劃分事務(wù)。 62 n如果服務(wù)器錯(cuò)誤使事務(wù)無法成功完成，如果服務(wù)器錯(cuò)誤使事務(wù)無法成功完成，SQL Server將自

52、動(dòng)回滾該事務(wù)，并釋放該事務(wù)占用的將自動(dòng)回滾該事務(wù)，并釋放該事務(wù)占用的 所有資源。如果客戶端與所有資源。如果客戶端與SQL Server的網(wǎng)絡(luò)連的網(wǎng)絡(luò)連 接中斷了，那么當(dāng)網(wǎng)絡(luò)告知接中斷了，那么當(dāng)網(wǎng)絡(luò)告知SQL Server該中斷該中斷 時(shí)，將回滾該連接的所有未完成事務(wù)。時(shí)，將回滾該連接的所有未完成事務(wù)。 n如果用戶需要手動(dòng)回滾事務(wù)，可以使用語句：如果用戶需要手動(dòng)回滾事務(wù)，可以使用語句： ROLLBACK TRANSACTION 事務(wù)名事務(wù)名 其中，其中，“事務(wù)名事務(wù)名”是給是給 BEGIN TRANSACTION 上的事務(wù)指派的名稱。上的事務(wù)指派的名稱。 63 nCOMMIT TRANSACTI

53、ON語句可以標(biāo)志一個(gè)語句可以標(biāo)志一個(gè) 成功的隱性事務(wù)或顯式事務(wù)的結(jié)束，它的基本成功的隱性事務(wù)或顯式事務(wù)的結(jié)束，它的基本 語法如下：語法如下： COMMIT TRANSACTION 事務(wù)名事務(wù)名 n注意，不能在發(fā)出注意，不能在發(fā)出COMMIT TRANSACTION 語句之后回滾事務(wù)，因?yàn)閿?shù)據(jù)修改已經(jīng)成為數(shù)語句之后回滾事務(wù)，因?yàn)閿?shù)據(jù)修改已經(jīng)成為數(shù) 據(jù)庫的永久部分。據(jù)庫的永久部分。 64 n定義一個(gè)事務(wù)，向定義一個(gè)事務(wù)，向“學(xué)生學(xué)生”表中插入兩條記錄。表中插入兩條記錄。 其中，第其中，第1條條INSERT語句是正確的，而第語句是正確的，而第2條條 INSERT語句是錯(cuò)誤的。執(zhí)行此事務(wù)語句后，查語句

54、是錯(cuò)誤的。執(zhí)行此事務(wù)語句后，查 看看“學(xué)生學(xué)生”表中的數(shù)據(jù)，確認(rèn)第表中的數(shù)據(jù)，確認(rèn)第1條語句沒有被條語句沒有被 執(zhí)行。執(zhí)行。 USE 學(xué)生管理學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生學(xué)生 VALUES(test1, 1, 一班一班, 500, 2) INSERT INTO 學(xué)生學(xué)生 VALUES(10, test2, 1, 二班二班, 500, 2) COMMIT TRANSACTION GO 65 n第第2條插入語句因?yàn)橹付藰?biāo)識(shí)列的值，所以產(chǎn)條插入語句因?yàn)橹付藰?biāo)識(shí)列的值，所以產(chǎn) 生錯(cuò)誤。執(zhí)行此事務(wù)語句的結(jié)果如下：生錯(cuò)誤。執(zhí)行此事務(wù)語句的結(jié)果如下：

55、服務(wù)器服務(wù)器: 消息消息 8101，級(jí)別，級(jí)別 16，狀態(tài)，狀態(tài) 1，行，行 1 n在查詢分析器中執(zhí)行如下語句：在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生學(xué)生 可以看到，因?yàn)榈诳梢钥吹?，因?yàn)榈?條條INSERT語句出現(xiàn)錯(cuò)誤，導(dǎo)語句出現(xiàn)錯(cuò)誤，導(dǎo) 致事務(wù)回滾，所以第致事務(wù)回滾，所以第1條語句所插入的記錄也沒條語句所插入的記錄也沒 有出現(xiàn)在結(jié)果集中。有出現(xiàn)在結(jié)果集中。 66 n定義一個(gè)事務(wù)，向定義一個(gè)事務(wù)，向“學(xué)生學(xué)生”表中插入一條記錄，表中插入一條記錄， 然后將事務(wù)回滾。執(zhí)行此事務(wù)語句后，查看然后將事務(wù)回滾。執(zhí)行此事務(wù)語句后，查看“學(xué)學(xué) 生生”表中的數(shù)據(jù)，確認(rèn)表中的數(shù)據(jù)，確認(rèn)I

56、NSERT語句插入的數(shù)據(jù)語句插入的數(shù)據(jù) 不在結(jié)果集中。不在結(jié)果集中。 USE 學(xué)生管理學(xué)生管理 GO BEGIN TRANSACTION INSERT INTO 學(xué)生學(xué)生 VALUES(test1, 1, 一班一班, 500, 2) ROLLBACK TRANSACTION GO 67 n執(zhí)行此事務(wù)語句的結(jié)果如下：執(zhí)行此事務(wù)語句的結(jié)果如下： （所影響的行數(shù)為（所影響的行數(shù)為 1 行）行） 證明證明INSERT語句已經(jīng)被執(zhí)行。語句已經(jīng)被執(zhí)行。 n在查詢分析器中執(zhí)行如下語句：在查詢分析器中執(zhí)行如下語句： SELECT * FROM 學(xué)生學(xué)生 可以看到，因?yàn)閳?zhí)行了可以看到，因?yàn)閳?zhí)行了ROLLBACK

57、 TRANSACTION語句，導(dǎo)致事務(wù)回滾，所以語句，導(dǎo)致事務(wù)回滾，所以 INSERT語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。 68 n在企業(yè)管理器中手動(dòng)地創(chuàng)建在企業(yè)管理器中手動(dòng)地創(chuàng)建 n使用使用CREATE TRIGGER語句創(chuàng)建語句創(chuàng)建 69 nCREATE TRIGGER語句必須是批處理中的第一語句必須是批處理中的第一 條語句。條語句。SQL Server將該批處理中隨后的其他所將該批處理中隨后的其他所 有語句解釋為有語句解釋為CREATE TRIGGER語句定義的一語句定義的一 部分。部分。 n創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不創(chuàng)建觸發(fā)器的權(quán)

58、限默認(rèn)分配給表的所有者，且不 能將該權(quán)限轉(zhuǎn)移給其他用戶。能將該權(quán)限轉(zhuǎn)移給其他用戶。 n 觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的 命名規(guī)則。命名規(guī)則。 n雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對(duì)象，但雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對(duì)象，但 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器。 70 n雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是 觸發(fā)器可以引用臨時(shí)表，不應(yīng)引用系統(tǒng)表。觸發(fā)器可以引用臨時(shí)表，不應(yīng)引用系統(tǒng)表。 n如果已經(jīng)給一個(gè)表的外鍵定義了級(jí)聯(lián)刪除或級(jí)聯(lián)如果已經(jīng)給一個(gè)表的外鍵定義了級(jí)聯(lián)刪除或

59、級(jí)聯(lián) 更新，則不能在該表上定義更新，則不能在該表上定義INSTEAD OF DELETE或或INSTEAD OF DELETE UPDATE 觸發(fā)觸發(fā) 器。器。 n雖然雖然TRUNCATE TABLE語句（刪除表中的所有語句（刪除表中的所有 行）類似于沒有行）類似于沒有WHERE子句的子句的DELETE語句，但語句，但 它并不會(huì)引發(fā)它并不會(huì)引發(fā)DELETE觸發(fā)器。觸發(fā)器。 nWRITETEXT語句（更新語句（更新text、ntext或或image類型類型 的列）不會(huì)引發(fā)的列）不會(huì)引發(fā)INSERT或或UPDATE觸發(fā)器。觸發(fā)器。 71 CREATE TRIGGER 觸發(fā)器名觸發(fā)器名 ON 表名表

60、名 | 視圖名視圖名 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS IF UPDATE ( 列列 ) AND | OR UPDATE ( 列列 ) .n SQL語句語句 .n 72 n觸發(fā)器名：必須符合標(biāo)識(shí)符的命名規(guī)則，并且在數(shù)據(jù)庫中觸發(fā)器名：必須符合標(biāo)識(shí)符的命名規(guī)則，并且在數(shù)據(jù)庫中 必須唯一?？梢赃x擇是否指定觸發(fā)器所有者的名稱。必須唯一?？梢赃x擇是否指定觸發(fā)器所有者的名稱。 n 表名表名 | 視圖名：是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱視圖名：是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱 為觸發(fā)器表或觸發(fā)器視