-用户自定义函数课件.ppt

1、第16章用户自定义函数 116.1 用户自定义函数的基本概念用户自定义函数的基本概念 SQLSQL Server Server允许创建用户定义函允许创建用户定义函数数用户定义函数是可返回值的例程用户定义函数是可返回值的例程 2用用户户定定义义函函数数种种类类返回可更返回可更新数据表新数据表的函数的函数 返回不可返回不可更新数据更新数据表的函数表的函数 返回标量返回标量值的函数值的函数 若函数含若函数含单单个个SELECTSELECT语句且语句且可更新，则返可更新，则返回的数据表可回的数据表可更新更新 若函数含若函数含多多个个SELECTSELECT语句语句或或一一个不可更新的个不可更新的SEL

2、ECTSELECT语句，则语句，则返回的数据表不返回的数据表不可更新可更新 316.2 创建用户自定义函数创建用户自定义函数 16.2.1创建用户自定义函数创建用户自定义函数 CREATECREATE FUNCTIONFUNCTION ownerowner-namename,function-name(parameterparameter-name scalar_parameter_datetype=default,n)参数名，参数名，局部变量局部变量 参数数参数数据类型据类型 4RETURNS scalar_return_datatypeWITHWITH ,n ASBEGINsql-stat

3、ementRETURN scalar-expressionEND用户自用户自定义函定义函数的返数的返回值回值 函数中返回值函数中返回值的表达式的表达式 5SCHEMABININGSCHEMABINING选项选项可将函数可将函数绑定到它所引绑定到它所引用的数据库对用的数据库对象象 function-optionENCRYPTIONENCRYPTION选项选项可以实现可以实现 SQLSQL Server加密包含加密包含CREATECREATE FUNCTIONFUNCTION语句文语句文本的系统表列本的系统表列6例：例：在在NorthwindNorthwind库上创建自库上创建自定义函数定义函数m

4、y_function1my_function1，通通过向该函数输入代表商品年过向该函数输入代表商品年销售额的销售额的moneymoney类型参数的大类型参数的大小返回小返回字符串字符串，若年销售额，若年销售额大于大于1000010000，返回，返回“热销商热销商品品”，否则返回，否则返回“非热销商非热销商品品”7USE NorthwindgoCREATECREATE FUNCTIONFUNCTION mymy_functionfunction1(moneyinput money)RETURNS nvarchar(5)8BEGINDECLAREDECLARE returnstring nvarc

5、har(5)IF moneyinput10000SET returnstring=非热销商品非热销商品ELSESET returnstring=热销商品热销商品RETURN returnstringEND9在在NorthwindNorthwind库上有一个记录各种库上有一个记录各种产品在产品在19971997年销售额的视图，通年销售额的视图，通过它来引用新建的过它来引用新建的my_function1my_function1，查看哪些商品属于热销商品查看哪些商品属于热销商品USE NorthwindgoSELECTSELECT ProductNameProductName,ProductSale

6、sProductSales,dbodbo.mymy_function1(ProductSales)ASAS DiscriptionFROM Product sales for 1997WHERE CategoryName=Dairy Products101116.2.2 查看用户自定义函数查看用户自定义函数 自定义函数的自定义函数的名称名称保存在保存在sysobjects系统表中系统表中创建自定义函数的创建自定义函数的源代码源代码保保存在存在syscomments系统表中系统表中 121.使用系统存储过程查看使用系统存储过程查看EXECEXEC sp_help(sp_helptext)例：例：

7、用系统存储过程用系统存储过程sp_helptext 查看用户自定义函数查看用户自定义函数my_funciton1的定义文本信息的定义文本信息USE USE NorthwindNorthwindgogoEXEC sp_EXEC sp_helptexthelptext my_function1 my_function1go go 13142.使用系统表或系统信息架构视图使用系统表或系统信息架构视图 在在SQLSQL ServerServer中中存在三个存在三个信息信息架构视图架构视图报告报告有关用户自定有关用户自定义函数的信息义函数的信息ROUTINESROUTINESPARAMETERSPARA

