函数的创建与管理.ppt

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

2、标量值（单个数据值）或一个表。,用户自定义函数有以下3种类型： 返回单值的标量函数； 类似于视图的可更新内嵌表值函数； 使用代码创建结果集的多语句表值函数。,14.2.1 标量函数 14.2.2 内嵌表值函数 14.2.3 多语句表值函数,14.2 创建用户自定义函数,标量函数类似于系统内置函数。函数的输入参数可以是所有标量数据类型，输出参数的类型可以是除了text、nText、image、cursor、timestamp以外的任何数据类型，函数主体在BEGIN-END块中定义。,14.2.1 标量函数,标量函数,标量函数是返回单个值的函数。标量函数可以接受多个参数进行计算，并且返回单个值。标

3、量函数一经定义后，就可以在SQL Server的表达式（如表的计算列）中使用该函数。 创建标量函数的语法： CREATE FUNCTION FunctionName(InputParameters) RETURNS Datatype AS BEGIN Sqlstatement RETURN Expression END,创建一个用户定义标量函数fsum，其功能为求两个整数的和。 CREATE FUNCTION dbo.fsum (num1 INT, num2 INT = 6) -参数num2默认值为6 RETURNS INT -函数返回值为整数类型 AS BEGIN RETURN num1 +

4、 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

5、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,use BlueSkyDB go create functi

6、on FunBookSale(bookID int) return int as begin declare quantity int select quantity=sum(quantity) from OrdreItems where bookID=bookID if(quantity is null) set quantity =0 return quantity end go,use BlueSkyDB go select * ,dbo.FunBookSale(bookID) as SaleQuantity from Books go,调用标量函数,变量函数注意事项 需要说明的是，标量

7、函数必须是确定性的，这意味着，如果使用同样的输入参数反复调用它，它每次都应当返回同样的结果值。 不能在标量函数中使用返回可变数据的函数和全局变量，如connections、getgate()、newid()等。 在表达式中任何可以使用单个值（数据类型相同）的地方都可以使用标量函数。调用用户定义标量函数时，必须始终使用两个部分构成的名字（owner.functionname）。,14.2.2 内嵌表值函数,第二种用户定义函数是内嵌表值函数。内嵌表值函数和视图类似，都包含有一条存储的SELECT语句。 内嵌表值函数可以使用参数，也可以不使用参数。,创建内嵌表值函数,创建用户定义内嵌表值函数的语法格

8、式： CREATE FUNCTION FunctionName(InputParameters) RETURNS Table AS RETURN (select statement) 例如： CREATE FUNCTION fstu1() RETURNS Table AS RETURN ( SELECT student.Stu_id, student.Stu_name, student. Stu_addr,class.class_id, class.class_name FROM Student INNER JOIN Class ON Student.stu_classid = Class.

9、class_id ),调用内嵌表值函数,用户定义内嵌表值函数创建以后，可以在SELECT语句的FROM子句中调用它。 例如：在SELECT语句的FROM子句中调用内嵌表值函数。 SELECT Stu_id, Stu_name FROM fstu1() 执行结果如下： Stu_id Stu_name - 0901001 张三 0901002 李四 第一次调用内嵌表值函数时，系统性能会有明显的下降，因为系统需要编译函数的代码，并将编译的结果存放于内存中。一旦编译完成后，此后对函数的调用执行速度都将很快。,使用参数,与视图相比，内嵌表值函数的一个优点是可以在预编译的SELECT语句中使用参数，而视图

10、则不能使用参数，它只能在调用视图的SELECT语句中使用WHERE子句来实现。以下举例说明视图和函数在运行时限制结果集的不同方法。 例如： CREATE VIEW V_stu AS SELECT Stu_id, Stu_name, Stu_classid FROM Student,要查询某个班级的学生信息，需要在下面调用视图的SELECT语句的WHERE子句的条件中实现： SELECT * FROM V_stu WHERE Stu_classid = 09计一 执行结果如下： Stu_id Stu_name Stu_classid - 0901001 张三 09计一,与视图相比，在函数中可以通

11、过为预编译的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) 执行结果

12、如下： 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,use BlueSkyDB go create function funbookselect(catecoryname nvarchar(50) returns table with encryp

13、tion as return ( select b.* from Books b join Categories c on b.categoryCode=c.categoryCode where c.categoryName=b.categoryName ) go,14.2.3 多语句表值函数,多语句表值用户定义函数既可以像标量函数那样包含复杂的代码，也可以像内嵌表值函数那样返回一个结果集。多语句表值函数会创建一个表变量，并使用代码对它进行填充，然后返回这个表变量，以便在SELECT语句中使用它。,创建多语句表值函数,创建多语句表值函数的语法格式： CREATE FUNCTION Functi

14、onName(InputParameters) RETURNS TableName Table(Columns) AS BEGIN Insert sqlstatement RETURN END,创建一个多语句表值用户定义函数，其作用为将属于特定系部的学生信息放入表变量中作为函数的输出。 CREATE FUNCTION fstu3(deptid char(10) RETURNS stu Table (stu_id char(7), stu_name varchar(10), dept_id char(10) AS BEGIN INSERT stu SELECT Student.stu_id, S

15、tudent.stu_name,Dept.id FROM Student,Class,Dept WHERE Student.stu_classid = Class.id AND Class.deptid = Dept.id AND Dept.id = deptid RETURN END,调用函数,要执行多语句表值函数，可以在SELECT语句的FROM子句中使用它。 例如： SELECT * FROM dbo.fstu3(计算机系) 执行结果如下： stu_id stu_name dept_id - 0301001 张三 计算机系 0301002 李四 计算机系,例14.7,use BlueSk

16、yDB go create function funorderofcustomer(customerID int) returns orderofcustomer table ( customerName nvarchar(20) not null, orderID int not null, orderDate datetime not null, title nvarchar(50) not null, unitPrice pricedecimal not null, quantity int not null ) as begin insert into orderofcustomer select c.customerName,o.orderID,o.orderdate,b