課題=第八章存儲過程和觸發(fā)器+++

1、第八章第八章 存儲過程和觸發(fā)器存儲過程和觸發(fā)器一、存儲過程一、存儲過程二二、觸發(fā)器觸發(fā)器一、存儲過程一、存儲過程、概念、概念SQL Server的存儲過程類似于編程語言中的過程。在使用Transact-SQL語言編程的過程中，我們可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個特定任務(wù)的代碼段編寫成一個過程，將其保存在數(shù)據(jù)庫中，并由SQL Server服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲過程。 一、存儲過程一、存儲過程存儲過程是一組預(yù)先編譯好的T-SQL代碼,作為一個整體用于執(zhí)行一系列特定的操作。存儲過程屬于數(shù)據(jù)庫對象，它們存放在數(shù)據(jù)庫中，需要時用戶可以調(diào)用它們，可以通過應(yīng)用程序調(diào)用它們，也可以在查

2、詢分析器中調(diào)用它們，也可以在一個存儲過程調(diào)用另一個存儲過程。 一、存儲過程一、存儲過程2 2、分類、分類系統(tǒng)存儲過程系統(tǒng)存儲過程在SQL Server2000中，很多管理活動都是通過系統(tǒng)存儲過程執(zhí)行的。像我們以前講過的創(chuàng)建登錄賬號，創(chuàng)建數(shù)據(jù)庫用戶賬號，創(chuàng)建角色等等。系統(tǒng)存儲過程創(chuàng)建和保存在master數(shù)據(jù)庫中，都是以“sp_”為前綴的，可以在任何數(shù)據(jù)庫中使用系統(tǒng)存儲過程。用戶創(chuàng)建的存儲過程最好不要以“sp_”為前綴。一、一、IntranetIntranet概述概述 一、存儲過程一、存儲過程用戶自定義的存儲過程用戶自定義的存儲過程是用戶自己根據(jù)需要編寫的存儲過程。一、存儲過程一、存儲過程3 3、

3、優(yōu)點(diǎn)、優(yōu)點(diǎn)實(shí)現(xiàn)了模塊化編程實(shí)現(xiàn)了模塊化編程存儲過程經(jīng)過了一次創(chuàng)建以后，可以被調(diào)用無數(shù)次。用戶可以獨(dú)立于應(yīng)用程序而對存儲過程進(jìn)行修改?？梢园凑摘?dú)特的功能模式設(shè)計(jì)不同的存儲過程以供使用。 一、存儲過程一、存儲過程存儲過程具有對數(shù)據(jù)庫立即訪問的功能存儲過程具有對數(shù)據(jù)庫立即訪問的功能存儲過程作為一個獨(dú)立的數(shù)據(jù)庫對象，可以作為一個單元被用戶的應(yīng)用程序調(diào)用。由于存儲過程是已經(jīng)編譯好的代碼，所以執(zhí)行的時候不必再次進(jìn)行編譯，從而提高了程序的運(yùn)行效率。一、存儲過程一、存儲過程存儲過程可以減少網(wǎng)絡(luò)流量存儲過程可以減少網(wǎng)絡(luò)流量存儲過程中可以包含大量的Transact-SQL語句，但存儲過程作為一個獨(dú)立的單元來使用。

4、在進(jìn)行調(diào)用時，只需要使用一個語句就可以實(shí)現(xiàn)，所以大大減少了網(wǎng)絡(luò)上數(shù)據(jù)的傳輸。 一、存儲過程一、存儲過程存儲過程可以加快程序的運(yùn)行速度存儲過程可以加快程序的運(yùn)行速度存儲過程在創(chuàng)建時就經(jīng)過了語法檢查和性能優(yōu)化，因此在執(zhí)行時不必再重復(fù)這些步驟。存儲過程在經(jīng)過第一次調(diào)用之后，就駐留在內(nèi)存中，不必再經(jīng)過編譯和優(yōu)化，所以執(zhí)行速度快。在有大量批處理的Transact-SQL語句要重復(fù)執(zhí)行的時候，使用存儲過程可以極大地提高運(yùn)行效率。一、存儲過程一、存儲過程存儲過程可以提高數(shù)據(jù)庫的安全性存儲過程可以提高數(shù)據(jù)庫的安全性可以設(shè)置用戶通過存儲過程對某些關(guān)鍵數(shù)據(jù)進(jìn)行訪問，但不允許用戶直接使用T-SQL或企業(yè)管理器對數(shù)據(jù)

