第13章 存儲(chǔ)過程、用戶自定義函數(shù)與觸發(fā)器.ppt

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

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

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

4、使用的服務(wù)器。 (2) 選擇要?jiǎng)?chuàng)建存儲(chǔ)過程的數(shù)據(jù)庫(kù)，在左窗格中單擊“存儲(chǔ)過程”文件夾，此時(shí)在右窗格中顯式該數(shù)據(jù)庫(kù)的所有存儲(chǔ)過程，如圖13-1所示。,13.2 創(chuàng)建、修改和刪除存儲(chǔ)過程,(3) 右擊“存儲(chǔ)過程”文件夾，在彈出菜單中選擇【新建存儲(chǔ)過程】選項(xiàng)，打開創(chuàng)建存儲(chǔ)過程對(duì)話框，如下圖。,圖13-2 創(chuàng)建存儲(chǔ)過程對(duì)話框,13.2 創(chuàng)建、修改和刪除存儲(chǔ)過程,(4) 在“文本”編輯框中輸入存儲(chǔ)過程正文。 (5) 單擊“檢查語(yǔ)法”按鈕，檢查語(yǔ)法是否正確。 (6) 單擊“確定”按鈕，保存存儲(chǔ)過程。,提示： 新創(chuàng)建存儲(chǔ)過程名字包含在CREATE PROCEDURE語(yǔ)句中，不在保存時(shí)輸入。,13.2 創(chuàng)建、

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

6、該存儲(chǔ)過程相關(guān)的對(duì)象,13.2 創(chuàng)建、修改和刪除存儲(chǔ)過程,創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分： 參數(shù)，所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。 過程體，被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過程的語(yǔ)句； 返回狀態(tài)，返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。,13.2 創(chuàng)建、修改和刪除存儲(chǔ)過程,不含參數(shù)的存儲(chǔ)過程 例13-1：查詢學(xué)生成績(jī)。 CREATE PROCEDURE 查詢學(xué)生成績(jī) AS SELECT 學(xué)生表.姓名, 課程表.課程名, 選課表.成績(jī) FROM 選課表 INNER JOIN 學(xué)生表 ON 選課表.學(xué)號(hào) = 學(xué)生表.學(xué)號(hào) INNER JOIN 課程表

7、ON 選課表.課程號(hào) = 課程表.課程號(hào) 執(zhí)行：EXEC 查詢學(xué)生成績(jī),13.2 創(chuàng)建、修改和刪除存儲(chǔ)過程,例13-2：查找1100之間的完全平方數(shù)。 CREATE PROCEDURE 查找完全平方數(shù) AS declare n int set n=1 while n*n=100 begin print cast(n*n as varchar(5) set n=n+1 end 執(zhí)行：exec 查找完全平方數(shù),13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,參數(shù) 存儲(chǔ)過程和調(diào)用者之間需要通過參數(shù)來(lái)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。 例如：查詢學(xué)號(hào)為s2008001的c01課程的成績(jī)。輸入

8、參數(shù)：學(xué)號(hào)（s2008001）和課程號(hào)（c01） 輸出參數(shù)：成績(jī) SQL Server支持這兩類參數(shù)。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,輸入?yún)?shù) 輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過程傳送數(shù)據(jù)值。 定義存儲(chǔ)過程的輸入?yún)?shù) 必須在CREATE PROCEDURE語(yǔ)句中聲明一個(gè)或多個(gè)變量及數(shù)據(jù)類型。 例13-3：創(chuàng)建帶參數(shù)的存儲(chǔ)過程，輸入學(xué)生學(xué)號(hào)，返回學(xué)生姓名、性別等個(gè)人信息。 CREATE PROCEDURE dbo.查詢指定學(xué)生信息 學(xué)號(hào) varchar(10) AS select * from 學(xué)生表 where 學(xué)號(hào)=學(xué)號(hào) 提示：定義參數(shù)的數(shù)據(jù)類型需和表內(nèi)字段類型一致。,13.3 存儲(chǔ)過程的執(zhí)

