存储过程与触发器课件.ppt

圖8-10一個DELETE類型的觸發器【例8-6】在資料庫XSCJ中設計一個觸發器，該觸發器能夠保證在學生基本資訊表中添加新的紀錄時，新學生的班級編號必須已經存在於班級表中。提示：設計該觸發器有助於實現學生資訊的完整性。在此例中由於涉及到了學生基本資訊表中的添加操作，因而需要設計一個INSERT類型的觸發器。

在查詢分析器中運行如下命令：USEXSCJ

GO

CREATETRIGGERinsert_xsON學生基本資訊表

AFTERINSERT

AS

IFEXISTS

(

SELECT*FROMINSERTED

WHERE班級編號IN(SELECT班級編號FROM班級表)

)

PRINT添加成功！

ELSE

BEGIN

PRINT班級編號與現有的班級不符！

ROLLBACKTRANSACTION

END運行結果如圖8-11所示。圖8-11一個INSERT類型的觸發器創建了觸發器insert_xs之後，我們可以添加新的學生紀錄進行測試，例如：

INSERTINTO學生基本資訊表(學號,姓名,性別,族別,班級編號)VALUES(000108,王松濤,男,漢

由於“班級表”中不存在編號班級，因而添加操作將會被取消。ROLLBACKTRANSACTION用於回滾已經完成的操作。運行結果如圖8-12所示。圖8-12添加操作被取消存儲過程與觸發器8.1存儲過程

8.1.1存儲過程基礎知識

1．存儲過程簡介

存儲過程是存儲在伺服器上的由SQL語句和控制流語句組成的一個預編譯集合。存儲過程劃分為系統存儲過程以及用戶存儲過程。一個存儲過程可以作為一個獨立的單元進行處理。在SQLServer中可以通過存儲過程來完成很多管理任務，利用一些常用的系統存儲過程可以很方便的查看、運算元據庫對象。系統存儲過程的命名通常以”sp_”作為首碼，並且存儲於Master資料庫中，如果用戶擁有足夠的許可權，就可以在任何資料庫中調用這些存儲過程。

存儲過程提供了一種封裝某一個需要重複執行任務的方法。一旦定義了一個存儲過程之後，在應用程式中就可以對其進行調用。在存儲過程中，不僅可以包含程式流、邏輯以及對數據庫的查詢，而且也可以接受參數、輸出參數、返回單個或多個結果集。所以通過設計自定義用戶存儲過程，用戶可以使其實現強大的編程功能。

使用存儲過程可以比單獨的SQL語句完成更為複雜的功能，並且系統會對存儲過程中的SQL語句進行了預編譯處理，使得執行速度有了大幅度的提升。存儲過程被第一次調用後，會保存在高速緩衝區中，這樣再次執行同一個存儲過程時，會提高了重複調用的效率。在實際應用中，可以將複雜的商業規則封裝在存儲過程中，從而提高程式語句的利用率。

2．存儲過程的優點存儲過程一旦執行一次後，其執行的計畫就會駐留在電腦的高速緩衝記憶體中。其後對同一個存儲過程的調用就可以直接利用編譯後在高速緩存中的二進位形式來完成操作。可以在單個存儲過程中執行一系列SQL語句，因而可以用於設計、封裝企業的功能模組。應用程式使用相同的存儲過程進行操作，確保了在數據訪問、操縱的一致性。可以在當前的存儲過程內部引用其他存儲過程，這樣可以將複雜語句進行簡化。提供了一種資料庫訪問的途徑，只需要用戶擁有執行存儲過程的許可權，那麼通過使用存儲過程就可以完成對數據庫的各種操作，如添加數據、修改數據、刪除數據等，而不需要考慮用戶是否擁有存儲過程所處理的數據對象的訪問許可權。3．使用存儲過程的注意事項在命名自定義存儲過程時避免與系統存儲過程名相同。存儲過程最多能夠支持32層的嵌套。命名存儲過程中的識別字時，長度不能超過128個字元。存儲過程中參數的個數不能超過2100。8.1.2創建和執行存儲過程

在SQLServer中，既可以通過企業管理器，也可以通過使用CREATEPROCEDRUE語句的方式來創建存儲過程。

1．使用企業管理器創建存儲過程

步驟：

①在企業管理器中，展開伺服器組、伺服器節點、資料庫節點。

②選擇某一個資料庫並在該節點上點擊滑鼠右鍵，選擇【新建】菜單下的【存儲過程】級聯菜單，將彈出如圖8-1所示的對話框。圖8-1新建存儲過程對話框③在【存儲過程屬性】對話框中的【文本】編輯框中，對[OWNER]以及[PROCEDURENAME]部分進行修改，分別修改為該存儲過程的所有者和存儲過程名稱，並且輸入該存儲過程所包含的SQL語句。如圖8-2所示為創建了一個用於查詢學生資訊的存儲過程。圖8