数据库-游标

16
游标的使用
WHILE ( @@FETCH_STATUS=0 ) BEGIN --显示变量@sName和@score中的值 SELECT @sName 学生姓名, @score 课程成绩 SET @sumScore=@sumScore+@score --计算总分 SET @countScore=@countScore+1 --计算选课人数 FETCH myCur INTO @sName, @score --获取下一个游标值 END IF @countScore>0 SELECT @sumScore/@countScore 课程平均分 ELSE SELECT 0.00 课程平均分 CLOSE myCur --关闭游标 DEALLOCATE myCur --释放游标
SET @sumScore=@sumScore+@score SET @countScore=@countScore+1 --计算总分 --计算选课人数
• 提取下一条游标记录： FETCH myCur INTO @sName, @score • 重复⑤，直到全部游标记录处理完毕，退出循环。
⑥处理完全部游标记录后：
获取游标数据
5.ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar}：给出 读取数据的位置与游标头或当前位置的关系，其中n必须
为整型常量， @nvar 必须为smallint、tinyint或int型的。
【例】 从游标XS_CUR1中提取数据。设该游标已经声明 并打开。 FETCH NEXT FROM XS_CUR1 执行结果如下：
1. Next：读取当前行的下一行，并使其置为当前行。如fetch next为对游标 的第一次提取操作，则读取第一行，next为默认值。 2.prior：读取当前行的前一行，并使其置为当前行。如是第一次操作，则 无值返回，游标被置于第一行之前。 3.first：读取第一行，并使其置为当前行。 st：读取最后一行，并使其置为当前行。
13
游标的使用
②要获得该课程的平均分，必须首先计算选课人数和总分
• 定义计数器和累加器变量@countScore、@sumScore，初始值为0 DECLARE @countScore smallint, @sumScore int SET @countScore=0 SET @sumScore=0
游标简介
• 一个对表进行操作的SQL语句(如select)通常都可产生或处 理一组记录，但是许多应用不能把整个结果集作为一个单元 来处理，所以就需要一种机制来保证每次处理结果其中的一 行或几行，游标（cursor）就提供了这种机制。 • SQL Server通过游标提供了对一个结果集进行逐行处理的能 力，游标可看做一种特殊的指针，它与某个查询结果相联系， 可以指向结果集的任意位置，以便对指定位置的数据进行处 理。使用游标可以在查询数据的同时对数据进行处理。 • 游标是系统为用户开设的一个数据缓冲区，存放SQL语句 的结果数据集，每个游标区都有一个名字，通过移动游标名 代表的指针来访问数据集中的数据
– 为游标集指定一个游标，该游标指向游标集中的第1个元 组
格式：Open 游标名；
例：Open cur_sc
打开游标
打开游标后，可以使用全局变量@@CURSOR_ROWS查看游标 集中数据行的数目。全局变量@@CURSOR_ROWS中保存着最后 打开的游标中的数据行数。当其值为0时，表示没有游标打开；当 其值为m（m为正整数）时，游标已被完全填充，m是游标中的数 据行数。 【例】 定义游标XS_CUR3，然后打开该游标，输出其行数。 DECLARE XS_CUR3 CURSOR SCROLL FOR SELECT 学号, 姓名, 总学分 FROM XSB FOR UPDATE OF 总学分 OPEN XS_CUR3 SELECT '游标XS_CUR3数据行数' = @@CURSOR_ROWS
游标的使用步骤
使用游标需要经历五个步骤：
– 定义游标：DECLARE – 打开游标：OPEN – 逐行提取游标集中的行：FETCH – 关闭游标：CLOSE – 释放游标：DEALLOCATE
2
• 游标的定义
游标的定义
DECLARE <游标名>[SCROLL]CURSOR FOR<SELECT语句> [for [read only|update {of <列名>}]
FETCH LAST FROM XS_CUR2 读取最后一行（当前行为最后一行），结果如下：
获取游标数据
FETCH RELATIVE -2 FROM XS_CUR2 读取当前行的上两行（当前行为倒数第三行），结果如下：
FETCH语句的执行状态保存在全局变量 @@FETCH_STATUS中，其值为0表示上一个FETCH执行成功； 为-1表示所要读取的行不在结果集中；为2表示被提取的行已 不存在（已被删除）。 例如，接着上例继续执行如下语句： FETCH RELATIVE 3 FROM XS_CUR2 SELECT 'FETCH执行情况' = @@FETCH_STATUS 执行结果如下：
• 游标可以放在触发器和存储过程中
17
③定义两个变量@sName和@score，用于接收游标集中当前游标中的学 生姓名和相应的选课成绩
DECLARE @sName varchar(20), @score tinyint
④由于FETCH命令每次仅从游标集中提取一条记录，必须通过一个循环来 重复提取，直到游标集中的全部记录被提取
• 全局变量 @@FETCH_STATUS用于判断是否正确地从游标 集中提取到了记录； • @@FETCH_STATUS=0表示已经正确提取到了游标记录； • 循环语句为：
获取游标数据
【例】 从游标XS_CUR2中提取数据。设该游标已经声明。 OPEN XS_CUR2 FETCH FIRST FROM XS_CUR2 读取游标第一行（当前行为第一行），结果如下：
FETCH NEXT FROM XS_CUR2 读取下一行（当前行为第二行），结果如下：
获取游标数据
FETCH PRIOR FROM XS_CUR2 读取上一行（当前行为第一行），结果如下：
• 变量列表赋值并显示的语句的语法为：
SELECT <@variableName> [= <expr | columnName>]
[, <@variableName> [= <expr | columnName>] ... ]
• 表达式列表的显示语句的语法为：
SELECT <expr> [, <expr>... ]
游标的使用
• [例] 创建一个游标，逐行显示选修了《计算机原理》课程的学生姓 名、相应成绩和该课程的平均分。 • 分析：
①选修《计算机原理》课程的同学可能不止一个，需要 使用游标查询选修该门课程的学生姓名和相应的选课 成绩。 定义游标为：
DECLARE myCur CURSOR FOR SELECT studentName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND courseName='计算机原理'
• SCROLL: 说明所声明的游标可以前滚、后滚，可使用所有的提取 选项。如省略，则只能使用NEXT提取选项。 • [ FOR [ READ ONLY | UPDATE {OF <列名>}]：
– READ ONLY表示当前游标集中的元组仅可以查询，不能修改； – UPDATE {OF <列名>}表示可以对当前游标集中的元组进行更新操作。如果有OF <列名>，表示仅可以对游标集中指定的属性列进行更新操作。 – 缺省为UPDATE
WHILE ( @@FETCH_STATUS=0 )
14
游标的使用
⑤在循环体内：
• 首先显示所提取到的学生姓名和相应的选课成绩，使用语 句：
SELECT @sName 学生姓名, @score 课程成绩
• 其次，计数器@countScore进行计数，并将提取到的成绩累 加到变量@sumScore中。语句为：
定义一个能够存放sc表数据的游标
Declare cur_sc cursor for
Select * from sc
打开游标
– 游标定义后，如果要使用游标，必须先打开游 标。
• 打开游标操作表示：
– 系统按照游标的定义从数据库中将数据检索出来，放在内 存的游标集中(如果内存不够，会放在临时数据库中)
• 关闭和释放游标 • 对计数器@countScore进行判断： 如果为0，表示没有同学选修，其平均分为0； 否则，平均分等于总分除以选课人数。
15
游标的使用
⑦程序如下：
/* 定义变量及赋初值 */ DECLARE @sName varchar(20), @score tinyint DECLARE @sumScore int, @countScore smallint SET @sumScore=0 SET @countScore=0 --定义游标 DECLARE myCur CURSOR FOR SELECT studentName, score FROM Student a, Course b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND courseName='计算机原理' OPEN myCur --打开游标 --获取当前游标的值放到变量@sName和@score中 FETCH myCur INTO @sName, @score