8、METERSROUTINEROUTINE_COLUMNSCOLUMNS这些这些信息架构视图信息架构视图也是基于系统也是基于系统表表sysobjectssysobjects和和syscommentssyscomments实现的实现的 15例：例：使用系统表使用系统表sysobjects查看数查看数据库据库Northwind上存在的所有用户上存在的所有用户自定义函数的相关信息。自定义函数的相关信息。USE NorthwindSELECT*from sysobjectsWHERE type=FNgo1616.3 用户自定义函数类型用户自定义函数类型 自定自定义函义函数有数有三种三种类型类型标量函数标

9、量函数内嵌表值函数内嵌表值函数多语句表值函数多语句表值函数 1716.3.1 标量函数标量函数 标量函数标量函数返回返回在在 RETURNSRETURNS子子句中定义的数句中定义的数据类型的单个据类型的单个数据值数据值标量函标量函数可重数可重复调用复调用18例：例：创建标量函数，要创建标量函数，要求将当前求将当前系统日期系统日期转化转化为为年月日年月日格式的字符串格式的字符串并返回，且默认的分隔并返回，且默认的分隔符为符为 :，并允许，并允许用户自行定义用户自行定义分隔符分隔符19CREATECREATE FUNCTIONFUNCTION my_function2(DATEDATE datet

10、ime,separator nvarchar(2)=:)RETURNS nvarchar(20)BEGINDECLAREDECLARE returnstring nvarchar(20)20SET returnstring=今天是今天是+CONVERTCONVERT(nvarchar(5),datepart(year,date)+年年 +separator+CONVERTCONVERT(nvarchar(5),datepart(month,date)+月月 +separator+CONVERTCONVERT(nvarchar(5),datepart(day,date)+日日RETURN ret

11、urnstringEND21如何调用新建立的标量函如何调用新建立的标量函数呢？数呢？可以运行如下命令：可以运行如下命令：SELECT dbo.my_function2(GETDATE(),-)返回当天的返回当天的年、月和日年、月和日2216.3.2 内嵌表值函数内嵌表值函数 在内嵌表值函数中，在内嵌表值函数中，RETURNRETURN子句子句中包含有一条单独的中包含有一条单独的SELECTSELECT语句，语句，该语句的结果构成了该语句的结果构成了内嵌表值函内嵌表值函数数所返回的表所返回的表 可替代视图，可用在可替代视图，可用在T-SQL查询中允许表或视查询中允许表或视图表达式的地方图表达式的

12、地方 23视图：视图：受限于单个受限于单个 SELECTSELECT 语句，不语句，不允许包含用户自己提供的参数允许包含用户自己提供的参数内嵌表值函数：内嵌表值函数：可包含附加的语可包含附加的语句，使函数所包含的逻辑比视图句，使函数所包含的逻辑比视图的逻辑更强的逻辑更强返回表的内嵌表值函数还可替换返回表的内嵌表值函数还可替换返回单个结果集的存储过程返回单个结果集的存储过程24例例:在在NorthwindNorthwind数据库上数据库上建立一个可以根据输入的建立一个可以根据输入的城市名返回所有该城市的城市名返回所有该城市的客户和供应商的信息的内客户和供应商的信息的内嵌表值函数嵌表值函数my_f

13、unction325USE NorthwindGoCREATECREATE FUNCTIONFUNCTION my_function3(RegionParameterRegionParameter nvarchar(25)RETURNS tableAS26RETURN(SELECT City,CompanyName,ContactNameContactName,CustomersCustomers ASAS Relationship FROM CustomersWHERE City=RegionParameterUNION SELECT City,CompanyName,ContactName

14、,SuppliersFROM SuppliersWHERE City=RegionParameter)27下面示例使用新建立的内嵌下面示例使用新建立的内嵌表值函数表值函数my_function3my_function3来获来获取取“巴黎巴黎”市的所有客户和市的所有客户和供应商的信息供应商的信息 SELECT*FROMdbo.my_function3(Paris)go 28如果用户想通过如果用户想通过视图视图“Customerand Suppliers by City”来实现同来实现同样的目的，则使用如下查询命令：样的目的，则使用如下查询命令：SELECT*FROM Customer and S

15、uppliers by CityWHERE City=Parisgo2916.3.3 多语句表值函数多语句表值函数 多语句表值多语句表值函数的主体函数的主体中允许使用中允许使用的语句的语句 赋值赋值控制流控制流DECLAREDECLARESELECTSELECT游标操作游标操作INSERTINSERTUPDATEUPDATEDELETEDELETEEXECUTEEXECUTE30例例:在在Northwind库上利用视图库上利用视图CustomerCustomer andand Suppliers by City，建立建立一个可以根据输入的城市名和用一个可以根据输入的城市名和用户要求返回所有该城

16、市的客户或户要求返回所有该城市的客户或供应商的信息的多语句表值函数供应商的信息的多语句表值函数my_function4，并将其与上例建立并将其与上例建立的内嵌表值函数的内嵌表值函数my_function3对比对比 31USE NorthwindGOCREATECREATE FUNCTION my_function4(RegionParameter nvarchar(25),SelectParameter char(9)RETURNS my_table TABLE(City nvarchar(15)NULL,CompanyNameCompanyName nvarchar(40)NOTNOT NU

17、LLNULL,ContactName nvarchar(30)NULL)32ASBEGININSERT my_table SELECT City,CompanyName,ContactName FROMFROM CustomerCustomer andand Suppliers by CityWHEREWHERE City=RegionParameterRegionParameter ANDAND Relationship=SelectParameterRETURNEND33下面举例说明使用多语句表值函数下面举例说明使用多语句表值函数my_function4my_function4分别获取来自

18、分别获取来自Paris的所的所有客户和供应商的信息。命令如下：有客户和供应商的信息。命令如下：SELECT*FROMdbo.my_function4(Paris,Customers)goSELECT*FROMdbo.my_function4(Paris,Suppliers)go3416.4 修改和删除用户自定义函数修改和删除用户自定义函数 16.4.1 修改用户自定义函数修改用户自定义函数 CREATECREATE FUNCTIONFUNCTION ower-name,function-name(parameter-name scalar-parameter-datetype=default,

19、n)RETURNS scalar-return-datatypeWITH ,n ASBEGINsql-statementRETURN scalar-expressionEND35例：例：修改在修改在Northwind库上创建库上创建的自定义函数的自定义函数my_function1，将将区分商品是否热销的年销售额由区分商品是否热销的年销售额由当初的当初的10000提高到提高到20000ALTERALTER FUNCTIONFUNCTION my_function1(moneyinput money)RETURNS nvarchar(5)36BEGINDECLAREDECLARE returnst

20、ring nvarchar(5)IF moneyinput20000SET returnstring=非热销商品非热销商品ELSESET returnstring=热销商品热销商品RETURN returnstringEND3716.4.2 删除用户自定义函数删除用户自定义函数 DROPDROP FUNCTIONFUNCTION owerower-name-name,function-name 例：例：删除在删除在NorthwindNorthwind库上创建库上创建的自定义函数的自定义函数my_function1my_function1 DROP FUNCTION my_function1 3

21、816.4.3 设置用户自定义函数的权限设置用户自定义函数的权限 设置自定义函数的权限类似于设设置自定义函数的权限类似于设置表或其他数据库对象的权限置表或其他数据库对象的权限要为用户授予要为用户授予 CREATECREATE FUNCTIONFUNCTION 权限权限才能进行创才能进行创建、修改或建、修改或删除自定义删除自定义函数的操作函数的操作39 对于对于标量值函数标量值函数，一般用户在使用此函一般用户在使用此函数前，必须先被授予数前，必须先被授予此函数上的此函数上的 EXECUTEEXECUTE 权限权限40如果函数是如果函数是表值函表值函数数，则用户在引用，则用户在引用函数前必须对函数函数前必须对函数具有具有 SELECTSELECT权限权限41若若CREATECREATE TABLETABLE或或ALTERALTER TABLETABLE语句在语句在CHECKCHECK约束、约束、DEFAULTDEFAULT约束、计算列中约束、计算列中引用了自定义函数，那引用了自定义函数，那么该表的所有者也要拥么该表的所有者也要拥有该函数有该函数 42