5、進(jìn)行訪問。 一、存儲過程一、存儲過程4 4、創(chuàng)建存儲過程、創(chuàng)建存儲過程例如：例如：在銷售公司，員工的工資是和銷售額掛勾的，銷售額在0-500，開基本工資的50%，銷售額在500-1000，開基本工資，銷售額在1000-2000，開基本工資的1.5倍。 表：表：employeeemployeeempid int empname charjbgz floatsse floatgz float一、存儲過程一、存儲過程一、存儲過程一、存儲過程語法語法CREATE CREATE PROCEDUREPROCEDURE 存儲過程名存儲過程名 ASAS語句體；語句體；GOGO一、存儲過程一、存儲過程在企業(yè)管理

6、器中創(chuàng)建存儲過程在企業(yè)管理器中創(chuàng)建存儲過程操作：操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫存儲過程右鍵新建存儲過程 一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程CREATE PROCEDURE pscgz ASupdate employee set gz=jbgz*0.5 where sse=500 and sse=1000 and sse2000;GO一、存儲過程一、存儲過程在查詢分析器中創(chuàng)建存儲過程在查詢分析器中創(chuàng)建存儲過程 一、存儲過程一、存儲過程一、存儲過程一、存儲過程USE studentGOIF EXISTS (select name

7、from sysobjects where name=pscgz1 and type=P)DROP PROCEDURE pscgz1GO（檢查是否有同名的存儲過程，如果有，則首先刪除這個存儲過程。Sysobjects是系統(tǒng)表，表中存放的是這個數(shù)據(jù)庫中的對象）CREATE PROCEDURE pscgz1 ASupdate employee set gz=jbgz*0.5 where sse=500 and sse=1000 and sse2000;GO一、存儲過程一、存儲過程注意：注意：創(chuàng)建存儲過程時可以傳遞參數(shù)，一種是輸入?yún)?shù)，也就是執(zhí)行這個存儲過程需要的參數(shù)，還有一種是輸出參數(shù)，也就是執(zhí)行

8、完這個存儲過程如果有返回值的話可以由輸出參數(shù)返回。我們不能像函數(shù)那樣通過存儲過程名來獲得返回結(jié)果。一、存儲過程一、存儲過程CREATE PROCEDURE 存儲過程名 參數(shù)名數(shù)據(jù)類型，-輸入?yún)?shù)參數(shù)名 數(shù)據(jù)類型 OUTPUT -輸出參數(shù)AS語句體；GO一、存儲過程一、存儲過程、執(zhí)行存儲過程、執(zhí)行存儲過程我們在創(chuàng)建存儲過程時，系統(tǒng)只是檢查存儲過程是否有語法錯誤，并將它做為對象保存起來。但是并沒有執(zhí)行存儲過程，也就是并沒有執(zhí)行存儲過程中的語句。要想執(zhí)行存儲過程，要進(jìn)入到查詢分析器中，執(zhí)行語句：exec 存儲過程名； 一、存儲過程一、存儲過程一、存儲過程一、存儲過程、修改存儲過程、修改存儲過程操作：

9、操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫存儲過程選中要修改的存儲過程右鍵屬性一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程一、存儲過程、重命名存儲過程、重命名存儲過程操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫存儲過程選中要重命名的存儲過程右鍵重命名、刪除存儲過程、刪除存儲過程操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫存儲過程選中要刪除的存儲過程右鍵刪除一、存儲過程一、存儲過程二、觸發(fā)器二、觸發(fā)器1 1、定義、定義 觸發(fā)器是一種與數(shù)據(jù)庫中表緊密關(guān)聯(lián)的特殊的存儲過程，當(dāng)該數(shù)據(jù)表有插入（INSERT）、更改（UPDATE）或刪除（DELETE）事件發(fā)生時，所設(shè)置的觸發(fā)器就會自動被執(zhí)行。觸發(fā)器只要滿足一

10、定的條件，它就可以觸發(fā)完成各種簡單和復(fù)雜的任務(wù)，可以幫助我們更好的維護(hù)數(shù)據(jù)庫中數(shù)據(jù)的完整性。二、觸發(fā)器二、觸發(fā)器觸發(fā)器被存儲于數(shù)據(jù)庫中，它是與一個表聯(lián)系在一起的，當(dāng)這個表被用戶所操作時，定義在表上的觸發(fā)器就被觸發(fā)執(zhí)行了。觸發(fā)器的主要特點(diǎn)在于，不管由何種原因造成的數(shù)據(jù)變化，它都能夠自動響應(yīng)。二、觸發(fā)器二、觸發(fā)器注意：注意：與一般意義上的存儲過程不同的是，觸發(fā)器是當(dāng)觸發(fā)器所關(guān)聯(lián)的表的內(nèi)容被更改時，自動執(zhí)行的存儲過程，不能直接被調(diào)用，也不能傳遞或接受參數(shù) 。二、觸發(fā)器二、觸發(fā)器2 2、作用、作用保證數(shù)據(jù)的安全性保證數(shù)據(jù)的安全性一是在用戶對一些重要數(shù)據(jù)進(jìn)行操作時，首先由觸發(fā)器判斷這個操作是否合法，如果

