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

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

本章以本書的“圖書銷售系統(tǒng)”為例，主要介紹了如何在SQLServer2000數(shù)據(jù)庫中建立、修改存儲過程和觸發(fā)器。

10．1存儲過程

10.1.1存儲過程簡介存儲過程是一組預先編譯好的SQL代碼。它是SQLSever上的一個非常有用的特性，是一個獨立的數(shù)據(jù)庫對象，可以通過調(diào)用命令（EXECUTE）執(zhí)行存儲過程，而不必發(fā)送大量Transact-SQL代碼，因此大大減少了網(wǎng)絡流量，或用戶的應用程序調(diào)用。存儲過程可用于向用戶返回數(shù)據(jù)、向表中插入新數(shù)據(jù)、修改、執(zhí)行系統(tǒng)和管理任務等。

存儲過程有如下優(yōu)點：（1）行速度快存儲過程在創(chuàng)建時經(jīng)過語法檢查和性能優(yōu)化，在第一次被調(diào)用后就駐留在內(nèi)存中，下次執(zhí)行不再經(jīng)過編譯。當某操作需大量使用或重復執(zhí)行，存儲過程將比Transact-SQL代碼要快。（2）模塊化的程序設計存儲過程在創(chuàng)建后，即存儲在數(shù)據(jù)庫中，可被應用程序無數(shù)次調(diào)用。用戶可按不同功能的程序模塊，設計不同的存儲過程以供使用。（3）減少網(wǎng)絡流量存儲過程是一組Transact-SQL語句，但其是作為一個獨立的單元來使用，在調(diào)用時，只需一個執(zhí)行語句即可實現(xiàn)。（4）安全性可以授權無直接執(zhí)行存儲過程語句權限的用戶執(zhí)行該存儲過程。（5）可以將復雜的任務劃分為多個簡單的商業(yè)邏輯（6）存儲過程對某些需要周期性處理的任務特別有用。（7）存儲過程在客戶端和服務器端提供了良好的并發(fā)性。在下例情況下，將使用到存儲過程。（1）不需要任何客戶端信息而在服務器端完成的操作（2）涉及到大量行的操作、運算（3）頻繁的、周期性的操作（4）那些被多個模塊共同執(zhí)行的操作。10.1.2創(chuàng)建存儲過程語法：

CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]

參數(shù)解釋：

procedure_name

新存儲過程的名稱。過程名必須符合標識符規(guī)則，且對于數(shù)據(jù)庫及其所有者必須唯一。

要創(chuàng)建局部臨時過程，可以在procedure_name前面加一個編號符(#procedure_name)，要創(chuàng)建全局臨時過程，可以在procedure_name前面加兩個編號符(##procedure_name)。完整的名稱（包括#或##）不能超過128個字符。指定過程所有者的名稱是可選的。

;number

是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將除去整個組。如果名稱中包含定界標識符，則數(shù)字不應包含在標識符中，只應在procedure_name前后使用適當?shù)亩ń绶parameter

過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個所聲明參數(shù)的值（除非定義了該參數(shù)的默認值）。存儲過程最多可以有2100個參數(shù)。使用@符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合標識符的規(guī)則。每個過程的參數(shù)僅用于該過程本身；相同的參數(shù)名稱可以用在其它過程中。默認情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其它數(shù)據(jù)庫對象的名稱?！咀⒁狻空{(diào)用存儲過程時，必須給出參數(shù)值，參數(shù)可為空格，但不能為空（NULL）

data_type

參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲過程的參數(shù)。不過，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時指定VARYING和OUTPUT關鍵字。

【說明】對于可以是cursor數(shù)據(jù)類型的輸出參數(shù)，沒有最大數(shù)目的限制。

VARYING

指定作為輸出參數(shù)支持的結果集（由存儲過程動態(tài)構造，內(nèi)容可以變化）。僅適用于游標參數(shù)。

default

參數(shù)的默認值。如果定義了默認值，不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或NULL。如果過程將對該參數(shù)使用LIKE關鍵字，那么默認值中可以包含通配符（%、_、[]和[^]）。

OUTPUT

表明參數(shù)是返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。Text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關鍵字的輸出參數(shù)可以是游標占位符。

n表示最多可以指定2100個參數(shù)的占位符。

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}RECOMPILE表明SQLServer不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內(nèi)存中的執(zhí)行計劃時，請使用RECOMPILE選項。

