七 用户自定义函数

七用户自定义函数《SQLServer数据库设计与实现》软件系统技术教研室21/6/20241tcl演示系统函数GETDATE()1/6/20242tcl7.1用户定义函数（掌握）7.2用户定义函数的定义（掌握）7.3用户定义函数的使用（掌握）1/6/20243tcl7.1用户定义函数SQLServer允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数并返回标量值或表SQLServer支持三种用户定义函数：标量函数、多语句表值函数和内嵌表值函数1/6/20244tcl标量函数标量函数返回一个标量（单值）结果可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数，包括SELECT语句中列的列表和WHERE子句、表达式、表定义中的约束表达式，甚至作为表中列的数据类型1/6/20245tcl多语句表值函数多语句表值函数返回一个由一条或多条Transact-SQL语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中被引用，仿佛视图一样1/6/20246tcl内嵌表值函数内嵌表值函数返回一个单条SELECT语句产生的结果的表，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能1/6/20247tcl8.2用户定义函数的定义创建用户定义函数创建架构绑定函数为用户定义函数设置权限更改和删除用户定义函数1/6/20248tcl创建用户定义函数每个完全合法的用户定义函数名（数据库名.拥有者名.函数名）必须惟一语句指定了输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型1/6/20249tcl创建用户定义函数标量用户定义函数可以在任何它们返回值的数据类型可以用的地方使用表值用户定义函数只能在SELECT语句的FROM子句中使用若SELECT子句是在DECLARECURSOR语句中，游标必须是STATIC或READ_ONLY的1/6/202410tcl练习创建用户定义函数fn_norecord，如果输入内容为空则使用字符串“notavailable”来替代USEpubsGOCREATEFUNCTIONfn_norecord(@myinputnvarchar(30))RETURNSnvarchar(30)BEGIN IF@myinputISNULL SET@myinput='notavailable' RETURN@myinputEND1/6/202411tcl练习使用用户定义函数fn_norecord来查询pubs数据库中的titles表的notes字段SELECTpubs.dbo.fn_norecord(notes)asnotesfrompubs.dbo.titles1/6/202412tcl使用标量用户定义函数标量函数以和内置函数相同的形式调用：

拥有者名.函数名([参数列表])不可省略拥有者名，也不能用命名参数的形式（例如@参数名=值），且参数也不可以省略（但可以使用DEFAULT关键字指明使用默认值）1/6/202413tcl使用标量用户定义函数SQLServer提供少量内置用户定义函数。与普通内置函数不同，它们的名字以“fn_”开头并且使用特定的调用语法：

::函数名([参数列表])

其中“::”代替了“拥有者名”的位置，表明函数为内置用户定义函数1/6/202414tcl使用标量用户定义函数也可以使用EXECUTE语句调用标量函数：

EXECUTE@返回变量=函数名(参数列表)

使用EXECUTE语句调用时，不必包括“拥有者名”，而且可以使用命名参数：

EXECUTE@返回变量=函数名@变量=值[,@变量=值]

当使用命名参数的时候，参数的次序不必按照在函数中声明的次序，但必须包括所有参数，不能省略任何参数1/6/202415tcl使用表值用户定义函数表值用户定义函数必须使用和内置函数相同的调用格式：函数名([参数列表])