9、行與參數(shù)傳遞,隨堂思考： (1) 擴(kuò)展例13-1，查詢指定學(xué)生指定課程成績(jī)； (2) 擴(kuò)展例13-2，增加輸入?yún)?shù)，要求顯示某整數(shù)范圍完全平方數(shù)。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,存儲(chǔ)過程的執(zhí)行 語(yǔ)法格式： EXECUTE return_status= procedure_name ;number|procedure_name_var parameter= value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE ,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程 在查詢分析器中執(zhí)行例13-3： exec 查詢指定學(xué)生信息 s200800

10、1 exec執(zhí)行（execute） 查詢指定學(xué)生信息存儲(chǔ)過程名 s2008001輸入?yún)?shù) 運(yùn)行結(jié)果： 提示： 需要根據(jù)輸入?yún)?shù)的數(shù)據(jù)類型加定界符。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,執(zhí)行時(shí)，參數(shù)可以由位置標(biāo)識(shí)，也可以由名字標(biāo)識(shí)。 例如，定義一個(gè)具有3個(gè)參數(shù)的存儲(chǔ)過程： CREATE PROC myproc val1 int, val2 int, val3 int AS . 參數(shù)以位置傳遞： EXEC myproc 10,20,15 參數(shù)以名字傳遞，每個(gè)值由對(duì)應(yīng)的參數(shù)名引導(dǎo)： EXEC myproc val2=20,val1=10,val3=15 按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性

11、。但是，按位置傳遞參數(shù)卻具有更快的速度。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,輸出參數(shù) 輸出參數(shù)允許存儲(chǔ)過程將數(shù)據(jù)值傳回調(diào)用程序。 用OUTPUT關(guān)鍵字指出能返回到調(diào)用它的存儲(chǔ)過程。 例13-4：創(chuàng)建存儲(chǔ)過程，查詢指定學(xué)生的某門課程成績(jī)。 CREATE PROCEDURE dbo.查詢成績(jī) 學(xué)號(hào) varchar(10), 課程號(hào) varchar(10), score int output AS select score=成績(jī) from 選課表 where 學(xué)號(hào)=學(xué)號(hào) and 課程號(hào)=課程號(hào),13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,執(zhí)行帶輸出參數(shù)的存儲(chǔ)過程 在查詢分析器中執(zhí)行例13-4： decla

12、re 學(xué)生成績(jī) int exec 查詢成績(jī) s2008001,c01,學(xué)生成績(jī) output select 學(xué)生成績(jī) 運(yùn)行結(jié)果： 提示： 輸出參數(shù)（返回值）需要提前聲明，數(shù)據(jù)類型應(yīng)同輸出參數(shù)的數(shù)據(jù)類型相匹配。 EXEC語(yǔ)句需要關(guān)鍵字OUTPUT以允許參數(shù)值返回變量。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,例13-5：編寫存儲(chǔ)過程“學(xué)生成績(jī)分析”，根據(jù)輸入?yún)?shù)“學(xué)號(hào)”指定的學(xué)生，計(jì)算該生的平均成績(jī)，若平均成績(jī)?cè)?5分及以上為優(yōu)秀，85分以下60分及以上為一般，60分以下為差，將內(nèi)容填入“學(xué)生考試評(píng)價(jià)”表中。 附：“學(xué)生考試評(píng)價(jià)”表結(jié)構(gòu)： 學(xué)號(hào) char(10) 平均成績(jī)int 考試評(píng)價(jià)varcha

13、r(10) 【學(xué)號(hào)】列是該表主鍵,CREATE PROCEDURE 學(xué)生成績(jī)分析 學(xué)號(hào) char(10) AS declare 平均分 as int,評(píng)價(jià) as varchar(10) select 平均分=avg(成績(jī)) from 選課表 where 學(xué)號(hào)=學(xué)號(hào) if 平均分=85 set 評(píng)價(jià)=優(yōu)秀 else begin if 平均分=60 set 評(píng)價(jià)=一般 else set 評(píng)價(jià)=差 end insert into 學(xué)生考試評(píng)價(jià)(學(xué)號(hào),平均成績(jī),考試評(píng)價(jià)) values(學(xué)號(hào),平均分,評(píng)價(jià)),13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,返回存儲(chǔ)過程的狀態(tài) 用RETURN語(yǔ)句定義返回值 存儲(chǔ)過程