ENCRYPTION表示SQLServer加密syscomments表中包含

CREATEPROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQLServer復制的一部分發(fā)布。

FORREPLICATION

指定不能在訂閱服務器上執(zhí)行為復制創(chuàng)建的存儲過程。.使用FORREPLICATION選項創(chuàng)建的存儲過程可用作存儲過程篩選，且只能在復制過程中執(zhí)行。本選項不能和WITHRECOMPILE選項一起使用。

AS指定過程要執(zhí)行的操作。

sql_statement

過程中要包含的任意數(shù)目和類型的Transact-SQL語句。但有一些限制。

n是表示此過程可以包含多條Transact-SQL語句的占位符。注釋存儲過程的最大值為128MB。用戶定義的存儲過程只能在當前數(shù)據(jù)庫中創(chuàng)建（臨時過程除外，臨時過程總是在tempdb中創(chuàng)建）。在單個批處理中，CREATEPROCEDURE語句不能與其它Transact-SQL語句組合使用。默認情況下，參數(shù)可為空。如果傳遞NULL參數(shù)值并且該參數(shù)在CREATE或ALTERTABLE語句中使用，而該語句中引用的列又不允許使用NULL，則SQLServer會產(chǎn)生一條錯誤信息。為了防止向不允許使用NULL的列傳遞NULL參數(shù)值，應向過程中添加編程邏輯或為該列使用默認值（使用CREATE或ALTERTABLE的DEFAULT關鍵字）。1．下面我們以本書的SQLServer數(shù)據(jù)庫“圖書銷售系統(tǒng)”為例，分別建立一個名為“cunchu”、“cunchu2”的存儲過程?！纠?0.1】本例創(chuàng)建一個名稱為“cunchu”的存儲過程，該存儲過程的作用是根據(jù)參數(shù)值，返回表BookRecord的相關記錄。執(zhí)行的操作是查詢記錄。步驟：（1）打開SQL企業(yè)管理器，連接服務器，展開數(shù)據(jù)庫列表，選擇“圖書銷售系統(tǒng)”數(shù)據(jù)庫。（2）右擊“圖書銷售系統(tǒng)”數(shù)據(jù)庫，選擇“新建”命令中的“存儲過程”選項，如圖10.1。

圖10.1新建存儲過程（3）在彈出的“存儲過程屬性—新建存儲過程”對話框中，輸入以下語句：

CREATEPROCEDUREcunchu@findBookNochar(30),@findBookNamechar(200)ASselectBookNO,BookName

fromBookRecord

whereBookNO=@findBookNOorBookName=@findBookName

該語句新建了一個名為cunchu的存儲過程，參數(shù)findBookNo對應字段BookNO，參數(shù)findBookName對應字段BookName。圖10.2存儲過程屬性（4）檢查語法。生成一個存儲過程前，應該執(zhí)行檢查語法操作。方法是點擊“檢查語法”按鈕。我們點擊該按鈕后，系統(tǒng)將會提示，如成功，則顯示“語法檢查成功”，否則將顯示其錯誤。語法無誤，點擊“確定”后，系統(tǒng)將生成該存儲過程?！纠?0.2】本例創(chuàng)建一個名為“cunchu2”的存儲過程，該存儲過程定義了四個參數(shù)，對應表Operator的四個字段。執(zhí)行的操作是向Operator表添加新記錄。步驟：步驟（1）、步驟（2）與例10.2的步驟（1）、步驟（2）相同，在此省略。（3）在彈出的“存儲過程屬性—新建存儲過程”對話框中，輸入以下語句：

CREATEPROCEDUREcunchu2@Accountchar(10),@Namechar(20),@Passwordchar(16),@IsdeletedBITASinsertOperatorvalues(@account,@name,@password,@isdeleted)

