數(shù)據(jù)庫應用基礎第九章用戶自定義函數(shù)ppt課件

1、程序運轉程序運轉USE pubsgoCREATE TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(他已添加了數(shù)據(jù)他已添加了數(shù)據(jù),10,1)instead of INSERTASraiserror(他不能添加數(shù)據(jù)他不能添加數(shù)據(jù),10,1)第九章第九章用戶自定義函數(shù)用戶自定義函數(shù) 14getdate() 得到當前系統(tǒng)日期時間得到當前系統(tǒng)日期時間year() 得到日期參數(shù)的年份得到日期參數(shù)的年份15169.1 用戶自定義函數(shù)概述用戶自定義函數(shù)概述 2 2 它可以擁有零個、一個或多個參它可以擁有零個、一個或多個參數(shù)，必需有前往值數(shù)，必需有前往值1

2、1 用戶定義函數(shù)是用戶定義的用戶定義函數(shù)是用戶定義的Transact-SQLTransact-SQL函數(shù)函數(shù)3 3 前往值可以是單個數(shù)值標量前往值可以是單個數(shù)值標量值，也可以是一個表值，也可以是一個表概念概念 171 創(chuàng)建步驟與方法創(chuàng)建步驟與方法 9.2 創(chuàng)建用戶自定義函數(shù)創(chuàng)建用戶自定義函數(shù)18例：創(chuàng)建一個自定義函數(shù)，計算某例：創(chuàng)建一個自定義函數(shù)，計算某人到如今為止的工齡工資人到如今為止的工齡工資思緒：思緒：工齡工資工齡工資=已任務年數(shù)已任務年數(shù) * 任務一年的工齡工資任務一年的工齡工資已任務年數(shù)已任務年數(shù)=當前時間當前時間 開場參與任務時間開場參與任務時間工齡工資工齡工資=(當前時間當前時間

3、-開場參與任務時間開場參與任務時間) *年工齡工資年工齡工資19例：在例：在Northwind庫上創(chuàng)建庫上創(chuàng)建自定義函數(shù)自定義函數(shù)my_function1，該函數(shù)實現(xiàn)輸入代表商品年該函數(shù)實現(xiàn)輸入代表商品年銷售額的銷售額的money類型參數(shù)的類型參數(shù)的值后前往字符串，假設年銷值后前往字符串，假設年銷售額大于售額大于10000，前往，前往“熱銷熱銷商品，否那么前往商品，否那么前往“非熱銷非熱銷商品商品USE NorthwindgoCREATE FUNCTION CREATE FUNCTION my_function1 my_function1 (moneyinput money)RETURNS n

4、varchar(5)前往值的數(shù)前往值的數(shù)據(jù)類型據(jù)類型IF moneyinput10000SET returnstring=非熱銷商品非熱銷商品BEGINENDDECLARE returnstring nvarchar(5)DECLARE returnstring nvarchar(5)ELSESET returnstring=熱銷商品熱銷商品RETURN returnstring前往的變量前往的變量在在Northwind庫上有一個記錄各種產品在庫上有一個記錄各種產品在2019年銷售額的視圖，經過它來援用新建年銷售額的視圖，經過它來援用新建的的my_function1，查看哪些商品屬于熱銷，查看

5、哪些商品屬于熱銷商品商品視圖視圖函數(shù)調用函數(shù)調用25商品稱號商品稱號銷售額銷售額銷售情況銷售情況Use 數(shù)據(jù)庫數(shù)據(jù)庫goSelect 函數(shù)名函數(shù)名(詳細參數(shù)值詳細參數(shù)值|字段名字段名)from 表名表名where 條件條件27 用戶自定義函數(shù)的稱號應是獨一的用戶自定義函數(shù)的稱號應是獨一的 一個函數(shù)最多可以定義一個函數(shù)最多可以定義1024 個參數(shù)，個參數(shù)，每個參數(shù)前用每個參數(shù)前用“符號標明符號標明 參數(shù)的作用范圍是整個函數(shù)參數(shù)的作用范圍是整個函數(shù) 參數(shù)只能替代常量，不能替代表名、參數(shù)只能替代常量，不能替代表名、列名或其它數(shù)據(jù)庫對象的稱號列名或其它數(shù)據(jù)庫對象的稱號 用戶自定義函數(shù)不支持輸出參數(shù)用戶

