《SQL Server數(shù)據(jù)庫系統(tǒng)基礎(chǔ)》課件009

第9章存儲過程和觸發(fā)器9.1存儲過程概述9.2創(chuàng)建存儲過程9.3執(zhí)行存儲過程9.4查看和修改存儲過程9.5重命名和刪除存儲過程9.6觸發(fā)器9.7創(chuàng)建觸發(fā)器9.8查看、修改和刪除觸發(fā)器9.9觸發(fā)器的應用9.10實戰(zhàn)訓練小結(jié)

思考題

在前面的章節(jié)中，我們已經(jīng)學習了數(shù)據(jù)庫的編程，但是，如何來保存和方便地運行用戶存儲過程呢？這是我們進一步要關(guān)心的問題。本章任務：學會查找和使用系統(tǒng)存儲過程，編寫和運行用戶存儲過程，學會觸發(fā)器的設(shè)計和應用。9.1存儲過程概述9.1.1存儲過程的概念

SQLServer的存儲過程類似于編程語言中的過程。使用Transact-SQL語言編程可以把某些需要多次調(diào)用以實現(xiàn)某個特定任務的代碼段編寫成一個程序塊，將其保存在數(shù)據(jù)庫中，在需要使用時由SQLServer服務器通過它們的名稱來調(diào)用，這樣的程序塊就叫做存儲過程。存儲過程在創(chuàng)建時經(jīng)過語法檢查被編譯和優(yōu)化，調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時可以直接執(zhí)行。存儲過程有以下特點：

(1)存儲過程可以包含一條或多條Transact-SQL語句。(2)存儲過程可以接受輸入?yún)?shù)并返回輸出值。

(3)一個存儲過程可以調(diào)用另一個存儲過程。

(4)存儲過程會返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序。9.1.2存儲過程的優(yōu)點使用存儲過程有很多優(yōu)點，具體如下：

(1)執(zhí)行速度快。存儲過程在創(chuàng)建時已經(jīng)通過語法檢查和編譯，調(diào)用時可直接執(zhí)行，程序的運行效率高，其執(zhí)行速度要比標準SQL語句快得多。當含有大量SQL語句的批處理需要重復多次執(zhí)行時，定義為存儲過程可大大提高運行效率。

(2)有利于模塊化程序設(shè)計。存儲過程創(chuàng)建后可多次調(diào)用?？筛鶕?jù)不同的功能模式設(shè)計不同的存儲過程以供調(diào)用。(3)便于程序的維護和管理。當用戶對數(shù)據(jù)庫的使用功能改變時，只需對相應的存儲過程進行修改而不用修改應用程序。

(4)減少網(wǎng)絡通信量。存儲過程可包含大量對數(shù)據(jù)庫進行復雜操作的SQL語句，它的存儲執(zhí)行都在SQLServer服務器(數(shù)據(jù)庫)端，網(wǎng)絡用戶使用時只需發(fā)送一個調(diào)用語句就可以實現(xiàn)，大大減少了網(wǎng)絡上SQL語句的傳輸。

(5)保證系統(tǒng)的安全性?？梢栽诖鎯^程中設(shè)置用戶對數(shù)據(jù)的訪問權(quán)限，只允許用戶調(diào)用存儲過程而不允許直接對數(shù)據(jù)進行訪問，以充分發(fā)揮安全機制的作用。

(6)具有業(yè)務邏輯的保密性。對存儲過程中代表業(yè)務邏輯的程序語句可以加密，存儲過程一旦加密，即使是系統(tǒng)管理員也難以解密。9.1.3存儲過程的分類

SQLServer中的存儲過程可分為兩類，即系統(tǒng)存儲過程和用戶自定義存儲過程。

(1)系統(tǒng)存儲過程：安裝數(shù)據(jù)庫系統(tǒng)時由系統(tǒng)自動創(chuàng)建，主要存儲在master數(shù)據(jù)庫中，以sp_或xp_為前綴。系統(tǒng)存儲過程的功能主要是從系統(tǒng)表中獲取信息，通過系統(tǒng)存儲過程，SQLServer中的許多管理性或信息性的活動都可以被順利而有效地完成?？梢栽谄渌麛?shù)據(jù)庫中調(diào)用系統(tǒng)存儲過程，在調(diào)用時不必在存儲過程名前加上數(shù)據(jù)庫名。

(2)用戶自定義存儲過程：由用戶創(chuàng)建并完成某一特定功能的存儲過程。下面介紹用戶自定義存儲過程的創(chuàng)建和使用方法。9.2創(chuàng)建存儲過程創(chuàng)建存儲過程應遵守的規(guī)則如下：

(1)作為存儲過程名稱的標識符其長度最大為128個字符，且必須唯一。

(2)每個存儲過程最多可以使用1024個參數(shù)。

(3)存儲過程的最大容量有一定的限制。

(4)存儲過程支持多達32層嵌套。

(5)在對存儲過程命名時，最好與系統(tǒng)存儲過程名加以區(qū)分。在SQLServer中，可以使用以下三種方法創(chuàng)建存儲過程。

(1)使用SQLServer企業(yè)管理器創(chuàng)建存儲過程。

(2)使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。(3)使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過程。默認情況下，創(chuàng)建存儲過程的許可權(quán)歸數(shù)據(jù)庫的所有者，數(shù)據(jù)庫的所有者可以把許可權(quán)授給其他用戶。當創(chuàng)建存儲過程時，需要確定存儲過程的以下三個組成部分：

(1)所有輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。

(2)被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句。

(3)返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。9.2.1使用企業(yè)管理器創(chuàng)建存儲過程使用SQLServer企業(yè)管理器創(chuàng)建存儲過程的步驟如下：