（4）檢查語法。點擊“檢查語法”按鈕后，如成功，則顯示“語法檢查成功”，否則將顯示其錯誤。語法無誤，點擊“確定”后，系統(tǒng)將生成該存儲過程。

2．執(zhí)行存儲過程：要執(zhí)行存儲過程，需在SQL查詢分析器中使用關鍵字“execute”，該關鍵字后面是存儲過程名，及相關參數(shù)。下面我們介紹如何執(zhí)行剛才新建的兩個存儲過程?！纠?0.3】本例介紹執(zhí)行存儲過程“cunchu”的步驟。步驟：（1）打開SQL企業(yè)管理器，連接服務器，選擇“圖書銷售系統(tǒng)”。（2）選擇“工具”菜單，在彈出的菜單中選擇“SQL查詢分析器”，彈出如圖10.3，輸入executecunchu"9787302000001",""，在工具欄中點擊“執(zhí)行查詢”按鈕，即可見執(zhí)行結果，如圖10.4：

圖10.3執(zhí)行存儲過程圖10.4執(zhí)行結果【例10.4】本例介紹執(zhí)行存儲過程“cunchu2”的步驟，并查看其結果。步驟：（1）打開SQL企業(yè)管理器，連接服務器，選擇“圖書銷售系統(tǒng)”。（2）點擊“工具”菜單，選擇“SQL查詢分析器”，在查詢分析器的文本區(qū)域內(nèi)輸入如下語句。

executecunchu2yb,楊斌,yb,0（3）在工具欄中點擊“執(zhí)行查詢”按鈕，第二個文本框出現(xiàn)提示“所影響的行數(shù)為一行”，表示執(zhí)行存儲過程成功。如圖10.5。我們可以打開表Operator，查看其結果。如圖10.6所示，表中新添加了一條記錄。

圖10.5執(zhí)行存儲過程成功圖10.6查看結果

3．修改存儲過程：修改存儲過程的方法很簡單，選擇要修改的存儲過程，雙擊即彈出“存儲過程屬性”對話框，在文本框中直接修改語句，檢查語法通過，點擊確定后即生效。

4．刪除存儲過程：刪除存儲過程最直接的方法是選擇相應數(shù)據(jù)庫，在該數(shù)據(jù)庫的存儲過程列表中選擇要刪除的存儲過程，右擊鼠標，彈出快捷菜單，選擇“刪除“即可。如圖10.7：圖10.7刪除存儲過程10．2觸發(fā)器

10.2.1觸發(fā)器簡介觸發(fā)器是一種特殊的存儲過程，其作用是當表中數(shù)據(jù)被UPDATE、INSERT、DELETE語句修改，或直接在表中數(shù)據(jù)被直接更改后，觸發(fā)器就會生效，自動執(zhí)行觸發(fā)器中的語句。對每個SQL語句，觸發(fā)器執(zhí)行一次，在SQL語句執(zhí)行完成后它立即啟動。如遇到錯誤，觸發(fā)器和激活它的SQL語句自動取消。使用觸發(fā)器的優(yōu)點：

1．觸發(fā)器是自動執(zhí)行的，它在對表的數(shù)據(jù)作了任何修改之后立即被激活。

2．觸發(fā)器可以通過數(shù)據(jù)庫中的相關表進行層疊和更改。

3．觸發(fā)器可以強制執(zhí)行。

4．觸發(fā)器可以引用其他表中的列。

使用觸發(fā)器的限制：

1．一個表中最多可以有三個觸發(fā)器。一個是“INSERT”觸發(fā)器，一個是“UPDATE”觸發(fā)器，還有一個是“DELETE”觸發(fā)器。

2．一個觸發(fā)器只能運行一個表，但是可以運行三個用戶動作，包括INSERT、UPDATE和DELETE。在下列情況下，將使用到觸發(fā)器。

1．beforeinsert/update觸發(fā)器可以檢查操作是否完成以及數(shù)據(jù)的一致性，或者為那些需要唯一編號的列提供數(shù)值

2．beforedelete觸發(fā)器可以在刪除時檢查主要明細表之間的關系，也可以終止刪除。

