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

1、第13章 存儲過程、用戶自定義函數(shù)與觸發(fā)器,計(jì)算中心,本章內(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語句寫好并用一個(gè)指定的名稱存儲起來的語句集合。 課本的定義：是SQL Server服務(wù)器上一組預(yù)編譯的T-SQL語句，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。 舉例,13.1 存儲過程概述,為

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

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

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

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

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

7、ON 選課表.課程號 = 課程表.課程號 執(zhí)行：EXEC 查詢學(xué)生成績,13.2 創(chuàng)建、修改和刪除存儲過程,例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 存儲過程的執(zhí)行與參數(shù)傳遞,參數(shù) 存儲過程和調(diào)用者之間需要通過參數(shù)來交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。 例如：查詢學(xué)號為s2008001的c01課程的成績。輸入

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

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

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

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

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

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

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

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

16、s=15 SELECT 語法錯(cuò)誤，未輸入?yún)?shù)! ELSE IF return_status=-101 SELECT 沒有找到該課程號. 執(zhí)行時(shí)，對不同的輸入值返回不同的狀態(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ù)體語句定義在BEGINEND語句內(nèi)。 (2) 內(nèi)嵌表值函數(shù) 內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個(gè)表。內(nèi)嵌表值函數(shù)的函數(shù)體不使用BEGINEND語句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當(dāng)于一個(gè)參數(shù)化的視圖。 (3) 多語句表值函數(shù) 多語句表值函數(shù)可以看作標(biāo)量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。其函數(shù)值也是一個(gè)表，但函數(shù)體用BEGINEND語句定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語句插入。,13.4 用戶自定義函數(shù),創(chuàng)建用戶自定義函數(shù),圖13-6 創(chuàng)建用戶自定義函數(shù)對話框,13.4 用戶自定義函數(shù),例13-7：創(chuàng)建標(biāo)量函數(shù)DatetoQuarter，將輸入的日

18、期數(shù)據(jù)轉(zhuǎn)換為該日期對應(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，輸出對應(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ù)輸入課程號，返回對應(yīng)的課程名。 CREATE FUNCTION F課程名 (courseID char(10) RETURNS char(20

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

21、.學(xué)號=xh),例13-11：創(chuàng)建多語句表值函數(shù)Stu_Info，根據(jù)輸入的學(xué)號、課程號，返回對應(yīng)的姓名、課程名和成績。 CREATE FUNCTION Stu_Info(學(xué)號 varchar(10),課程號 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é)號=學(xué)號 select courseName=課程名 from 課程表 where 課程號=課程號 select score=成績 from 選課表 where 學(xué)號=學(xué)號 and 課程號=課程號 insert into stu_info values(stuName,courseName,score) return END,13.4 用戶自定義函數(shù),修改和刪除用戶自定義函數(shù) 用企業(yè)管理器修改用戶定義函數(shù)，選擇要修改函數(shù)，雙擊或單擊右鍵，從快捷菜單中選擇“屬性”選項(xiàng)，打開圖13-6所示的“用戶定義函數(shù)屬性”對話框。在該對話框中可以修改用戶定義函

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