1、大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計1第7章 存儲過程和觸發(fā)器n存儲過程存儲過程n觸發(fā)器觸發(fā)器大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計27.1 7.1 存儲過程存儲過程 存儲過程是一種數(shù)據(jù)庫對象，將執(zhí)行計劃存儲在數(shù)據(jù)庫的服務器中。存儲過程是一種數(shù)據(jù)庫對象，將執(zhí)行計劃存儲在數(shù)據(jù)庫的服務器中。它的運行速度比獨立運行同樣的程序要快。它的運行速度比獨立運行同樣的程序要快。存儲過程類型存儲過程類型創(chuàng)建存儲過程創(chuàng)建存儲過程執(zhí)行存儲過程執(zhí)行存儲過程修改存儲過程修改存儲過程刪除存儲過程刪除存儲過

2、程大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計37.1.1 7.1.1 存儲過程類型存儲過程類型 系統(tǒng)存儲過程系統(tǒng)存儲過程 存儲在存儲在mastermaster數(shù)據(jù)庫中，以數(shù)據(jù)庫中，以sp_sp_為前綴。可以在其他為前綴。可以在其他 數(shù)據(jù)庫中對其進行調(diào)用。數(shù)據(jù)庫中對其進行調(diào)用。 用戶自定義存儲過程用戶自定義存儲過程 由用戶創(chuàng)建并能完成某一特定功能的存儲過程。或由用戶創(chuàng)建并能完成某一特定功能的存儲過程。或 稱本地存儲過程。包括臨時存儲過程、遠程存儲過稱本地存儲過程。包括臨時存儲過程、遠程存儲過 程、擴展存儲過程。程、擴展存儲過程。 臨時存儲過程

3、又包括局部的和全局的臨時存儲過臨時存儲過程又包括局部的和全局的臨時存儲過 程，前者在過程名的前面帶程，前者在過程名的前面帶# #，后者在過程名的前面，后者在過程名的前面 帶帶#。全局臨時存儲過程對所有用戶都可見。全局臨時存儲過程對所有用戶都可見。 擴展存儲過程的前綴是擴展存儲過程的前綴是xp_ xp_ 。 大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計47.1.2 7.1.2 創(chuàng)建存儲過程創(chuàng)建存儲過程用企業(yè)管理器創(chuàng)建用企業(yè)管理器創(chuàng)建用用T-SQLT-SQL命令創(chuàng)建命令創(chuàng)建 存儲過程的三個組成部分：存儲過程的三個組成部分：（1 1）所有的輸入?yún)?shù)

4、以及傳給調(diào)用者的輸出參數(shù)；）所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)；（2 2）被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他）被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他 存儲過程的語句；存儲過程的語句；（3 3）返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是）返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是 失敗。失敗。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計57.1.2 7.1.2 創(chuàng)建存儲過程創(chuàng)建存儲過程T-SQLT-SQL創(chuàng)建存儲過程的基本語法格式：創(chuàng)建存儲過程的基本語法格式：CREATE PROCEDURE CREATE PROCEDURE 存

5、儲過程名稱存儲過程名稱 參數(shù)定義參數(shù)定義AS AS SQLSQL語句語句例例7.17.1：創(chuàng)建存儲過程，實現(xiàn)查詢所有學生信息的功能：創(chuàng)建存儲過程，實現(xiàn)查詢所有學生信息的功能。Create proc proc_7_1Create proc proc_7_1AsAsSelect Select * * From xsqk From xsqk思考：創(chuàng)建存儲過程，實現(xiàn)查詢所有學生的學號、姓名、所選課程號、課思考：創(chuàng)建存儲過程，實現(xiàn)查詢所有學生的學號、姓名、所選課程號、課程名、成績及學分信息的功能。程名、成績及學分信息的功能。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000

