数据库 第14章 实现用户定义函数课件

第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数用户定义函数SQLServer2000允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数并返回标量值或表SQLServer2000支持三种用户定义函数：标量函数、多语句表值函数、内嵌表值函数标量函数标量函数返回一个标量（单值）结果返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。16.1用户定义函数用户定义函数SQLServer2000允许用户设计自己用户定义函数（续）多语句表值函数返回一个由一条或多条Transact-SQL语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中被引用，仿佛视图一样内嵌表值函数返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能16.1用户定义函数用户定义函数（续）多语句表值函数16.1用户定义函数第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数创建标量用户定义函数标量函数返回

RETURNS子句中定义的数据类型的单个数据值在BEGIN…END块之间定义了函数体，包含返回值的一系列Transact-SQL语句返回值可以是除了text、ntext、image、cursor或timestamp之外的任何数据类型额外的限制标量函数内的SQL语句不能包括任何非确定性系统函数16.2.1使用标量用户定义函数创建标量用户定义函数标量函数返回RETURNS子句中定义标量用户函数定义CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,...n]])RETURNS

返回数据类型[WITH<函数选项>][AS]BEGIN 函数体

RETURN返回表达式ENDSELECTown_name.function_name[(参数值[,...n])]16.2.2标量用户定义函数示例必须指出所有者名称创建函数调用函数标量用户函数定义CREATEFUNCTION[own_n标量用户定义函数示例USEpubsGOCREATEFUNCTIONMyFun(@typechar(12))

RETURNS

money

AS

BEGIN

DECLARE@varmoneySELECT@var=sum(price*ytd_sales)FROMtitlesWHEREtype=@type

RETURN@var

ENDSELECTdbo.MyFun('business')16.2.2标量用户定义函数示例创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额。调用函数标量用户定义函数示例USEpubsSELECTdbo.M标量用户定义函数示例（续）USEpubsGOCREATEFUNCTIONMyFun(@typechar(12)=‘business’)

RETURNSmoney

AS

BEGIN

DECLARE@varmoneySELECT@var=sum(price*ytd_sales)FROMtitlesWHEREtype=@type

RETURN@var

ENDSELECTdbo.MyFun(DEFAULT)16.2.2标量用户定义函数示例不能省略参数带默认值创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额（带默认值）

。调用函数标量用户定义函数示例（续）USEpubsSELECTdb使用多语句表值函数多语句表值函数BEGIN...END限定了函数体RETURNS子句指定table作为返回的数据类型RETURNS子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部16.2.3使用多语句表值函数使用多语句表值函数多语句表值函数16.2.3使用多语句表值多语句表值函数定义CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,...n]])RETURNS@返回变量

