sql游标使用

 1 /****************************
 2            说明：游标使用
 3            时间：07/07/15
 4 ******************************/
 5 use student_15
 6 go
 7 declare student_cur cursor
 8 
 9    for select * from student
10 go
11 
12 declare @sid int
13 declare @sname varchar(20)
14 declare @ssex char(1)
15 declare @sage int
16 declare @classid int
17 
18 open student_cur
19    fetch next from student_cur into @sid,@sname,@ssex,@sage,@classid
20     while @@fetch_status=0
21     begin
22       print @sname
23       fetch next from student_cur into @sid,@sname,@ssex,@sage,@classid
24     end
25 close student_cur
26 deallocate student_cur
27 /*******************************
28 
29       说明：游标的操作(update)
30       时间：07/07/15
31 *******************************/
32 use student_15
33 go
34 select * from student
35 go
36 
37 declare student_cur cursor
38     scroll   --游标的移动位置
39     for select * from student
40     for update of sname,sage
41 go   
42 declare @sid int
43 declare @sname varchar(20)
44 declare @ssex char(1)
45 declare @sage int
46 declare @classid int
47 
48 open student_cur
49    fetch first from student_cur into @sid,@sname,@ssex,@sage,@classid 
50      /*更新游标数据*/
51    update student set sage=19,sname='11' where current of student_cur 
52     while @@fetch_status =0    begin
53 
54           fetch next from student_cur into @sid,@sname,@ssex,@sage,@classid
55          
56           if @sid=1003   begin
57            update student set sage=19,sname='11' where current of student_cur 
58           end
59           
60      end
61 close student_cur
62 deallocate student_cur
63 
64 /*************游标操作删除***************/
65 use student_15
66 go
67 
68 declare student_cur cursor
69     scroll
70     for select * from student
71     for update of sid
72 go
73 
74 declare @sid int
75 declare @sname varchar(20)
76 declare @ssex char(1)
77 declare @sage int
78 declare @classid int
79 
80 open student_cur
81 
82       fetch first from student_cur into @sid,@sname,@ssex,@sage,@classid
83 
84       while @@fetch_status=0
85 
86          begin
87 
88       fetch next from student_cur into @sid,@sname,@ssex,@sage,@classid
89 
90       if @sid=1003
91     
92         begin
93   
94          delete student where current of student_cur
95    
96         end
97      end
98 close student_cur
99 deallocate student_cur