不需要指定“拥有者名”，但必须包括所有定义的参数1/6/202416tcl创建用户定义函数函数的限制非确定性函数：每次使用相同的输入值集调用的时候，返回不同的结果值的函数内置非确定性函数，包括@@ERROR@@IDENTITY@@ROWCOUNT@@TRANCOUNTAPP_NAMECURRENT_TIMESTAMPCURRENT_USERDATENAMEFORMATMESSAGEGETANSINULLGETDATEGETUTCDATEHOST_IDHOST_NAMEIDENT_INCRIDENT_SEEDIDENTITYNEWIDPERMISSIONSSESSION_USERSTATS_DATESYSTEM_USERTEXTPTRTEXTVALIDUSER_NAME1/6/202417tcl创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对象若函数创建时使用SCHEMABINDING选项，则此函数引用的数据库对象不能被更改（使用ALTER语句）或删除（使用DROP语句）1/6/202418tcl创建架构绑定函数必须满足以下条件才能在CREATEFUNCTION中指定SCHEMABINDING选项该函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”1/6/202419tcl创建架构绑定函数必须满足以下条件才能在CREATEFUNCTION中指定SCHEMABINDING选项该函数所引用的所有对象必须与函数位于同一数据库中执行CREATEFUNCTION语句的用户必须具有对该函数中引用的所有对象的REFERENCE权限1/6/202420tcl为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有CREATEFUNCTION权限若函数拥有者之外的用户要在Transact-SQL语句中使用函数，则必须先被授予EXECUTE权限1/6/202421tcl为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的REFERENCE权限若在CREATETABLE或ALTERTABLE语句的CHECK约束、DEFAULT子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的REFERENCE权限1/6/202422tcl更改和删除用户定义函数更改函数 语法：ALTERFUNCTION……使用新的函数定义代替原来的函数定义保留所有已分配的权限删除函数语法：DROPFUNCTION……删除一个用户定义函数1/6/202423tcl7.3用户定义函数的使用使用标量用户定义函数标量用户定义函数示例使用多语句表值函数多语句表值函数示例使用内嵌表值函数内嵌表值函数示例1/6/202424tcl使用标量用户定义函数标量函数返回RETURNS子句中定义的数据类型的单个数据值在BEGIN…END块之间定义了函数体，包含返回值的一系列Transact-SQL语句返回值可以是除了text、ntext、image、cursor或timestamp之外的任何数据类型额外的限制标量函数内的SQL语句不能包括任何非确定性系统函数1/6/202425tcl标量用户定义函数示例USEPubsGOCREATEFUNCTIONfn_DateFormat(@separatorchar(1))

RETURNSNchar(20)/*将系统当前日期按规定格式输出*/

AS

BEGIN

DECLARE@indate

datetime SELECT@indate=GETDATE() RETURN

CONVERT(Nvarchar(20),datepart(mm,@indate))

+@separator

+CONVERT(Nvarchar(20),datepart(dd,@indate))

+@separator

+CONVERT(Nvarchar(20),datepart(yy,@indate))

END创建函数1/6/202426tcl标量用户定义函数示例调用函数SELECTdbo.fn_DateFormat(':')1/6/202427tcl标量用户定义函数示例USEPubsGOCREATEFUNCTIONfn_DateFormat

(@indate

datetime,@separatorchar(1))

RETURNSNchar(20)

AS

BEGIN

RETURN

CONVERT(Nvarchar(20),datepart(mm,@indate))

+@separator

+CONVERT(Nvarchar(20),datepart(dd,@indate))

+@separator

+CONVERT(Nvarchar(20),datepart(yy,@indate))

END创建函数1/6/202428tcl标量用户定义函数示例SELECTdbo.fn_DateFormat(GETDATE(),':')调用函数1/6/202429tcl使用多语句表值函数多语句表值函数BEGIN和END分隔了函数体RETURNS子句指定table作为返回的数据类型RETURNS子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部1/6/202430tcl使用多语句表值函数函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的DECLARE语句SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量1/6/202431tcl使用多语句表值函数函数体内只允许如下语句游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。仅允许使用FETCH语句通过INTO子句给局部变量赋值，不允许使用FETCH语句将数据返回到客户端修改函数的局部table变量的INSERT、UPDATE和DELETE语句1/6/202432tcl使用多语句表值函数副作用：指的是对函数外某些永久性状态的改变函数体内不允许出现返回非函数返回值的语句以及产生副作用的语句下列语句是不允许的：对数据库表的更新、全局游标语句、创建对象的语句、事务控制语句1/6/202433tcl多语句表值函数示例创建函数USEPubsGOCREATEFUNCTIONfn_Employees(@lengthnvarchar(9))RETURNS@fn_Employeetable/*按格式要求返回雇员的代号和名字*/(EmployeeIDchar(9)NOTNULL,[EmployeeName]nvarchar(61)NOTNULL)ASBEGINIF@length='ShortName'INSERT@fn_EmployeeSELECTEmp_ID,LNameFROMEmployeeELSEIF@length='LongName'INSERT@fn_EmployeeSELECTEmp_ID,(FName+''+LName)FROMEmployeeRETURNEND

1/6/202434tcl多语句表值函数示例调用函数SELECT*FROMdbo.fn_Employees('LongName')或SELECT*FROMdbo.fn_Employees('ShortName')

1/6/202435tcl使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被引用，就像视图一样1/6/202436tcl使用内嵌表值函数关于使用内嵌表值函数的依据和指导方针RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表函数体不由BEGIN和END分隔RETURN指定table作为返回的数据类型不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置1/6/202