![用户自定义函数课件_第1页](http://file4.renrendoc.com/view/ecd025d061c52d457360724f096e46a0/ecd025d061c52d457360724f096e46a01.gif)
![用户自定义函数课件_第2页](http://file4.renrendoc.com/view/ecd025d061c52d457360724f096e46a0/ecd025d061c52d457360724f096e46a02.gif)
![用户自定义函数课件_第3页](http://file4.renrendoc.com/view/ecd025d061c52d457360724f096e46a0/ecd025d061c52d457360724f096e46a03.gif)
![用户自定义函数课件_第4页](http://file4.renrendoc.com/view/ecd025d061c52d457360724f096e46a0/ecd025d061c52d457360724f096e46a04.gif)
![用户自定义函数课件_第5页](http://file4.renrendoc.com/view/ecd025d061c52d457360724f096e46a0/ecd025d061c52d457360724f096e46a05.gif)
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
第16章用户自定义函数.1第16章.116.1用户自定义函数的基本概念SQLServer允许创建用户定义函数用户定义函数是可返回值的例程.216.1用户自定义函数的基本概念SQLServer允许用户定义函数种类返回可更新数据表的函数返回不可更新数据表的函数返回标量值的函数若函数含单个SELECT语句且可更新,则返回的数据表可更新若函数含多个SELECT语句或一个不可更新的SELECT语句,则返回的数据表不可更新.3用户定义函数种类返回可更新数据表的函数返回不可更新数据表的16.2创建用户自定义函数16.2.1 创建用户自定义函数CREATE
FUNCTION[owner-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])参数名,局部变量参数数据类型.416.2创建用户自定义函数16.2.1 创建用户自定义RETURNSscalar_return_datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND用户自定义函数的返回值函数中返回值的表达式.5RETURNSscalar_return_datatypeSCHEMABINING选项可将函数绑定到它所引用的数据库对象function-optionENCRYPTION选项可以实现SQLServer加密包含CREATE
FUNCTION语句文本的系统表列.6SCHEMABINING选项可将函数绑定到它所引用的数据库对例:在Northwind库上创建自定义函数my_function1,通过向该函数输入代表商品年销售额的money类型参数的大小返回字符串,若年销售额大于10000,返回“热销商品”,否则返回“非热销商品”.7例:在Northwind库上创建自定义函数my_functiUSENorthwindgoCREATE
FUNCTION
my_function1
(@moneyinputmoney)RETURNSnvarchar(5).8USENorthwind.8BEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<10000SET@returnstring='非热销商品'ELSESET@returnstring='热销商品'RETURN@returnstringEND.9BEGIN.9在Northwind库上有一个记录各种产品在1997年销售额的视图,通过它来引用新建的my_function1,查看哪些商品属于热销商品USENorthwindgoSELECT
ProductName,ProductSales,dbo.my_function1(ProductSales)ASDiscriptionFROM[Productsalesfor1997]WHERECategoryName=’DairyProducts’.10在Northwind库上有一个记录各种.10.11.1116.2.2 查看用户自定义函数自定义函数的名称保存在sysobjects系统表中创建自定义函数的源代码保存在syscomments系统表中.1216.2.2 查看用户自定义函数自定义函数的名称保存在sy1.使用系统存储过程查看EXECsp_help(sp_helptext)<function-name>例:用系统存储过程sp_helptext查看用户自定义函数my_funciton1的定义文本信息USENorthwindgoEXECsp_helptextmy_function1go.131.使用系统存储过程查看EXECsp_help(sp_.14.142.使用系统表或系统信息架构视图在SQL
Server中存在三个信息架构视图报告有关用户自定义函数的信息ROUTINESPARAMETERSROUTINE_COLUMNS这些信息架构视图也是基于系统表sysobjects和syscomments实现的.152.使用系统表或系统信息架构视图在SQLServer中例:使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。
USENorthwindSELECT*fromsysobjectsWHEREtype='FN'go.16例:使用系统表sysobjects查看数据库Northwin16.3用户自定义函数类型自定义函数有三种类型标量函数内嵌表值函数多语句表值函数.1716.3用户自定义函数类型自定义函数有三种类型标量函数内16.3.1标量函数标量函数返回在RETURNS子句中定义的数据类型的单个数据值标量函数可重复调用.1816.3.1标量函数标量函数返回在RETURNS子句例:创建标量函数,要求将当前系统日期转化为年月日格式的字符串并返回,且默认的分隔符为‘::’,并允许用户自行定义分隔符.19例:创建标量函数,要求将当前系统日期转化为年月日格式的字符串CREATE
FUNCTIONmy_function2(@DATEdatetime,
@separatornvarchar(2)='::')RETURNSnvarchar(20)BEGINDECLARE
@returnstringnvarchar(20).20CREATEFUNCTIONmy_function2SET@returnstring='今天是'+CONVERT(nvarchar(5),datepart(year,@date))+‘年’+@separator+CONVERT(nvarchar(5),datepart(month,@date))+‘月’+@separator+CONVERT(nvarchar(5),datepart(day,@date))+'日'RETURN@returnstringEND.21SET@returnstring='今天是'.21如何调用新建立的标量函数呢?可以运行如下命令:
SELECT
dbo.my_function2(GETDATE(),’--‘)返回当天的年、月和日.22如何调用新建立的标量函数呢?返回当天的年、月和日.2216.3.2内嵌表值函数在内嵌表值函数中,RETURN子句中包含有一条单独的SELECT语句,该语句的结果构成了内嵌表值函数所返回的表可替代视图,可用在T-SQL查询中允许表或视图表达式的地方.2316.3.2内嵌表值函数在内嵌表值函数中,RETURN子视图:受限于单个SELECT语句,不允许包含用户自己提供的参数内嵌表值函数:可包含附加的语句,使函数所包含的逻辑比视图的逻辑更强返回表的内嵌表值函数还可替换返回单个结果集的存储过程.24视图:受限于单个SELECT语句,不允许包含用户自己提供例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function3.25例:在Northwind数据库上建立一个可以根据输入的城市USENorthwindGoCREATE
FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableAS.26USENorthwind.26RETURN(SELECTCity,CompanyName,ContactName,'Customers'AS
Relationship
FROMCustomersWHERECity=@RegionParameterUNIONSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter).27RETURN.27下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息
SELECT*FROMdbo.my_function3('Paris')go
.28下面示例使用新建立的内嵌表值函数my_function3来获如果用户想通过视图“CustomerandSuppliersbyCity”来实现同样的目的,则使用如下查询命令:
SELECT*FROM[CustomerandSuppliersbyCity]WHERECity='Paris'go.29如果用户想通过视图“Customer.2916.3.3多语句表值函数多语句表值函数的主体中允许使用的语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE.3016.3.3多语句表值函数多语句表值函数的主体中允许使用例:在Northwind库上利用视图Customer
andSuppliersbyCity,建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4,并将其与上例建立的内嵌表值函数my_function3对比.31例:在Northwind库上利用视图CustomeraUSENorthwindGOCREATEFUNCTIONmy_function4(@RegionParameternvarchar(25),@SelectParameterchar(9))RETURNS@my_tableTABLE(Citynvarchar(15)NULL,
CompanyNamenvarchar(40)NOT
NULL,ContactNamenvarchar(30)NULL).32USENorthwind.32ASBEGININSERT@my_tableSELECTCity,CompanyName,ContactName
FROM[Customer
andSuppliersbyCity]WHERECity=@RegionParameter
ANDRelationship=@SelectParameterRETURNEND.33AS.33下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。命令如下:SELECT*FROMdbo.my_function4('Paris','Customers')goSELECT*FROMdbo.my_function4('Paris','Suppliers')go.34下面举例说明使用多语句表值函数my_function4分别获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-expressionEND.3516.4修改和删除用户自定义函数16.4.1修改用户例:修改在Northwind库上创建的自定义函数my_function1,将区分商品是否热销的年销售额由当初的10000提高到20000ALTER
FUNCTIONmy_function1(@moneyinputmoney)RETURNSnvarchar(5).36例:修改在Northwind库上创建的自定义函数my_funBEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<20000SET@returnstring='非热销商品'ELSESET@returnstring='热销商品'RETURN@returnstringEND.37BEGIN.3716.4.2删除用户自定义函数DROP
FUNCTION[ower-name,]function-name例:删除在Northwind库上创建的自定义函数my_function1
DROPFUNCTIONmy_function1
.3816.4.2删除用户自定义函数DROPFUNCTIO16.4.3设置用户自定义函数的权限设置自定义函数的权限类似于设置表或其他数据库对象的权限要为用户授予CREATE
FUNCTION权限才能进行创建、修改或删除自定义函数的操作.3916.4.3设置用户自定义函数的权限设置自定义函数的权对于标量值函数,一般用户在使用此函数前,必须先被授予此函数上的EXECUTE权限.40对于标量值函数,一般用户在使用此函数前,必须先被如果函数是表值函数,则用户在引用函数前必须对函数具有SELECT权限.41如果函数是表值函数,则用户在引用函数前必须对函数具有SEL若CREATE
TABLE或ALTER
TABLE语句在CHECK约束、DEFAULT约束、计算列中引用了自定义函数,那么该表的所有者也要拥有该函数.42若CREATETABLE或ALTERTABLE语句在CH第16章用户自定义函数.43第16章.116.1用户自定义函数的基本概念SQLServer允许创建用户定义函数用户定义函数是可返回值的例程.4416.1用户自定义函数的基本概念SQLServer允许用户定义函数种类返回可更新数据表的函数返回不可更新数据表的函数返回标量值的函数若函数含单个SELECT语句且可更新,则返回的数据表可更新若函数含多个SELECT语句或一个不可更新的SELECT语句,则返回的数据表不可更新.45用户定义函数种类返回可更新数据表的函数返回不可更新数据表的16.2创建用户自定义函数16.2.1 创建用户自定义函数CREATE
FUNCTION[owner-name,]function-name([{@parameter-namescalar_parameter_datetype[=default]}[,…n]])参数名,局部变量参数数据类型.4616.2创建用户自定义函数16.2.1 创建用户自定义RETURNSscalar_return_datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND用户自定义函数的返回值函数中返回值的表达式.47RETURNSscalar_return_datatypeSCHEMABINING选项可将函数绑定到它所引用的数据库对象function-optionENCRYPTION选项可以实现SQLServer加密包含CREATE
FUNCTION语句文本的系统表列.48SCHEMABINING选项可将函数绑定到它所引用的数据库对例:在Northwind库上创建自定义函数my_function1,通过向该函数输入代表商品年销售额的money类型参数的大小返回字符串,若年销售额大于10000,返回“热销商品”,否则返回“非热销商品”.49例:在Northwind库上创建自定义函数my_functiUSENorthwindgoCREATE
FUNCTION
my_function1
(@moneyinputmoney)RETURNSnvarchar(5).50USENorthwind.8BEGINDECLARE@returnstringnvarchar(5)IF@moneyinput<10000SET@returnstring='非热销商品'ELSESET@returnstring='热销商品'RETURN@returnstringEND.51BEGIN.9在Northwind库上有一个记录各种产品在1997年销售额的视图,通过它来引用新建的my_function1,查看哪些商品属于热销商品USENorthwindgoSELECT
ProductName,ProductSales,dbo.my_function1(ProductSales)ASDiscriptionFROM[Productsalesfor1997]WHERECategoryName=’DairyProducts’.52在Northwind库上有一个记录各种.10.53.1116.2.2 查看用户自定义函数自定义函数的名称保存在sysobjects系统表中创建自定义函数的源代码保存在syscomments系统表中.5416.2.2 查看用户自定义函数自定义函数的名称保存在sy1.使用系统存储过程查看EXECsp_help(sp_helptext)<function-name>例:用系统存储过程sp_helptext查看用户自定义函数my_funciton1的定义文本信息USENorthwindgoEXECsp_helptextmy_function1go.551.使用系统存储过程查看EXECsp_help(sp_.56.142.使用系统表或系统信息架构视图在SQL
Server中存在三个信息架构视图报告有关用户自定义函数的信息ROUTINESPARAMETERSROUTINE_COLUMNS这些信息架构视图也是基于系统表sysobjects和syscomments实现的.572.使用系统表或系统信息架构视图在SQLServer中例:使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。
USENorthwindSELECT*fromsysobjectsWHEREtype='FN'go.58例:使用系统表sysobjects查看数据库Northwin16.3用户自定义函数类型自定义函数有三种类型标量函数内嵌表值函数多语句表值函数.5916.3用户自定义函数类型自定义函数有三种类型标量函数内16.3.1标量函数标量函数返回在RETURNS子句中定义的数据类型的单个数据值标量函数可重复调用.6016.3.1标量函数标量函数返回在RETURNS子句例:创建标量函数,要求将当前系统日期转化为年月日格式的字符串并返回,且默认的分隔符为‘::’,并允许用户自行定义分隔符.61例:创建标量函数,要求将当前系统日期转化为年月日格式的字符串CREATE
FUNCTIONmy_function2(@DATEdatetime,
@separatornvarchar(2)='::')RETURNSnvarchar(20)BEGINDECLARE
@returnstringnvarchar(20).62CREATEFUNCTIONmy_function2SET@returnstring='今天是'+CONVERT(nvarchar(5),datepart(year,@date))+‘年’+@separator+CONVERT(nvarchar(5),datepart(month,@date))+‘月’+@separator+CONVERT(nvarchar(5),datepart(day,@date))+'日'RETURN@returnstringEND.63SET@returnstring='今天是'.21如何调用新建立的标量函数呢?可以运行如下命令:
SELECT
dbo.my_function2(GETDATE(),’--‘)返回当天的年、月和日.64如何调用新建立的标量函数呢?返回当天的年、月和日.2216.3.2内嵌表值函数在内嵌表值函数中,RETURN子句中包含有一条单独的SELECT语句,该语句的结果构成了内嵌表值函数所返回的表可替代视图,可用在T-SQL查询中允许表或视图表达式的地方.6516.3.2内嵌表值函数在内嵌表值函数中,RETURN子视图:受限于单个SELECT语句,不允许包含用户自己提供的参数内嵌表值函数:可包含附加的语句,使函数所包含的逻辑比视图的逻辑更强返回表的内嵌表值函数还可替换返回单个结果集的存储过程.66视图:受限于单个SELECT语句,不允许包含用户自己提供例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function3.67例:在Northwind数据库上建立一个可以根据输入的城市USENorthwindGoCREATE
FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableAS.68USENorthwind.26RETURN(SELECTCity,CompanyName,ContactName,'Customers'AS
Relationship
FROMCustomersWHERECity=@RegionParameterUNIONSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter).69RETURN.27下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息
SELECT*FROMdbo.my_function3('Paris')go
.70下面示例使用新建立的内嵌表值函数my_function3来获如果用户想通过视图“CustomerandSuppliersbyCity”来实现同样的目的,则使用如下查询命令:
SELECT*FROM[CustomerandSuppliersbyCity]WHERECity='Paris'go.71如果用户想通过视图“Customer.2916.3.3多语句表值函数多语句表值函数的主体中允许使用的语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE.7216.3.3多语句表值函数多语句表值函数的主体中允许使用例:在Northwind库上利用视图Customer
andSuppliersbyCity,建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4,并将其与上例建立的内嵌表值函数my_function3对比.73例:在Northwind库上利用视图CustomeraUSENorthwindGOCREATEFUNCTIONmy_function4(@RegionParameternvarchar(25),@SelectParameterchar(9))RETURNS@my_tableTABLE(Citynvarchar(15)NULL,
CompanyNamenvarchar(40)NOT
NULL,ContactNamenvarchar(30)NULL).74USENorthwind.32ASBEGININSERT@my_tableSELECTCity,CompanyName,ContactName
FROM[Customer
andSuppliersbyCity]WHERECity=@RegionParameter
ANDRelationship=@SelectParameterRETURNEND.75AS.33下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。命令如下:SELECT*FROMdbo.my_function4('Paris','Customers')goSELECT*FROMdbo.my_function4('Paris','Suppliers')go.76下面举
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2024-2029年过电流保护继电器行业市场现状供需分析及市场深度研究发展前景及规划投资研究报告
- 2024-2029年轮刷行业市场现状供需分析及重点企业投资评估规划分析研究报告
- 2024-2029年轧机衬里系统行业市场现状供需分析及市场深度研究发展前景及规划投资研究报告
- 2024-2029年超薄太阳能电池行业市场现状供需分析及重点企业投资评估规划分析研究报告
- 2024-2029年货梯产业规划研究报告
- 2024-2029年触摸屏透明导电膜行业市场现状供需分析及市场深度研究发展前景及规划投资研究报告
- 2024-2029年表观遗传学仪器行业市场现状供需分析及重点企业投资评估规划分析研究报告
- 2024-2029年药片袋行业市场现状供需分析及市场深度研究发展前景及规划投资研究报告
- 2024-2029年节能建材行业市场发展分析及发展趋势与规划建议研究报告
- 2024-2029年航空保险产业政府战略管理与区域发展战略研究咨询报告
- 残疾人基本康复服务目录
- 小班安全《咬人的缝隙》
- 新课标背景下初中语文大单元教学设计与实施路径初探
- 2023年健康照护师(中级)理论知识考核试题
- 【语文大单元教学研究国内外文献综述6400字】
- 路基沉陷灌浆施工方案完整
- 2022年德阳市公安局警务辅助人员考试真题
- 人文地理学考试题库含答案全套
- 资质挂靠合作协议
- 儿科脑膜炎教学
- 2023届浙江省高三高考物理压轴题专项训练30题(含答案详解)
评论
0/150
提交评论