11、不合法就進(jìn)行禁止，保證了數(shù)據(jù)的安全性。實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則二、觸發(fā)器二、觸發(fā)器完成級聯(lián)的修改或刪除操作完成級聯(lián)的修改或刪除操作級聯(lián)操作就是我們在執(zhí)行一個操作時，自動執(zhí)行一系列操作。在數(shù)據(jù)庫中，有時我們在刪除一條記錄時，需要刪除其它表中相關(guān)聯(lián)的記錄，這時候我們就用觸發(fā)器來完成這些連級聯(lián)刪除操作。 二、觸發(fā)器二、觸發(fā)器3 3、類型、類型AFTERAFTER觸發(fā)器觸發(fā)器將在數(shù)據(jù)變動（INSERT、UPDATE和DELETE操作）完成后才被激發(fā)。AFTER觸發(fā)器只能在表上定義。二、觸發(fā)器二、觸發(fā)器INSTEAD OF INSTEAD OF 觸發(fā)器觸發(fā)器將在數(shù)據(jù)變動以前被激發(fā)

12、，并取代變動數(shù)據(jù)（INSERT、UPDATE和DELETE操作）的操作，轉(zhuǎn)而去執(zhí)行觸發(fā)器定義的操作。 INSTEAD OF觸發(fā)器既可以在表上定義，也可以在視圖上定義。 二、觸發(fā)器二、觸發(fā)器4 4、創(chuàng)建觸發(fā)器、創(chuàng)建觸發(fā)器例如：學(xué)生選課系統(tǒng)，有學(xué)生表，課程表，學(xué)生選課表在stu表上創(chuàng)建一個觸發(fā)器，如果刪除了stu表中的一條學(xué)生的信息，則觸發(fā)此觸發(fā)器，同時刪除xk表中這個學(xué)生的選課記錄。二、觸發(fā)器二、觸發(fā)器stu表sidsnameSclasscourse表cidCnamexk表xkidsidcid二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器語法語法CREATE TRIGGER 觸發(fā)器名 ON 表名FOR

13、 INSERT, UPDATE, DELETE （-FOR代表AFTER，INSERT, UPDATE, DELETE表示對表執(zhí)行這些操作時觸發(fā)器這個觸發(fā)器，可以任選其一，也可以組合選擇。）AS語句體；GO二、觸發(fā)器二、觸發(fā)器在企業(yè)管理器中創(chuàng)建觸發(fā)器在企業(yè)管理器中創(chuàng)建觸發(fā)器操作：操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫選中要創(chuàng)建觸發(fā)器的表右鍵所有任務(wù)管理觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器CREATE TRIGGER CREATE TRIGGER dexk dexk ON ON stustuFOR FOR DELETEDELETE ASASdelete from xk

14、 where sid IN (select distinct sid from delete from xk where sid IN (select distinct sid from deleted)deleted)GOGO二、觸發(fā)器二、觸發(fā)器注意：注意：使用UPDATE, DELETE語句，可以同時對多行數(shù)據(jù)進(jìn)行修改，但不管修改了多少行數(shù)據(jù)，觸發(fā)器只觸發(fā)一次。delete from xk where sid IN (select distinct sid from deleted)二、觸發(fā)器二、觸發(fā)器在查詢分析器中創(chuàng)建觸發(fā)器在查詢分析器中創(chuàng)建觸發(fā)器 二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)

15、器USE studentGOIF EXISTS (select name from sysobjects where name=dexk1 and type=TR)DROP TRIGGER dexk1GOCREATE TRIGGER dexk1 ON stuFOR DELETE ASdelete from xk where sid IN (select distinct sid from deleted)GO二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器、修改觸發(fā)器、修改觸發(fā)器操作：操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫選中要修改觸發(fā)器的表右鍵所有任務(wù)管理觸發(fā)器在名稱處選擇所要修改的觸發(fā)器 。二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器二、觸發(fā)器6 6、重命名觸發(fā)器、重命名觸發(fā)器操作：操作：在查詢分析器中執(zhí)行：EXEC sp_rename 原觸發(fā)器名, 新觸發(fā)器名二、觸發(fā)器二、觸發(fā)器7 7、刪除觸發(fā)器、刪除觸發(fā)器操作：操作：在企業(yè)管理器中打開服務(wù)器數(shù)據(jù)庫選中要刪除觸發(fā)器