(1)在SQLServer企業(yè)管理器中，選擇指定的服務器和數(shù)據(jù)庫，右擊要創(chuàng)建存儲過程的數(shù)據(jù)庫，在彈出的快捷菜單中依次選擇“新建”→“存儲過程”選項，如圖9-1所示，或者右擊數(shù)據(jù)庫中的存儲過程圖標，從彈出的快捷菜單中選擇“新建存儲過程”選項，如圖9-2所示，均會出現(xiàn)“新建存儲過程”對話框，如圖9-3所示。圖9-1選擇新建存儲過程窗口(1)圖9-2選擇新建存儲過程窗口(2)圖9-3“新建存儲過程”對話框(2)在文本框中可以輸入創(chuàng)建存儲過程的Transact-SQL語句，這里創(chuàng)建一個名稱為“顯示學生信息”的存儲過程，輸入的代碼如下：

CREATEPROCEDURE顯示學生信息ASSELECT班級表.班級名稱,學生表.學號,學生表.姓名,學生表.性別,

學生表.出生年月

FROM學生表INNERJOIN班級表ON班級表.班級編號=學生表.

班級編號存儲過程“顯示學生信息”完成的功能是：在學生表中查詢學生的姓名、性別、出生年月、地址身份證、學號和班級名稱等字段的內(nèi)容。

(3)輸入完畢單擊“檢查語法”按鈕進行語法檢查，檢查成功后系統(tǒng)會彈出如圖9-4所示的提示信息框。圖9-4語法檢查提示信息框(4)在提示信息框中單擊“確定”按鈕，再在存儲過程屬性框中單擊“確定”按鈕保存該存儲過程，并關(guān)閉該對話框。9.2.2使用Transact-SQL語句創(chuàng)建存儲過程可以使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過程。創(chuàng)建存儲過程前，應該注意下列事項：

(1)不能將CREATEPROCEDURE語句與其他SQL語句組合到單個批處理中。

(2)創(chuàng)建存儲過程的權(quán)限默認屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。

(3)存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標識符規(guī)則。

(4)只能在當前數(shù)據(jù)庫中創(chuàng)建存儲過程。

創(chuàng)建存儲過程的Transact-SQL語句其語法形式如下：

CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n]；

WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}[FORREPLICATION]ASsql_statement[,…n]其中，各參數(shù)的說明如下：●?procedure_name：用于指定所要創(chuàng)建存儲過程的名稱。存儲過程的命名必須符合標識符命名規(guī)則。在一個數(shù)據(jù)庫中或者對其所有者而言，存儲過程的名稱必須唯一?！?@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。用戶必須在執(zhí)行過程時提供每個聲明參數(shù)的值(除非定義了該參數(shù)的默認值)。存儲過程最多可以有2100個參數(shù)。●?data_type：用于指定參數(shù)的數(shù)據(jù)類型。在存儲過程中，所有的數(shù)據(jù)類型(包括text、ntext和image)均可以用作存儲過程的參數(shù)?！?VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集(由存儲過程動態(tài)構(gòu)造，內(nèi)容可以變化)。該參數(shù)僅適用于游標參數(shù)?！?Default：用于指定參數(shù)的默認值。如果定義了默認值，則不必指定該參數(shù)的值即可執(zhí)行過程。默認值必須是常量或空值。如果過程對該參數(shù)使用LIKE關(guān)鍵字，那么默認值中可以包含通配符(%、_、[]和[^])?！?OUTPUT：表明該參數(shù)是一個返回參數(shù)。該選項的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過程。text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標占位符?！?RECOMPILE：表明SQLServer不會保存該存儲過程的執(zhí)行計劃，該存儲過程每執(zhí)行一次都要重新編譯。●?ENCRYPTION：表示對存儲過程文本進行加密。在系統(tǒng)表syscomments中的text字段是包含CREATEPROCEDURE語句的存儲過程文本。使用ENCRYPTION關(guān)鍵字無法通過查看syscomments表來查看存儲過程的內(nèi)容?！?FORREPLICATION：用于指定該存儲過程只能在數(shù)據(jù)復制時使用。本選項不能和WITHRECOMPILE選項一起使用?！?AS：用于指定該存儲過程要執(zhí)行的操作。●?sql_statement：是存儲過程中包含的任意數(shù)目和類型的Transact-SQL語句。

【例9-1】創(chuàng)建一個存儲過程“學生成績信息”，完成的功能是在班組表、學生表、課程表和成績表中查詢以下字段：班級名稱、學號、姓名、性別、課程名稱、考試成績。

下列程序清單如圖9-5上半部分所示，下半部分是選中存儲過程中SELECT語句的運行結(jié)果。

--打開college數(shù)據(jù)庫

USEcollege--創(chuàng)建存儲過程

CREATEPROCEDURE學生成績信息

ASSELECT班級表.班級名稱,學生表.學號,學生表.姓名,學生表.性別,

課程表.課程名稱,成績表.考試成績

FROM班級表INNERJOIN

學生表ON班級表.班級編號=學生表.班級編號INNERJOIN

成績表ON學生表.學號=成績表.學號INNERJOIN

課程表ON成績表.課程號=課程表.課程號

GO圖9-5創(chuàng)建與運行存儲過程

【例9-2】創(chuàng)建一個帶有參數(shù)的存儲過程“學生個人成績”，該存儲過程根據(jù)傳入的學生編號，在學生表中查詢此學生的成績信息。程序清單如下：

--創(chuàng)建存儲過程

USEcollegeGOCREATEPROCEDURE學生個人成績

@學號char(8)ASSELECTdbo.學生表.姓名,dbo.學生表.學號,dbo.課程表.課程名稱,dbo.成績表.考試成績FROMdbo.成績表INNERJOINdbo.學生表ONdbo.成績表.學號=dbo.學生表.學號INNERJOINdbo.課程表ONdbo.成績表.課程號=dbo.課程表.課程號

