游标

印度阿三17 2019-11-15

展开全文


  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 for
103 select *
104 from student s
105 where sage = 20
106 open delete_s
107 fetch absolute 5 from delete_s
108 delete
109 from student
110 where current of delete_s
111 close delete_s
112 deallocate delete_s
113 --CURRENT OF cursor_name： 
114 --表示当前游标指针所指的当前行数据。CURRENT OF 只能在UPDATE和DELETE语句中使用。

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