6、程序設計程序設計67.1.2 7.1.2 創(chuàng)建存儲過程創(chuàng)建存儲過程例例7.27.2：創(chuàng)建存儲過程：創(chuàng)建存儲過程proc_7_2proc_7_2，要求實現(xiàn)根據(jù)學生學號，產(chǎn)生不同結果，要求實現(xiàn)根據(jù)學生學號，產(chǎn)生不同結果，如果該學生信息不存在，則顯示如果該學生信息不存在，則顯示“無此學號的學生！無此學號的學生！”，否則返回該學生的，否則返回該學生的基本信息基本信息。Create proc proc_7_2 sno char(8)Create proc proc_7_2 sno char(8)AsAsIf exists(Select If exists(Select * * From xsqk whe

7、re From xsqk where 學號學號= = sno)sno) select select * * From xsqk where From xsqk where 學號學號= = snosnoElse Else print print 無此學號的學生！無此學號的學生！思考：創(chuàng)建存儲過程思考：創(chuàng)建存儲過程testproc2testproc2，實現(xiàn)根據(jù)學生的學號，查詢此學生的學號、實現(xiàn)根據(jù)學生的學號，查詢此學生的學號、姓名、所選課程號、課程名、成績及學分等信息。姓名、所選課程號、課程名、成績及學分等信息。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設

8、計程序設計77.1.2 7.1.2 創(chuàng)建存儲過程創(chuàng)建存儲過程說明：說明：（1 1）在一個批處理中，）在一個批處理中，Create procedureCreate procedure語句不能與其語句不能與其 他他SQLSQL語句合并在一起。語句合并在一起。（2 2）數(shù)據(jù)庫所有者具有默認的創(chuàng)建存儲過程的權限。）數(shù)據(jù)庫所有者具有默認的創(chuàng)建存儲過程的權限。（3 3）存儲過程作為數(shù)據(jù)庫對象其命名必須符合命名規(guī)）存儲過程作為數(shù)據(jù)庫對象其命名必須符合命名規(guī) 則。則。（4 4）只能在當前數(shù)據(jù)庫中創(chuàng)建屬于當前數(shù)據(jù)庫的存儲過）只能在當前數(shù)據(jù)庫中創(chuàng)建屬于當前數(shù)據(jù)庫的存儲過 程。程。（5 5）一個存儲過程的最大尺寸為

9、）一個存儲過程的最大尺寸為128128M M。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計87.1.3 7.1.3 執(zhí)行存儲過程執(zhí)行存儲過程語句格式：語句格式： EXECUTE EXECUTE 存儲過程名稱存儲過程名稱 參數(shù)值參數(shù)值例例7.37.3：執(zhí)行例：執(zhí)行例7.17.1的存儲過程。的存儲過程。 exec proc_7_1 exec proc_7_1例例7.47.4：執(zhí)行例：執(zhí)行例7.27.2的存儲過程，查詢學號為的存儲過程，查詢學號為“”“”學生的基本信息。學生的基本信息。 exec proc_7_2 exec proc_7_2 或或

10、exec proc_7_2 sno= exec proc_7_2 sno= 大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計97.1.3 7.1.3 舉例舉例例例7.57.5：在學生成績庫中創(chuàng)建存儲過程：在學生成績庫中創(chuàng)建存儲過程proc_7_t1proc_7_t1，要求實現(xiàn)如下功能：產(chǎn)生要求實現(xiàn)如下功能：產(chǎn)生計算機計算機02030203班學生的選課情況列表，其中包括學號、姓名、性別、課程號、班學生的選課情況列表，其中包括學號、姓名、性別、課程號、課程名稱、學分等。并調(diào)用此存儲過程，顯示執(zhí)行結果。課程名稱、學分等。并調(diào)用此存儲過程，顯示執(zhí)行結果。

11、create proc proc_7_t1asSelect xsqk.學號學號, 姓名姓名, 性別性別, xskc.課程號課程號, 課程名課程名, xskc.學分學分 From xsqk, xscj, xskc Where xsqk.學號學號 = xscj.學號學號 and xscj.課程號課程號 = xskc.課程號課程號 and 班級班級 = 計算機計算機0203大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計10例例7.67.6：在學生成績庫中創(chuàng)建存儲過程：在學生成績庫中創(chuàng)建存儲過程proc_7_t2proc_7_t2，要求實現(xiàn)如下功能：根

