商洛学院经济与管理学院数据库原理及应用课件第十三章 函数和游标.pptVIP

13.2 用户自定义函数（UDF） 13.2.1 基本概念 13.2.2 标量函数 13.2.3 内联表值函数 13.2.4 多语句表值函数 用户自定义函数可以扩展数据操作的功能，它在概念上类似于一般的程序设计语言中定义的函数。 用户自定义函数具有如下优点： 模块化程序设计 执行速度更快 减少网络流量 13.2.2 标量函数 标量用户自定义函数接受0个或多个参数并返回一个值。 CREATE FUNCTION [ 架构名.] 函数名 ( [ { @参数名 [ AS ] 参数数据类型 [ = 默认值 ] } [ ,...n ] ] ) RETURNS 返回值数据类型 [ AS ] BEGIN 函数体 RETURN 标量表达式 END 示例 例1．创建统计指定学生（学号）的选课门数的标量函数。 CREATE FUNCTION dbo.f_Count(@sno char(7)) RETURNS int AS BEGIN DECLARE @x int SELECT @x = count(*) FROM SC WHERE SNO = @sno RETURN @x END 示例 例2．创建查询指定课程（课程号）的考试平均成绩的标量函数。 CREATE FUNCTION dbo.f_AvgGrade(@cno varchar(20)) RETURNS int AS BEGIN DECLARE @avg int SELECT @avg = AVG(Grade) FROM SC WHERE Cno = @cno RETURN @avg END 调用标量函数 调用标量函数时，必须提供至少由两部分组成的名称：函数所属架构名和函数名。 可在任何允许出现表达式的SQL语句中调用标量函数，只要类型一致。 例3．调用例1定义的函数，查询“计算机系”的学生姓名和该系学生的选课门数。 SELECT Sname AS 姓名, dbo.f_Count(Sno) AS 选课门数 FROM Student WHERE Sdept = 计算机系 示例 例4．调用例2定义的函数，查询第2~4学期开设的每门课程的课程名、开课学期和考试平均成绩，将查询结果按学期升序排序。 SELECT Cname AS 课程名, Semester AS 开课学期, dbo.f_AvgGrade(Cno) AS 平均成绩 FROM Course WHERE Semester BETWEEN 2 AND 4 ORDER BY Semester ASC 13.2.3 内联表值函数 内联表值函数的返回值是一个表，该表的内容是一个查询语句的结果。 定义内联表值函数的语法为： CREATE FUNCTION [ 架构名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = 默认值 ] } [ ,...n ] ] ) RETURNS TABLE [ AS ] RETURN [ ( ] SELECT语句 [ ) ] 示例 例5．创建查询指定系的学生学号、姓名和考试平均成绩的内联表值函数。 CREATE FUNCTION dbo.f_SnoAvg(@dept char(20)) RETURNS TABLE AS RETURN ( SELECT S.Sno, Sname, Avg(Grade) AS AvgGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Sdept = @dept GROUP BY S.Sno, Sname ) 示例 例6．创建查询选课门数高于指定门数的学生的姓名、所在系以及所选的课程名和开课学期的内联表值函数。 CREATE FUNCTION dbo.f_MoreCount(@c int) RETURNS TABLE AS RETURN ( SELECT Sname, Sdept, Cname, Semester FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE S.Sno IN ( SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) @c )) 调用内联表值函数 对内联表值函数的使用与视图非常类似，需要放置在查询语句的FROM子句部分，它的作用很象是带参数的