WHERE學生表.學號=@學號

GO--運行存儲過程，查詢學號為05209006的成績。學生個人成績'05209006'

輸入上述語句并執(zhí)行，將得到如圖9-6所示的結(jié)果。圖9-6創(chuàng)建與運行帶輸入?yún)?shù)存儲過程【例9-3】如圖9-7所示，創(chuàng)建一個帶有參數(shù)的存儲過程“計算學生年齡”，該存儲過程根據(jù)傳入的學生編號，在學生表中計算此學生的年齡，并根據(jù)程序的執(zhí)行結(jié)果返回不同的值。程序執(zhí)行成功，返回整數(shù)0；如果執(zhí)行出錯，則返回錯誤號。

--創(chuàng)建存儲過程

USEcollegeGOalterPROCEDURE計算學生年齡

@學號char(8),@年齡tinyintOUTPUTAS--定義并初始化局部變量，用于保存返回值

DECLARE@Errint--,@年齡tinyintSET@Err=0--求此學生的年齡SELECT@年齡=YEAR(GETDATE())-YEAR(出生年月)FROM學生表WHERE學生表.學號=@學號--return@年齡--根據(jù)程序的執(zhí)行結(jié)果返回不同的值,IF(@@ERROR<>0)beginSET@Err=@@ERRORRETURN@Errendgodeclare@年齡tinyintexec計算學生年齡'05209003',@年齡outputselect@年齡圖9-7創(chuàng)建與運行帶輸出參數(shù)存儲過程9.2.3使用向?qū)?chuàng)建存儲過程使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程的步驟如下：

(1)在企業(yè)管理器中選中某個SQLServer服務器，這里選中l(wèi)ocal(代表本地機)，選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，這里選中college。選擇工具菜單中的“向?qū)А辈藛雾?，系統(tǒng)會彈出“選擇向?qū)А睂υ捒?，單擊向?qū)е小皵?shù)據(jù)庫”選項左邊的加號，選中“創(chuàng)建存儲過程向?qū)А边x項，如圖9-8所示。

(2)單擊“確定”按鈕，出現(xiàn)“歡迎使用創(chuàng)建存儲過程向?qū)А睂υ捒?，如圖9-9所示。

(3)單擊“下一步”按鈕，出現(xiàn)“選擇數(shù)據(jù)庫”對話框，如圖9-10所示。該對話框用于選擇創(chuàng)建存儲過程中使用的數(shù)據(jù)庫。圖9-8“選擇向?qū)А睂υ捒驁D9-9“歡迎使用創(chuàng)建存儲過程向?qū)А睂υ捒驁D9-10“選擇數(shù)據(jù)庫”對話框(4)在圖9-10中，選擇默認的數(shù)據(jù)庫college，單擊“下一步”按鈕，出現(xiàn)“選擇存儲過程”對話框，如圖9-11所示。在該對話框中，列出了所有可選擇的表，以及可以對表進行的數(shù)據(jù)庫操作，即插入、刪除和更新。這里要對班級表進行操作，因此選中班級表對應的插入、刪除并更新下面的復選框。

(5)單擊“下一步”按鈕，出現(xiàn)確認存儲過程信息對話框，如圖9-12所示。如果需要修改前面的設(shè)置，則可在該對話框中單擊“上一步”按鈕。圖9-11“選擇存儲過程”對話框圖9-12“正在完成創(chuàng)建存儲過程向?qū)А睂υ捒?6)選中名稱為“insert_班級表_1”的存儲過程，單擊“編輯”按鈕，出現(xiàn)“編輯存儲過程屬性”對話框，如圖9-13所示。在該對話框中，可以在“名稱”文本框中修改存儲過程的名稱，在下面的列表框中列出了所選表的所有字段，包括字段名稱、數(shù)據(jù)類型、長度和是否在存儲過程中使用。單擊“編輯SQL”按鈕，將會出現(xiàn)創(chuàng)建存儲過程的Transact-SQL語句的編輯界面，如圖9-14所示。用戶可以在已有Transact-SQL語句的基礎(chǔ)上進行編輯修改，然后單擊“分析”按鈕，執(zhí)行語法正確性檢驗。

(7)在圖9-14中，選中存儲過程insert_班級表_1，查看它們對應的SQL語句。

insert_班級表_1存儲過程對應的SQL語句如圖9-14所示。圖9-13“編輯存儲過程屬性”對話框圖9-14“編輯存儲過程SQL”對話框(8)編輯完各個存儲過程的屬性后，在圖9-14中單擊“確定”按鈕，即可完成存儲過程的創(chuàng)建任務。這里同時創(chuàng)建了三個存儲過程：insert_班級表_1、update_班級表_1和delete_班級表_1，分別完成對班級表中數(shù)據(jù)的插入、修改和刪除操作。創(chuàng)建成功后，系統(tǒng)會給出如圖9-15所示的存儲過程創(chuàng)建成功提示信息框。

(9)存儲過程創(chuàng)建完成后，選中企業(yè)管理器中的college數(shù)據(jù)庫，單擊其中的“存儲過程”，就可以看到新創(chuàng)建的這三個存儲過程。圖9-15存儲過程創(chuàng)建成功提示信息框9.3執(zhí)行存儲過程存儲過程創(chuàng)建成功后，將保存在數(shù)據(jù)庫中。在SQLServer中，可以使用EXECUTE命令直接執(zhí)行存儲過程，其語法形式如下：

[EXEC[UTE]][@return_status=]{procedure_name|@procedure_name_var}[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]

