16-用户自定义函数省公开课一等奖全国示范课微课金奖课件

第16章用户自定义函数1第1页16.1用户自定义函数基本概念SQLServer允许创建用户定义函数用户定义函数是可返回值例程2第2页用户定义函数种类返回可更新数据表函数返回不可更新数据表函数返回标量值函数若函数含单个SELECT语句且可更新，则返回数据表可更新若函数含多个SELECT语句或一个不可更新SELECT语句，则返回数据表不可更新3第3页16.2创建用户自定义函数16.2.1 创建用户自定义函数CREATE

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

FUNCTION语句文本系统表列6第6页例：在Northwind库上创建自定义函数my_function1，经过向该函数输入代表商品年销售额money类型参数大小返回字符串，若年销售额大于10000，返回“热销商品”，不然返回“非热销商品”7第7页USENorthwindgoCREATE

FUNCTION

my_function1

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

ProductName,ProductSales,dbo.my_function1(ProductSales)ASDiscriptionFROM[Productsalesfor1997]WHERECategoryName=’DairyProducts’10第10页11第11页16.2.2 查看用户自定义函数自定义函数名称保留在sysobjects系统表中创建自定义函数源代码保留在syscomments系统表中12第12页1.使用系统存放过程查看EXECsp_help(sp_helptext)<function-name>例：用系统存放过程sp_helptext查看用户自定义函数my_funciton1定义文本信息USENorthwindgoEXECsp_helptextmy_function1go13第13页14第14页2.使用系统表或系统信息架构视图在SQL

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

USENorthwindSELECT*fromsysobjectsWHEREtype='FN'go16第16页16.3用户自定义函数类型自定义函数有三种类型标量函数内嵌表值函数多语句表值函数17第17页16.3.1标量函数标量函数返回在RETURNS子句中定义数据类型单个数据值标量函数可重复调用18第18页例：创建标量函数，要求将当前系统日期转化为年月日格式字符串并返回，且默认分隔符为‘::’，并允许用户自行定义分隔符19第19页CREATE

FUNCTIONmy_function2(@DATEdatetime,

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

@returnstringnvarchar(20)20第20页SET@returnstring='今天是'+CONVERT(nvarchar(5),datepart(year,@date))+‘年’+@separator+CONVERT(nvarchar(5),datepart(month,@date))+‘月’+@separator+CONVERT(nvarchar(5),datepart(day,@date))+'日'RETURN@returnstringEND21第21页怎样调用新建立标量函数呢？能够运行以下命令：

SELECT

dbo.my_function2(GETDATE(),’--‘)返回当日年、月和日22第22页16.3.2内嵌表值函数在内嵌表值函数中，RETURN子句中包含有一条单独SELECT语句，该语句结果组成了内嵌表值函数所返回表可替换视图，可用在T-SQL查询中允许表或视图表示式地方23第23页视图：受限于单个SELECT语句，不允许包含用户自己提供参数内嵌表值函数：可包含附加语句，使函数所包含逻辑比视图逻辑更强返回表内嵌表值函数还可替换返回单个结果集存放过程24第24页例:在Northwind数据库上建立一个能够依据输入城市名返回全部该城市客户和供给商信息内嵌表值函数my_function325第25页USENorthwindGoCREATE

FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableAS26第26页RETURN(SELECTCity,CompanyName,ContactName,'Customers'AS

Relationship

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

SELECT*FROMdbo.my_function3('Paris')go

28第28页假如用户想经过视图“CustomerandSuppliersbyCity”来实现同样目标，则使用以下查询命令：

SELECT*FROM[CustomerandSuppliersbyCity]WHERECity='Paris'go29第29页16.3.3多语句表值函数多语句表值函数主体中允许使用语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE30第30页例:在Northwind库上利用视图Customer

andSuppliersbyCity，建立一个能够依据输入城市名和用户要求返回全部该城市客户或供给商信息多语句表值函数my_function4，并将其与上例建立内嵌表值函数my_function3对比31第31页USENorthwindGOCREATEFUNCTIONmy_function4(@RegionParameternvarchar(25),@SelectParameterchar(9))RETURNS@my_tableTABLE(Citynvarchar(15)NULL,

CompanyNamenvarchar(40)NOT

NULL,ContactNamenvarchar(30)NULL)32第32页ASBEGININSERT@my_tableSELECTCity,CompanyName,ContactName

FROM[Customer

andSuppliersbyCity]WHERECity=@RegionParameter

ANDRelationship=@SelectParameterRETURNEND33第33页下面举例说明使用多语句表值函数my_function4分别获取来自Paris全部客户和供给商信息。命令以下：SELECT*FROMdbo.my_function4('Paris','Customers')goSELECT*FROMdbo.my_function4('Paris','Suppliers')go34第34页16.4修改和删除用户自定义函数16.4.1修改用户自定义函数CREATE

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

FUNCTIONmy_function1(@moneyinputmoney)RETURNSnvarchar(5)36第36页BEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<0SET@returnstring='非热销商品'ELSESET@returnstring='热销商品'RETURN@returnstringEND37第37页16.4.2删除用户自定义函数DROP

FUNCTION[ower-name,]function-name例：删除在Northwind库上创建自定义函数my_function1

DROPFUNCTIONmy_function1

38第38页16.4.3设置用户自定义函数权限设置自定义函数权限类似于设置表或其它数据库