《函数的创建与管理.ppt》由会员分享，可在线阅读，更多相关《函数的创建与管理.ppt（27页珍藏版）》请在三一办公上搜索。
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 DatatypeASBEGIN Sqlstatement RETURN ExpressionEND,创建一个用户定义标量函数fsum，其功能为求两个整数的和。CREATE FUNCTION dbo.fsum(num1 INT,num2 INT=6)-参数num2默认值为6RETURNS INT-函数返回值为整数类型ASBEGIN RETURN num1+num2-返回值表达式ENDGOSELECT
4、 dbo.fsum(2,8)SELECT dbo.fsum(7,default)执行结果如下：-1013,下面的用户定义标量函数可以根据学生的学号查询学生的出生日期，由此计算出并返回学生的年龄值。-参数sid为学生学号，year为当前年份CREATE FUNCTION dbo.fage(sid char(10),year INT=2004)RETURNS INT-函数返回值为整数类型ASBEGIN DECLARE birdate DATETIME SELECT birdate=stu_birthdate FROM Student WHERE stu_id=sid RETURN year-YEA
5、R(birdate)-返回值表达式 ENDGOSELECT dbo.fage(0901001,2004)SELECT dbo.fage(0901002,default)执行结果如下：-2624,查看定义：select*from sys.sql_modules 或select definition from sys.sql_modules where object_id=OBJECT_ID(fsum),例14-1,use BlueSkyDBgocreate function FunBookSale(bookID int)return intasbegindeclare quantity ints
6、elect quantity=sum(quantity)from OrdreItems where bookID=bookIDif(quantity is null)set quantity=0return quantity endgo,use BlueSkyDBgoselect*,dbo.FunBookSale(bookID)as SaleQuantityfrom Booksgo,调用标量函数,变量函数注意事项需要说明的是，标量函数必须是确定性的，这意味着，如果使用同样的输入参数反复调用它，它每次都应当返回同样的结果值。不能在标量函数中使用返回可变数据的函数和全局变量，如connection
7、s、getgate()、newid()等。在表达式中任何可以使用单个值（数据类型相同）的地方都可以使用标量函数。调用用户定义标量函数时，必须始终使用两个部分构成的名字（owner.functionname）。,14.2.2 内嵌表值函数,第二种用户定义函数是内嵌表值函数。内嵌表值函数和视图类似，都包含有一条存储的SELECT语句。内嵌表值函数可以使用参数，也可以不使用参数。,创建内嵌表值函数,创建用户定义内嵌表值函数的语法格式：CREATE FUNCTION FunctionName(InputParameters)RETURNS TableASRETURN(select statement)
8、例如：CREATE FUNCTION fstu1()RETURNS TableASRETURN(SELECT student.Stu_id,student.Stu_name,student.Stu_addr,class.class_id,class.class_nameFROM Student INNER JOIN ClassON Student.stu_classid=Class.class_id),调用内嵌表值函数,用户定义内嵌表值函数创建以后，可以在SELECT语句的FROM子句中调用它。例如：在SELECT语句的FROM子句中调用内嵌表值函数。SELECT Stu_id,Stu_nam
9、e FROM fstu1()执行结果如下：Stu_id Stu_name-0901001 张三0901002 李四第一次调用内嵌表值函数时，系统性能会有明显的下降，因为系统需要编译函数的代码，并将编译的结果存放于内存中。一旦编译完成后，此后对函数的调用执行速度都将很快。,使用参数,与视图相比，内嵌表值函数的一个优点是可以在预编译的SELECT语句中使用参数，而视图则不能使用参数，它只能在调用视图的SELECT语句中使用WHERE子句来实现。以下举例说明视图和函数在运行时限制结果集的不同方法。例如：CREATE VIEW V_stuASSELECT Stu_id,Stu_name,Stu_cla
10、ssid FROM Student,要查询某个班级的学生信息，需要在下面调用视图的SELECT语句的WHERE子句的条件中实现：SELECT*FROM V_stuWHERE Stu_classid=09计一执行结果如下：Stu_id Stu_name Stu_classid-0901001 张三 09计一,与视图相比，在函数中可以通过为预编译的SQL SELECT语句传递补贴的参数对返回的结果集进行限制，如下:创建一个自定义函数。CREATE FUNCTION dbo.fstu2(classid char(10)=null)RETURNS TableASRETURN(SELECT Stu_id
11、,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 St
12、u_name Stu_classid-0901001 张三 09计一,例14.4,use BlueSkyDBgocreate function funbookselect(catecoryname nvarchar(50)returns tablewith encryptionas return(select b.*from Books b join Categories c on b.categoryCode=c.categoryCodewhere c.categoryName=b.categoryName)go,多语句表值函数,多语句表值用户定义函数既可以像标量函数那样包含复杂的代码，也可
13、以像内嵌表值函数那样返回一个结果集。多语句表值函数会创建一个表变量，并使用代码对它进行填充，然后返回这个表变量，以便在SELECT语句中使用它。,创建多语句表值函数,创建多语句表值函数的语法格式：CREATE FUNCTION FunctionName(InputParameters)RETURNS TableName Table(Columns)ASBEGINInsert sqlstatementRETURN END,创建一个多语句表值用户定义函数，其作用为将属于特定系部的学生信息放入表变量中作为函数的输出。CREATE FUNCTION fstu3(deptid char(10)RETUR
14、NS stu Table(stu_id char(7),stu_name varchar(10),dept_id char(10)ASBEGININSERT stu SELECT Student.stu_id,Student.stu_name,Dept.id FROM Student,Class,DeptWHERE Student.stu_classid=Class.id AND Class.deptid=Dept.id AND Dept.id=deptidRETURN END,调用函数,要执行多语句表值函数，可以在SELECT语句的FROM子句中使用它。例如：SELECT*FROM dbo.
15、fstu3(计算机系)执行结果如下：stu_id stu_name dept_id-0301001 张三 计算机系 0301002 李四 计算机系,例14.7,use BlueSkyDBgocreate 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 price
16、decimal not null,quantity int not null)asbegininsert into orderofcustomerselect c.customerName,o.orderID,o.orderdate,b.title,b.unitPrice,i.quantity from Customers c join Orders o on c.customerID=o.customerID join OrderItems i on o.orderID=i.orderID join Books b on b.bookID=i.bookIDwhere c.customerID=customerIDreturnendgo,14.3 管理函数,1、修改函数用ALTER修改函数2、删除函数用DROP删除函数,