其中，各選項的說明如下：●?EXECUTE：執(zhí)行存儲過程的命令關(guān)鍵字。如果此語句是批處理中的第一條語句，則可以省略此關(guān)鍵字?！?@return_status：是一個可選的整型變量，保存存儲過程的返回狀態(tài)。這個變量在使用前必須先在批處理、存儲過程或函數(shù)中聲明?！?procedure__name：指定執(zhí)行的存儲過程的名稱?！?@procedure_name_var：局部定義變量名，代表存儲過程名稱。●?@parameter：在創(chuàng)建存儲過程時定義的過程參數(shù)。調(diào)用時向存儲過程所傳遞的參數(shù)值由value參數(shù)或@variable變量提供，或者使用DEFAULT關(guān)鍵字指定該參數(shù)的默認值，OUTPUT參數(shù)說明指定參數(shù)為返回參數(shù)。●?WITHRECOMPILE：指定在執(zhí)行存儲過程時重新編譯執(zhí)行計劃。

執(zhí)行存儲過程時，需要指定要執(zhí)行的存儲過程的名稱和參數(shù)，使用一個存儲過程去執(zhí)行一組Transact-SQL語句，可以在首次運行時被編譯，在編譯過程中把Transact-SQL語句從字符形式轉(zhuǎn)化為可執(zhí)行形式。

【例9-4】執(zhí)行前面創(chuàng)建的“顯示學生信息”存儲過程，它是一個無參的存儲過程。程序清單如下：

USEcollegeEXEC顯示學生信息或直接寫存儲過程的名稱：

USEcollegeGO

顯示學生信息

程序的執(zhí)行結(jié)果如下：姓名性別出生年月電話 地址張三男1987-02-0355502103 松花江路892弄43號李四女1986-12-0656565821 共和新路423弄456號唐寶家男1987-05-0155021456 中山路332弄55號顧葉男1985-08-2656897142 滬太路555弄973號李佳誠女1988-04-0454869218 老滬太路8529弄1號王安女1986-09-1855057694 國順路81弄120號李靜女1987-12-1956421308 福州路3489弄68號李朋男1988-10-0357610246 大華新村356弄99號劉興男1987-07-0154239187 江灣鎮(zhèn)4087弄167號

(所影響的行數(shù)為9行)

注意：如果省略EXECUTE關(guān)鍵字，則存儲過程必須是批處理中的第一條語句，否則會出錯?！纠?-5】執(zhí)行存儲過程“學生成績信息”。程序清單如下：

USEcollegeEXEC學生成績信息程序的執(zhí)行結(jié)果如下：班級名稱 學號姓名性別課程名稱考試成績

--------------------------------------------------------------------------------05網(wǎng)絡1班 05209001張三男C語言 9005網(wǎng)絡1班 05209001張三男高等數(shù)學 8805網(wǎng)絡1班 05209001張三男鄧小平理論6005網(wǎng)絡1班 05209002李四女C語言 7005網(wǎng)絡1班 05209002李四女高等數(shù)學 7805網(wǎng)絡1班 05209002李四女鄧小平理論8005網(wǎng)絡1班 05209003唐寶家男C語言 8505網(wǎng)絡1班 05209003唐寶家男高等數(shù)學 9005網(wǎng)絡1班 05209003唐寶家男鄧小平理論9005軟件班 05209004顧葉男C語言 7505軟件班 05209004顧葉男高等數(shù)學 7805軟件班 05209004顧葉男鄧小平理論 8505軟件班 05209005李佳誠女C語言 9005軟件班 05209005李佳誠女高等數(shù)學 8705軟件班 05209005李佳誠女鄧小平理論 6005軟件班 05209006王安女C語言 6005軟件班 05209006王安女高等數(shù)學 5905軟件班 05209006王安女鄧小平理論 8305網(wǎng)絡2班 05209007李靜女C語言 7205網(wǎng)絡2班 05209007李靜女高等數(shù)學 8005網(wǎng)絡2班 05209007李靜女鄧小平理論 75(所影響的行數(shù)為21行)

【例9-6】執(zhí)行存儲過程“學生個人成績”，該存儲過程有一個輸入?yún)?shù)“學號”，在執(zhí)行時需要傳入一個學號值。程序清單如下：

USEcollegeGOEXECUTE學生個人成績'05209006'

或

USEcollegeGOEXECUTE學生個人成績@學號='05209006'

程序的執(zhí)行結(jié)果如下：姓名學號課程名稱考試成績

-----------------------------------------------

王安05209006C語言 60

王安05209006高等數(shù)學 59

王安05209006鄧小平理論 83(所影響的行數(shù)為3行)【例9-7】執(zhí)行例9-3存儲過程“計算學生年齡”，該存儲過程有一個輸入?yún)?shù)“學號”，另外，還有一個輸出參數(shù)“年齡”。程序清單如下：

declare@年齡tinyintexec計算學生年齡'05209003',@年齡outputselect@年齡程序的執(zhí)行結(jié)果如下：本程序的執(zhí)行結(jié)果：

----209.4查看和修改存儲過程9.4.1查看存儲過程創(chuàng)建存儲過程之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表svscornments中?？梢允褂闷髽I(yè)管理器或系統(tǒng)存儲過程查看用戶創(chuàng)建的存儲過程。

1.使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程

(1)在企業(yè)管理器中，打開指定的服務器和數(shù)據(jù)庫項，指定服務器下的college數(shù)據(jù)庫，并單擊college中的“存儲過程”文件夾，此時在右窗格中就會顯示出college數(shù)據(jù)庫中的所有存儲過程，如圖9-16所示。圖9-16存儲過程顯示窗口(2)右擊要查看的存儲過程，這里右擊存儲過程“顯示學生信息”，從彈出的快捷菜單中選擇“屬性”選項，彈出“存儲過程屬性”對話框。在此對話框中能夠看到存儲過程的源代碼，如圖9-17所示。

