游标

  1 --游标知识点  2 --1、在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句时， 游标将自动设定INSENSITIVE 选项。  3 --2、SCROLL表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。  4 --例8.4.1： 游标演示  5 declare c_cursor scroll cursor for  6 select * from student  7 open c_cursor  8 fetch last from c_cursor  9 fetch prior from c_cursor 10 fetch absolute 2 from c_cursor 11 fetch relative 2 from c_cursor 12 fetch relative -2 from c_cursor 13 fetch first from c_cursor 14 fetch prior from c_cursor 15 fetch next from c_cursor 16 fetch c_cursor 17 close c_cursor 18 deallocate c_cursor 19 --例8.4.2 定义一个查询全部姓“张”的学生姓名的游标，并使用FETCH NEXT逐个提取这些行。 20 declare sname_cur1 cursor for 21 select sname from student 22 where sname like '张%' 23 open sname_cur1 24 fetch next from sname_cur1 25 WHILE @@FETCH_STATUS = 0 26 BEGIN 27    FETCH NEXT FROM Sname_cur1 28 END 29 CLOSE Sname_cur1 30 DEALLOCATE Sname_cur1 31 --例8.4.3 定义一个查询全部姓“张”的学生姓名和所在系的游标，但将FETCH语句的输出保存在局部变量中，而不是直接返回给客户端。用PRINT语句将变量组合成一个字符串，字符串形式为：系名＋“学生：”＋学生姓名。  32 DECLARE @sname varchar(10), @dept varchar(30) 33 DECLARE Sname_cur2 CURSOR FOR 34    SELECT Sname, Sdept FROM Student 35    WHERE Sname LIKE '张%' 36    ORDER BY Sage 37 OPEN Sname_cur2 38 FETCH NEXT FROM Sname_cur2 INTO @sname, @dept  39 WHILE @@FETCH_STATUS = 0 40 BEGIN 41    PRINT @dept   '学生: '   @sname  42    FETCH NEXT FROM Sname_cur2 INTO @sname, @dept 43 END 44 CLOSE Sname_cur2 45 DEALLOCATE Sname_cur2 46 --例8.4.4 声明查询计算机系学生姓名、选的课程名和成绩的游标，并将游标内容按成绩降序排序。 47 DECLARE CS_cursor SCROLL CURSOR FOR 48   SELECT Sname, Cname, Grade FROM Student S  49   JOIN SC ON S.Sno = SC.Sno 50   JOIN Course C ON C.Cno = SC.Cno 51   WHERE Sdept = 'cs' 52   ORDER BY Grade DESC 53 OPEN CS_cursor 54 fetch last from cs_cursor 55 CLOSE CS_cursor 56 DEALLOCATE CS_cursor 57 --例8.4.5 生成显示如下报表形式的游标：报表首先列出一门课程的课程号和课程名（只针对有人选的课程），然后在此课程下列出选了此门课程且成绩大于等于80的学生姓名、所在系和此门课程的考试成绩；然后再列出下一门课程的课程号和课程名，然后在此课程下列出选了此门课程且成绩大于等于80的学生姓名、所在系和此门课程的考试成绩；依此类推，直到列出全部课程。 58 declare show_course cursor for 59 select cno, cname 60 from course c 61 where cno in (select cno from sc) 62 order by cno 63 open show_course 64 declare @cno char(10), @cname char(20) 65 fetch next from show_course into @cno, @cname 66 while @@FETCH_STATUS = 0 67 begin 68     print @cno   '   '   @cname 69     print '--------------' 70     declare show_student cursor for 71     select sname, sdept, grade 72     from student s, course c, sc 73     where s.sno = sc.sno and c.cno = sc.cno and c.cno = @cno and grade >= 80 74     open show_student 75     declare @sname char(10), @sdept char(10), @grade int 76     fetch next from show_student into @sname, @sdept, @grade 77     while @@fetch_status = 0 78     begin 79         print @sname   ' '   @sdept   ' '   convert(varchar(10), @grade)--注意类型转换 80         fetch next from show_student into @sname, @sdept, @grade 81     end 82     close show_student 83     deallocate show_student 84     fetch next from show_course into @cno, @cname 85 end 86 close show_course 87 deallocate show_course 88 --例：把年龄为20岁的第三个学生的年龄改为18。 89 declare c scroll cursor for 90 select sno,sage from student 91 where sage=20 92 open c 93 fetch absolute 3 from c 94 update student 95 set sage=18 96 where current of c 97 close c 98 deallocate c 99 --违反了 PRIMARY KEY 约束“PK_student”。不能在对象“dbo.student”中插入重复键。重复键值为 (200515006)。100 101 --例：把年龄为20岁的第五个学生从学生表中删除。102 declare delete_s scroll cursor for103 select *104 from student s105 where sage = 20106 open delete_s107 fetch absolute 5 from delete_s108 delete109 from student110 where current of delete_s111 close delete_s112 deallocate delete_s113 --CURRENT OF cursor_name： 114 --表示当前游标指针所指的当前行数据。CURRENT OF 只能在UPDATE和DELETE语句中使用。 

 

来源：https://www.icode9.com/content-4-560051.html