14、可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。 如果存儲(chǔ)過程沒有顯式設(shè)置返回代碼的值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數(shù)，表示沒有成功執(zhí)行。也可以使用RETURN語(yǔ)句，用大于0或小于-99的整數(shù)來(lái)定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。,13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,例13-6：創(chuàng)建存儲(chǔ)過程，輸入課程號(hào)，返回課程名稱。在存儲(chǔ)過程中，用值15表示用戶沒有提供參數(shù)；值-101表示沒有輸入課程號(hào)；值0表示過程運(yùn)行沒有出錯(cuò)。 CREATE PROCEDURE dbo.查詢課程名 課程號(hào) as varchar(10)=null AS

15、if 課程號(hào)=null return 15 if not exists(select * from 課程表 where 課程號(hào)=課程號(hào)) return -101 select 課程名 from 課程表 where 課程號(hào)=課程號(hào),13.3 存儲(chǔ)過程的執(zhí)行與參數(shù)傳遞,捕獲返回狀態(tài)值 在執(zhí)行過程時(shí)，要正確接收返回的狀態(tài)值，必須使用語(yǔ)句：EXECUTE status_var=procedure_name 參數(shù) 例13-6的存儲(chǔ)過程查詢課程名執(zhí)行時(shí)使用以下語(yǔ)句： DECLARE return_status int EXEC return_status=查詢課程名 c01 IF return_statu

16、s=15 SELECT 語(yǔ)法錯(cuò)誤，未輸入?yún)?shù)! ELSE IF return_status=-101 SELECT 沒有找到該課程號(hào). 執(zhí)行時(shí)，對(duì)不同的輸入值返回不同的狀態(tài)值。,13.4 用戶自定義函數(shù),用戶自定義函數(shù)概述 內(nèi)置函數(shù)不能滿足用戶（應(yīng)用程序）需求，創(chuàng)建的自己定義的函數(shù) 參數(shù)：零個(gè)、一個(gè)或 多個(gè) 返回值：一個(gè) 單個(gè)數(shù)值 一個(gè)表 根據(jù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型,13.4 用戶自定義函數(shù),(1) 標(biāo)量函數(shù) 標(biāo)量函數(shù)返回一個(gè)確定類型的標(biāo)量值，其函數(shù)值類型為SQL Server的系統(tǒng)數(shù)據(jù)類型（除text、ntext、image、cursor、timestamp、tabl

17、e類型外）。函數(shù)體語(yǔ)句定義在BEGINEND語(yǔ)句內(nèi)。 (2) 內(nèi)嵌表值函數(shù) 內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個(gè)表。內(nèi)嵌表值函數(shù)的函數(shù)體不使用BEGINEND語(yǔ)句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當(dāng)于一個(gè)參數(shù)化的視圖。 (3) 多語(yǔ)句表值函數(shù) 多語(yǔ)句表值函數(shù)可以看作標(biāo)量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。其函數(shù)值也是一個(gè)表，但函數(shù)體用BEGINEND語(yǔ)句定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語(yǔ)句插入。,13.4 用戶自定義函數(shù),創(chuàng)建用戶自定義函數(shù),圖13-6 創(chuàng)建用戶自定義函數(shù)對(duì)話框,13.4 用戶自定義函數(shù),例13-7：創(chuàng)建標(biāo)量函數(shù)DatetoQuarter，將輸入的日

18、期數(shù)據(jù)轉(zhuǎn)換為該日期對(duì)應(yīng)的季度值。如輸入2006-8-5，返回3Q2006，表示2006年3季度。 CREATE FUNCTION DatetoQuarter(dqdate datetime) RETURNS char(6) AS BEGIN RETURN(datename(q,dqdate)+Q+datename(yyyy,dqdate) END,例13-8：創(chuàng)建標(biāo)量函數(shù)NumToStr，輸入阿拉伯?dāng)?shù)字09，輸出對(duì)應(yīng)的中文大寫。 CREATE FUNCTION NumToStr(num as int) RETURNS char(2) AS BEGIN declare ChineseCap as