(3)如果從彈出的快捷菜單中依次選擇“所有任務”→“顯示相關(guān)性”選項，則會彈出“相關(guān)性”對話框，顯示與所選擇的存儲過程有依賴關(guān)系的其他數(shù)據(jù)庫對象的名稱，如圖9-18所示。圖9-17“存儲過程屬性”對話框圖9-18“相關(guān)性”對話框2.使用系統(tǒng)存儲過程查看用戶創(chuàng)建的存儲過程除了使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程外，也可以使用系統(tǒng)存儲過程查看。可供使用的系統(tǒng)存儲過程及其語法形式如下：●sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型。

sp_help[[@obj_name=]name]

參數(shù)name為要查看的存儲過程的名稱?！駍p_helptext：用于顯示存儲過程的源代碼。

sp_helptext[[@obj_name=]name]

參數(shù)name為要查看的存儲過程的名稱?！駍p_depends：用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象。

sp_depende[@obj_name=]'object'

參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱?！駍p_stored_procedures：用于返回當前數(shù)據(jù)庫中的存儲過程列表。

sp_stored_procedures[[@sp_name=]'name'][，[@sp_owner=]'owner'][，[@qualifier=]'qualifier']

其中，[@sp_name=]'name'用于指定返回目錄信息的過程名；[@sp_owner=]'owner'用于指定過程所有者的名稱；[@qualifier=]'qualifier'用于指定過程限定符的名稱。【例9-8】使用系統(tǒng)存儲過程查看“計算學生年齡”存儲過程的參數(shù)及其數(shù)據(jù)類型。程序清單如下：

--查看參數(shù)及其數(shù)據(jù)類型

USEcollegeGOsp_help計算學生年齡

GO

程序的執(zhí)行結(jié)果如圖9-19所示。圖9-19程序執(zhí)行結(jié)果顯示窗口【例9-9】使用系統(tǒng)存儲過程查看“學生成績信息”存儲過程的相關(guān)數(shù)據(jù)庫對象。程序清單如下：

--查看相關(guān)的數(shù)據(jù)庫對象

USEcollegeGOsp_depends學生成績信息

GO

程序的執(zhí)行結(jié)果如圖9-20所示。圖9-20程序執(zhí)行結(jié)果顯示窗口9.4.2修改存儲過程

1.使用企業(yè)管理器修改存儲過程使用企業(yè)管理器可以很方便地修改存儲過程的定義。在企業(yè)管理器中，展開存儲過程，右擊要修改的存儲過程，從彈出的快捷菜單中選擇“屬性”選項，將會出現(xiàn)存儲過程的“屬性”對話框。在該對話框中，可以直接修改定義該存儲過程的Transact-SQL語句。單擊“檢查語法”按鈕，可以進行語法檢查；單擊“權(quán)限”按鈕，可以修改用戶執(zhí)行該存儲過程的權(quán)限。2.使用Transact-SQL語句修改存儲過程使用Transact-SQL語言中的ALTERPROCEDURE命令可以更改先前通過執(zhí)行CREATEPROCEDURE語句創(chuàng)建的過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程。其語法形式如下：

