SQL_server2000的使用__存儲過程及觸發(fā)器

1、存儲過程與觸發(fā)器存儲過程一、存儲過程的特點在大型數(shù)據(jù)庫應用中，隨著功能的不斷完善，整個系統(tǒng)也越來越復雜。大量的情況是許多SQL語句代碼被許多次反復使用，如果每一次使用都輸入相同的代碼，效率會很低。數(shù)據(jù)庫管理系統(tǒng)提供了一種方法，可以把一組常用的SQL語句集中起來，編譯成可執(zhí)行代碼，存儲在數(shù)據(jù)庫服務器上，這樣可以方便完成特定任務，這種方法就是存儲過程。存儲過程是SQL語句和流程控制語句的集合，以一個名字保存，并作為一個單元來處理。存儲過程是數(shù)據(jù)庫中的一個獨立的對象，保存在數(shù)據(jù)庫中，可以由應用程序來調(diào)用執(zhí)行，大大簡化應用程序的開發(fā)，因此是SQL服務器端開發(fā)的主要手段之一。如果不使用存儲過程，那么當客

2、戶端輸入SQL語句之后，它會發(fā)送到數(shù)據(jù)庫服務器，由服務器進行下列操作： 語法分析：檢查所輸入的SQL語句，例如關鍵字拼寫是否正確。 語義檢查：檢查SQL語句中所涉及的表、列是否存在。 優(yōu)化分析：選擇如何把數(shù)據(jù)從磁盤調(diào)入內(nèi)存快速獲取數(shù)據(jù)行的方法。 可執(zhí)行代碼：一組由數(shù)據(jù)庫服務器解釋執(zhí)行的代碼。當同樣的SQL再次執(zhí)行時，則需要重新進行前面4個步驟。這樣的操作浪費了時間和空間、降低了數(shù)據(jù)庫服務器的效率。如果使用了存儲過程，當再次執(zhí)行前面的SQL語句時，不再需要執(zhí)行語法分析、語義檢查和優(yōu)化分析，直接調(diào)用以前生成好的可執(zhí)行代碼，顯然這樣的執(zhí)行效率要高。存儲過程具有以下特點：n 具有立即訪問數(shù)據(jù)庫的能力。

3、n 它是數(shù)據(jù)庫服務器端的執(zhí)行代碼，在服務器端執(zhí)行操作，減少網(wǎng)絡通訊，提高執(zhí)行效率。n 保證數(shù)據(jù)庫安全，自動完成預先定義的作業(yè)。1. 存儲過程分為系統(tǒng)過程和用戶定義的存儲過程。系統(tǒng)存儲過程是一組特殊的存儲過程，它們在安裝SQL時自動建立，存儲在Master中。所有系統(tǒng)存儲過程名稱都以SP_開始的。例如前面介紹過的SP_helpdb（顯示所有數(shù)據(jù)庫的信息）。系統(tǒng)存儲過程為系統(tǒng)管理員提供管理SQL的支持，并且通過系統(tǒng)存儲過程能夠得到系統(tǒng)信息或完成管理工作。用戶存儲過程是指創(chuàng)建在每個用戶自己數(shù)據(jù)庫中的存儲過程。這種存儲過程的名字由用戶命名，且名稱前面沒有前綴SP_。例1：在企業(yè)管理器中查看常用的系統(tǒng)存

4、儲過程。具體操作如下：打開企業(yè)管理器，打開數(shù)據(jù)庫，master,存儲過程。例2：通過存儲過程來查詢當前數(shù)據(jù)庫的信息。SP_Helpdb或EXEC SP_Helpdb二、使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程例如：創(chuàng)建對于“課程表”進行插入操作的存儲過程。具體操作如下：在企業(yè)管理器中單擊工具欄中運行向?qū)?，在彈出的選擇向?qū)υ捒蛑姓归_“數(shù)據(jù)庫創(chuàng)建存儲過程向?qū)А保瑥棾鰵g迎使用創(chuàng)建存儲過程向?qū)υ捒?。以下操作看演示。那么如何?zhí)行創(chuàng)建完的存儲過程呢？在查詢分析器中輸入下列內(nèi)容：插入課程表 c009,ejb實用技術，孫高然后執(zhí)行即可。為了驗證這個存儲過程的作用，在查詢分析器中執(zhí)行下列SQL語句。SELECT *