12、要求實現(xiàn)如下功能：根據(jù)學生學號，如果此學生存在，則產(chǎn)生該生的課程成績列表，其中包括學據(jù)學生學號，如果此學生存在，則產(chǎn)生該生的課程成績列表，其中包括學號、課程號、課程名稱、成績、學分等；如果此學生不存在，則顯示號、課程號、課程名稱、成績、學分等；如果此學生不存在，則顯示“無無此學生！此學生！”。并調(diào)用此存儲過程，顯示。并調(diào)用此存儲過程，顯示“”“”學生的課程成績情況。學生的課程成績情況。 create proc proc_7_t2 sno char(8)create proc proc_7_t2 sno char(8) as as if exists(select if exists(selec

13、t * * from xsqk where from xsqk where 學號學號=sno) sno) Select Select 學號學號, ,xscj.xscj.課程號課程號, ,課程名課程名, ,成績成績, ,xskc.xskc.學分學分 From xscj, xskc From xscj, xskc Where xscj. Where xscj.課程號課程號= =xskc.xskc.課程號課程號 and and 學號學號= = snosno else else print print 無此學生！無此學生！大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000

14、程序設計程序設計117.1.4 7.1.4 修改存儲過程修改存儲過程語法格式：語法格式：ALTER PROCEDURE ALTER PROCEDURE 存儲過程名稱存儲過程名稱 參數(shù)定義參數(shù)定義AS AS SQLSQL語句語句例例7.77.7：修改在例：修改在例7.57.5中已創(chuàng)建的存儲過程中已創(chuàng)建的存儲過程proc_7_t1proc_7_t1，要求在顯示列中加要求在顯示列中加入成績列。入成績列。alter proc proc_7_t1alter proc proc_7_t1asas Select xsqk. Select xsqk.學號學號, ,姓名姓名, ,性別性別, ,xskc.xskc

15、.課程號課程號, , 課程名課程名, , xskc.xskc.學分學分, , 成績成績 From xsqk, xscj, xskc From xsqk, xscj, xskc Where xsqk. Where xsqk.學號學號 = = xscj.xscj.學號學號 and xscj. and xscj.課程號課程號 = = xskc.xskc.課程號課程號 and and 班級班級 = = 計算機計算機02030203大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計127.1.5 7.1.5 刪除存儲過程刪除存儲過程語法格式：語法格式：DRO

16、P PROCEDURE 存儲過程名稱存儲過程名稱例例7.8：刪除存儲過程：刪除存儲過程proc_7_1。 drop proc proc_7_1 一般地，在用一般地，在用T-SQLT-SQL命令創(chuàng)建存儲過程時，總是先確定要創(chuàng)建的存儲過程命令創(chuàng)建存儲過程時，總是先確定要創(chuàng)建的存儲過程是否已經(jīng)存在，如果存在，那么就刪除重建。我們可以用如下語句實現(xiàn)：是否已經(jīng)存在，如果存在，那么就刪除重建。我們可以用如下語句實現(xiàn)： If exists( select name from sysobjects where name = proc_7_1 and type = P) drop proc proc_7_1大型

17、數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計137.2 7.2 觸發(fā)器觸發(fā)器 觸發(fā)器是一種特殊類型的存儲過程，當表中數(shù)據(jù)被修改時，觸發(fā)器是一種特殊類型的存儲過程，當表中數(shù)據(jù)被修改時，SQL ServerSQL Server自動執(zhí)行觸發(fā)器中定義的自動執(zhí)行觸發(fā)器中定義的T-SQLT-SQL語句。使用觸發(fā)器可語句。使用觸發(fā)器可以實施更為復雜的數(shù)據(jù)完整性約束。以實施更為復雜的數(shù)據(jù)完整性約束。觸發(fā)器概述觸發(fā)器概述創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器修改觸發(fā)器修改觸發(fā)器刪除觸發(fā)器刪除觸發(fā)器大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設

18、計程序設計147.2.1 7.2.1 觸發(fā)器概述觸發(fā)器概述 觸發(fā)器的概念及作用觸發(fā)器的概念及作用 觸發(fā)器是一種特殊類型的存儲過程，主要觸發(fā)器是一種特殊類型的存儲過程，主要通過事件進行觸發(fā)執(zhí)行的通過事件進行觸發(fā)執(zhí)行的, ,是維護數(shù)據(jù)庫完整是維護數(shù)據(jù)庫完整性最強大的工具，能在任何情況下維護數(shù)據(jù)庫性最強大的工具，能在任何情況下維護數(shù)據(jù)庫的完整性。的完整性。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計157.2.1 7.2.1 觸發(fā)器概述觸發(fā)器概述觸發(fā)器可以完成的任務觸發(fā)器可以完成的任務l數(shù)據(jù)庫中相關表的級聯(lián)操作數(shù)據(jù)庫中相關表的級聯(lián)操作l不接受或回滾

