數(shù)據(jù)庫應(yīng)用基礎(chǔ)第九章用戶自定義函數(shù)58張課件

1、復(fù)習(xí) 1 什么是存儲過程？2 什么是觸發(fā)器？1復(fù)習(xí) 1 什么是存儲過程？11 什么是存儲過程？系統(tǒng)存儲過程本地存儲過程例：my_procdure221 什么是存儲過程？系統(tǒng)存儲過程本地存儲過程例：my_pr程序運(yùn)行3程序運(yùn)行3按位置傳遞參數(shù)4按位置傳遞參數(shù)4使用參數(shù)名傳送參數(shù)值5使用參數(shù)名傳送參數(shù)值5USE pubsgoCREATE TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(你已添加了數(shù)據(jù),10,1)2 什么是觸發(fā)器？instead of INSERTASraiserror(你不能添加數(shù)據(jù),10,1)6USE pubsCREATE T

2、RIGGER my_trig觸發(fā)器的作用？約束觸發(fā)器強(qiáng)制數(shù)據(jù)完整性數(shù)據(jù)類型7觸發(fā)器的作用？約束觸發(fā)器強(qiáng)制數(shù)據(jù)完整性數(shù)據(jù)類型7第九章用戶自定義函數(shù) 8第九章8什么是函數(shù)？ 是一組編譯好的Transact-SQL語句，他們可以帶一個(gè)或一組數(shù)值做參數(shù)，也可以不帶參數(shù)，它返回一個(gè)數(shù)值、數(shù)值集合，或執(zhí)行一些操作。 函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫代碼。9什么是函數(shù)？ 是一組編譯好的Transact-SQL語函數(shù)類型 SQL Server 2019 支持用戶定義函數(shù)和內(nèi)置函數(shù)。 1 內(nèi)置函數(shù) 是一組預(yù)定義的函數(shù)，是Transact- SQL語言的一部分，按Transact-SQL參考中定義的方式

3、運(yùn)行且不能修改。10函數(shù)類型 SQL Server 2019 支持用戶定義聚合函數(shù)11聚合函數(shù)11系統(tǒng)函數(shù)12系統(tǒng)函數(shù)12日期函數(shù)13日期函數(shù)1314getdate() 得到當(dāng)前系統(tǒng)日期時(shí)間year() 得到日期參數(shù)的年份1414getdate() 得到當(dāng)前系統(tǒng)日期時(shí)間1415151515169.1 用戶自定義函數(shù)概述 2 它可以擁有零個(gè)、一個(gè)或多個(gè)參數(shù)，必須有返回值1 用戶定義函數(shù)是用戶定義的Transact-SQL函數(shù)3 返回值可以是單個(gè)數(shù)值（標(biāo)量值），也可以是一個(gè)表概念 16169.1 用戶自定義函數(shù)概述 2 它可以擁有零個(gè)、一個(gè)或多171 創(chuàng)建步驟與方法 9.2 創(chuàng)建用戶自定義函數(shù)17

4、171 創(chuàng)建步驟與方法 9.2 創(chuàng)建用戶自定義函數(shù)1718例：創(chuàng)建一個(gè)自定義函數(shù)，計(jì)算某人到現(xiàn)在為止的工齡工資思路：工齡工資=已工作年數(shù) * 工作一年的工齡工資已工作年數(shù)=當(dāng)前時(shí)間 開始參加工作時(shí)間工齡工資=(當(dāng)前時(shí)間-開始參加工作時(shí)間) *年工齡工資1818例：創(chuàng)建一個(gè)自定義函數(shù)，計(jì)算某人到現(xiàn)在為止的工齡工資思路19191919例：在Northwind庫上創(chuàng)建自定義函數(shù)my_function1，該函數(shù)實(shí)現(xiàn)輸入代表商品年銷售額的money類型參數(shù)的值后返回字符串，如果年銷售額大于10000，返回“熱銷商品”，否則返回“非熱銷商品”20例：在Northwind庫上創(chuàng)建自定義函數(shù)my_functi

5、USE NorthwindgoCREATE FUNCTION my_function1 (moneyinput money)RETURNS nvarchar(5)返回值的數(shù)據(jù)類型21USE NorthwindCREATE FUNCTION IF moneyinput10000SET returnstring=非熱銷商品BEGINENDDECLARE returnstring nvarchar(5)ELSESET returnstring=熱銷商品RETURN returnstring返回的變量22IF moneyinput10000BEGINDECLAR執(zhí)行命令23執(zhí)行命令23在Northwi

