搜索
数据库系统原理及应用主讲：林芳SQLServer2005编程--自定义函数§使用函数§实现用户定义函数使用函数§函数是T-SQL的语法元素，它接收一系列参数，计算后给调用者返回值§用法：1.可用于选择和赋值中2.用于过滤规则中3.用于表达式中4.作为检查和默认规则5.替代表1.可用于选择和赋值中declare@d1datetimeset@d1=getdate()select@d1goselectorderid,DateDiff(day,OrderDate,RequiredDate)天数fromordersgo2.用于过滤规则中select*fromorderswhereRequiredDateGetDate()andShippedDateisNULL3.用于表达式中declare@aintset@a=10if@a0print10*sqrt(@a);elseprint10*sqrt(-@a)4.作为检查和默认规则createtablestu1(snochar(8)primarykey,snamechar(8),sendatetimedefaultgetdate()--入学日期)insertintostu1(sno,sname)values('1','111')select*fromstu15.替代表SELECTa.*FROMOPENROWSET('SQLOLEDB','服务器名';'sa';'pass',SELECT*FROMpubs.dbo.authorsORDERBYau_lname,au_fname')ASa实现用户定义函数§用户定义函数§用户定义函数的定义§用户定义函数示例§推荐操作用户定义函数§SQLServer2000允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能§用户定义函数采用零或多个输入参数并返回标量值或表§用户定义函数的输入参数可以是除timestamp、cursor、table之外的任何数据类型；§用户定义函数不支持输出参数；§SQLServer2000支持三种用户定义函数：标量函数多语句表值函数内嵌表值函数用户定义函数---标量函数§标量函数返回一个标量（单值）结果可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数，包括SELECT语句中列的列表和WHERE子句、表达式、表定义中的约束表达式，甚至作为表中列的数据类型标量函数的返回值不能是非标量类型（如cursor或table），不能是timestamp、text、ntext或image数据类型，也不能是用户定义类型；标量函数创建语法CREATEFUNCTIONfunction_name([参数名参数类型[=默认值][,...n]])RETURNS返回值类型[WITHfunction_option[[,]...n]][AS]BEGINfunction_bodyRETURN表达式END使用标量用户定义函数§标量函数返回RETURNS子句中定义的数据类型的单个数据值在BEGIN…END块之间定义了函数体，包含返回值的一系列Transact-SQL语句返回值可以是除了text、ntext、image、cursor和timestamp之外的任何数据类型§额外的限制标量函数只能返回标量值；标量函数体内不能包括任何非确定性系统函数；标量用户定义函数示例——创建函数USENorthwindCREATEFUNCTIONfn_DateFormat(@indatedatetime,@separatorchar(1))RETURNSNchar(20)ASBEGINRETURNCONVERT(Nvarchar(20),datepart(mm,@indate))+@separator+CONVERT(Nvarchar(20),datepart(dd,@indate))+@separator+CONVERT(Nvarchar(20),datepart(yy,@indate))END标量用户定义函数示例——调用函数SELECTdbo.fn_DateFormat(GETDATE(),':')用户定义函数---多语句表值函数§多语句表值函数返回一个由一条或多条T-SQL语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在SELECT语句的FROM子句中被引用，仿佛视图一样多语句表值函数CREATEFUNCTIONfunction_name([参数名参数类型[=默认值][,...n]])RETURNS@return_variableTABLE表定义[WITHfunction_option[[,]...n]][AS]BEGINfunction_bodyRETURNEND使用多语句表值函数§多语句表值函数BEGIN和END分隔了函数体RETURNS子句指定table作为返回的数据类型RETURNS子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部使用多语句表值函数§函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的DECLARE语句SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。仅允许使用FETCH语句通过INTO子句给局部变量赋值，不允许使用FETCH语句将数据返回到客户端修改函数的局部table变量的INSERT、UPDATE和DELETE语句使用多语句表值函数函数体内不允许出现返回非函数返回值的语句以及产生副作用的语句副作用：指的是对函数外某些永久性状态的改变下列语句是不允许的：对数据库表的更新、全局游标语句、创建对象的语句、事务控制语句多语句表值函数示例USENorthwindGOCREATEFUNCTIONfn_Employees(@lengthnvarchar(9))RETURNS@fn_Employeestable(EmployeeIDintPRIMARYKEYNOTNULL,[EmployeeName]nvarchar(61)NOTNULL)ASBEGINIF@length='ShortName'INSERT@fn_EmployeesSELECTEmployeeID,LastNameFROMEmployeesELSEIF@length='LongName'INSERT@fn_EmployeesSELECTEmployeeID,(FirstName+''+LastName)FROMEmployeesRETURNENDSELECT*FROMdbo.fn_Employees('LongName')或SELECT*FROMdbo.fn_Employees('ShortName')用户定义函数---内嵌表值函数§内嵌表值函数返回一个单条SELECT语句产生的结果的表，类似于视图§相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能内嵌表值函数CREATEFUNCTIONfunction_name([参数名参数类型[=默认值][,...n]])RETURNSTABLE[WITHfunction_option[[,]...n]][AS]RETURN[(]select-stmt[)]使用内嵌表值函数§内嵌表值函数返回表，可在FROM子句中被引用，就像视图一样§关于使用内嵌表值函数的依据和指导方针RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表函数体不由BEGIN和END分隔RETURN指定table作为返回的数据类型不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置内嵌表值函数示例USENorthwindGOCREATEFUNCTIONfn_CustomerNamesInRegion(@RegionParameternvarchar(30))RETURNStableASRETURN(SELECTCustomerID,CompanyNameFROMNorthwind.dbo.CustomersWHERERegion=@RegionParameter)SELECT*FROMfn_CustomerNamesInRegion('WA')创建用户定义函数§创建用户定义函数每个完全合法的用户定义函数名（数据库名.拥有者名.函数名）必须惟一语句指定了输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型§在Transact-SQL语句中使用用户定义函数标量用户定义函数可以在任何它们返回值的数据类型可以用的地方使用表值用户定义函数只能在SELECT语句的FROM子句中使用若SELECT子句是在DECLARECURSOR语句中，游标必须是STATIC或READ_ONLY的类型创建用户定义函数§使用标量用户定义函数标量函数以和内置函数相同的形式调用：拥有者名.函数名([参数列表])不可省略拥有者名，也不能用命名参数的形式（例如@参数名=值），且参数也不可以省略（但可以使用DEFAULT关键字指明使用默认值）SQLServer提供少量内置用户定义函数。与普通内置函数不同，它们的名字以“fn_”开头并且使用特定的调用语法：::函数名([参数列表])其中“::”代替了“拥有者名”的位置，表明函数为内置用户定义函数创建用户定义函数使用标量用户定义函数（续）也可以使用EXECUTE语句调用标量函数：EXECUTE@返回变量=函数名(参数列表)使用EXECUTE语句调用时，不必包括“拥有者名”，而且可以使用命名参数：EXECUTE@返回变量=函数名@变量=值[,@变量=值]当使用命名参数的时候，参数的次序不必按照在函数中声明的次序，但必须包括所有参数，不能省略任何参数使用表值用户定义函数表值用户定义函数必须使用和内置函数相同的调用格式：函数名([参数列表])不需要指定“拥有者名”，但必须包括所有定义的参数创建用户定义函数函数的限制非确定性函数：每次使用相同的输入值集调用的时候，返回不同的结果值的函数用户定义函数体中不允许使用内置非确定性函数，包括@@ERROR@@IDENTITY@@ROWCOUNT@@TRANCOUNTAPP_NAMECURRENT_TIMESTAMPCURRENT_USERDATENAMEFORMATMESSAGEGETANSINULLGETDATEGETUTCDATEHOST_IDHOST_NAMEIDENT_INCRIDENT_SEEDIDENTITYNEWIDPERMISSIONSSESSION_USERSTATS_DATESYSTEM_USERTEXTPTRTEXTVALIDUSER_NAME为用户定义函数设置权限§用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有CREATEFUNCTION权限若函数拥有者之外的用户要在Transact-SQL语句中使用函数，则必须先被授予EXECUTE权限若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的REFERENCE权限若在CREATETABLE或ALTERTABLE语句的CHECK约束、DEFAULT子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的REFERENCE权限更改和删除用户定义函数§更改函数语法：ALTERFUNCTION……使用新的函数定义代替原来的函数定义保留所有已分配的权限§删除函数语法：DROPFUNCTION……删除一个用户定义函数推荐操作使用多语句表值函数代替返回表的存储过程在小的结果集上使用复杂的标量函数使用内嵌表值函数创建参数化视图回顾学习完本章后，将能够：§描述用户定义函数的三种类型§创建和更改用户定义函数§创建每种类型的用户定义函数练习：§创建一个标量函数，输入学号得到该学生各门课的平均成绩。§创建一个多语句函数，以课程号为参数，