19、違反參照完整性的數(shù)據(jù)修改事務不接受或回滾違反參照完整性的數(shù)據(jù)修改事務l強制執(zhí)行遠復雜于強制執(zhí)行遠復雜于CHECKCHECK約束的限制約束約束的限制約束l通過通過INSERT,UPDATEINSERT,UPDATE和和DELETEDELETE語句比較數(shù)據(jù)修改前后的狀態(tài)語句比較數(shù)據(jù)修改前后的狀態(tài), ,并根并根據(jù)修改據(jù)修改, ,執(zhí)行一定的操作執(zhí)行一定的操作. .大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計167.2.1 7.2.1 觸發(fā)器概述觸發(fā)器概述 觸發(fā)器的種類觸發(fā)器的種類 AFTERAFTER觸發(fā)器觸發(fā)器 只有對表執(zhí)行某一操作之后，才能被觸發(fā)

20、。可以為只有對表執(zhí)行某一操作之后，才能被觸發(fā)。可以為 表的同一操作定義多個觸發(fā)器，其觸發(fā)次序可使用表的同一操作定義多個觸發(fā)器，其觸發(fā)次序可使用 sp_settriggerorder sp_settriggerorder來完成。來完成。 INSTEAD OFINSTEAD OF觸發(fā)器觸發(fā)器 并不執(zhí)行其所定義的操作而僅是執(zhí)行觸發(fā)器本身，并不執(zhí)行其所定義的操作而僅是執(zhí)行觸發(fā)器本身， 對同一操作只能定義一個對同一操作只能定義一個instead of instead of 觸發(fā)器。觸發(fā)器。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計177.2.1 7.

21、2.1 觸發(fā)器概述觸發(fā)器概述 INSERTEDINSERTED和和DELETEDDELETED臨時表臨時表 觸發(fā)器代碼最強大的功能之一是能夠比較記錄的新舊觸發(fā)器代碼最強大的功能之一是能夠比較記錄的新舊 版本，用戶可以根據(jù)記錄的改變適時地做出決定。版本，用戶可以根據(jù)記錄的改變適時地做出決定。 插入一行時，插入一行時，INSERTEDINSERTED表保存了一份插入行的拷貝。表保存了一份插入行的拷貝。 刪除一行時，刪除一行時，DELETEDDELETED表保存了刪除行的拷貝。表保存了刪除行的拷貝。 更新一行時，更新一行時，INSERTEDINSERTED表中保存了一份新行的拷貝，表中保存了一份新行

22、的拷貝， DELETED DELETED表中保存了一份舊行的拷貝。表中保存了一份舊行的拷貝。 可以使用這兩個臨時的駐留內(nèi)存的表測試某些數(shù)據(jù)修可以使用這兩個臨時的駐留內(nèi)存的表測試某些數(shù)據(jù)修 改的效果及設置觸發(fā)器操作的條件，但不能直接對這改的效果及設置觸發(fā)器操作的條件，但不能直接對這 兩個臨時表中的數(shù)據(jù)進行更改。兩個臨時表中的數(shù)據(jù)進行更改。大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計18利用 CREATE TRIGGER 命令創(chuàng)建觸發(fā)器CREATE TRIGGER trigger_name ON ( table | view ) WITH ENC

23、RYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION ASSql_statementn | FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE (column) AND | OR UPDATE (column) .n | IF ( C O LUMN S_UPDA T E D () bitwise_operator updated_bi

24、tmask) comparison_operator column_bitmask .n sql_statement .n 大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計197.2.2 7.2.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器用企業(yè)管理器創(chuàng)建用企業(yè)管理器創(chuàng)建用用T-SQLT-SQL命令創(chuàng)建命令創(chuàng)建語法格式：語法格式：CREATE TRIGGER CREATE TRIGGER 觸發(fā)器名稱觸發(fā)器名稱ON ON 表或視圖表或視圖 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF DELETEDELETE，INSERTINSERT，UPDATEUPDATEAS AS SQLSQL語句語句 大型數(shù)據(jù)庫大型數(shù)據(jù)庫SQL Server 2000SQL Server 2000程序設計程序設計207.2.2 7.2.2 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器v CTEATE TRIGGER CTEATE TRIGGER 語句必須是批處理的第一個語句