19、 char(2) set ChineseCap=(case num when 0 then 零 when 1 then 壹 when 2 then 貳 when 3 then 叁 when 4 then 肆 when 5 then 伍 when 6 then 陸 when 7 then 柒 when 8 then 捌 when 9 then 玖 end) return ChineseCap END,13.4 用戶自定義函數(shù),例13-9：通過自定義函數(shù)根據(jù)輸入課程號(hào)，返回對(duì)應(yīng)的課程名。 CREATE FUNCTION F課程名 (courseID char(10) RETURNS char(20

20、) AS BEGIN declare courseName char(20) select courseName=課程名 from 課程表 where 課程號(hào)=courseID return courseName END,13.4 用戶自定義函數(shù),例13-10：創(chuàng)建一個(gè)內(nèi)嵌表值函數(shù)stuinfo，輸入學(xué)生學(xué)號(hào)，返回學(xué)生姓名及各科成績(jī)。 CREATE FUNCTION stuinfo (xh varchar(10) RETURNS table AS return(select 姓名,課程號(hào),成績(jī) from 學(xué)生表 inner join 選課表 on 學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào) where 選課表

21、.學(xué)號(hào)=xh),例13-11：創(chuàng)建多語(yǔ)句表值函數(shù)Stu_Info，根據(jù)輸入的學(xué)號(hào)、課程號(hào)，返回對(duì)應(yīng)的姓名、課程名和成績(jī)。 CREATE FUNCTION Stu_Info(學(xué)號(hào) varchar(10),課程號(hào) varchar(10) RETURNS stu_info table(stuName varchar(8), courseName varchar(20), score smallint) AS BEGIN declare stuName as varchar(8),courseName as varchar(20) declare score as smallint select st

22、uName=姓名 from 學(xué)生表 where 學(xué)號(hào)=學(xué)號(hào) select courseName=課程名 from 課程表 where 課程號(hào)=課程號(hào) select score=成績(jī) from 選課表 where 學(xué)號(hào)=學(xué)號(hào) and 課程號(hào)=課程號(hào) insert into stu_info values(stuName,courseName,score) return END,13.4 用戶自定義函數(shù),修改和刪除用戶自定義函數(shù) 用企業(yè)管理器修改用戶定義函數(shù)，選擇要修改函數(shù)，雙擊或單擊右鍵，從快捷菜單中選擇“屬性”選項(xiàng)，打開圖13-6所示的“用戶定義函數(shù)屬性”對(duì)話框。在該對(duì)話框中可以修改用戶定義函

23、數(shù)的函數(shù)體、參數(shù)等。 從快捷菜單中選擇“刪除”選項(xiàng)，打開“除去對(duì)象”對(duì)話框，則可刪除用戶自定義函數(shù)。,13.4 用戶自定義函數(shù),用戶自定義函數(shù)的使用 當(dāng)調(diào)用標(biāo)量值函數(shù)時(shí)，必須加上“所有者”，通常是dbo（但不是絕對(duì)，可以在企業(yè)管理器中的“用戶定義函數(shù)”中查看所有者） 當(dāng)調(diào)用表值函數(shù)時(shí)，可以只使用函數(shù)名。,13.4 用戶自定義函數(shù),例13-12：調(diào)用例13-7函數(shù)，返回當(dāng)前日期對(duì)應(yīng)的季度值。 select dbo.DatetoQuarter(getdate() 例13-13：調(diào)用例13-9函數(shù)，返回c02對(duì)應(yīng)的課程名。 select dbo.F課程名(c02) 例13-14：調(diào)用例13-10函數(shù)，返回學(xué)號(hào)為s2008001的學(xué)生姓名和各科成績(jī)。 select * from stuinfo(s2008001) 例13-15：調(diào)用例13-11，返回學(xué)號(hào)為 s2007031課程號(hào)為c01的學(xué)生姓名、課程名和成績(jī)。 select * from stu_info(s2007031,c01),例13-16：使用例13-8函數(shù)，完成099的數(shù)字大寫轉(zhuǎn)換。 declare num as i