游标

  1 --游标知识点
--1、在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句时， 游标将自动设定INSENSITIVE 选项。
--2、SCROLL表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。
--例8.4.1： 游标演示
declare c_cursor scroll cursor for
select * from student
open c_cursor
fetch last from c_cursor
fetch prior from c_cursor
fetch absolute 2 from c_cursor
fetch relative 2 from c_cursor
fetch relative -2 from c_cursor
fetch first from c_cursor
fetch prior from c_cursor
fetch next from c_cursor
fetch c_cursor
close c_cursor
deallocate c_cursor
--例8.4.2 定义一个查询全部姓“张”的学生姓名的游标，并使用FETCH NEXT逐个提取这些行。
declare sname_cur1 cursor for
select sname from student
where sname like '张%'
open sname_cur1
fetch next from sname_cur1
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM Sname_cur1
END
CLOSE Sname_cur1
DEALLOCATE Sname_cur1
--例8.4.3 定义一个查询全部姓“张”的学生姓名和所在系的游标，但将FETCH语句的输出保存在局部变量中，而不是直接返回给客户端。用PRINT语句将变量组合成一个字符串，字符串形式为：系名＋“学生：”＋学生姓名。 
DECLARE @sname varchar(10), @dept varchar(30)
DECLARE Sname_cur2 CURSOR FOR
   SELECT Sname, Sdept FROM Student
   WHERE Sname LIKE '张%'
   ORDER BY Sage
OPEN Sname_cur2
FETCH NEXT FROM Sname_cur2 INTO @sname, @dept 
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @dept + '学生: ' + @sname 
   FETCH NEXT FROM Sname_cur2 INTO @sname, @dept
END
CLOSE Sname_cur2
DEALLOCATE Sname_cur2
--例8.4.4 声明查询计算机系学生姓名、选的课程名和成绩的游标，并将游标内容按成绩降序排序。
DECLARE CS_cursor SCROLL CURSOR FOR
  SELECT Sname, Cname, Grade FROM Student S 
  JOIN SC ON S.Sno = SC.Sno
  JOIN Course C ON C.Cno = SC.Cno
  WHERE Sdept = 'cs'
  ORDER BY Grade DESC
OPEN CS_cursor
fetch last from cs_cursor
CLOSE CS_cursor
DEALLOCATE CS_cursor
--例8.4.5 生成显示如下报表形式的游标：报表首先列出一门课程的课程号和课程名（只针对有人选的课程），然后在此课程下列出选了此门课程且成绩大于等于80的学生姓名、所在系和此门课程的考试成绩；然后再列出下一门课程的课程号和课程名，然后在此课程下列出选了此门课程且成绩大于等于80的学生姓名、所在系和此门课程的考试成绩；依此类推，直到列出全部课程。
declare show_course cursor for
select cno, cname
from course c
where cno in (select cno from sc)
order by cno
open show_course
declare @cno char(10), @cname char(20)
fetch next from show_course into @cno, @cname
while @@FETCH_STATUS = 0
begin
    print @cno + '   ' + @cname
    print '--------------'
    declare show_student cursor for
    select sname, sdept, grade
    from student s, course c, sc
    where s.sno = sc.sno and c.cno = sc.cno and c.cno = @cno and grade >= 80
    open show_student
    declare @sname char(10), @sdept char(10), @grade int
    fetch next from show_student into @sname, @sdept, @grade
    while @@fetch_status = 0
    begin
        print @sname + ' ' + @sdept + ' ' + convert(varchar(10), @grade)--注意类型转换
        fetch next from show_student into @sname, @sdept, @grade
    end
    close show_student
    deallocate show_student
    fetch next from show_course into @cno, @cname
end
close show_course
deallocate show_course
--例：把年龄为20岁的第三个学生的年龄改为18。
declare c scroll cursor for
select sno,sage from student
where sage=20
open c
fetch absolute 3 from c
update student
set sage=18
where current of c
close c
deallocate c
--违反了 PRIMARY KEY 约束“PK_student”。不能在对象“dbo.student”中插入重复键。重复键值为 (200515006)。

--例：把年龄为20岁的第五个学生从学生表中删除。
declare delete_s scroll cursor for
select *
from student s
where sage = 20
open delete_s
fetch absolute 5 from delete_s
delete
from student
where current of delete_s
close delete_s
deallocate delete_s
--CURRENT OF cursor_name： 
--表示当前游标指针所指的当前行数据。CURRENT OF 只能在UPDATE和DELETE语句中使用。 

create table 借书表
(
    学号 char(10),
    索书号 char(10),
    结束日期 date,
    应还日期 date,
    是否续借 char(2)
)
create table 欠款表
(
    学号 char(10),
    日期 date,
    欠款金额 int
)

create table 还书表
(
    学号 char(10),
    索书号 char(10),
    还书日期 date
)


create table book
(
    bno char(10) primary key,
    bname char(20),
    grade int
)


declare b1 cursor for
select bname, grade
from book

declare @bname char(10), @grade int
open b1
fetch next from b1 into @bname, @grade
while @@FETCH_STATUS = 0
begin
    set @pr = case
    when @dj >= 50 then '价格高'
    when @dj >= 30 then '价格偏高' 
    else '价格太低'
    end
    print 'xxxxxxxx'
    fetch next from b1 into @bname, @grade
end