《SQLServer数据库应用基础教程》第十章用户自定义函数

第十章用户自定义函数

1什么是函数？

是一组编译好的Transact-SQL语句，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。2函数类型：

SQLServer2005支持用户定义函数和内置函数。内置函数

是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。310.1用户自定义函数概述用户定义函数是用户定义的Transact-SQL函数，它可以拥有零个、一个或多个参数，必须返回单一的返回值。返回值可以是单个数值，也可以是一个表。4用户定义函数种类返回可更新数据表的函数返回不可更新数据表的函数返回标量值的函数若函数含单个SELECT语句且可更新，则返回的数据表可更新若函数含多个SELECT语句或一个不可更新的SELECT语句，则返回的数据表不可更新510.2创建用户自定义函数10.2.1 创建用户自定义函数CREATE

FUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])参数名，局部变量参数数据类型6RETURNSscalar_return_datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND用户自定义函数的返回值函数中返回值的表达式7SCHEMABINING选项可将函数绑定到它所引用的数据库对象function-optionENCRYPTION选项可以实现SQLServer加密包含CREATE

FUNCTION语句文本的系统表列8例：在Northwind库上创建自定义函数my_function1，该函数实现输入代表商品年销售额的money类型参数的值后返回字符串，如果年销售额大于10000，返回“热销商品”，否则返回“非热销商品”9USENorthwindgoCREATE

FUNCTION

my_function1

(@moneyinputmoney)RETURNSnvarchar(5)10BEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<10000SET@returnstring='非热销商品'ELSESET@returnstring='热销商品'RETURN@returnstringEND11在Northwind库上有一个记录各种产品在1997年销售额的视图，通过它来引用新建的my_function1，查看哪些商品属于热销商品USENorthwindgoSELECT

ProductName,ProductSales,dbo.my_function1(ProductSales)AS

DiscriptionFROM[Productsalesfor1997]WHERECategoryName=’DairyProducts’121310.2.2 查看用户自定义函数自定义函数的名称保存在sysobjects系统表中创建自定义函数的源代码保存在syscomments系统表中141.使用系统存储过程查看EXEC

sp_help(sp_helptext)<function-name>例：用系统存储过程sp_helptext

查看用户自定义函数my_funciton1的定义文本信息USENorthwindgoEXECsp_helptextmy_function1go15162.使用系统表或系统信息架构视图在SQL

Server中存在三个信息架构视图报告有关用户自定义函数的信息ROUTINESPARAMETERSROUTINE_COLUMNS这些信息架构视图也是基于系统表sysobjects和syscomments实现的17例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。

USENorthwindSELECT*fromsysobjectsWHEREtype='FN'go1810.3用户自定义函数类型自定义函数有三种类型标量函数内嵌表值函数多语句表值函数1910.3.1标量函数标量函数返回在RETURNS子句中定义的数据类型的单个数据值标量函数可重复调用20例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为‘

::’，并允许用户自行定义分隔符21CREATE

FUNCTIONmy_function2(@DATEdatetime,

@separatornvarchar(2)='::')RETURNSnvarchar(20)BEGINDECLARE

@returnstringnvarchar(20)22SET@returnstring='今天是'+CONVERT(nvarchar(5),

datepart(year,@date))+‘年’

+@separator+CONVERT(nvarchar(5),

datepart(month,@date))+‘月’

+@separator+CONVERT(nvarchar(5),

datepart(day,@date))+'日'RETURN@returnstringEND23如何调用新建立的标量函数呢？可以运行如下命令：

SELECT

dbo.my_function2(GETDATE(),’--‘)返回当天的年、月和日2410.3.2内嵌表值函数在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表可替代视图，可用在T-SQL查询中允许表或视图表达式的地方25视图：受限于单个SELECT

语句，不允许包含用户自己提供的参数内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强返回表的内嵌表值函数还可替换返回单个结果集的存储过程26CREATEFUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])27RETURNSTABLE[WITH<function-option>[,…n]][AS]RETURNSELECT_STATEMENT定义内嵌表值函数返回值返回值的单个select语句

指定表值函数的返回值为表28例:

在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function329USENorthwindGoCREATE

FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableAS30RETURN(SELECTCity,CompanyName,ContactName,

'Customers'AS

Relationship

FROMCustomersWHERECity=@RegionParameterUNIONSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter)31下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息

SELECT*FROMdbo.my_function3('Paris')go

32如果用户想通过视图“CustomerandSuppliersbyCity”来实现同样的目的，则使用如下查询命令：

SELECT*FROM[CustomerandSuppliersbyCity]WHERECity='Paris'go3310.3.3多语句表值函数多语句表值函数的主体中允许使用的语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE34CREATEFUNCTION[ower-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])35RETURNS@return_variableTABLE<table_type_definition>[WITH<function-option>[,…n]][AS]BEGIN

function_bodyRETURNEND函数体，由sql语句组成，返回表变量是TABLE变量，用于存储作为函数值返回的行36TABLE<table_type_definition>::=({column_definition|table_constraint}[,…])表的列声明表约束37例:

在Northwind库上利用视图Customer

andSuppliersbyCity，建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4，并将其与上例建立的内嵌表值函数my_function3对比38USENorthwindGOCREATEFUNCTIONmy_function4(@RegionParameternvarchar(25),@SelectParameterchar(9))RETURNS@my_tableTABLE(Citynvarchar(15)NULL,

CompanyNamenvarchar(40)NOT

NULL,

ContactNamenvarchar(30)NULL)39ASBEGININSERT@my_tableSELECTCity,

CompanyName,ContactName

FROM[Customer

andSuppliersbyCity]WHERECity=@RegionParameter

AND

Relationship=@SelectParameterRETURNEND40下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。命令如下：SELECT*FROMdbo.my_function4('Paris','Customers')goSELECT*FROMdbo.my_function4('Paris','Suppliers')go4110.4修改和删除用户自定义函数10.4.1修改用户自定义函数ALTER

FUNCTION[ower-name,]function-name([{@parameter-namescalar-parameter-datetype[=default]}[,…n]])RETURNSscalar-return-datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND42例：修改在Northwind库上创建的自定义函数my_function1，将区分商品是否热销的年销售额由当初的10000提高到20000ALTER

FUNCTIONmy_function1(@moneyinputmone