TABLE<表定义>[WITH<函数选项>][AS]BEGIN 函数体RETURNENDSELECT*fromown_name.function_name[(参数值[,...n])][where<条件>]16.2.3使用多语句表值函数可以省略创建函数调用函数多语句表值函数定义CREATEFUNCTION[own_多语句表值函数示例创建函数，根据要求返回ID及Lastname或Lastname、firstname组合CREATEFUNCTIONfn_Employees(@lengthnvarchar(10))RETURNS@fn_Employeestable(EmployeeIDintPRIMARYKEYNOTNULL,EmployeeNamenvarchar(20)NOTNULL)ASBEGINIF@length='ShortName'

INSERT@fn_Employees

SELECTEmployeeID,LastNameFROMEmployeesELSEIF@length='LongName'

INSERT@fn_Employees

SELECTEmployeeID,(FirstName+''+LastName)FROMEmployeesRETURNEND

SELECT*FROMdbo.fn_Employees('ShortName')16.2.3多语句表值函数示例SELECT*FROMdbo.fn_Employees('LongName')WHEREemployeeid<5

调用函数表定义多语句表值函数示例创建函数，根据要求返回ID及Lastnam使用多语句表值函数（续）函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的DECLARE语句SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游标操作，如声明、打开、关闭和释放局部游标。仅允许使用FETCH语句通过INTO子句给局部变量赋值，不允许使用FETCH语句将数据返回到客户端修改函数中定义的局部表变量的INSERT、UPDATE和DELETE语句16.2.3使用多语句表值函数使用多语句表值函数（续）函数体内只允许如下语句16.2.3使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被引用，就像视图一样关于使用内嵌表值函数的依据和指导方针RETURNS子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表函数体不由BEGIN和END分隔RETURNS指定table作为返回的数据类型不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置16.2.4使用内嵌表值函数使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被内嵌表值函数示例USENorthwindGOCREATEFUNCTIONfn_CustomerNamesInRegion(@RegionParameternvarchar(30))RETURNS

tableASRETURN(SELECTCustomerID,CompanyNameFROMNorthwind.dbo.CustomersWHERERegion=@RegionParameter)SELECT*FROMfn_CustomerNamesInRegion('WA')16.2.4内嵌表值函数示例创建函数，根据地区查看其所在的客户姓名。使用参数调用函数内嵌表值函数示例USENorthwindSELECT*更改和删除用户定义函数更改函数

语法：ALTERFUNCTION……使用新的函数定义代替原来的函数定义保留所有已分配的权限删除函数语法：DROPFUNCTION……删除一个用户定义函数16.2.5更改和删除用户定义函数更改和删除用户定义函数更改函数16.2.5更改和删除用户定第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对象若函数创建时使用SCHEMABINDING选项，则此函数引用的数据库对象不能被更改（使用ALTER语句）或删除（使用DROP语句）必须满足以下条件才能在CREATEFUNCTION中指定SCHEMABINDING选项该函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”该函数所引用的所有对象必须与函数位于同一数据库中执行CREATEFUNCTION语句的用户必须具有对该函数中引用的所有对象的REFERENCE权限16.3.1创建架构绑定函数创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对创建架构绑定函数（续）创建架构绑定的函数16.3.1创建架构绑定函数CREATEFUNCTIONfn_Employees(@employeeidint)RETURNSvarchar(100)withSCHEMABINDINGASBEGINdeclare@emLastNamevarchar(50),@emFirstNamevarchar(50)select@emLastName=lastName,@emFirstName=firstname fromdbo.viwEmployee whereemployeeID=@employeeidRETURN(@emLastName+''+@emfirstName)END创建架构绑定的视图CREATEVIEWdbo.ViwEmployeewithschemabindingASSELECTemployeeid,lastname,firstname,cityfromdbo.employees必须先构建绑定视图创建架构绑定函数（续）创建架构绑定的函数16.3.1创建架为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有CREATEFUNCTION权限若函数拥有者之外的用户要在Transact-SQL语句中使用函数，则必须先被授予EXECUTE权限若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的REFERENCE权限若在CREATETABLE或ALTERTABLE语句的CHECK约束、DEFAULT子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的REFERENCE权限16.3.2为用户定义函数设置权限为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象第14章实现用户定义函数用户定义函数用户定义函数的定义架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数推荐操作使用多语句表值函数代替返回表的存储过程使用内嵌表值函数过滤索引视图在小的结果集上使用复杂的标量函数使用内嵌表值函数创建参数化视图16.4推荐操作推荐操作使用多语句表值函数代替返回表的存储过程使用内嵌表值函随堂练习1表AppUsers中存放了用户的姓名与密码，要求密码经过加密后，以二进制的方式存放，请提供加密与解密函数来实现该功能。createfunctiondbo.fn_EncryptPassword(@Passwordvarchar(20))returnsbinary(20)begin declare@EPasswordbinary(20) set@EPassword=cast(@Passwordasbinary(20)) return@EPasswordendcreatefunctiondbo.fn_DecryptPassword(@EPasswordbinary(20))returnsvarchar(20)begin declare@Passwordvarchar(20) set@Password=cast(@EPasswordasvarchar(20)) return@Passwordend随堂练习1表AppUsers中存放了用户的姓名与密码，要求密随堂练习1（续）在向表AppUsers插入数据时，使用加密函数对密码进行加密后再插入，在从表中读取数据时，用解密函数对密码进行解密insertintoAppUsers(UserName,[Password])values('Tom',dbo.fn_EncryptPassword('00001'))selectusername,dbo.fn_DecryptPassword([password])fromAppUsers随堂练习1（续）在向表AppUsers插入数据时，使用加密函随堂练习2在设计某个销售公司的数据库系统时，客户要求设计一张季度销售情况的视图。这张视图连接多张表同时使用聚集函数计算所需的统计信息。客户要求提供一个带参数的查询方法，输出的结果要作为其他的SELECT语句的列值使用。请问应当如何设计该视图？

A、使用ALTERVIEW语句在视图定义中添加参数B、创建带参数的存储过程，将结果集作为一个行集合返回C、创建一个接收参数输入的标量用户自定义函数D、创建一个接收参数输入的内联用户自定义函数随堂练习2在设计某个销售公司的数据库系统时，客户要求设计在设计某个公司的数据库系统时，要求设计一张数据表。这张数据表使用聚集函数计算所需的统计信息并使用索引提高查询速度。要求将该数据表查询输出的结果要放在其他SELECT语句的FROM子句使用，下列做法哪个是正确的？

A、创建一个接收参数输入标量用户自定义函数B、创建一个接收参数输入并将结果集作为一个行集合返回的标量用户自定义函数C、创建一个接收参数输入内联用户自定义函数D、创建一个接收参数输入的存储过程随堂练习3在设计某个公司的数据库系统时，要求设计一张数据A、创建一个30分钟

实验创建用户定义函数目标创建标量用户定义函数创建多语句表值用户定义函数创建内嵌表值用户定义函数练习1创建标量用户定义函数练习2创建多语句表值用户定义函数练习3创建内嵌表值用户定义函数

30分钟实验创建用户定义函数目标回顾学习完本章后，将能够：描述用户定义函数的三种类型创建和更改用户定义函数创建每种类型的用户定义函数回顾学习完本章后，将能够：第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数用户定义函数SQLServer2000允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数并返回标量值或表SQLServer2000支持三种用户定义函数：标量函数、多语句表值函数、内嵌表值函数标量函数标量函数返回一个标量（单值）结果返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。16.1用户定义函数用户定义函数SQLServer2000允许用户设计自己用户定义函数（续）多语句表值函数返回一个由一条或多条Transact-SQL语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中被引用，仿佛视图一样内嵌表值函数返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能16.1用户定义函数用户定义函数（续）多语句表值函数16.1用户定义函数第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数创建标量用户定义函数标量函数返回

RETURNS子句中定义的数据类型的单个数据值在BEGIN…END块之间定义了函数体，包含返回值的一系列Transact-SQL语句返回值可以是除了text、ntext、image、cursor或timestamp之外的任何数据类型额外的限制标量函数内的SQL语句不能包括任何非确定性系统函数16.2.1使用标量用户定义函数创建标量用户定义函数标量函数返回RETURNS子句中定义标量用户函数定义CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,...n]])RETURNS