3．所有的before觸發(fā)器均可以通過激發(fā)異常終止操作回退事務

4．所有的after觸發(fā)器均可以在更改完成后做一些審計或更改其他相關表的操作。10.2.2創(chuàng)建觸發(fā)器語法

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{

{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}

[WITHAPPEND]

[NOTFORREPLICATION]

AS

[{IFUPDATE(column)

[{AND|OR}UPDATE(column)]

[...n]

|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask)

{comparison_operator}column_bitmask[...n]

}]

sql_statement[...n]

}

參數(shù)

trigger_name

觸發(fā)器的名稱。觸發(fā)器名稱必須符合標識符規(guī)則，并且在數(shù)據(jù)庫中必須唯一?？梢赃x擇是否指定觸發(fā)器所有者名稱。

Table|view

是在其上執(zhí)行觸發(fā)器的表或視圖，有時稱為觸發(fā)器表或觸發(fā)器視圖?？梢赃x擇是否指定表或視圖的所有者名稱。

WITHENCRYPTION

加密syscomments表中包含CREATETRIGGER語句文本的條目。使用WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復制的一部分發(fā)布。

AFTER

指定觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。

如果僅指定FOR關鍵字，則AFTER是默認設置。不能在視圖上定義AFTER觸發(fā)器。

INSTEADOF

指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而替代觸發(fā)語句的操作。在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEADOF觸發(fā)器。然而，可以在每個具有INSTEADOF觸發(fā)器的視圖上定義視圖。

INSTEADOF觸發(fā)器不能在WITHCHECKOPTION的可更新視圖上定義。如果向指定了WITHCHECKOPTION選項的可更新視圖添加INSTEADOF觸發(fā)器，SQLServer將產(chǎn)生一個錯誤。用戶必須用ALTERVIEW刪除該選項后才能定義INSTEADOF觸發(fā)器。

{[DELETE][,][INSERT][,][UPDATE]}

是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許使用以任意順序組合的這些關鍵字。如果指定的選項多于一個，需用逗號分隔這些選項。

對于INSTEADOF觸發(fā)器，不允許在具有ONDELETE級聯(lián)操作引用關系的表上使用DELETE選項。同樣，也不允許在具有ONUPDATE級聯(lián)操作引用關系的表上使用UPDATE選項。

WITHAPPEND

指定應該添加現(xiàn)有類型的其它觸發(fā)器。

WITHAPPEND不能與INSTEADOF觸發(fā)器一起使用，或者，如果顯式聲明AFTER觸發(fā)器，也不能使用該子句。只有當出于向后兼容而指定FOR時（沒有INSTEADOF或AFTER），才能使用WITHAPPEND。

NOTFORREPLICATION

表示當復制進程更改觸發(fā)器所涉及的表時，不應執(zhí)行該觸發(fā)器。

AS是觸發(fā)器要執(zhí)行的操作。

Sql_statement

是觸發(fā)器的條件和操作。觸發(fā)器條件指定其它準則，以確定DELETE、INSERT或UPDATE語句是否導致執(zhí)行觸發(fā)器操作。

當嘗試DELETE、INSERT或UPDATE操作時，Transact-SQL語句中指定的觸發(fā)器操作將生效。觸發(fā)器可以包含任意數(shù)量和種類的Transact-SQL語句。觸發(fā)器旨在根據(jù)數(shù)據(jù)修改語句檢查或更改數(shù)據(jù)；它不應將數(shù)據(jù)返回給用戶。觸發(fā)器中的Transact-SQL語句常常包含控制流語言。CREATETRIGGER語句中使用幾個特殊的表：

·

deleted和inserted是邏輯（概念）表。這些表在結構上類似于定義觸發(fā)器的表（也就是在其中嘗試用戶操作的表）；這些表用于保存用戶操作可能更改的行的舊值或新值。例如，若要檢索deleted表中的所有值，請使用：

·

SELECT*

·

FROMdeleted

n

是表示觸發(fā)器中可以包含多條Transact-SQL語句的占位符。對于IFUPDATE(column)語句，可以通過重復UPDATE(column)子句包含多列。

