《SQLServer数据库应用第十章.ppt》由会员分享，可在线阅读，更多相关《SQLServer数据库应用第十章.ppt（49页珍藏版）》请在金锄头文库上搜索。
1、第十章第十章用户自定义函数用户自定义函数 1什么是函数？什么是函数？ 是一组编译好的是一组编译好的Transact-SQLTransact-SQL语句，语句，他们可以带一个或一组数值做参数，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、也可以不带参数，它返回一个数值、数值集合，或执行一些操作。数值集合，或执行一些操作。 函数能够重复执行一些操作，从而函数能够重复执行一些操作，从而避免不断重写代码。避免不断重写代码。2函数类型：函数类型：SQL Server 2005 支持用户定义函数和内置函数。 内置函数内置函数 是一组预定义的函数，是Transact- Transact-
2、SQLSQL语言的一部分，按语言的一部分，按Transact-SQLTransact-SQL参参考中定义的方式运行且不能修改。考中定义的方式运行且不能修改。310.1用户自定义函数概述用户自定义函数概述 用户定义函数是用户定义的用户定义函数是用户定义的Transact-SQLTransact-SQL函数，它可以拥函数，它可以拥有零个、一个或多个参数，必有零个、一个或多个参数，必须返回单一的返回值。返回值须返回单一的返回值。返回值可以是单个数值，也可以是一可以是单个数值，也可以是一个表。个表。4用用户户定定义义函函数数种种类类返回返回可更可更新数据表新数据表的函数的函数返回返回不可不可更新数据更
3、新数据表表的函数的函数返回返回标量标量值值的函数的函数若函数含若函数含单单个个SELECTSELECT语句且语句且可更新，则返可更新，则返回的数据表可回的数据表可更新更新若函数含若函数含多多个个SELECTSELECT语句语句或或一一个不可更新的个不可更新的SELECTSELECT语句，则语句，则返回的数据表不返回的数据表不可更新可更新510.2创建用户自定义函数创建用户自定义函数10.2.1创建用户自定义函数创建用户自定义函数CREATECREATEFUNCTIONFUNCTIONowerower-namename,function-name(parameterparameter-names
4、calar_parameter_datetype=default,n)参数名，参数名，局部变量局部变量参数数参数数据类型据类型6RETURNSscalar_return_datatypeWITHWITH,nASBEGINsql-statementRETURNscalar-expressionEND用户自用户自定义函定义函数的返数的返回值回值函数中返回值函数中返回值的表达式的表达式7SCHEMABININGSCHEMABINING选项选项可将函数可将函数绑定到它所引绑定到它所引用的数据库对用的数据库对象象function-optionENCRYPTIONENCRYPTION选项选项可以实现可以实
5、现SQLSQLServer加密包含加密包含CREATECREATEFUNCTIONFUNCTION语句文语句文本的系统表列本的系统表列8例：例：在在NorthwindNorthwind库上创建自库上创建自定义函数定义函数my_function1my_function1，该，该函数实现输入代表商品年销函数实现输入代表商品年销售额的售额的moneymoney类型参数的值后类型参数的值后返回返回字符串字符串，如果年销售额，如果年销售额大于大于1000010000，返回，返回“热销商品热销商品”，否则返回，否则返回“非热销商品非热销商品”9USENorthwindgoCREATECREATEFUNCT
6、IONFUNCTIONmymy_functionfunction1(moneyinputmoney)RETURNSnvarchar(5)10BEGINDECLAREDECLAREreturnstringnvarchar(5)IFmoneyinput10000SETreturnstring=非热销商品非热销商品ELSESETreturnstring=热销商品热销商品RETURNreturnstringEND11在在NorthwindNorthwind库上有一个记录各种库上有一个记录各种产品在产品在19971997年销售额的视图，通年销售额的视图，通过它来过它来引用引用新建的新建的my_funct
7、ion1my_function1，查看哪些商品属于热销商品查看哪些商品属于热销商品USENorthwindgoSELECTSELECTProductNameProductName,ProductSalesProductSales,dbodbo.mymy_function1(ProductSales)ASASDiscriptionFROMProductsalesfor1997WHERECategoryName=DairyProducts121310.2.2 查看用户自定义函数查看用户自定义函数自定义函数的自定义函数的名称名称保存在保存在sysobjects系统表中系统表中创建自定义函数的创建自定
8、义函数的源代码源代码保保存在存在syscomments系统表中系统表中141.使用系统存储过程查看使用系统存储过程查看EXECEXECsp_help(sp_helptext)例：例：用系统存储过程用系统存储过程sp_helptext查看用户自定义函数查看用户自定义函数my_funciton1的定义文本信息的定义文本信息USE USE NorthwindNorthwindgogoEXEC EXEC sp_helptextsp_helptext my_function1 my_function1go go 15162.使用系统表或系统信息架构视图使用系统表或系统信息架构视图在在SQLSQLServ
9、erServer中中存在三个存在三个信息信息架构视图架构视图报告报告有关用户自定有关用户自定义函数的信息义函数的信息ROUTINESROUTINESPARAMETERSPARAMETERSROUTINEROUTINE_COLUMNSCOLUMNS这些这些信息架构视图信息架构视图也是基于系统也是基于系统表表sysobjectssysobjects和和syscommentssyscomments实现的实现的17例：例：使用系统表使用系统表sysobjects查看数查看数据库据库Northwind上存在的所有用户上存在的所有用户自定义函数的相关信息。自定义函数的相关信息。USENorthwindSE
10、LECT*fromsysobjectsWHEREtype=FNgo1810.3用户自定义函数类型用户自定义函数类型自定自定义函义函数有数有三种三种类型类型标量函数标量函数内嵌表值函数内嵌表值函数多语句表值函数多语句表值函数1910.3.1标量函数标量函数标量函数标量函数返回返回在在RETURNSRETURNS子子句中定义的数句中定义的数据类型的单个据类型的单个数据值数据值标量函标量函数可重数可重复调用复调用20例：例：创建标量函数，要创建标量函数，要求将当前求将当前系统日期系统日期转化转化为为年月日年月日格式的字符串格式的字符串并返回，且默认的分隔并返回，且默认的分隔符为符为:，并允许，并允许
11、用户自行定义用户自行定义分隔符分隔符21CREATECREATEFUNCTIONFUNCTIONmy_function2(DATE DATE datetime, separatornvarchar(2)=:)RETURNSnvarchar(20)BEGINDECLAREDECLAREreturnstringnvarchar(20)22SETreturnstring=今天是今天是+CONVERTCONVERT(nvarchar(5),datepart(year,date)+年年+separator+CONVERTCONVERT(nvarchar(5),datepart(month,date)+月
12、月+separator+CONVERTCONVERT(nvarchar(5),datepart(day,date)+日日RETURNreturnstringEND23如何调用新建立的标量函如何调用新建立的标量函数呢？数呢？可以运行如下命令：可以运行如下命令：SELECTdbo.my_function2(GETDATE(),-)返回当天的返回当天的年、月和日年、月和日2410.3.2内嵌表值函数内嵌表值函数在内嵌表值函数中，在内嵌表值函数中，RETURNRETURN子句子句中包含有一条单独的中包含有一条单独的SELECTSELECT语句，语句，该语句的结果构成了该语句的结果构成了内嵌表值函内嵌表
13、值函数数所返回的表所返回的表可替代视图，可用在可替代视图，可用在T-SQL查询中允许表或视查询中允许表或视图表达式的地方图表达式的地方25视图：视图：受限于单个受限于单个SELECTSELECT语句，语句，不允许包含用户自己提供的参数不允许包含用户自己提供的参数内嵌表值函数：内嵌表值函数：可包含附加的语可包含附加的语句，使函数所包含的逻辑比视图句，使函数所包含的逻辑比视图的逻辑更强的逻辑更强返回表的内嵌表值函数还可替换返回表的内嵌表值函数还可替换返回单个结果集的存储过程返回单个结果集的存储过程26CREATEFUNCTIONower-name,function-name(parameter-n
14、amescalar_parameter_datetype=default,n)27RETURNSTABLEWITHWITH,nASRETURNSELECT_STATEMENT定义内嵌表值函数定义内嵌表值函数返回值返回值的单返回值返回值的单个个select语句语句指定表值函数的指定表值函数的返回值为表返回值为表28例例: : 在在NorthwindNorthwind数据库上建立数据库上建立一个可以根据一个可以根据输入的城市名输入的城市名返回返回所有该城市的客户和供应商的信所有该城市的客户和供应商的信息的内嵌表值函数息的内嵌表值函数my_function3my_function329USENort
15、hwindGoCREATECREATEFUNCTIONFUNCTIONmy_function3(RegionParameterRegionParameternvarchar(25)RETURNStableAS30RETURN(SELECTCity,CompanyName,ContactNameContactName,CustomersCustomersASASRelationshipFROMCustomersWHERECity=RegionParameterUNIONSELECTCity,CompanyName,ContactName,SuppliersFROMSuppliersWHERECi
16、ty=RegionParameter)31下面示例使用新建立的内嵌下面示例使用新建立的内嵌表值函数表值函数my_function3my_function3来获来获取取“巴黎巴黎”市的所有客户和市的所有客户和供应商的信息供应商的信息SELECT*FROMdbo.my_function3(Paris)go32如果用户想通过如果用户想通过视图视图“CustomerandSuppliersbyCity”来实现同来实现同样的目的，则使用如下查询命令：样的目的，则使用如下查询命令：SELECT*FROMCustomerandSuppliersbyCityWHERECity=Parisgo3310.3.3多
17、语句表值函数多语句表值函数多语句表值多语句表值函数的主体函数的主体中允许使用中允许使用的语句的语句赋值赋值控制流控制流DECLAREDECLARESELECTSELECT游标操作游标操作INSERTINSERTUPDATEUPDATEDELETEDELETEEXECUTEEXECUTE34CREATEFUNCTIONower-name,function-name(parameter-namescalar_parameter_datetype=default,n)35RETURNSreturn_variableTABLEWITHWITH,nASBEGINfunction_bodyRETURNEN
18、D函数体，由函数体，由sql语句组成，语句组成，返回表变量返回表变量是是TABLE变量，用于存储变量，用于存储作为函数值返回的行作为函数值返回的行36TABLE:=(column_definition|table_constraint,)表的列声表的列声明明表约束表约束37例例:在在Northwind库上利用视图库上利用视图CustomerCustomerandandSuppliersbyCity，建立建立一个可以根据一个可以根据输入的城市名和用输入的城市名和用户户要求要求返回返回所有该城市的客户或所有该城市的客户或供应商的信息的多语句表值函数供应商的信息的多语句表值函数my_function
19、4，并将其与上例建立并将其与上例建立的内嵌表值函数的内嵌表值函数my_function3对比对比38USENorthwindGOCREATECREATEFUNCTIONmy_function4(RegionParameternvarchar(25),SelectParameterchar(9)RETURNSmy_tableTABLE(Citynvarchar(15)NULL,CompanyNameCompanyNamenvarchar(40)NOTNOTNULLNULL,ContactNamenvarchar(30)NULL)39ASBEGININSERTmy_tableSELECTCity,
20、CompanyName,ContactNameFROMFROMCustomerCustomerandandSuppliersbyCityWHEREWHERECity=RegionParameterRegionParameterANDANDRelationship=SelectParameterRETURNEND40下面举例说明使用多语句表值函数下面举例说明使用多语句表值函数my_function4my_function4分别获取来自分别获取来自Paris的所的所有客户和供应商的信息。命令如下：有客户和供应商的信息。命令如下：SELECT*FROMdbo.my_function4(Paris,C
21、ustomers)goSELECT*FROMdbo.my_function4(Paris,Suppliers)go4110.4修改和删除用户自定义函数修改和删除用户自定义函数10.4.1修改用户自定义函数修改用户自定义函数ALTERALTERFUNCTIONFUNCTIONower-name,function-name(parameter-namescalar-parameter-datetype=default,n)RETURNSscalar-return-datatypeWITH,nASBEGINsql-statementRETURNscalar-expressionEND42例：例：修改
22、在修改在Northwind库上创建库上创建的自定义函数的自定义函数my_function1，将将区分商品是否热销的年销售额由区分商品是否热销的年销售额由当初的当初的10000提高到提高到20000ALTERALTERFUNCTIONFUNCTIONmy_function1(moneyinputmoney)RETURNSnvarchar(5)43BEGINDECLAREDECLAREreturnstringnvarchar(5)IFmoneyinput20000SETreturnstring=非热销商品非热销商品ELSESETreturnstring=热销商品热销商品RETURNreturnst
23、ringEND4410.4.2删除用户自定义函数删除用户自定义函数DROPDROPFUNCTIONFUNCTIONowerower-name-name,function-name例：例：删除在删除在NorthwindNorthwind库上创建库上创建的自定义函数的自定义函数my_function1my_function1DROPFUNCTIONmy_function14510.4.3设置用户自定义函数的权限设置用户自定义函数的权限设置自定义函数的权限类似于设设置自定义函数的权限类似于设置表或其他数据库对象的权限置表或其他数据库对象的权限要为用户授予要为用户授予 CREATECREATEFUNC
24、TIONFUNCTION权限权限才能进行创才能进行创建、修改或建、修改或删除自定义删除自定义函数的操作函数的操作46对于对于标量值函数标量值函数，一般用户在使用此函一般用户在使用此函数前，必须先被授予数前，必须先被授予此函数上的此函数上的EXECUTEEXECUTE权限权限47如果函数是如果函数是表值函表值函数数，则用户在引用，则用户在引用函数前必须对函数函数前必须对函数具有具有SELECTSELECT权限权限48若若CREATECREATETABLETABLE或或ALTERALTERTABLETABLE语句在语句在CHECKCHECK约束、约束、DEFAULTDEFAULT约束、计算列中约束、计算列中引用了自定义函数，那引用了自定义函数，那么该表的所有者也要拥么该表的所有者也要拥有该函数有该函数49