返回数据类型[WITH<函数选项>][AS]BEGIN 函数体

RETURN返回表达式ENDSELECTown_name.function_name[(参数值[,...n])]16.2.2标量用户定义函数示例必须指出所有者名称创建函数调用函数标量用户函数定义CREATEFUNCTION[own_n标量用户定义函数示例USEpubsGOCREATEFUNCTIONMyFun(@typechar(12))

RETURNS

money

AS

BEGIN

DECLARE@varmoneySELECT@var=sum(price*ytd_sales)FROMtitlesWHEREtype=@type

RETURN@var

ENDSELECTdbo.MyFun('business')16.2.2标量用户定义函数示例创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额。调用函数标量用户定义函数示例USEpubsSELECTdbo.M标量用户定义函数示例（续）USEpubsGOCREATEFUNCTIONMyFun(@typechar(12)=‘business’)

RETURNSmoney

AS

BEGIN

DECLARE@varmoneySELECT@var=sum(price*ytd_sales)FROMtitlesWHEREtype=@type

RETURN@var

ENDSELECTdbo.MyFun(DEFAULT)16.2.2标量用户定义函数示例不能省略参数带默认值创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额（带默认值）

。调用函数标量用户定义函数示例（续）USEpubsSELECTdb使用多语句表值函数多语句表值函数BEGIN...END限定了函数体RETURNS子句指定table作为返回的数据类型RETURNS子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部16.2.3使用多语句表值函数使用多语句表值函数多语句表值函数16.2.3使用多语句表值多语句表值函数定义CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,...n]])RETURNS@返回变量

