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

2、程大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設計程序設計37.1.1 7.1.1 存儲過程類型存儲過程類型 系統存儲過程系統存儲過程 存儲在存儲在mastermaster數據庫中，以數據庫中，以sp_sp_為前綴。可以在其他為前綴?？梢栽谄渌?數據庫中對其進行調用。數據庫中對其進行調用。 用戶自定義存儲過程用戶自定義存儲過程 由用戶創建并能完成某一特定功能的存儲過程?；蛴捎脩魟摻ú⒛芡瓿赡骋惶囟üδ艿拇鎯^程。或 稱本地存儲過程。包括臨時存儲過程、遠程存儲過稱本地存儲過程。包括臨時存儲過程、遠程存儲過 程、擴展存儲過程。程、擴展存儲過程。 臨時存儲過程

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

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

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

6、程序設計程序設計67.1.2 7.1.2 創建存儲過程創建存儲過程例例7.27.2：創建存儲過程：創建存儲過程proc_7_2proc_7_2，要求實現根據學生學號，產生不同結果，要求實現根據學生學號，產生不同結果，如果該學生信息不存在，則顯示如果該學生信息不存在，則顯示“無此學號的學生！無此學號的學生！”，否則返回該學生的，否則返回該學生的基本信息基本信息。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 無此學號的學生！無此學號的學生！思考：創建存儲過程思考：創建存儲過程testproc2testproc2，實現根據學生的學號，查詢此學生的學號、實現根據學生的學號，查詢此學生的學號、姓名、所選課程號、課程名、成績及學分等信息。姓名、所選課程號、課程名、成績及學分等信息。大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設

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

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

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

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

12、要求實現如下功能：根據學生學號，如果此學生存在，則產生該生的課程成績列表，其中包括學據學生學號，如果此學生存在，則產生該生的課程成績列表，其中包括學號、課程號、課程名稱、成績、學分等；如果此學生不存在，則顯示號、課程號、課程名稱、成績、學分等；如果此學生不存在，則顯示“無無此學生！此學生！”。并調用此存儲過程，顯示。并調用此存儲過程，顯示“”“”學生的課程成績情況。學生的課程成績情況。 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 無此學生！無此學生！大型數據庫大型數據庫SQL Server 2000SQL Server 2000

14、程序設計程序設計117.1.4 7.1.4 修改存儲過程修改存儲過程語法格式：語法格式：ALTER PROCEDURE ALTER PROCEDURE 存儲過程名稱存儲過程名稱 參數定義參數定義AS AS SQLSQL語句語句例例7.77.7：修改在例：修改在例7.57.5中已創建的存儲過程中已創建的存儲過程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大型數據庫大型數據庫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命令創建存儲過程時，總是先確定要創建的存儲過程命令創建存儲過程時，總是先確定要創建的存儲過程是否已經存在，如果存在，那么就刪除重建。我們可以用如下語句實現：是否已經存在，如果存在，那么就刪除重建。我們可以用如下語句實現： If exists( select name from sysobjects where name = proc_7_1 and type = P) drop proc proc_7_1大型

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

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

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

20、?？梢詾橹挥袑Ρ韴绦心骋徊僮髦?，才能被觸發?？梢詾?表的同一操作定義多個觸發器，其觸發次序可使用表的同一操作定義多個觸發器，其觸發次序可使用 sp_settriggerorder sp_settriggerorder來完成。來完成。 INSTEAD OFINSTEAD OF觸發器觸發器 并不執行其所定義的操作而僅是執行觸發器本身，并不執行其所定義的操作而僅是執行觸發器本身， 對同一操作只能定義一個對同一操作只能定義一個instead of instead of 觸發器。觸發器。大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設計程序設計177.2.1 7.

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

22、的拷貝， DELETED DELETED表中保存了一份舊行的拷貝。表中保存了一份舊行的拷貝。 可以使用這兩個臨時的駐留內存的表測試某些數據修可以使用這兩個臨時的駐留內存的表測試某些數據修 改的效果及設置觸發器操作的條件，但不能直接對這改的效果及設置觸發器操作的條件，但不能直接對這 兩個臨時表中的數據進行更改。兩個臨時表中的數據進行更改。大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設計程序設計18利用 CREATE TRIGGER 命令創建觸發器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 大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設計程序設計197.2.2 7.2.2 創建觸發器創建觸發器用企業管理器創建用企業管理器創建用用T-SQLT-SQL命令創建命令創建語法格式：語法格式：CREATE TRIGGER CREATE TRIGGER 觸發器名稱觸發器名稱ON ON 表或視圖表或視圖 FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF DELETEDELETE，INSERTINSERT，UPDATEUPDATEAS AS SQLSQL語句語句 大型數據庫大型數據庫SQL Server 2000SQL Server 2000程序設計程序設計207.2.2 7.2.2 創建觸發器創建觸發器v CTEATE TRIGGER CTEATE TRIGGER 語句必須是批處理的第一個語句