6、自定義函數(shù)不支持輸出參數(shù)留意：留意：2 查看用戶自定義函數(shù)查看用戶自定義函數(shù) 自定義函數(shù)的稱號保管在自定義函數(shù)的稱號保管在sysobjects系統(tǒng)表中系統(tǒng)表中創(chuàng)建自定義函數(shù)的源代碼保創(chuàng)建自定義函數(shù)的源代碼保管在管在syscomments系統(tǒng)表中系統(tǒng)表中 1運用系統(tǒng)存儲過程查看運用系統(tǒng)存儲過程查看EXEC sp_help(sp_helptext) EXEC sp_help(sp_helptext) 例：用系統(tǒng)存儲過程例：用系統(tǒng)存儲過程sp_helptext 查看用戶自定義函數(shù)查看用戶自定義函數(shù)my_funciton1的定義文本信息的定義文本信息USE NorthwindgoEXEC sp_hel

7、ptext my_function1go 2運用系統(tǒng)表或系統(tǒng)信息架構視圖運用系統(tǒng)表或系統(tǒng)信息架構視圖 在在SQL Server中存在三個信中存在三個信息架構視圖報息架構視圖報告有關用戶自告有關用戶自定義函數(shù)的信定義函數(shù)的信息息ROUTINESROUTINESPARAMETERSPARAMETERSROUTINE_COLUMNSROUTINE_COLUMNS這些信息架構視圖也是基于系統(tǒng)這些信息架構視圖也是基于系統(tǒng)表表sysobjects和和syscomments實現(xiàn)實現(xiàn)的的 例：運用系統(tǒng)表例：運用系統(tǒng)表sysobjects查看數(shù)據(jù)庫查看數(shù)據(jù)庫Northwind上存在的一切用戶自定義函數(shù)的上存在的

8、一切用戶自定義函數(shù)的相關信息。相關信息。9.3 用戶自定義函數(shù)類型用戶自定義函數(shù)類型 標量函數(shù)標量函數(shù)自定自定義函義函數(shù)有數(shù)有三種三種類型類型內嵌表值函數(shù)內嵌表值函數(shù)多語句表值函數(shù)多語句表值函數(shù) 1 標量函數(shù)標量函數(shù) 標量函數(shù)前往標量函數(shù)前往在在 RETURNS子子句中定義的數(shù)句中定義的數(shù)據(jù)類型的單個據(jù)類型的單個數(shù)據(jù)值數(shù)據(jù)值標量函標量函數(shù)可反數(shù)可反復調用復調用36例：創(chuàng)建標量函數(shù)，要求將當前系例：創(chuàng)建標量函數(shù)，要求將當前系統(tǒng)日期轉化為年月日格式的字符串統(tǒng)日期轉化為年月日格式的字符串并前往，且默許的分隔符為并前往，且默許的分隔符為 : ，并允許用戶自行定義分隔符并允許用戶自行定義分隔符conve