TABLE<表定义>[WITH<函数选项>][AS]BEGIN 函数体RETURNENDSELECT*fromown_name.function_name[(参数值[,...n])][where<条件>]16.2.3使用多语句表值函数可以省略创建函数调用函数多语句表值函数定义CREATEFUNCTION[own_多语句表值函数示例创建函数，根据要求返回ID及Lastname或Lastname、firstname组合CREATEFUNCTIONfn_Employees(@lengthnvarchar(10))RETURNS@fn_Employeestable(EmployeeIDintPRIMARYKEYNOTNULL,EmployeeNamenvarchar(20)NOTNULL)ASBEGINIF@length='ShortName'

INSERT@fn_Employees

SELECTEmployeeID,LastNameFROMEmployeesELSEIF@length='LongName'

INSERT@fn_Employees

SELECTEmployeeID,(FirstName+''+LastName)FROMEmployeesRETURNEND

SELECT*FROMdbo.fn_Employees('ShortName')16.2.3多语句表值函数示例SELECT*FROMdbo.fn_Employees('LongName')WHEREemployeeid<5

调用函数表定义多语句表值函数示例创建函数，根据要求返回ID及Lastnam使用多语句表值函数（续）函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的DECLARE语句SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游标操作，如声明、打开、关闭和释放局部游标。仅允许使用FETCH语句通过INTO子句给局部变量赋值，不允许使用FETCH语句将数据返回到客户端修改函数中定义的局部表变量的INSERT、UPDATE和DELETE语句16.2.3使用多语句表值函数使用多语句表值函数（续）函数体内只允许如下语句16.2.3使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被引用，就像视图一样关于使用内嵌表值函数的依据和指导方针RETURNS子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表函数体不由BEGIN和END分隔RETURNS指定table作为返回的数据类型不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置16.2.4使用内嵌表值函数使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被内嵌表值函数示例USENorthwindGOCREATEFUNCTIONfn_CustomerNamesInRegion(@RegionParameternvarchar(30))RETURNS

tableASRETURN(SELECTCustomerID,CompanyNameFROMNorthwind.dbo.CustomersWHERERegion=@RegionParameter)SELECT*FROMfn_CustomerNamesInRegion('WA')16.2.4内嵌表值函数示例创建函数，根据地区查看其所在的客户姓名。使用参数调用函数内嵌表值函数示例USENorthwindSELECT*更改和删除用户定义函数更改函数

语法：ALTERFUNCTION……使用新的函数定义代替原来的函数定义保留所有已分配的权限删除函数语法：DROPFUNCTION……删除一个用户定义函数16.2.5更改和删除用户定义函数更改和删除用户定义函数更改函数16.2.5更改和删除用户定第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对象若函数创建时使用SCHEMABINDING选项，则此函数引用的数据库对象不能被更改（使用ALTER语句）或删除（使用DROP语句）必须满足以下条件才能在CREATEFUNCTION中指定SCHEMABINDING选项该函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”该函数所引用的所有对象必须与函数位于同一数据库中执行CREATEFUNCTION语句的用户必须具有对该函数中引用的所有对象的REFERENCE权限16.3.1创建架构绑定函数创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对创建架构绑定函数（续）创建架构绑定的函数16.3.1创建架构绑定函数CREATEFUNCTIONfn_Employees(@employeeidint)RETURNSvarchar(100)withSCHEMABINDINGASBEGINdeclare@emLastNamevarchar(50),@emFirstNamevarchar(50)select@emLastName=lastName,@emFirstName=firstname fromdbo.viwEmployee whereemployeeID=@employeeidRETURN(@emLastName+''+@emfirstName)END创建架构绑定的视图CREATEVIEWdbo.ViwEmployeewithschemabindingASSELECTemployeeid,lastname,firstname,cityfromdbo.employees必须先构建绑定视图创建架构绑定函数（续）创建架构绑定的函数16.3.1创建架为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有CREATEFUNCTION权限若函数拥有者之外的用户要在Transact-SQL语句中使用函数，则必须先被授予EXECUTE权限若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的REFERENCE权限若在CREATETABLE或ALTERTABLE语句的CHECK约束、DEFAULT子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的REFERENCE权限16.3.2为用户定义函数设置权限为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象第14章实现用户定义函数用户定义函数用户定义函数的定义架构绑定函数的创建推荐操作第14章实现用户定义函数用户定义函数推荐操作使用多语句表值函数代替返回表的存储过程使用内嵌表值函数过滤索引视图在小的结果集上使用复杂的标量函数使用内嵌表值函数创建参数化视图16.4推荐操作推荐操作使用多语句表值函数代替返回表的存储过程使用内嵌表值函随堂练习1表AppUsers中存放了用户的姓名与密码，要求密码经过加密后，以二进制的方式存放，请提供加密与解密函数来实现该功