存儲過程用戶自定義函數(shù)與觸發(fā)器課件

1、第十三章 存儲過程、用戶自定義函數(shù)與觸發(fā)器計算中心本章內(nèi)容13.1 存儲過程概述13.2 通過企業(yè)管理器創(chuàng)建、修改和刪除存儲過程13.3 存儲過程的執(zhí)行與參數(shù)傳遞13.4 用戶自定義函數(shù)13.5 觸發(fā)器及其作用、效果演示13.1 存儲過程概述存儲過程（Stored Procedure）是什么？簡單的說：存儲過程是將常用的或很復(fù)雜的工作，預(yù)先用T-SQL語句寫好并用一個指定的名稱存儲起來的語句集合。課本的定義：是SQL Server服務(wù)器上一組預(yù)編譯的T-SQL語句，用于完成某項任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。舉例13.1 存儲過程概述為什么要使用存儲過程？存儲過程在

2、創(chuàng)建時即在服務(wù)器上進行編譯，所以執(zhí)行起來比SQL語句快，且能減少網(wǎng)絡(luò)通信的負擔?？梢栽趩蝹€存儲過程中執(zhí)行一系列SQL語句，完成復(fù)雜的操作。 存儲過程可以重復(fù)使用，減少數(shù)據(jù)庫開發(fā)人員的工作量 。安全性高，可設(shè)定只有某些用戶才具有對指定存儲過程的使用權(quán)。13.1 存儲過程概述存儲過程的類型系統(tǒng)存儲過程例如：EXEC sp_helpdb用戶定義存儲過程：由用戶創(chuàng)建并能完成某一特定功能的存儲過程。臨時存儲過程擴展存儲過程例如：EXEC xp_cmdshell dir d:13.1 存儲過程概述存儲過程的功能(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值。(2)包含執(zhí)行數(shù)據(jù)庫操作的編程

3、語句，包括調(diào)用其他過程。(3)為調(diào)用過程或批處理返回一個狀態(tài)值，以表示成功或失敗(及失敗原因)。存儲過程特點13.2 創(chuàng)建、修改和刪除存儲過程創(chuàng)建存儲過程的指導(dǎo)原則避免出現(xiàn)存儲過程的擁有者和底層對象的擁有者不同的情況，建議由dbo用戶擁有數(shù)據(jù)庫中所有對象每個存儲過程完成單個任務(wù)命名本地存儲過程的時候，避免使用“sp_”前綴盡量少使用臨時存儲過程，以避免頻繁連接 tempdb 里的系統(tǒng)表不要直接從 syscomments 系統(tǒng)表里刪除項13.2 創(chuàng)建、修改和刪除存儲過程創(chuàng)建存儲過程(1) 啟動企業(yè)管理器，登錄到要使用的服務(wù)器。(2) 選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，在左窗格中單擊“存儲過程”文件夾，

4、此時在右窗格中顯式該數(shù)據(jù)庫的所有存儲過程，如圖13-1所示。圖13-1 企業(yè)管理器中顯示的存儲過程信息13.2 創(chuàng)建、修改和刪除存儲過程(3) 右擊“存儲過程”文件夾，在彈出菜單中選擇【新建存儲過程】選項，打開創(chuàng)建存儲過程對話框，如下圖。圖13-2 創(chuàng)建存儲過程對話框13.2 創(chuàng)建、修改和刪除存儲過程(4) 在“文本”編輯框中輸入存儲過程正文。(5) 單擊“檢查語法”按鈕，檢查語法是否正確。(6) 單擊“確定”按鈕，保存存儲過程。圖13-3 輸入存儲過程內(nèi)容輸入內(nèi)容區(qū)域提示：新創(chuàng)建存儲過程名字包含在CREATE PROCEDURE語句中，不在保存時輸入。13.2 創(chuàng)建、修改和刪除存儲過程修改存

5、儲過程(1) 在企業(yè)管理器中展開服務(wù)器組，再展開服務(wù)器。(2) 展開“數(shù)據(jù)庫”文件夾，再展開要修改存儲過程的數(shù)據(jù)庫。(3) 在要修改的存儲過程上右擊，并在彈出的快捷菜單中選擇【屬性】項，或雙擊該存儲過程，彈出“存儲過程屬性”對話框。圖13-4 控制臺目錄13.2 創(chuàng)建、修改和刪除存儲過程刪除存儲過程類似于刪除表操作，在存儲過程顯示列表中選擇要刪除的存儲過程（可以用ctrl或shift選多個）。右鍵單擊選中的存儲過程，在彈出的快捷菜單中選擇【刪除】項，打開“除去對象”對話框，如下圖，單擊【全部除去】按鈕，完成刪除。圖13-5 “除去對象”對話框顯示與該存儲過程相關(guān)的對象13.2 創(chuàng)建、修改和刪除

6、存儲過程創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分： 參數(shù)，所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。 過程體，被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句； 返回狀態(tài)，返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。13.3 存儲過程的執(zhí)行與參數(shù)傳遞參數(shù)存儲過程和調(diào)用者之間需要通過參數(shù)來交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。 例如：查詢學(xué)號為s2008001的c01課程的成績。輸入?yún)?shù)：學(xué)號（s2008001）和課程號（c01）輸出參數(shù)：成績SQL Server支持這兩類參數(shù)。13.3 存儲過程的執(zhí)行與參數(shù)傳遞輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。

