第5章 视图、存储过程与函数概要1.ppt

5.3.1 函数概念 函数是由一个或多个SQL 语句组成的子程序，它可用于封装代码以提供代码共享的功能。 在概念上类似于一般的程序设计语言中定义的函数。 SQL Server 2000 支持三种用户自定义函数： 标量函数 内嵌表值函数 多语句表值函数 5.3.2 创建和调用标量函数 定义标量函数 CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ] ] ) RETURNS 返回值类型 [ AS ] BEGIN 函数体 RETURN 标量表达式 END 创建示例 例1．创建计算立方体的体积的函数。三个输入参数，分别为立方体的长、宽和高，类型均为整型。 CREATE FUNCTION dbo.CubicVolume (@CubeLength int, @CubeWidth int, @CubeHeight int) RETURNS int AS BEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight ) END 创建示例 例2．创建统计指定课程的选课人数的函数。 CREATE FUNCTION dbo.f_count(@cname varchar(20)) RETURNS int AS BEGIN DECLARE @x int SELECT @x=count(*) from course c join sc on sc.cno = c.cno WHERE cname = @cname RETURN @x END 调用标量函数 调用例1所定义的函数，计算长、宽、高分别为4、6、8的立方体的体积。 SELECT dbo.CubicVolume(4,6,8) 调用例2所定义的函数，查询“VB”课程的选课人数。 SELECT cname as 课程名, dbo.f_count(VB) as 选课人数 FROM course WHERE cname = VB 5.3.3 创建和定义内嵌表值函数 定义内嵌表值函数： CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ] ] ) RETURNS TABLE [ AS ] RETURN [ ( ] select语句 [ ) ] 创建示例 例3．创建查询指定系的学生的姓名、年龄和性别的函数。 CREATE FUNCTION dbo.f_sdept (@dept varchar(20)) RETURNS TABLE AS RETURN ( SELECT sname, sage, ssex from student WHERE sdept = @dept) 创建示例 例4．创建查询指定课程中成绩大于指定分数的学生的姓名、所在系和这门课程的考试成绩。 CREATE FUNCTION dbo.f_grade ( @cname varchar(20), @grade int) RETURNS TABLE AS RETURN ( SELECT sname, sdept, grade from student s JOIN sc ON s.sno = sc.sno JOIN course c ON c.cno = sc.cno WHERE cname = @cname and grade @grade) 调用内嵌表值函数 利用例3定义的内嵌表值函数，查询“计算机系”学生的信息。 SELECT * FROM dbo.f_sdept (计算机系) 利用例4所定义的内嵌表值函数，查询“VB”课程成绩大于70分的学生信息。 SELECT * FROM dbo.f_grade (VB, 70) 5.3.4 创建和调用多语句表值函数 定义多语句表值函数： CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ] ] ) RETURNS 返回变量 TABLE 表定义 [ AS ] BEGIN 函数体 RETURN END 表定义 ：：= ( { 列定义 | 表约束 } [ , ...n ] ) 创建示例 例5．创建返回考试成绩为指定层次的学生的学号、姓