游标是取用一组数据并能够一次与一个单独的数据进行交互的方法。关系数据库中的操作会对整个数据行集起作用。由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行。
一个完整的游标由5部分组成:
(1)声明游标
(2)打开游标
(3)从一个游标中查找信息
(4)关闭游标
(5)释放游标
1.使用游标打印OrderManagement库中各订单中的总金额,要求按总金额降序排。
声明游标
declare cur1 cursor
for select 订单号, SUM(单价*数量)
from order_detail
group by(订单号)
order by SUM(数量*单价)
打开游标
open cur1
declare @ono char(6)
declare @mon int
fetch next from cur1 into @ono, @mon
print '订单号 总金额'
print '--------------'
while @@FETCH_STATUS = 0
begin
print @ono + ' ' + cast(@mon as char(5))
fetch next from cur1 into @ono, @mon
end
关闭游标
close cur1
释放游标
deallocate cur1
2.使用游标提取学生课程库中3-105课的前三名学生的信息和后三名学生的信息, 包括学号、姓名、课程名和成绩。(不考虑成绩相同的情况)
declare cur2 scroll cursor
for select student.sno, sname, cname, degree
from student, course, score
where student.sno = score.sno and score.cno = course.cno and course.cno = '3-105'
order by degree
open cur2
declare @ssno char(5)
declare @ssname char(10)
declare @ccname char(16)
declare @ddgree float
-- fetch next from cur2 --提取下一行
-- fetch last from cur2 --提取最后一行
-- fetch prior from cur2 --提取当前行的前一行
print '学号 姓名 课程名 成绩'
print '---------------------------------'
fetch absolute 1 from cur2 into @ssno, @ssname, @ccname, @ddgree --提取第一行
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
fetch absolute 2 from cur2 into @ssno, @ssname, @ccname, @ddgree --提取第二行
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
fetch absolute 3 from cur2 into @ssno, @ssname, @ccname, @ddgree --提取第三行
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
fetch last from cur2 into @ssno, @ssname, @ccname, @ddgree --提取最后一行
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
fetch prior from cur2 into @ssno, @ssname, @ccname, @ddgree --提取当前行的前一行
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
fetch prior from cur2 into @ssno, @ssname, @ccname, @ddgree
print @ssno + @ssname + @ccname + cast(@ddgree as char(5))
close cur2
deallocate cur2
3.使用游标修改OrderManagement库的order_detail表中订单号为***的单价最高的器件的单价和单价最低的器件的单价。
declare cur3 scroll cursor
for select *
from order_detail
where 订单号 = 'OR-01C'
order by 单价
for update
open cur3
fetch first from cur3
update order_detail
set 单价 = 100
where current of cur3
fetch last from cur3
update order_detail
set 单价 = 200
where current of cur3
close cur3
deallocate cur3