7、定義存儲過程的輸入?yún)?shù)必須在CREATE PROCEDURE語句中聲明一個或多個變量及數(shù)據(jù)類型。例13-1：創(chuàng)建帶參數(shù)的存儲過程，輸入學(xué)生學(xué)號，返回學(xué)生姓名、性別等個人信息。CREATE PROCEDURE dbo.查詢指定學(xué)生信息 學(xué)號 varchar(10) ASselect * from 學(xué)生表 where 學(xué)號=學(xué)號提示：定義參數(shù)的數(shù)據(jù)類型需和表內(nèi)字段類型一致。13.3 存儲過程的執(zhí)行與參數(shù)傳遞存儲過程的執(zhí)行語法格式：EXECUTE return_status= procedure_name ;number|procedure_name_var parameter= value|var

8、iable OUTPUT|DEFAULT ,.n WITH RECOMPILE 13.3 存儲過程的執(zhí)行與參數(shù)傳遞執(zhí)行帶輸入?yún)?shù)的存儲過程在查詢分析器中執(zhí)行例13-1： exec 查詢指定學(xué)生信息 s2008001 exec執(zhí)行（execute） 查詢指定學(xué)生信息存儲過程名 s2008001輸入?yún)?shù)運行結(jié)果：提示：需要根據(jù)輸入?yún)?shù)的數(shù)據(jù)類型加定界符。13.3 存儲過程的執(zhí)行與參數(shù)傳遞執(zhí)行時，參數(shù)可以由位置標識，也可以由名字標識。例如，定義一個具有3個參數(shù)的存儲過程：CREATE PROC myproc val1 int, val2 int, val3 intAS .參數(shù)以位置傳遞：EXEC m

9、yproc 10,20,15參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：EXEC myproc val2=20,val1=10,val3=15按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。13.3 存儲過程的執(zhí)行與參數(shù)傳遞輸出參數(shù)輸出參數(shù)允許存儲過程將數(shù)據(jù)值傳回調(diào)用程序。用OUTPUT關(guān)鍵字指出能返回到調(diào)用它的存儲過程。例13-2：創(chuàng)建存儲過程，查詢指定學(xué)生的某門課程成績。CREATE PROCEDURE dbo.查詢成績 學(xué)號 varchar(10), 課程號 varchar(10),score int output ASselect score=成績f

10、rom 選課表 where 學(xué)號=學(xué)號 and 課程號=課程號 13.3 存儲過程的執(zhí)行與參數(shù)傳遞執(zhí)行帶輸出參數(shù)的存儲過程在查詢分析器中執(zhí)行例13-2：declare 學(xué)生成績 intexec 查詢成績 s2008001,c01,學(xué)生成績 outputselect 學(xué)生成績運行結(jié)果：提示：輸出參數(shù)（返回值）需要提前聲明，數(shù)據(jù)類型應(yīng)同輸出參數(shù)的數(shù)據(jù)類型相匹配。EXEC語句需要關(guān)鍵字OUTPUT以允許參數(shù)值返回變量。13.3 存儲過程的執(zhí)行與參數(shù)傳遞返回存儲過程的狀態(tài)用RETURN語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。如果存儲過程沒有顯式設(shè)置返回代碼的

