函数的创建与管理

函数的创建与管理第一页，共二十七页，编辑于2023年，星期日函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的例程。返回值可以是单个标量值或结果集。SQLServer2005中有多种函数，根据返回值的类型和是否由系统提供，分为标量函数、表值函数和内置函数。SQLServer2005支持3种用户定义函数：标量函数、表值函数和聚合函数。本章介绍用户定义函数的创建、修改及删除。第二页，共二十七页，编辑于2023年，星期日14.1用户自定义函数简介SQLServer不但提供了系统内置函数，而且还允许用户根据实际需要创建用户自定义函数。用户自定义函数是由一条或多条T-SQL语句组成的子程序，保存在数据库内。它可以具有多个输入参数，并返回一个标量值（单个数据值）或一个表。

第三页，共二十七页，编辑于2023年，星期日用户自定义函数有以下3种类型：返回单值的标量函数；类似于视图的可更新内嵌表值函数；使用代码创建结果集的多语句表值函数。第四页，共二十七页，编辑于2023年，星期日14.2.1标量函数14.2.2内嵌表值函数14.2.3多语句表值函数14.2创建用户自定义函数第五页，共二十七页，编辑于2023年，星期日标量函数类似于系统内置函数。函数的输入参数可以是所有标量数据类型，输出参数的类型可以是除了text、nText、image、cursor、timestamp以外的任何数据类型，函数主体在BEGIN-END块中定义。14.2.1标量函数第六页，共二十七页，编辑于2023年，星期日标量函数标量函数是返回单个值的函数。标量函数可以接受多个参数进行计算，并且返回单个值。标量函数一经定义后，就可以在SQLServer的表达式（如表的计算列）中使用该函数。创建标量函数的语法：CREATEFUNCTIONFunctionName(InputParameters)RETURNSDatatypeASBEGINSqlstatementRETURNExpressionEND第七页，共二十七页，编辑于2023年，星期日创建一个用户定义标量函数fsum，其功能为求两个整数的和。CREATEFUNCTIONdbo.fsum(@num1INT,@num2INT=6)--参数@num2默认值为6RETURNSINT--函数返回值为整数类型ASBEGINRETURN@num1+@num2--返回值表达式ENDGOSELECTdbo.fsum(2,8)SELECTdbo.fsum(7,default)执行结果如下：---------------------------1013第八页，共二十七页，编辑于2023年，星期日下面的用户定义标量函数可以根据学生的学号查询学生的出生日期，由此计算出并返回学生的年龄值。--参数@sid为学生学号，@year为当前年份CREATEFUNCTIONdbo.fage(@sidchar(10),@yearINT=2004)RETURNSINT--函数返回值为整数类型ASBEGINDECLARE@birdateDATETIMESELECT@birdate=stu_birthdateFROMStudentWHEREstu_id=@sidRETURN@year-YEAR(@birdate)--返回值表达式ENDGOSELECTdbo.fage('0901001',2004)SELECTdbo.fage('0901002',default)执行结果如下：---------------------------2624第九页，共二十七页，编辑于2023年，星期日查看定义：select*fromsys.sql_modules

或selectdefinitionfromsys.sql_modules