9、rt(數(shù)據(jù)類型，字段名，日期款式編號數(shù)據(jù)類型，字段名，日期款式編號)37BEGINDECLARE returnstring nvarchar(20)CREATE FUNCTION CREATE FUNCTION my_function2my_function2(date datetime, separator nvarchar(2)=:)RETURNS nvarchar(20)SET returnstring=今天是今天是+CONVERT(nvarchar(5), datepart(year,date)+年年+separator+CONVERT(nvarchar(5), datepart(mo

10、nth,date)+月月 +separator+CONVERT(nvarchar(5), datepart(day,date)+日日RETURN returnstringEND2 內嵌表值函數(shù)內嵌表值函數(shù) 在內嵌表值函數(shù)中，在內嵌表值函數(shù)中，RETURN子子句中包含有一條單獨的句中包含有一條單獨的SELECT語句，該語句的結果構成了內嵌語句，該語句的結果構成了內嵌表值函數(shù)所前往的表表值函數(shù)所前往的表 可替代視圖，可用在可替代視圖，可用在T-SQL查詢中允許表或視查詢中允許表或視圖表達式的地方圖表達式的地方 視圖：受限于單個視圖：受限于單個 SELECT 語語句，不允許包含用戶本人提供的句，不允

11、許包含用戶本人提供的參數(shù)參數(shù)內嵌表值函數(shù)：可包含附加的語內嵌表值函數(shù)：可包含附加的語句，使函數(shù)所包含的邏輯比視圖句，使函數(shù)所包含的邏輯比視圖的邏輯更強的邏輯更強前往表的內嵌表值函數(shù)還可交換前往表的內嵌表值函數(shù)還可交換前往單個結果集的存儲過程前往單個結果集的存儲過程例例: :在在NorthwindNorthwind數(shù)據(jù)庫上建立一個數(shù)據(jù)庫上建立一個可以根據(jù)輸入的城市名前往一切該可以根據(jù)輸入的城市名前往一切該城市的客戶和供應商的信息的內嵌城市的客戶和供應商的信息的內嵌表值函表值函my_function3my_function345假設已有一個視圖城市中客戶與供應商列表假設已有一個視圖城市中客戶與供應

12、商列表“Customer and Suppliers by CityUSE NorthwindGoCREATE FUNCTION CREATE FUNCTION my_function3my_function3(RegionParameter nvarchar(25)RETURNS tableasRETURNSELECT City, CompanyName, ContactName, SuppliersFROM SuppliersWHERE City=RegionParameter)(SELECT City, CompanyName, ContactName, Customers AS Rel

13、ationship FROM CustomersWHERE City=RegionParameterUNION 49下面例如運用新建立的內嵌表值函數(shù)下面例如運用新建立的內嵌表值函數(shù)my_function3來獲取來獲取“巴黎市的一切巴黎市的一切客戶和供應商的信息客戶和供應商的信息 3 多語句表值函數(shù)多語句表值函數(shù) 多語句表值多語句表值函數(shù)的主體函數(shù)的主體中允許運用中允許運用的語句的語句 賦值賦值控制流控制流DECLARESELECT游標操作游標操作INSERTUPDATEDELETEEXECUTE51例例: 在在Northwind庫上利用視圖庫上利用視圖Customer and Supplier

14、s by City某一某一城市的客戶與供應商，建立一個可以城市的客戶與供應商，建立一個可以根據(jù)輸入的城市名和用戶，要求前往一根據(jù)輸入的城市名和用戶，要求前往一切該城市的客戶或供應商的信息的多語切該城市的客戶或供應商的信息的多語句表值函數(shù)句表值函數(shù)my_function4，并將其與上，并將其與上例建立的內嵌表值函數(shù)例建立的內嵌表值函數(shù)my_function3對對比比 下面舉例闡明運用多語句表值函數(shù)下面舉例闡明運用多語句表值函數(shù)my_function4分別獲取來自分別獲取來自Paris的的一切客戶和供應商的信息。一切客戶和供應商的信息。529.4 修正和刪除用戶自定義函數(shù)修正和刪除用戶自定義函數(shù)

15、1 修正用戶自定義函數(shù)修正用戶自定義函數(shù) ALTER FUNCTION ower-name, function-nameALTER FUNCTION ower-name, function-name( parameter-name ( parameter-name scalar-parameter-datetype scalar-parameter-datetype =default,n)=default,n)RETURNS scalar-return-datatypeRETURNS scalar-return-datatypeWITH ,n WITH ,n ASASBEGINBEGINsql-statementsql-statementRETURN scalar-expressionRETURN scalar-expressionENDEND例：修正在例：修正在Northwind庫上創(chuàng)建庫上創(chuàng)建的自定義函數(shù)的自定義函數(shù)my_function1，將，將區(qū)分商品能否熱銷的年銷售額由區(qū)分商品能否熱銷的年銷售額由當初的當初的10000提高到提高到20000ALTER FUNCTION A