第11章 函数和游标

29/50
声明游标参数说明
FORWARD_ONLY：指定游标只能从第一行滚 动到最后一行。这种方式的游标只支持FETCH NEXT 提取选项。 STATIC：静态游标。游标的结果集在打开时建 立在tempdb数据库中。 KEYSET：键集游标。指定当游标打开时，游标 中行的成员和顺序已经固定。 DYNAMIC：动态游标。该类游标反映在结果集 中做的所有更改。 FAST_FORWARD：只向前的游标。 UPDATE [OF column_name [，...n]]：定义游标 内可更新的列。
25/50
11.2.1 游标概念 游标（cursor）包括如下两部分内容：
游标结果集：由SELECT语句返回的查询结果。 游标当前行指针：指向结果集中某一行的指针
游标当前 行指针
…
游标结 果集
26/50
游标特点
允许定位结果集中的特定行。 允许从结果集当前位置检索一行或多行。 支持对结果集中当前行的数据进行修改。 为由其他用户对显示在结果集中的数据所做的更 改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中用于访问结果集 中的数据的 T-SQL 语句。
11.2.1 游标概念 11.2.2 使用游标 11.2.3 游标示例
24/50
概述
由SELECT语句返回的行集被称为结果集。 在执行SELECT语句进行查询时，就可以得到这 个结果集。 关系数据库中的操作会对整个行集起作用。 但有时用户需要对结果集中的每一行或部分行进 行单独的处理，这在SELECT的结果集中是无法 实现的。 游标就是提供这种机制的结果集扩展，它使我们 可以逐行处理结果集。 作为面向集合的DBMS和面向行的程序设计间的 桥梁，使这两种处理方式得以有效沟通。
3/50
1. 基本概念
可以扩展数据操作的功能， 在概念上类似于一般的程序设计语言中定 义的函数。 SQL Server 支持三种用户自定义函数：
标量函数 内联表值函数 多语句表值函数
4/50
11.2 创建和调用标量函数
标量函数是返回单个数据值的函数。
定义标量函数的语法：
CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ] ] ) RETURNS 返回值类型 [ AS ] BEGIN 函数体 RETURN 标量表达式 END
10/50
11.1.3 创建和调用内联表值函数
内联表值函数的返回值是一个表，该表的内 容是一个查询语句的结果。 定义内联表值函数的语法：
CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ]]) RETURNS TABLE [ AS ] RETURN [ ( ] select语句 [ ) ]
CREATE FUNCTION [ 拥有者名.] 函数名 ( [ { @参数名 [AS] 标量数据类型 [ = default ] } [ ,...n ]]) RETURNS 返回变量 TABLE < 表定义 > [ AS ] BEGIN 函数体 RETURN END <表定义> ：：= ({ 列定义|表约束 }[,...n ] )
14/50
11.1.4 创建和调用多语句表值函数
多语句表值函数的功能是视图和存储过程的 组合。 可利用多语句表值函数返回一个表，表中的 内容可由复杂的逻辑和多条SQL语句构建（ 类似于存储过程）。 可以在SELECT语句的FROM子句中使用多 语句表值函数（同视图）。
15/50
创建多语句表值函数
5/50
示例
例1．创建计算立方体的体积的标量函数，此函数有三 个输入参数，分别为立方体的长、宽和高，类型均 为整型，函数的返回值的类型也为整型。 CREATE FUNCTION dbo.CubicVolume (@CubeLength int, @CubeWidth int, @CubeHeight int) RETURNS int AS BEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight ) END
8/50
示例
例4．调用例2定义的函数，查询“计算机系”的 学生姓名和该系学生的选课门数。 SELECT Sname AS 姓名, dbo.f_Count(Sno) AS 选课门数
FROM Student
WHERE Dept = '计算机系'
9/50
示例
例5．调用例3定义的函数，查询第2-4学期开设的每 门课程的课程名、开课学期和考试平均成绩，将查 询结果按学期升序排序。 SELECT Cname AS 课程名, Semester AS 开课学期, dbo.f_AvgGrade(Cno) AS 平均成绩 FROM Course WHERE Semester BETWEEN 2 AND 4 ORDER BY Semester ASC
16/50
示例
例10．定义查询指定系的学生姓名、性别和年龄 类型的多语句表值函数，其中年龄类型列的值为 ：如果该学生的年龄超过该系学生平均年龄2岁， 则为“偏大年龄”；如果在-1和+2范围内，则为 “正常年龄”；如果小于平均年龄-1，则为“偏 小年龄”。
17/50
例10
CREATE FUNCTION f_SType(@dept varchar(20)) RETURNS @retSType table(Sname char(10),Sex char(2),SType char(8)) AS BEGIN DECLARE @AvgAge int SET @AvgAge = (SELECT AVG(Sage) FROM Student WHERE Dept = @dept) INSERT INTO @retSType SELECT Sname, Sex, CASE WHEN Sage > @AvgAge+2 THEN '偏大年龄' WHEN Sage BETWEEN @AvgAge-1 AND @AvgAge+2 THEN '正 常年龄' ELSE '偏小年龄' END FROM Student WHERE Dept = @dept RETURN END
11/50
示例
例6．创建查询指定系的学生学号、姓名和考试平均 成绩的内联表值函数。 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 Dept = @dept GROUP BY S.Sno, Sname )
12/50
示例
例7．创建查询选课门数高于指定门数的学生的姓名 、所在系以及所选的课程名和开课学期。 CREATE FUNCTION dbo.f_MoreCount(@c int) RETURNS TABLE AS RETURN (SELECT Sname, Dept, Cname, Semester FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON o = o WHERE S.Sno IN (SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > @c ))
27/50
11.2.2 使用游标
声明游标 打开游标
提取数据 否
处理完成？
是
关闭游标
释放游标
28/50
1.声明游标 DECLARE cursor_namSTATIC | KEYSET | DYNAMIC | FAST_FORWARD ] FOR select_statement [FOR UPDATE [ OF column_name [,...n ]]]
调用多语句表值函数
对多语句表值函数的使用是放在SELECT语句的 FROM子句部分。
例11．调用例10定义的函数，查询信息系学生的姓 名和年龄类型。 SELECT Sname, SType FROM f_SType('信息系')
19/50
查看用户定义函数
展开数据库 “可编程性 ”“函数” ，在“函数” 下分为：表值 函数、标量值 函数、聚合函 数和系统函数 四类。
13/50
调用内联表值函数
对内联表值函数的使用与视图非常类似，需要放置 在查询语句的FROM子句部分，很象是带参数的视 图。 例8．调用例6定义的内联表值函数，查询计算机系 学生的学号、姓名和考试平均成绩。 SELECT * FROM dbo.f_SnoAvg('计算机系')
例9．调用例7定义的内联表值函数，查询选课门数 超过2门的学生姓名、所在系、选的课程名和课程 开课学期。 SELECT * FROM dbo.f_MoreCount(2)
20/50
修改用户定义函数
修改函数的定义使用ALTER FUNCTION语句 来实现。
语法格式：
ALTER FUNCTION 函数名 <新函数定义语句>
21/50
示例
例12．修改f_SnoAvg函数为：查询指定系的学生学号 、姓名、选课门数和考试平均成绩的内联表值函数。 ALTER FUNCTION dbo.f_SnoAvg(@dept char(20)) RETURNS TABLE AS RETURN ( SELECT S.Sno, Sname, COUNT(*) AS TotalCno, Avg(Grade) AS AvgGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = @dept GROUP BY S.Sno, Sname )