第14章 函数创建和管理.ppt

第14章 用户自定义函数与事务 ; 函数是接受参数、执行操作（例如复杂计算）并将操作结果以值的形式返回的例程。返回值可以是单个标量值或结果集。SQL Server 2005中有多种函数，根据返回值的类型和是否由系统提供，分为标量函数、表值函数和内置函数。SQL Server 2005支持3种用户定义函数：标量函数、表值函数和聚合函数。本章介绍用户定义函数的创建、修改及删除。;14.1 用户自定义函数简介 ; 用户自定义函数有以下3种类型： 返回单值的标量函数； 类似于视图的可更新内嵌表值函数； 使用代码创建结果集的多语句表值函数。;14.2.1 标量函数 14.2.2 内嵌表值函数 14.2.3 多语句表值函数; 标量函数类似于系统内置函数。函数的输入参数可以是所有标量数据类型，输出参数的类型可以是除了text、nText、image、cursor、timestamp以外的任何数据类型，函数主体在BEGIN-END块中定义。;标量函数;创建一个用户定义标量函数fsum，其功能为求两个整数的和。 CREATE FUNCTION dbo.fsum (@num1 INT, @num2 INT = 6) --参数@num2默认值为6 RETURNS INT --函数返回值为整数类型 AS BEGIN RETURN @num1 + @num2 --返回值表达式 END GO SELECT dbo.fsum (2,8) SELECT dbo.fsum (7, default) 执行结果如下： --------------------------- 10 13;下面的用户定义标量函数可以根据学生的学号查询学生的出生日期，由此计算出并返回学生的年龄值。 --参数@sid为学生学号，@year为当前年份 CREATE FUNCTION dbo.fage(@sid char(10),@year INT = 2004) RETURNS INT --函数返回值为整数类型 AS BEGIN DECLARE @birdate DATETIME SELECT @birdate = stu_birthdate FROM Student WHERE stu_id = @sid RETURN @year - YEAR(@birdate) --返回值表达式 END GO SELECT dbo.fage(0901001,2004) SELECT dbo.fage(0901002, default) 执行结果如下： --------------------------- 26 24;查看定义： select * from sys.sql_modules 或 select definition from sys.sql_modules where object_id=OBJECT_ID(‘fsum’) ;例14-1;;调用标量函数;14.2.2 内嵌表值函数;创建内嵌表值函数;调用内嵌表值函数;使用参数;;与视图相比，在函数中可以通过为预编译的SQL SELECT语句传递补贴的参数对返回的结果集进行限制，如下: 创建一个自定义函数。 CREATE FUNCTION dbo.fstu2(@classid char(10) = null) RETURNS Table AS RETURN ( SELECT Stu_id, Stu_name, Stu_classid FROM Student WHERE stu_classid = @classid OR @classid IS NULL) 如果使用default关键字来调用该函数，就会返回对应班级的学生信息： SELECT * FROM dbo.fstu2(DEFAULT) 执行结果如下： Stu_id Stu_name Stu_classid -------------------------------------------- 0901001 张三 09计一 0901002 李四 09计二 ;如果通过输入参数传递了班级代码，函数内预编译的SELECT语句就会返回对应班级的学生信息。 SELECT * FROM dbo.fstu2(09计一) 执行结果如下： Stu_id Stu_name Stu_classid -------------------------------------------- 0901001 张三 09计一 ;例14.4;14.2.3 多语句表值函数 ;创建多语句表值函数;创