《用戶自定義函數(shù)》PPT課件.ppt

1、1/42,第16章 用戶自定義函數(shù),2/42,16.1 用戶自定義函數(shù)的基本概念,SQL Server允許創(chuàng)建用戶定義函數(shù) 用戶定義函數(shù)是可返回值的例程,3/42,用戶定義函數(shù)種類,返回可更新數(shù)據(jù)表的函數(shù),返回不可更新數(shù)據(jù)表的函數(shù),返回標(biāo)量值的函數(shù),若函數(shù)含單個SELECT語句且可更新，則返回的數(shù)據(jù)表可更新,若函數(shù)含多個SELECT語句或一個不可更新的SELECT語句，則返回的數(shù)據(jù)表不可更新,4/42,16.2 創(chuàng)建用戶自定義函數(shù),16.2.1創(chuàng)建用戶自定義函數(shù),CREATE FUNCTION owner-name, function-name (parameter-name scalar_p

2、arameter_datetype =default,n),參數(shù)名，局部變量,參數(shù)數(shù)據(jù)類型,5/42,RETURNS scalar_return_datatype WITH ,n AS BEGIN sql-statement RETURN scalar-expression END,用戶自定義函數(shù)的返回值,函數(shù)中返回值的表達式,6/42,SCHEMABINING選項可將函數(shù)綁定到它所引用的數(shù)據(jù)庫對象,function-option,ENCRYPTION選項可以實現(xiàn) SQL Server加密包含CREATE FUNCTION語句文本的系統(tǒng)表列,7/42,例：在Northwind庫上創(chuàng)建自定義函數(shù)

3、my_function1，通過向該函數(shù)輸入代表商品年銷售額的money類型參數(shù)的大小返回字符串，若年銷售額大于10000，返回“熱銷商品”，否則返回“非熱銷商品”,8/42,USE Northwind go CREATE FUNCTION my_function1 (moneyinput money) RETURNS nvarchar(5),9/42,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput10000 SET returnstring=非熱銷商品 ELSE SET returnstring=熱銷商品 RETURN returnst

4、ring END,10/42,在Northwind庫上有一個記錄各種 產(chǎn)品在1997年銷售額的視圖，通 過它來引用新建的my_function1， 查看哪些商品屬于熱銷商品 USE Northwind go SELECT ProductName, ProductSales, dbo.my_function1(ProductSales) AS Discription FROM Product sales for 1997 WHERE CategoryName=Dairy Products,11/42,12/42,16.2.2查看用戶自定義函數(shù),自定義函數(shù)的名稱保存在sysobjects系統(tǒng)表中,

5、創(chuàng)建自定義函數(shù)的源代碼保存在syscomments系統(tǒng)表中,13/42,1. 使用系統(tǒng)存儲過程查看,EXEC sp_help(sp_helptext) ,例：用系統(tǒng)存儲過程sp_helptext 查看用戶自定義函數(shù)my_funciton1的定義文本信息 USE Northwind go EXEC sp_helptext my_function1 go,14/42,15/42,2. 使用系統(tǒng)表或系統(tǒng)信息架構(gòu)視圖,在SQL Server中存在三個信息架構(gòu)視圖報告有關(guān)用戶自定義函數(shù)的信息,ROUTINES PARAMETERS ROUTINE_COLUMNS,這些信息架構(gòu)視圖也是基于系統(tǒng)表sysob

6、jects和syscomments實現(xiàn)的,16/42,例：使用系統(tǒng)表sysobjects查看數(shù)據(jù)庫Northwind上存在的所有用戶自定義函數(shù)的相關(guān)信息。 USE Northwind SELECT * from sysobjects WHERE type=FN go,17/42,16.3 用戶自定義函數(shù)類型,自定義函數(shù)有三種類型,標(biāo)量函數(shù),內(nèi)嵌表值函數(shù),多語句表值函數(shù),18/42,16.3.1 標(biāo)量函數(shù),標(biāo)量函數(shù)返回在 RETURNS子句中定義的數(shù)據(jù)類型的單個數(shù)據(jù)值,標(biāo)量函數(shù)可重復(fù)調(diào)用,19/42,例：創(chuàng)建標(biāo)量函數(shù)，要求將當(dāng)前系統(tǒng)日期轉(zhuǎn)化為年月日格式的字符串并返回，且默認(rèn)的分隔符為 : ，并允