11、值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于0或小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。13.3 存儲過程的執(zhí)行與參數(shù)傳遞例13-3：創(chuàng)建存儲過程，輸入課程號，返回課程名稱。在存儲過程中，用值15表示用戶沒有提供參數(shù)；值-101表示沒有輸入課程號；值0表示過程運行沒有出錯。CREATE PROCEDURE dbo.查詢課程名 課程號 as varchar(10)=nullASif 課程號=nullreturn 15if not exists(select * from 課程表 wh

12、ere 課程號=課程號)return -101select 課程名 from 課程表 where 課程號=課程號13.3 存儲過程的執(zhí)行與參數(shù)傳遞捕獲返回狀態(tài)值在執(zhí)行過程時，要正確接收返回的狀態(tài)值，必須使用語句：EXECUTE status_var=procedure_name 參數(shù)例13-3的存儲過程查詢課程名執(zhí)行時使用以下語句：DECLARE return_status intEXEC return_status=查詢課程名 c01IF return_status=15 SELECT 語法錯誤，未輸入?yún)?shù)!ELSE IF return_status=-101 SELECT 沒有找到該課程號

13、. 執(zhí)行時，對不同的輸入值返回不同的狀態(tài)值。13.4 用戶自定義函數(shù)用戶自定義函數(shù)概述根據(jù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型。(1) 標量函數(shù)標量函數(shù)返回一個確定類型的標量值，其函數(shù)值類型為SQL Server的系統(tǒng)數(shù)據(jù)類型（除text、ntext、image、cursor、timestamp、table類型外）。函數(shù)體語句定義在BEGINEND語句內(nèi)。(2) 內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個表。內(nèi)嵌表值函數(shù)的函數(shù)體不使用BEGINEND語句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當于一個參數(shù)化的視圖。(3) 多語句表值函數(shù)多語句表值函數(shù)可

14、以看作標量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。其函數(shù)值也是一個表，但函數(shù)體也用BEGINEND語句定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語句插入。13.4 用戶自定義函數(shù)創(chuàng)建用戶自定義函數(shù)圖13-6 創(chuàng)建用戶自定義函數(shù)對話框13.4 用戶自定義函數(shù)例13-4：創(chuàng)建一個用戶定義函數(shù)DatetoQuarter，將輸入的日期數(shù)據(jù)轉(zhuǎn)換為該日期對應(yīng)的季度值。如輸入2006-8-5，返回3Q2006，表示2006年3季度。CREATE FUNCTION DatetoQuarter(dqdate datetime)RETURNS char(6)ASBEGIN RETURN(datename(q,dqdate)+Q+d

15、atename(yyyy,dqdate)END13.4 用戶自定義函數(shù)例13-5：通過自定義函數(shù)根據(jù)輸入課程號，返回對應(yīng)的課程名。CREATE FUNCTION F課程名 (courseID char(10) RETURNS char(20) AS BEGIN declare courseName char(20)select courseName=課程名 from 課程表 where 課程號=courseIDreturn courseNameEND13.4 用戶自定義函數(shù)例13-6：創(chuàng)建一個表值函數(shù)stuinfo，輸入學(xué)生學(xué)號，返回學(xué)生姓名及各科成績。CREATE FUNCTION stui

16、nfo (xh varchar(10) RETURNS table AS return(select 姓名,課程號,成績from 學(xué)生表 inner join 選課表 on 學(xué)生表.學(xué)號=選課表.學(xué)號where 選課表.學(xué)號=xh)13.4 用戶自定義函數(shù)修改和刪除用戶自定義函數(shù)用企業(yè)管理器修改用戶定義函數(shù)，選擇要修改函數(shù)，雙擊或單擊右鍵，從快捷菜單中選擇“屬性”選項，打開圖13-6所示的“用戶定義函數(shù)屬性”對話框。在該對話框中可以修改用戶定義函數(shù)的函數(shù)體、參數(shù)等。從快捷菜單中選擇“刪除”選項，打開“除去對象”對話框，則可刪除用戶自定義函數(shù)。13.4 用戶自定義函數(shù)用戶自定義函數(shù)的使用當調(diào)用標

17、量值函數(shù)時，必須加上“所有者”，通常是dbo（但不是絕對，可以在企業(yè)管理器中的“用戶定義函數(shù)”中查看所有者）當調(diào)用表值函數(shù)時，可以只使用函數(shù)名。例13-7：調(diào)用例13-4函數(shù)，返回當前日期對應(yīng)的季度值。 select dbo.DatetoQuarter(getdate()例13-8：調(diào)用例13-6函數(shù)，返回學(xué)號為s2008001的學(xué)生姓名和各科成績。 select * from stuinfo(s2008001)13.5 觸發(fā)器及其作用、效果演示觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要是通過事件進行觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調(diào)用。當對某一表進行UPDATE、INSERT、DELETE操作時，SQL Server就會自動執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)