whereobject_id=OBJECT_ID(‘fsum’)第十页，共二十七页，编辑于2023年，星期日例14-1useBlueSkyDBgocreatefunctionFunBookSale(@bookIDint)returnintasbegindeclare@quantityintselect@quantity=sum(quantity)fromOrdreItemswherebookID=@bookIDif(@quantityisnull) set@quantity=0return@quantityendgo第十一页，共二十七页，编辑于2023年，星期日useBlueSkyDBgoselect*,dbo.FunBookSale(bookID)asSaleQuantityfromBooksgo第十二页，共二十七页，编辑于2023年，星期日调用标量函数变量函数注意事项需要说明的是，标量函数必须是确定性的，这意味着，如果使用同样的输入参数反复调用它，它每次都应当返回同样的结果值。不能在标量函数中使用返回可变数据的函数和全局变量，如@@connections、getgate()、newid()等。在表达式中任何可以使用单个值（数据类型相同）的地方都可以使用标量函数。调用用户定义标量函数时，必须始终使用两个部分构成的名字（owner.functionname）。第十三页，共二十七页，编辑于2023年，星期日14.2.2内嵌表值函数第二种用户定义函数是内嵌表值函数。内嵌表值函数和视图类似，都包含有一条存储的SELECT语句。内嵌表值函数可以使用参数，也可以不使用参数。第十四页，共二十七页，编辑于2023年，星期日创建内嵌表值函数创建用户定义内嵌表值函数的语法格式：CREATEFUNCTIONFunctionName(InputParameters)RETURNSTableASRETURN(selectstatement)例如：CREATEFUNCTIONfstu1()RETURNSTableASRETURN(SELECTstudent.Stu_id,student.Stu_name,student.Stu_addr,class.class_id,class.class_nameFROMStudentINNERJOINClassONStudent.stu_classid=Class.class_id)第十五页，共二十七页，编辑于2023年，星期日调用内嵌表值函数用户定义内嵌表值函数创建以后，可以在SELECT语句的FROM子句中调用它。例如：在SELECT语句的FROM子句中调用内嵌表值函数。SELECTStu_id,Stu_nameFROMfstu1()执行结果如下：Stu_idStu_name----------------------------0901001张三0901002李四 第一次调用内嵌表值函数时，系统性能会有明显的下降，因为系统需要编译函数的代码，并将编译的结果存放于内存中。一旦编译完成后，此后对函数的调用执行速度都将很快。第十六页，共二十七页，编辑于2023年，星期日使用参数与视图相比，内嵌表值函数的一个优点是可以在预编译的SELECT语句中使用参数，而视图则不能使用参数，它只能在调用视图的SELECT语句中使用WHERE子句来实现。以下举例说明视图和函数在运行时限制结果集的不同方法。例如：CREATEVIEWV_stuASSELECTStu_id,Stu_name,Stu_classidFROMStudent第十七页，共二十七页，编辑于2023年，星期日要查询某个班级的学生信息，需要在下面调用视图的SELECT语句的WHERE子句的条件中实现：SELECT*FROMV_stuWHEREStu_classid='09计一'执行结果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001张三 09计一 第十八页，共二十七页，编辑于2023年，星期日与视图相比，在函数中可以通过为预编译的SQLSELECT语句传递补贴的参数对返回的结果集进行限制，如下:创建一个自定义函数。CREATEFUNCTIONdbo.fstu2(@classidchar(10)=null)RETURNSTableASRETURN(SELECTStu_id,Stu_name,Stu_classidFROMStudentWHEREstu_classid=@classidOR@classidISNULL)如果使用default关键字来调用该函数，就会返回对应班级的学生信息：SELECT*FROMdbo.fstu2(DEFAULT)执行结果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001张三 09计一0901002李四 09计二第十九页，共二十七页，编辑于2023年，星期日如果通过输入参数传递了班级代码，函数内预编译的SELECT语句就会返回对应班级的学生信息。SELECT*FROMdbo.fstu2('09计一')执行结果如下：Stu_idStu_nameStu_classid--------------------------------------------0901001张三 09计一第二十页，共二十七页，编辑于2023年，星期日例14.4useBlueSkyDBgocreatefunctionfunbookselect(@catecorynamenvarchar(50))returnstablewithencryptionasreturn(selectb.*fromBooksbjoinCategoriesconb.categoryCode=c.categoryCodewherec.categoryName=b.categoryName)go第二十一页，共二十七页，编辑于2023年，星期日14.2.3多语句表值函数

多语句表值用户定义函数既可以像标量函数那样包含复杂的代码，也可以像内嵌表值函数那样返回一个结果集。多语句表值函数会创建一个表变量，并使用代码对它进行填充，然后返回这个表变量，以便在SELECT语句中使用它。第二十二页，共二十七页，编辑于2023年，星期日创建多语句表值函数创建多语句表值函数的语法格式：CREATEFUNCTIONFunctionName(InputParameters)RETURNS@TableNameTable(Columns)ASBEGINInsertsqlstatementRETURNEND第二十三页，共二十七页，编辑于2023年，星期日创建一个多语句表值用户定义函数，其作用为将属于特定系部的学生信息放入表变量中作为函数的输出。CREATEFUNCTIONfstu3(@deptidchar(10))RETURNS@stuTable(stu_idchar(7),stu_namevarchar(10),dept_idchar(10))ASBEGININSERT@stuSELECTStudent.stu_id,Student.stu_name,Dept.idFROMStudent,Class,DeptWHEREStudent.stu_classid=Class.idANDClass.deptid=Dept.idANDDept.id=@deptidRETURNEND第二十四页，共二十七页，编辑于2023年，星期日调用函数要执行多语句表值函数，可以在SELECT语句的FROM子句中使用它。例如：SELECT*FROMdbo.fstu3('计算机系')执行结果如下：stu_idstu_namedept_id-----------------------------------------0301001张三 计算机系 0301002李四 计算机系第二十五页，共二十七页，编辑于2023年，星期日例14.7useBlueSkyDBgocreatefunctionfunorderofcustomer