ALTERPROC[EDURE]procedure_name[；number][{@parameterdata-type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]

修改存儲過程時，應該注意以下幾點：

(1)如果在CREATEPROCEDURE語句中使用過參數(shù)，那么在ALTERPROCEDURE語句中也應該使用這些參數(shù)。

(2)每次只能修改一個存儲過程。

(3)存儲過程的創(chuàng)建者、db_owner和db_ddladmin的成員擁有執(zhí)行ALTERPROCEDURE語句的許可，其他用戶不能使用。

(4)用ALTERPROCEDURE更改的存儲過程的權(quán)限和啟動屬性保持不變。

【例9-10】修改前面創(chuàng)建的“顯示學生信息”存儲過程，使之完成以下功能：根據(jù)傳入的學號在學生表、課程表和成績表中查詢此學生的班級、姓名、性別、考試課程名稱和考試分數(shù)。

程序清單如下：

--修改存儲過程

USEcollegeGOALTERPROCEDURE顯示學生信息

@學號varchar(10)ASSELECT班級編號,姓名,性別,課程表.課程名稱,成績表.總評成績

FROM學生表,課程表,成績表

WHERE學生表.學號=@學號

AND學生表.學號=成績表.學號

AND課程表.課程號=成績表.課程號

GO

執(zhí)行修改后的顯示學生信息的存儲過程：

USEcollegeGO

顯示學生信息'05209006'GO

程序的執(zhí)行結(jié)果如下：班級編號姓名性別課程名稱總評成績

-----------------------------------------------------------052005王安女C語言 65052005王安女高等數(shù)學 69052005王安女鄧小平理論 84(所影響的行數(shù)為3行)9.5重命名和刪除存儲過程9.5.1重命名存儲過程

1.使用企業(yè)管理器修改存儲過程名稱方法是：在企業(yè)管理器中，右擊要操作的存儲過程名稱，從彈出的快捷菜單中選擇“重命名”選項，修改該存儲過程的名稱，最后在彈出的確認重命名對話框中單擊“是”按鈕即可。

2.使用系統(tǒng)存儲過程修改存儲過程名稱修改存儲過程的名稱也可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下：

sp_rename原存儲過程名稱，新存儲過程名稱

【例9-11】使用系統(tǒng)存儲過程將“顯示學生信息”存儲過程的名稱修改為“學生信息資料”。程序清單如下：

USEcollegeGOSp_rename顯示學生信息,學生信息資料

GO

程序的執(zhí)行結(jié)果如下：

object已重命名為'學生信息資料'。

注意:

更改對象名的任一部分都可能破壞腳本和存儲過程。9.5.2刪除存儲過程

1.使用企業(yè)管理器刪除存儲過程在企業(yè)管理器中，右擊要刪除的存儲過程，從彈出的快捷菜單中選擇“刪除”選項，彈出“除去對象”對話框，單擊“全部除去”按鈕，即可完成刪除操作。在刪除該對象之前，單擊“顯示相關(guān)性”按鈕，可以查看與該存儲過程有依賴關(guān)系的其他數(shù)據(jù)庫對象名稱。

2.使用Transact-SQL語句刪除存儲過程刪除存儲過程也可以使用Transact-SQL語言中的DROP命令，DROP命令可以將若干個存儲過程或者存儲過程組從當前數(shù)據(jù)庫中刪除，其語法形式如下：

DROPprocedure{procedure}[，...n]【例9-12】使用DROP命令一次刪除update_班級表_1和delete_班級表_1兩個存儲過程。程序清單如下：

USEcollegeGODROPprocedureupdate_班級表_1，delete_班級表_1GO

程序執(zhí)行結(jié)果如下：命令已成功完成。如果程序返回了上面的執(zhí)行結(jié)果，則表示已成功刪除了update_班級表_1和delete_班級表_1這兩個存儲過程。9.6觸發(fā)器9.6.1觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹過的一般的存儲過程。一般的存儲過程通過存儲過程名稱被直接調(diào)用，而觸發(fā)器主要通過事件進行觸發(fā)而被執(zhí)行。觸發(fā)器是一個功能強大的工具，它隨時監(jiān)視數(shù)據(jù)表，當表中數(shù)據(jù)發(fā)生變化時自動執(zhí)行。觸發(fā)器可以用于SQLServer約束、默認值和規(guī)則的完整性檢查，還可以完成用普通約束難以實現(xiàn)的復雜功能。

當在某一個數(shù)據(jù)表中插入、修改或者刪除記錄時，SQLServer就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。在觸發(fā)器中可以查詢其他數(shù)據(jù)表，甚至復雜的SQL語句。觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語句被當作一次事務處理，如果這次事務未獲得成功，則SQLServer會自動返回該事務執(zhí)行前的狀態(tài)。和CHECK約束相比較，觸發(fā)器可以強制實現(xiàn)更加復雜的數(shù)據(jù)完整性，而且可以引用其他表中的字段。9.6.2觸發(fā)器的優(yōu)點觸發(fā)器具有以下優(yōu)點：

(1)觸發(fā)器是自動執(zhí)行的。在對表中的數(shù)據(jù)做了任何修改(比如手工輸入或者應用程序采取的操作)之后立即被激活。

(2)觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進行級聯(lián)更改。例如，可以在學生表的學號列上寫入一個刪除觸發(fā)器，以使成績表中的各匹配行采取刪除操作。該觸發(fā)器用學號列作為唯一鍵，在成績表中對各匹配行進行定位。

(3)觸發(fā)器可以強制限制。這些限制比用CHECK約束所定義的更復雜。與CHECK約束不同的是，觸發(fā)器可以引用其他表中的列。9.6.3觸發(fā)器的類型觸發(fā)器可以分為AFTER觸發(fā)器和INSTEADOF觸發(fā)器兩種。

(1)?AFTER觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動(INSERT、UPDATE和DELETE操作)完成以后才被觸發(fā)。可以對變動的數(shù)據(jù)進行檢查，如果發(fā)現(xiàn)錯誤，則將拒絕接受或回滾變動的數(shù)據(jù)。AFTER觸發(fā)器只能在表上定義。在同一個數(shù)據(jù)表中可以創(chuàng)建多個AFTER觸發(fā)器。

(2)?INSTEADOF觸發(fā)器：INSTEADOF觸發(fā)器是SQLServer2000中新增的功能。這種類型的觸發(fā)器將在數(shù)據(jù)變動以前被觸發(fā)，即用執(zhí)行觸發(fā)器定義的操作取代變動數(shù)據(jù)的操作(INSERT、UPDATE和DELETE操作)。INSTEADOF觸發(fā)器可以在表或視圖上定義。在表或視圖上，每個INSERT、UPDATE和DELETE語句最多可以定義一個INSTEADOF觸發(fā)器。9.7創(chuàng)?建?觸?發(fā)?器在SQLServer中，可以使用企業(yè)管理器或者Transact-SQL語句創(chuàng)建觸發(fā)器。在創(chuàng)建觸發(fā)器時應該注意以下幾個問題：

(1)?CREATETRIGGER語句必須是批處理中的第一個語句。將該批處理中隨后的其他所有語句解釋為CREATETRIGGER語句定義的一部分。

(2)創(chuàng)建觸發(fā)器的權(quán)限默認分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。

(3)觸發(fā)器為數(shù)據(jù)庫對象，其名稱必須遵循標識符的命名規(guī)則。

(4)只能在當前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，但觸發(fā)器可以引用當前數(shù)據(jù)庫以外的對象。(5)?TRUNCATETABLE語句不會引發(fā)DELETE觸發(fā)器。

(6)?WRITETEXT語句不會引發(fā)INSERT或UPDATE觸發(fā)器。9.7.1使用企業(yè)管理器創(chuàng)建觸發(fā)器使用企業(yè)管理器創(chuàng)建觸發(fā)器的步驟如下：

(1)在企業(yè)管理器中展開指定的服務器和數(shù)據(jù)庫，這里展開LOCAL服務器下面的college數(shù)據(jù)庫，右擊學生表，從彈出的快捷菜單中依次選擇“所有任務”→“管理觸發(fā)器”選項，將出現(xiàn)“觸發(fā)器屬性”對話框，如圖9-21所示。圖9-21“觸發(fā)器屬性”對話框(2)在“觸發(fā)器屬性”對話框的“名稱”文本框中選擇“新建”，然后在文本框中輸入創(chuàng)建觸發(fā)器的文本。在創(chuàng)建一個觸發(fā)器時必須指定以下幾項內(nèi)容：●觸發(fā)器的名稱；●需要監(jiān)視數(shù)據(jù)操作的表；●觸發(fā)器將由數(shù)據(jù)的增、刪、改中的什么事件觸發(fā)；●執(zhí)行觸發(fā)操作的程序語句。在文本框中輸入以下文本：

CREATETRIGGERtr_學生_InsertON學生表

FORINSERTASDECLARE@標示char(30)SET@標示="你插入了一條新記錄!"PRINT@標示上面這段程序的功能是創(chuàng)建一個由INSERT事件觸發(fā)的觸發(fā)器，當在學生表中插入一條新記錄時，該觸發(fā)器給出“你插入了一條新記錄!”的提示信息。

(3)單擊“檢查語法”按鈕，可以檢查語法是否正確，顯示結(jié)果如圖9-22所示。

(4)單擊“應用”按鈕，在名稱下拉列表中出現(xiàn)新創(chuàng)建的tr_學生_Insert觸發(fā)器的名稱，單擊“確定”按鈕，即可關(guān)閉該對話框，成功創(chuàng)建觸發(fā)器。圖9-22語法檢查結(jié)果對話框

【例9-13】創(chuàng)建tr_學生_Insert觸發(fā)器后，查看向?qū)W生表中插入數(shù)據(jù)時此觸發(fā)器所完成的功能。在查詢分析器中輸入以下SQL語句：