5、 FROM 課程表WHERE 課程號=C009三、使用SQL語句創(chuàng)建存儲過程語法格式：CREATE PROC (,）AS |語句塊其中：是存儲過程的輸入輸出參數(shù)。 說明參數(shù)類型?；蚴嵌x存儲過程內(nèi)容的語句。例如：建立判斷男生與女生的平均獎學金高低的存儲過程。如果學生基本表中信息男生獎學金的平均值高于女生獎學金的平均值，則顯示“男生的獎學金平均值高于女生的獎學金平均值”，否則顯示“男生的獎學金平均值不高于女生的獎學金平均值”，創(chuàng)建存儲過程的SQL語句如下：CREATE PROC 按照性別判斷平均獎學金高低AS IF （SELECT AVG（獎學金） FROM 學生基本表 WHERE 性別=男）

6、（SELECT AVG（獎學金） FROM 學生基本表 WHERE 性別=女） PRINT 男生的獎學金平均值高于女生的獎學金平均值ELSE PRINT 男生的獎學金平均值不高于女生的獎學金平均值這樣存儲過程就創(chuàng)建完畢。執(zhí)行存儲過程有兩種方式： 存儲過程名 EXEC 存儲過程名例如：顯示男生獎學金平均值、女生獎學金平均值并且給出判斷信息。CREATE PROC 按照性別判斷平均獎學金高低1ASBEGIN DECLARE X NUMERIC(8,2),Y UNMERIC(8,2) SET X=(SELECT AVG（獎學金） FROM 學生基本表 WHERE 性別=男） SET Y=(SELEC

7、T AVG（獎學金） FROM 學生基本表 WHERE 性別=女）PRINT XPRINT YIF (XY) PRINT 男生的獎學金平均值高于女生的獎學金平均值ELSE PRINT 男生的獎學金平均值不高于女生的獎學金平均值END四、管理存儲過程使用SQL語句可以更為有效地管理存儲過程。例如：建立一個查詢指定學號獎學金信息的存儲過程“查詢獎學金”，輸入?yún)?shù)為學號，如果沒有輸入學號，則輸出提示信息。 建立這個存儲過程的SQL 語句如下：CREATE PROC 查詢獎學金 xh char(4)=nullAS IF XH IS NULL BEGIN PRINT 請輸入要查詢獎學金的學號 RETUR

8、N ENDELSE BEGIN SELECT 學號，姓名，獎學金 FROM 學生基本表 WHERE 學號=xh END這里要注意的是，這個存儲過程有一個輸入?yún)?shù)XH，使用XH表示，同時定義了數(shù)據(jù)類型為CHAR（4），這與學生基本表中學號列的定義一樣的。當定義了輸入?yún)?shù)之后，可以在存儲過程體中采用XH來使用。執(zhí)行存儲過程：查詢獎學金 x101或者查詢獎學金請輸入要查詢獎學金的學號X101修改存儲過程，其語法格式如下：ALTER PROC (AS SQL語句|語句塊例如：修改存儲過程“查詢獎學金”，要求增加輸出性別信息。ALTER PROC 查詢獎學金 xh char(4)=nullAS IF X

9、H IS NULL BEGIN PRINT 請輸入要查詢獎學金的學號 RETURN ENDELSE BEGIN SELECT 學號，姓名，性別，獎學金 FROM 學生基本表 WHERE 學號=xh END刪除存儲過程：DROP PROCEDURE 例如：刪除存儲過程“查詢獎學金”DROP PROCEDURE 查詢獎學金存儲過程的綜合練習：DROP PROC 查詢獎學金GOUSE 學生信息庫GOCREATE PROC 查詢獎學金 xh char(4)=nullAS IF XH IS NULL BEGINPRINT 請輸入要查詢獎學金的學號 RETURN ENDELSE BEGIN SELECT

10、學號，姓名，獎學金 FROM 學生基本表 WHERE 學號=xh ENDGOEXEC 查詢獎學金 X101GO該實例是在學生信息庫中給出學生的學號，查出獎學金，在該批處理中，包括了刪除存儲過程、建立存儲過程、執(zhí)行存儲過程的SQL語句。此外，SQL還提供了查看存儲過程的系統(tǒng)存儲過程。 SP_help :輸出指定存儲過程的參數(shù)和數(shù)據(jù)類型。 SP_helptext ：輸出定義指定存儲過程內(nèi)容的源代碼。 SP_depends :輸出與指定存儲過程相關的數(shù)據(jù)庫對象。例如：查看存儲過程“查詢獎學金”的源代碼。SP_HELPTEXT 查詢獎學金希望同學們對以上存儲過程有一個全面的理解和認識。觸發(fā)器一、觸發(fā)器

11、的概念觸發(fā)器是一種特殊類型的存儲過程，這是因為觸發(fā)器也包含了一組SQL語句。但是觸發(fā)器又與存儲過程明顯不同，例如觸發(fā)器可以自動執(zhí)行。當有操作影響到觸發(fā)器保護的數(shù)據(jù)時，觸發(fā)器就自動觸發(fā)執(zhí)行。因此觸發(fā)器是在特定表上進行定義的，該表也稱為觸發(fā)器表。對有操作針對觸發(fā)器表時，例如在表中插入、刪除、修改數(shù)據(jù)時，如果該表有相應操作類型的觸發(fā)器，那么觸發(fā)器就自動觸發(fā)執(zhí)行。在SQL中，有三種類型的觸發(fā)器，即INSERT類型、UPDATE類型和DELETE類型。當向某一個表插入數(shù)據(jù)時，若該表有INSERT類型的觸發(fā)器，那么該觸發(fā)器就觸發(fā)執(zhí)行；當向一個表進行修改操作時，若該表有UPDATE類型觸發(fā)器，那么該觸發(fā)器就

12、觸發(fā)執(zhí)行；當刪除一個表的數(shù)據(jù)時，若該表有DELETE類型的觸發(fā)器，那么同樣該觸發(fā)器觸發(fā)執(zhí)行。觸發(fā)器與存儲過程相比，觸發(fā)器有其特殊性，這主要體現(xiàn)在以下幾個方面： 自動執(zhí)行：當對指定數(shù)據(jù)進行修改時，觸發(fā)器自動執(zhí)行，不需要按照名稱手動執(zhí)行。 連續(xù)執(zhí)行：由于觸發(fā)器自動執(zhí)行的特性，有些時候會出現(xiàn)觸發(fā)器的瀑布觸發(fā)，在表T1上定義了觸發(fā)器S1（這里有對表T2的DML操作），在表Tn上定義了觸發(fā)器Sn，對表T1的一個操作，會引起T1至Tn共n張表的操作。 強制限制：當對數(shù)據(jù)庫對象施加指定操作時會自動觸發(fā)指定的動作，利用這樣的機制可以強制限制某些操作，實際上，前面介紹的表的約束條件都是通過觸發(fā)器來實現(xiàn)的。二、觸

13、發(fā)器的工作原理 參照完整性觸發(fā)器保持外鍵的值與主鍵的值一致。當數(shù)據(jù)操作影響一列時，觸發(fā)器用稱為觸發(fā)器檢查表的臨時工作表來比較新的列值與相關的鍵。編寫觸發(fā)器時，比較的數(shù)據(jù)存放在臨時的觸發(fā)器檢查表中。觸發(fā)器檢查表為INSERTED和DELETED，其中INSERTSD保存的是插入的數(shù)，DELETED保存的是刪除的數(shù)據(jù)。 觸發(fā)器檢查表INSERTED和DELETED是用來檢查一些數(shù)據(jù)操作的印象和觸發(fā)器的動作設置條件。它不能直接地改變觸發(fā)器檢查表中的數(shù)據(jù)，但是能夠使用SELECT語句來檢測INSERT、DELETE和UPDATE等操作的影響：n在INSERT語句執(zhí)行期間，新行被添加到觸發(fā)器表的同時，也

14、被添加到INSERTED表中；n在DELETED語句執(zhí)行期間，從觸發(fā)器表中被刪除的行轉(zhuǎn)移到了DELETED表中，也就是說DELETED表此時存放的是從觸發(fā)器表中刪除的行；nUPDATE操作實際上是兩個動作的連續(xù)，第一個動作是刪除觸發(fā)器表中的舊數(shù)據(jù)，這時的舊數(shù)據(jù)轉(zhuǎn)移到DELETED表中；第二個動作是插入新數(shù)據(jù)，在插入到觸發(fā)器表的同時，也插入到INSERTED表中。三、觸發(fā)器的創(chuàng)建CREATE TRIGGER ON |FOR INSERT|UPDATE|DELETEASSQL語句|語句塊其中：是要新建的觸發(fā)器名稱.|是想要建立觸發(fā)器的數(shù)據(jù)庫對象名稱.|是定義觸發(fā)動作的代碼.例1:創(chuàng)建INSERT類

15、型的觸發(fā)器.假設建立一個按照院系名稱統(tǒng)計學生獎學金總額的表, 表的名稱為”按照學院統(tǒng)計獎學金總額表”,創(chuàng)建表的SQL語句如下:CREATE TABLE 按照學院統(tǒng)計獎學金總額表(院系名稱 CHAR(20) NOT NULL,獎學金總額 MONEY,CONSTRAINT PK_獎學金總額表 PRIMARY KEY NONCLUSTERED(院系名稱)由于目前學生基本表中只有三個學院的學生,所以使用下列SQL語句對”按照學院統(tǒng)計獎學金總額表”進行初始化:INSERT INTO 按照學院統(tǒng)計獎學金總額表 VALUES(信息學院,0)INSERT INTO 按照學院統(tǒng)計獎學金總額表 VALUES(商學

16、院,0)INSERT INTO 按照學院統(tǒng)計獎學金總額表 VALUES(法學院,0) 下面要在學生基本表中建立一個INSERT類型的觸發(fā)器,每當往學生基本表中插入一行數(shù)據(jù)時,要自動更新按照學院統(tǒng)計獎學金總額表中對應院系的獎學金總額列值.創(chuàng)建觸發(fā)器的SQL語句如下:CREATE TRIGGER 獎學金總額觸發(fā)器 ON 學生基本表FOR INSERT ASUPDATE 按照學院統(tǒng)計獎學金總額表 SET 獎學金總額=獎學金總額+獎學金 FROM INSERTED -觸發(fā)器檢查表 WHERE 按照學院統(tǒng)計獎學金總額表.院系名稱=INSERTED.院系名稱在查看這個觸發(fā)器作用之前,首先查看按照學院統(tǒng)計獎

17、學金總額表中數(shù)據(jù)行,使用下列SQL語句:SELECT * FROM 按照學院統(tǒng)計獎學金總額表下面開始往學生基本表中插入三行數(shù)據(jù).INSERT INTO 學生基本表(學號,姓名,性別,出生日期,院系名稱,獎學金) VALUES(T001,張小小,男,1988-10-2,信息學院,1500)INSERT INTO 學生基本表(學號,姓名,性別,出生日期,院系名稱,獎學金) VALUES(T002,李小小,男,1988-11-3,法學院,3000)INSERT INTO 學生基本表(學號,姓名,性別,出生日期,院系名稱,獎學金) VALUES(T003,汪小小,男,1989-2-23,信息學院,10

18、00)這時重新查詢按照學院統(tǒng)計獎學金總額表中數(shù)據(jù).就可看到結(jié)果了.這說明所創(chuàng)建的觸發(fā)器在起作用.例2: 創(chuàng)建UPDATE類型的觸發(fā)器下面在按照學院統(tǒng)計獎學金總額表中建立一個UPDATE類型的觸發(fā)器,要求每當”獎學金總額”列被修改時,顯示修改前后的信息.假設這個觸發(fā)器的名稱為”獎學金總額修改觸發(fā)器”, 則創(chuàng)建它的SQL語句如下:CREATE TRIGGER 獎學金總額修改觸發(fā)器ON 按照學院統(tǒng)計獎學金總額表FOR UPDATEASDECLARE OLD MONEY,NEW MONEYSELECT OLD=獎學金總額 FROM DELETEDSELECT NEW=獎學金總額 FROM INSERTEDPRINNT 修改之前 總額是:PRINT CONVERT(VARCHTR(10),OLD)PRINT 修改之后