6、nd庫上有一個(gè)記錄各種產(chǎn)品在2019年銷售額的視圖，通過它來引用新建的my_function1，查看哪些商品屬于熱銷商品視圖函數(shù)調(diào)用24在Northwind庫上有一個(gè)記錄各種產(chǎn)品在2019年銷售額25商品名稱銷售額銷售情況2525商品名稱銷售額銷售情況25如何調(diào)用用戶自定義函數(shù)？Use 數(shù)據(jù)庫goSelect 函數(shù)名(具體參數(shù)值|字段名)from 表名where 條件26如何調(diào)用用戶自定義函數(shù)？Use 數(shù)據(jù)庫2627用戶自定義函數(shù)的名稱應(yīng)是惟一的一個(gè)函數(shù)最多可以定義1024 個(gè)參數(shù)，每個(gè)參數(shù)前用“”符號標(biāo)明參數(shù)的作用范圍是整個(gè)函數(shù)參數(shù)只能替代常量，不能替代表名、列名或其它數(shù)據(jù)庫對象的名稱用戶自

7、定義函數(shù)不支持輸出參數(shù)注意：2727用戶自定義函數(shù)的名稱應(yīng)是惟一的注意：272 查看用戶自定義函數(shù) 自定義函數(shù)的名稱保存在sysobjects系統(tǒng)表中創(chuàng)建自定義函數(shù)的源代碼保存在syscomments系統(tǒng)表中 282 查看用戶自定義函數(shù) 自定義函數(shù)的名稱保存在sysobj（1）使用系統(tǒng)存儲過程查看EXEC sp_help(sp_helptext) 例：用系統(tǒng)存儲過程sp_helptext 查看用戶自定義函數(shù)my_funciton1的定義文本信息USE NorthwindgoEXEC sp_helptext my_function1go 29（1）使用系統(tǒng)存儲過程查看EXEC sp_help(s

8、p_h3030（2）使用系統(tǒng)表或系統(tǒng)信息架構(gòu)視圖 在SQL Server中存在三個(gè)信息架構(gòu)視圖報(bào)告有關(guān)用戶自定義函數(shù)的信息ROUTINESPARAMETERSROUTINE_COLUMNS這些信息架構(gòu)視圖也是基于系統(tǒng)表sysobjects和syscomments實(shí)現(xiàn)的 31（2）使用系統(tǒng)表或系統(tǒng)信息架構(gòu)視圖 在SQL Server中例：使用系統(tǒng)表sysobjects查看數(shù)據(jù)庫Northwind上存在的所有用戶自定義函數(shù)的相關(guān)信息。32例：使用系統(tǒng)表sysobjects查看數(shù)據(jù)庫Northwin9.3 用戶自定義函數(shù)類型 標(biāo)量函數(shù)自定義函數(shù)有三種類型內(nèi)嵌表值函數(shù)多語句表值函數(shù) 339.3 用戶自

9、定義函數(shù)類型 標(biāo)量函數(shù)自定義函數(shù)有三種類型內(nèi)嵌1 標(biāo)量函數(shù) 標(biāo)量函數(shù)返回在 RETURNS子句中定義的數(shù)據(jù)類型的單個(gè)數(shù)據(jù)值標(biāo)量函數(shù)可重復(fù)調(diào)用341 標(biāo)量函數(shù) 標(biāo)量函數(shù)返回在 RETURNS子句中定義的數(shù)例：my_function135例：my_function13536例：創(chuàng)建標(biāo)量函數(shù)，要求將當(dāng)前系統(tǒng)日期轉(zhuǎn)化為年月日格式的字符串并返回，且默認(rèn)的分隔符為 : ，并允許用戶自行定義分隔符convert(數(shù)據(jù)類型，字段名，日期樣式編號)3636例：創(chuàng)建標(biāo)量函數(shù)，要求將當(dāng)前系統(tǒng)日期轉(zhuǎn)化為年月日格式的字37373737BEGINDECLARE returnstring nvarchar(20)CREAT

10、E FUNCTION my_function2(date datetime, separator nvarchar(2)=:)RETURNS nvarchar(20)38BEGINCREATE FUNCTION my_funcSET returnstring=今天是+CONVERT(nvarchar(5), datepart(year,date)+年+separator+CONVERT(nvarchar(5), datepart(month,date)+月 +separator+CONVERT(nvarchar(5), datepart(day,date)+日RETURN returnstri

11、ngEND39SET RETURN returnstring39執(zhí)行程序40執(zhí)行程序4041412 內(nèi)嵌表值函數(shù) 在內(nèi)嵌表值函數(shù)中，RETURN子句中包含有一條單獨(dú)的SELECT語句，該語句的結(jié)果構(gòu)成了內(nèi)嵌表值函數(shù)所返回的表 可替代視圖，可用在T-SQL查詢中允許表或視圖表達(dá)式的地方 422 內(nèi)嵌表值函數(shù) 在內(nèi)嵌表值函數(shù)中，RETURN子句中包含有視圖：受限于單個(gè) SELECT 語句，不允許包含用戶自己提供的參數(shù)內(nèi)嵌表值函數(shù)：可包含附加的語句，使函數(shù)所包含的邏輯比視圖的邏輯更強(qiáng)返回表的內(nèi)嵌表值函數(shù)還可替換返回單個(gè)結(jié)果集的存儲過程43視圖：受限于單個(gè) SELECT 語句，不允許包含用戶自己提供例

12、:在Northwind數(shù)據(jù)庫上建立一個(gè)可以根據(jù)輸入的城市名返回所有該城市的客戶和供應(yīng)商的信息的內(nèi)嵌表值函my_function344例:在Northwind數(shù)據(jù)庫上建立一個(gè)可以根據(jù)輸入的城市名45如果已有一個(gè)視圖（城市中客戶與供應(yīng)商列表）“Customer and Suppliers by City”4545如果已有一個(gè)視圖（城市中客戶與供應(yīng)商列表）45USE NorthwindGoCREATE FUNCTION my_function3(RegionParameter nvarchar(25)RETURNS tableas46USE NorthwindCREATE FUNCTION RETU

13、RNSELECT City, CompanyName, ContactName, SuppliersFROM SuppliersWHERE City=RegionParameter)(SELECT City, CompanyName, ContactName, Customers AS Relationship FROM CustomersWHERE City=RegionParameterUNION 47RETURNSELECT City, CompanyName執(zhí)行命令48執(zhí)行命令4849下面示例使用新建立的內(nèi)嵌表值函數(shù)my_function3來獲取“巴黎”市的所有客戶和供應(yīng)商的信息 49

14、49下面示例使用新建立的內(nèi)嵌表值函數(shù)my_function33 多語句表值函數(shù) 多語句表值函數(shù)的主體中允許使用的語句 賦值控制流DECLARESELECT游標(biāo)操作INSERTUPDATEDELETEEXECUTE503 多語句表值函數(shù) 多語句表值函數(shù)的主體中允許使用的語句 賦51例: 在Northwind庫上利用視圖Customer and Suppliers by City（某一城市的客戶與供應(yīng)商），建立一個(gè)可以根據(jù)輸入的城市名和用戶，要求返回所有該城市的客戶或供應(yīng)商的信息的多語句表值函數(shù)my_function4，并將其與上例建立的內(nèi)嵌表值函數(shù)my_function3對比 5151例: 在N

15、orthwind庫上利用視圖Customer下面舉例說明使用多語句表值函數(shù)my_function4分別獲取來自Paris的所有客戶和供應(yīng)商的信息。5252下面舉例說明使用多語句表值函數(shù)my_function4分別獲9.4 修改和刪除用戶自定義函數(shù) 1 修改用戶自定義函數(shù) ALTER FUNCTION ower-name, function-name( parameter-name scalar-parameter-datetype =default,n)RETURNS scalar-return-datatypeWITH ,n ASBEGINsql-statementRETURN scalar-expressionEND539.4 修改和刪除用戶自定義函數(shù) 1 修改用戶自定義函數(shù) 例：修改在Northwind庫上創(chuàng)建的自定義函數(shù)my_function1，將區(qū)分商品是否熱銷的年銷售額由當(dāng)初的10000提高到20000ALTER FUNCTION my_function1 (moneyinput money)RETURNS nvarchar(5)54例：修改在Northwind庫上創(chuàng)建的自定義函數(shù)my_funBEGINDECLARE returnstring nvarchar(5)IF moneyinput20000SET