-
游标的基本概念
- 游标是处理数据的一种方法,它提供了数据的逐行访问功能
- 游标类似于C语言中的指针,它可以指向结果集中的任何数据行,允许用户对该行数据进行处理
- 游标由游标结果集和游标位置两部分组成
- 游标的结果集为定义游标的select语句返回的数据行的集合,游标位置为指向结果集某一行的当前指针
-
游标的使用步骤:声明游标、打开游标、读取游标、关闭游标、释放游标
-
声明游标的语法:
declare cursor_name cursor[ local | global ] --游标作用域 --声明 游标名 局部右边/全局游标 局部:出了所在批处理,就不能用了 全局:当前这个连接都能用 [ forward_only | scroll ] --移动方向,默是只进游标forward_only --只进游标 /滚动游标 只进:只往前走,从第一行走到最后一行 滚动:可以指定找哪行 [ static | dynamic | keyset ] --游标类型:默认——动游标dynamic --静态 | 动态 |键值 [ read only | fast_forward | scroll_locks ] --访问属性 --只读 | 一个方向的移动 | 锁定 for select_statement --查询语句 [for update [of column_name[,......n] ] ] --可以修改游标位置的数据 --指定哪些列可以做修改
-
使用游标逐行访问Employees表
--创建 declare cur_Employees cursor for select * from Employees --打开游标 open cur_Employees --提取游标 fetch next from cur_Employees --修改 当前 update Employees set 姓名='赵燕' where current of cur_Employees --修改当前行 update Employees set 姓名='赵飞燕' where current of cur_Employees --修改当前行 delete from Employees where current of cur_Employees --删除当前行 --关闭游标 close cur_Employees --释放/删除游标 deallocate cur_Employees
-
使用滚动游标逐行访问Employees表
--创建 declare cur_Employees cursor scroll for select * from Employees --打开游标 --全局变量:@@cursor_rows, 打开游标后可以使用此变量用来查看数据有多少行 open cur_Employees print '员工人数:'+cast(@@cursor_rows as varchar(3)) --提取游标 fetch first from cur_Employees --提取第一行 fetch last from cur_Employees --提取最后一行 fetch absolute 5 from cur_Employees --absolute:绝对 提取第五行 fetch relative -2 from cur_Employees --relative:相对 相对当前位置往前2行 fetch relative 2 from cur_Employees --relative:相对 相对当前位置往后2行 fetch prior from cur_Employees --prior:往前一行 fetch next from cur_Employees --prior:往后一行 --关闭游标 close cur_Employees --释放/删除游标 deallocate cur_Employees
-
使用变量的游标
--提取员工销售额放在定义好的变量中,然后做出评价 declare cur_Sellsum cursor for select 编号,姓名,销售额=sum(零售价*S.数量) from Employees E,Sell S ,Goods G where E.编号=S.售货员工编号 and G.商品编号=S.商品编号 group by 编号 , 姓名 open cur_Sellsum declare @id char(4) , @name varchar(8) , @money money fetch next from cur_Sellsum into @id ,@name ,@money print '编号为 '+@id +' 的员工 '+@name+' 的销售额为:'+cast(@money as varchar(10))+'元' if @money >=30000 print '优秀' else if @money >=15000 print '合格' else print '不合格' close cur_Sellsum deallocate cur_Sellsum
-
存储过程中的游标
if exists (select * from sysobjects where name='proc_cur_Sellsum') drop proc proc_cur_Sellsum go create proc proc_cur_Sellsum as declare cur_Sellsum cursor for select 编号,姓名,销售额=sum(零售价*S.数量) from Employees E,Sell S ,Goods G where E.编号=S.售货员工编号 and G.商品编号=S.商品编号 group by 编号 , 姓名 open cur_Sellsum declare @id char(4) , @name varchar(8) , @money money fetch next from cur_Sellsum into @id ,@name ,@money print '编号为 '+@id +' 的员工 '+@name+' 的销售额为:'+cast(@money as varchar(10))+'元' if @money >=30000 print '优秀' else if @money >=15000 print '合格' else print '不合格' close cur_Sellsum deallocate cur_Sellsum go --调用 exec proc_cur_Sellsum --只能读取游标中的第一行数据
-
循环读取游标结果集中的数据
--游标状态 @@fetch_status --0:读取成功 -1、-2 :读取失败/游标中无数据 if exists (select * from sysobjects where name='proc_cur_Sellsum') drop proc proc_cur_Sellsum go create proc proc_cur_Sellsum as declare @id char(4) , @name varchar(8) , @money money declare cur_Sellsum cursor for select 编号,姓名,销售额=sum(零售价*S.数量) from Employees E,Sell S ,Goods G where E.编号=S.售货员工编号 and G.商品编号=S.商品编号 group by 编号 , 姓名 open cur_Sellsum while @@fetch_status=0 begin fetch next from cur_Sellsum into @id ,@name ,@money --变量的个数相等、类型匹配、顺序一致 print '编号为 '+@id +' 的员工 '+@name+' 的销售额为:'+cast(@money as varchar(10))+'元' if @money >=30000 print '优秀' else if @money >=15000 print '合格' else print '不合格' end close cur_Sellsum deallocate cur_Sellsum go --调用 exec proc_cur_Sellsum --调用员工销售额 ——原来的调用,只能单个调用 declare @m money exec proc_销售额 '1102' ,@m output
-
使用游标循环调用存储过程
declare @m money , @Eid char(4) declare cur_Employees cursor for select 编号 from Employees open cur_Employees fetch next from cur_Employees into @Eid while @@fetch_status=0 begin exec proc_销售额 @Eid,@m output fetch next from cur_Employees into @Eid if @m>=30000 print '优秀员工' else if @m>=10000 print '合格员工' else print '无销售员工' end close cur_Employees deallocate cur_Employees
-
练习
use XK go --复制表 select * into Course1 from Course select * into StuCou1 from StuCou select * from StuCou1 select * from Course1 if exists (select * from sysobjects where name='proc_Choosecourse') drop proc proc_Choosecourse go create proc proc_Choosecourse @willorder smallint as --声明的变量与声明游标时查询的列 数目相同,类型匹配、顺序一致 declare @CouNo nvarchar(3), @LimitNum int, @WillNum int , @ChooseNum int , @num int declare cur_Course1 cursor for select CouNo , LimitNum , WillNum , ChooseNum from Course1 open cur_Course1 --提取课程 fetch next from cur_Course1 into @CouNo , @LimitNum , @WillNum , @ChooseNum --逐行提取 提取出来,赋给变量 while @@fetch_status=0 begin --对课程里报名的学生进行选中 if @LimitNum-@ChooseNum>=@WillNum --报名人数小于限选人数则把报名状态修改为“选中”, update StuCou1 set State='选中' where State='报名' and CouNo=@CouNo else --反之—— 按志愿号和学号顺序进行逐个分配 begin declare cur_StuCou1 cursor for select * from StuCou1 where CouNo=@CouNo and WillOrder=@willorder open cur_StuCou1 fetch next from cur_StuCou1 --定义一个变量用来接收状态改变的学生的个数 当报名人数<=限选人数时,循环执行 set @num=1 while @@fetch_status=0 and @num<=@LimitNum-@ChooseNum begin update StuCou1 set State='选中' where current of cur_StuCou1 --current of :当前行 set @num += 1 fetch next from cur_StuCou1 end close cur_StuCou1 deallocate cur_StuCou1 end --每一次报名后修改Course1表中的报名人数, update Course1 set ChooseNum =(select count(*) from StuCou1 where CouNo=@CouNo and state='选中') where CouNo=@CouNo fetch next from cur_Course1 into @CouNo , @LimitNum , @WillNum , @ChooseNum end close cur_course1 deallocate cur_course1 --调用 exec proc_Choosecourse 1 --志愿号为1的学生的选中 exec proc_Choosecourse 2 exec proc_Choosecourse 3 exec proc_Choosecourse 4 exec proc_Choosecourse 5 --查询选过课后的课程表以及选课志愿表 select * from StuCou1 select * from Course1 --任何志愿都没有被选中的 select * from Student where StuNo not in (select StuNo from (select StuNo , count(state) 选课 from StuCou1 group by state ,StuNo having state='选中') xuanke) --先以选课状态为列统计选中课的学生的学号,只要选中课的学生就都选出来 select StuNo , count(state) 选课 from StuCou1 group by state ,StuNo having state='选中' --在上面表的基础上查询选中课的学生的学号 select StuNo from (select StuNo , count(state) 选课 from StuCou1 group by state ,StuNo having state='选中') xuanke --在学生表中查询没有选中课的学生:学号不在上面那个表中的学生都是没选中课的 select * from Student where StuNo not in (select StuNo from (select StuNo , count(state) 选课 from StuCou1 group by state ,StuNo having state='选中') xuanke) --以下这种方式错误 select * from Student where StuNo not in(select StuNo , count(state) 选课 from StuCou1 group by state ,StuNo having state='选中') -- 当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。
【基础9】SQL 游标的创建,在存储过程中的调用,使用变量的游标
最新推荐文章于 2024-06-29 12:02:59 发布