IFUPDATE(column)

測試在指定的列上進行的INSERT或UPDATE操作，不能用于DELETE操作?？梢灾付ǘ嗔小Ｒ驗樵贠N子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要測試在多個列上進行的INSERT或UPDATE操作，請在第一個操作后指定單獨的UPDATE(column)子句。在INSERT操作中IFUPDATE將返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值?！菊f明】IFUPDATE(column)子句的功能等同于IF、IF...ELSE或WHILE語句，并且可以使用BEGIN...END語句塊?？梢栽谟|發(fā)器主體中的任意位置使用UPDATE(column)。

column

是要測試INSERT或UPDATE操作的列名。該列可以是SQLServer支持的任何數(shù)據(jù)類型。但是，計算列不能用于該環(huán)境中。IF(COLUMNS_UPDATED())

測試是否插入或更新了提及的列，僅用于INSERT或UPDATE觸發(fā)器中。COLUMNS_UPDATED返回varbinary位模式，表示插入或更新了表中的哪些列。

COLUMNS_UPDATED函數(shù)以從左到右的順序返回位，最左邊的為最不重要的位。最左邊的位表示表中的第一列；向右的下一位表示第二列，依此類推。如果在表上創(chuàng)建的觸發(fā)器包含8列以上，則COLUMNS_UPDATED返回多個字節(jié)，最左邊的為最不重要的字節(jié)。在INSERT操作中COLUMNS_UPDATED將對所有列返回TRUE值，因為這些列插入了顯式值或隱性(NULL)值?？梢栽谟|發(fā)器主體中的任意位置使用COLUMNS_UPDATED。

bitwise_operator

是用于比較運算的位運算符。updated_bitmask

是整型位掩碼，表示實際更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE觸發(fā)器，若要檢查列C2、C3和C4是否都有更新，指定值14；若要檢查是否只有列C2有更新，指定值2。

comparison_operator

是比較運算符。使用等號(=)檢查updated_bitmask中指定的所有列是否都實際進行了更新。使用大于號(>)檢查updated_bitmask中指定的任一列或某些列是否已更新。

column_bitmask

是要檢查的列的整型位掩碼，用來檢查是否已更新或插入了這些列。我們以“圖書銷售系統(tǒng)”數(shù)據(jù)庫中的表InRecord為例創(chuàng)建三個觸發(fā)器：

1．INSERT觸發(fā)器【例10.5】本例創(chuàng)建了一個名為OrdDet_Insert的觸發(fā)器，該觸發(fā)器的作用是當有新書入庫的時候，則更新庫存中該書的庫存量。步驟：（1）打開SQL企業(yè)管理器，連接服務器，雙擊“數(shù)據(jù)庫”，展開數(shù)據(jù)庫列表，選擇“圖書銷售系統(tǒng)”數(shù)據(jù)庫。（2）展開表，右擊“InRecord”，選擇“所有任務”下的子菜單“管理觸發(fā)器”，如圖10.8：圖10.8創(chuàng)建觸發(fā)器(3)在“觸發(fā)器屬性”對話框的文本區(qū)域輸入以下語句：

CREATETRIGGEROrdDet_Insert

ONInRecord

FORINSERTAS

UPDATEPSETQuantity=P.Quantity+I.Quantity

FROMBookRecordASPINNERJOINInRecordASIONP.BookNO=I.BookNO

（4）單擊“檢查語法”按鈕，無誤后單擊“確定”命令。2．UPDATE觸發(fā)器【例10.6】本觸發(fā)器的名稱為OrdDet_Update，當SalesDetail表發(fā)生更新的時候，更新BookRecord表的相關記錄。步驟：（1）打開SQLServer企業(yè)管理器，連接服務管理器，展開數(shù)據(jù)庫列表，選擇“圖書銷售系統(tǒng)”數(shù)據(jù)庫。（2）展開表，右擊“SalesDetail”表，選擇“所有任務”下子菜單的“管理觸發(fā)器”。（3）在“觸發(fā)器屬性”對話框的文本區(qū)