USEcollegeGOinsertinto學生表

(學號,姓名,性別,出生年月,班級編號,電話,地址,身份證)values(‘05209030’,‘程晨’,‘女’,‘1986-11-11’,‘052005’,‘65214213’,‘松花江路33弄5號603室',)GO

程序的執(zhí)行結(jié)果如下：你插入了一條新記錄!(所影響的行數(shù)為1行)9.7.2使用Transact-SQL語句創(chuàng)建觸發(fā)器使用Transact-SQL語言中的CREATETRIGGER命令也可以創(chuàng)建觸發(fā)器，其中需要指定觸發(fā)器所監(jiān)視的表、觸發(fā)器執(zhí)行的事件和觸發(fā)器的所有指令。創(chuàng)建觸發(fā)器的過程類似于創(chuàng)建存儲過程，其語法形式如下：

CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{(FOR|AFTER|INSTEADOF){[DELETE][，][INSERT][，][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS[{IFUPDATE(column)[(AND|OR)UPDATE(column)][,...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[,...n]}]sql_statement[,...n]}}

其中，各參數(shù)的說明如下：●?trigger_name：用于指定觸發(fā)器的名稱。觸發(fā)器的名稱必須符合SQLServer標識符規(guī)則，并且其名稱在當前數(shù)據(jù)庫中必須是唯一的。另外，還可以選擇是否指定觸發(fā)器所有者的名稱?！?table|view：用于指定在其上執(zhí)行觸發(fā)器的表或視圖，有時稱為觸發(fā)器表或觸發(fā)器視圖。另外，還可以選擇是否指定表或視圖的所有者名稱?！?WITHENCRYPTION：用于加密syscomments表中包含CREATETRIGGER語句文本的條目。使用WITHENCRYPTION可防止將觸發(fā)器作為SQLServer復制的一部分發(fā)布。●?AFTER：用于規(guī)定此觸發(fā)器只有在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認設(shè)置。注意，該類型觸發(fā)器僅能在表上創(chuàng)建，而不能在視圖上定義該觸發(fā)器?！?INSTEADOF：用于規(guī)定執(zhí)行的是觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而用觸發(fā)器替代觸發(fā)語句的操作。在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEADOF觸發(fā)器。INSTEADOF觸發(fā)器不能在WITHCHECKOPTION的可更新視圖上定義。如果向指定的WITHCHECKOPTION選項的可更新視圖上添加INSTEADOF觸發(fā)器，則SQLServer將產(chǎn)生一個錯誤。用戶必須用ALTERVIEW刪除該選項后才能定義INSTEADOF觸發(fā)器?！?{[DELETE][,][INSERT][,][UPDATE]}：用于指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項。在觸發(fā)器定義中允許以任意順序組合這些關(guān)鍵字。如果指定的選項多于一個，則需用逗號分隔這些選項?！?WITHAPPEND：用于指定應該添加現(xiàn)有類型的其他觸發(fā)器。只有當兼容級別(指某一數(shù)據(jù)庫行為與以前版本的SQLServer的兼容程度)是65或更低時，才需要使用該可選子句。如果兼容級別是70或更高，則不必使用該子句?！?NOTFORREPLICATION：表示當復制進程更改觸發(fā)器所涉及的表時，不應執(zhí)行該觸發(fā)器?！?AS：觸發(fā)器要執(zhí)行的操作?！?sql_statement：觸發(fā)器的條件和操作。觸發(fā)器條件指定其他準則，以確定DELETE、INSERT或UPDATE語句是否導致執(zhí)行觸發(fā)器操作?！?IFUPDATE(column)：用于測試在指定的列上進行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因為在ON子句中指定了表名，所以在IFUPDATE子句的列名前不要包含表名。若要測試在多個列上進行的INSERT或UPDATE操作，則應在第一個操作后指定單獨的UPDATE(column)子句。在INSERT操作中，IFUPDATE將返回TRUE值，因為這些列插入了顯式值或隱性值(NULL)?！?IF(COLUMNS_UPDATED()：用于測試是否插入或更新了所涉及的列，僅用于INSERT或者UPDATE觸發(fā)器?！?bitwise_operator：用于比較運算的位邏輯運算符?！?updated_bitmask：整型位掩碼，表示實際更新或插入的列。例如，表t1包含列C1、C2、C3、C4和C5。假定表t1上有UPDATE觸發(fā)器，若要檢查列C2、C3和C4是否都已更新，則指定值14；若要檢查是否只有列C2已更新，則指定值2?！?comparison_operator：比較運算符。使用等號(=)檢查updated_bitmask中指定的所有列是否都實際進行了更新。使用大于號(>)檢查updated_bitmask中指定的任一列或某些列是否已更新?！?column_bitmask：檢查列的整型位掩碼，用來檢查是否已更新或插入了這些列。注意：當創(chuàng)建觸發(fā)器時，如果使用了相同名稱的觸發(fā)器，則后面建立的觸發(fā)器將會覆蓋前面建立的觸發(fā)器。用戶不能在系統(tǒng)表上創(chuàng)建用戶自定義的觸發(fā)器。在創(chuàng)建觸發(fā)器時，可以使用兩個特殊的臨時表，它們分別是inserted表和deleted表，這兩個表都存在于內(nèi)存中。

inserted表中存儲著被INSERT和UPDATE語句影響的新的數(shù)據(jù)行。執(zhí)行INSERT或UPDATE語句時，新的數(shù)據(jù)行被添加到基本表中，同時這些數(shù)據(jù)行的備份被復制到inserted臨時表中。deleted表中存儲著被DELETE和UPDATE語句影響的舊的數(shù)據(jù)行。執(zhí)行DELETE或UPDATE語句時，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到deleted表中。在基本表和deleted表中一般不存在相同的數(shù)據(jù)行。一個UPDATE操作實際上是由一個DELETE操作和一個INSERT操作組成的。在執(zhí)行UPDATE操作時，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后將新的數(shù)據(jù)行同時插入基本表和inserted表中。下面舉例說明如何使用Transact-SQL語句創(chuàng)建觸發(fā)器，并在觸發(fā)器中使用inserted和deleted臨時表?！纠?-14】創(chuàng)建一個AFTER觸發(fā)器，要求實現(xiàn)以下功能：在成績表上創(chuàng)建一個插入、更新類型的觸發(fā)器“檢查分數(shù)觸發(fā)器”，當在考試成績字段中插入或修改考試分數(shù)后，觸發(fā)該觸發(fā)器，檢查分數(shù)是否在0～100之間。．程序清單如下：

USEcollegeGOCREATETRIGGER檢查分數(shù)觸發(fā)器

ON成績表

FORINSERT,UPDATEASIFUPDATE(考試成績)PRINT'AFTER觸發(fā)器開始執(zhí)行……'BEGINDECLARE@分數(shù)intSELECT@分數(shù)=(SELECT考試成績

FROMinserted)IF(@分數(shù)NOTbetween0and100)PRINT'輸入的分數(shù)超出范圍，請重新輸入的考試分數(shù)!'ENDGO

創(chuàng)建了“檢查分數(shù)觸發(fā)器”之后，在查詢分析器中輸入以下SQL語句：

USEcollegeGOPRINT'在成績表中插入記錄時觸發(fā)器執(zhí)行結(jié)果：'PRINT''INSERTINTO成績表VALUES('05209030','059001',35,-25,5)INSERTINTO成績表VALUES('05209030','059002',35,225,100)GOPRINT'在成績表中修改記錄時觸發(fā)器執(zhí)行結(jié)果：'PRINT''UPDATE成績表SET考試成績=115WHERE學號='05209030'and課程號='059001'UPDATE成績表SET考試成績=-65WHERE學號='05209030'and課程號='059002'

執(zhí)行上面的SQL語句，結(jié)果如下：在成績表中插入記錄時觸發(fā)器執(zhí)行結(jié)果：

AFTER觸發(fā)器開始執(zhí)行……

輸入的分數(shù)超出范圍，請重新輸入的考試分數(shù)!(所影響的行數(shù)為1行)

在成績表中修改記錄時觸發(fā)器執(zhí)行結(jié)果如下：

AFTER觸發(fā)器開始執(zhí)行……

輸入的分數(shù)超出范圍，請重新輸入的考試分數(shù)!(所影響的行數(shù)為1行)【例9-15】創(chuàng)建一個AFTER觸發(fā)器，要求實現(xiàn)以下功能：在學生表上創(chuàng)建一個刪除類型的觸發(fā)器tr_學生刪除，當在學生表中刪除某一條記錄后，觸發(fā)該觸發(fā)器，在成績表中刪除與此學號對應的記錄。

USEcollegeGOCREATETRIGGERtr_學生刪除

ON學生表

FORDELETEASPRINT'刪除觸發(fā)器開始執(zhí)行……'DECLARE@學號char(10)PRINT'把在學生表中刪除的記錄的學號賦值給局部變量@學號。'SELECT@學號=學號

FROMdeletedPRINT'開始查找并刪除成績表中的相關(guān)記錄……'DELETEFROM成績表

WHERE學號=@學號

PRINT'刪除了成績表中的學號為'+RTRIM(@學號)+'的記錄。'GO

創(chuàng)建了tr_學生刪除觸發(fā)器之后，在查詢分析器中輸入以下SQL語句：

USEcollegeGODELETEFROM學生表WHERE學號='05209006'DELETEFROM成績表WHERE學號='05209006'GO

程序的執(zhí)行結(jié)果如下：刪除觸發(fā)器開始執(zhí)行……

把在學生表中刪除的記錄的學號賦值給局部變量@學號。開始查找并刪除成績表中的相關(guān)記錄……(所影響的行數(shù)為3行)(所影響的行數(shù)為1行)

刪除了成績表中的學號為05209006的記錄。

(所影響的行數(shù)為0行)

【例9-16】創(chuàng)建一個INSTEADOF觸發(fā)器，要求實現(xiàn)以下功能：在教師表上創(chuàng)建一個刪除類型的觸發(fā)器“不可刪除”，當在教師表中刪除