7、許用戶自行定義分隔符,20/42,CREATE FUNCTION my_function2 (DATE datetime, separator nvarchar(2)=:) RETURNS nvarchar(20) BEGIN DECLARE returnstring nvarchar(20),21/42,SET returnstring=今天是 +CONVERT(nvarchar(5), datepart(year,date)+年 +separator +CONVERT(nvarchar(5), datepart(month,date)+月 +separator +CONVERT(nvarc

8、har(5), datepart(day,date)+日 RETURN returnstring END,22/42,如何調(diào)用新建立的標(biāo)量函數(shù)呢？ 可以運行如下命令： SELECT dbo.my_function2(GETDATE(),-),返回當(dāng)天的年、月和日,23/42,16.3.2 內(nèi)嵌表值函數(shù),在內(nèi)嵌表值函數(shù)中，RETURN子句中包含有一條單獨的SELECT語句，該語句的結(jié)果構(gòu)成了內(nèi)嵌表值函數(shù)所返回的表,可替代視圖，可用在T-SQL查詢中允許表或視圖表達式的地方,24/42,視圖：受限于單個 SELECT 語句，不允許包含用戶自己提供的參數(shù),內(nèi)嵌表值函數(shù)：可包含附加的語句，使函數(shù)所包含

9、的邏輯比視圖的邏輯更強,返回表的內(nèi)嵌表值函數(shù)還可替換返回單個結(jié)果集的存儲過程,25/42,例: 在Northwind數(shù)據(jù)庫上建立一個可以根據(jù)輸入的城市名返回所有該城市的客戶和供應(yīng)商的信息的內(nèi)嵌表值函數(shù)my_function3,26/42,USE Northwind Go CREATE FUNCTION my_function3 (RegionParameter nvarchar(25) RETURNS table AS,27/42,RETURN (SELECT City, CompanyName, ContactName, Customers AS Relationship FROM Cust

10、omers WHERE City=RegionParameter UNION SELECT City, CompanyName, ContactName, Suppliers FROM Suppliers WHERE City=RegionParameter),28/42,下面示例使用新建立的內(nèi)嵌表值函數(shù)my_function3來獲取“巴黎”市的所有客戶和供應(yīng)商的信息 SELECT * FROM dbo.my_function3(Paris) go,29/42,如果用戶想通過視圖“Customer and Suppliers by City”來實現(xiàn)同 樣的目的，則使用如下查詢命令： SELEC

11、T * FROM Customer and Suppliers by City WHERE City=Paris go,30/42,16.3.3 多語句表值函數(shù),多語句表值函數(shù)的主體中允許使用的語句,賦值 控制流 DECLARE SELECT 游標(biāo)操作 INSERT UPDATE DELETE EXECUTE,31/42,例: 在Northwind庫上利用視圖Customer and Suppliers by City，建立一個可以根據(jù)輸入的城市名和用戶要求返回所有該城市的客戶或供應(yīng)商的信息的多語句表值函數(shù)my_function4，并將其與上例建立的內(nèi)嵌表值函數(shù)my_function3對比,3

12、2/42,USE Northwind GO CREATE FUNCTION my_function4 (RegionParameter nvarchar(25), SelectParameter char(9) RETURNS my_table TABLE (City nvarchar(15) NULL, CompanyName nvarchar(40) NOT NULL, ContactName nvarchar(30) NULL),33/42,AS BEGIN INSERT my_table SELECT City, CompanyName, ContactName FROM Custom

13、er and Suppliers by City WHERE City=RegionParameter AND Relationship=SelectParameter RETURN END,34/42,下面舉例說明使用多語句表值函數(shù)my_function4分別獲取來自Paris的所有客戶和供應(yīng)商的信息。命令如下： SELECT * FROM dbo.my_function4(Paris,Customers) go SELECT * FROM dbo.my_function4(Paris,Suppliers) go,35/42,16.4 修改和刪除用戶自定義函數(shù),16.4.1 修改用戶自定義函

14、數(shù),CREATE FUNCTION ower-name, function-name ( parameter-name scalar-parameter-datetype =default,n) RETURNS scalar-return-datatype WITH ,n AS BEGIN sql-statement RETURN scalar-expression END,36/42,例：修改在Northwind庫上創(chuàng)建的自定義函數(shù)my_function1，將區(qū)分商品是否熱銷的年銷售額由當(dāng)初的10000提高到20000 ALTER FUNCTION my_function1 (moneyin

15、put money) RETURNS nvarchar(5),37/42,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput20000 SET returnstring=非熱銷商品 ELSE SET returnstring=熱銷商品 RETURN returnstring END,38/42,16.4.2 刪除用戶自定義函數(shù),DROP FUNCTION ower-name, function-name,例：刪除在Northwind庫上創(chuàng)建的自定義函數(shù)my_function1 DROP FUNCTION my_function1,39/42,16.4.3 設(shè)置用戶自定義函數(shù)的權(quán)限,設(shè)置自定義函數(shù)的權(quán)