SQL查询艺术学习笔记--游标

--SQL中游标的使用
--概念：
--游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
--游标由结果集和结果集(零条或一条或select检索多条记录)中指向特定记录的游标位置组成，游标起到指针的作用。一次只指向一行。
--游标作为临时的数据库对象，即可存放数据表中数据行的副本，也可指向存储在数据库表中数据行的指针。
--游标提供了逐行操作数据表中数据的方法。


-- 游标的作用:
--游标可以选择一组数据，并可在记录集上下滚动。
--游 标另一作用是保存查询结果。以便以后使用


--游标的使用步骤：
--1:创建游标，定义游标的名称，特性及可调用的查询方式
--2:打开游标，调用查询表达式，以便在过程或应用中使用它
--3:每次检出（fetch)数据记录中的一行，直到记录中的最后一行。
--4:完成操作后关闭游标并清除分配给游标的内存。


--游标的使用:
--使用declare cursor创建游标,语法如下：
declare cursor_name [insensitive][scroll] cursor
for select_statement
[for {read only|update[of column_name [,...n]]}]
--说明如下：
--cursor_name为游标的名字
--insensitive:通知dbms制作查询结果副本，不影响真实数据。同时数据库中的update
--delete都不会反应至游标数据。查询为只读查询，相对应如果未指定insensitive，则
--相对应的操作会反应至游标数据。
--scroll:游标所有的提取操作都可以执行。即fetch选项：first prior next relative
--absolute，不指明则只能进行next提取操作。
--select_statement:定义结果集的select语名
--read only：防止游标使用者或应用程序更新数据或删除行来改变游标内容。read only
--游标不能用于更新底层表中数据。在不指定情况下，游标是允许更新的。
--updat[of column_name[,...n]]定义游标可更新列。如不指定需要更新的列。游标将允许
--更新所有的列。


declare teacher_cursor scroll cursor
--scroll 所有提取操作
for
select tname,dname,age from teacher
order by age
--创建查询teacher表游标


--游标的打开和关闭 open/close cursor
open cursor_name
--打开游标
open teacher_cursor
close cursor_name
--关闭游标
close teacher_cursor


--使用fetch语句对数据进行检索 [使用前需打开指定游标]
--语法：
fetch [ [fetch_orientation] from ]
cursor_name into host_variables
--orientation:方向 定向
-- host_variable 为接收游标检索行值的主变量
--Fetch_orientation在使用scroll前提下有如下选项：


--next:返回结果集中当前行的下一行，在第一次读取数据时返回第一行非第二行。
--prior:返回结果集中当前行的前一行，在第一次读取数据时无返回，定位至第一行
--first:返回游标的第一行
--last:返回游标中的最后一行
--absolute N:返回结果集的第N行，N为正数则从第一行起至第N行，如果为负数，则从
--最后一行起返回第N行。
--relative N:从游标指针的当前行移动N行，同样为正数则从当前向后移N行，为负数，
--则从当前行起向前移N行。
--*fetch 语句没有定位选项，dbms将自动执行fetch next选项，即指向下一行。


--游标检索数据操作：Fetch 选项的各种用法
open teacher_cursor
--打开游标
declare @t_name char(12),@t_department char(12),@t_age int
--定义游标赋值接收变量
fetch last from teacher_cursor into @t_name,@t_department,@t_age
print '姓名:'+@t_name+'所在系:'+@t_department+'年龄:'+cast(@t_age as char(2))
--从最后一个开始，以年龄排序
fetch prior from teacher_cursor into @t_name,@t_department,@t_age
print '姓名:'+@t_name+'所在系:'+@t_department+'年龄:'+cast(@t_age as char(2))
--游标返回当前行的前一行
fetch prior from teacher_cursor into @t_name,@t_department,@t_age
print '姓名:'+@t_name+'所在系:'+@t_department+'年龄:'+cast(@t_age as char(2))
--再次返回前一行
fetch first from teacher_cursor into @t_name,@t_department,@t_age
print '姓名:'+@t_name+'所在系:'+@t_department+'年龄:'+cast(@t_age as char(2))
--定位至第一行
fetch next from teacher_cursor into @t_name,@t_department,@t_age
print '姓名:'+@t_name+'所在系:'+@t_department+'年龄:'+cast(@t_age as char(2))
--第二行: next
close teacher_cursor
--关闭游标


--基于游标的delete语句
--在declare cursor时不包括read only 选项，我们可以用游标来更新底表数据。
--基于游标的delete语句称为定位delete语句，它会让dbms从基于游标行指针的当前位置
--表中删除一行。注意：如果游标的select诘句包括order by 子句，dbms将把游标限制为
--read only 基于游游的定位delete语句将不能执行。同样也适用于Update语句。
--语法：
declare from table_name
where current of cursor_name


declare mt_cursor cursor
for
select * from teacher where sex='男'
for update
--指明游标是可以更新
open mt_cursor
--打开游标
fetch from mt_cursor
--定位置第一条记录
delete from teacher where CURRENT of mt_cursor
--删除第一条记录
fetch from mt_cursor
--检索至下一条记录
delete from teacher where current of mt_cursor
--删除下一条记录
close mt_cursor
insert into teacher values(5,'高伟',8,2100,'电子工程','男',39)
insert into teacher values(6,'李伟',7,1200,'机械工程','男',29)
--恢复数据


--基于游标的定位update语句
--说明：定位Update语句的where子句中，搜索条件是基于游标当前行指针的位置，而不是搜索基于
--一列或多列要修改值的行。
--语法：
update table name
set {column_name=value1[,....,column_name n=valuen]}
where current of cursor_name


--创建更新游标，用于update操作
declare wm_cursor cursor
for
select sal from teacher where sex='女'
for update
--用于更新
declare @avg_sal int,@t_sal int
--定义两个变量
select @avg_sal=AVG(sal)  from teacher where sex='女'
--为@avg变量赋值为女教师工资平均值
open wm_cursor
fetch from wm_cursor  
into @t_sal 
--读取记录值
while @@FETCH_STATUS=0
--@@fetch_status 全局变量 0值表示fetch语句执行成功 -1表示fetch值执行失败或不在
--结果集中。-2表示提取的行不存在。-9光标未执行提取操作。
begin
 if @t_sal is null
 update teacher set sal=@avg_sal 
 where CURRENT of wm_cursor
 fetch from wm_cursor into @t_sal
 end
 close wm_cursor
 --检查结果集变更
 select * from teacher where sex='女'
 
--SQL server 中游标的扩展
--语法如下：
declare cursor_name cursor
[local|global]
[forward_only|scroll]
[static|keyset|dynamic|fast_froward]
[read_only|scroll_locks|optimistic]
[type_warning]
for select_statement
[for update [of column_name[,....n]]]
--语法说明：
--local:定义游标的作用域为仅限所在的触发器，存储过程，批处理中，结束完成后自动释放。
--可以用output保留字将游标传递给调用者，直到最后引用该游标变量释放。
--global:定义游标的作用域为整个会话层
--forward_only:指明从游标提取数据时，只能从第一行到最后一行按顺序操作，此时只能用fetch next操作
--如使用的是keyset dynamic 游标为scroll游标。在参数未指明时默认为forward_only选项
--static:该项等同于insensitive，游标定义的数据会存放于一个临时表内进行操作。源表和游标相对独立
--相互的修改互不影响。
--keyset:类似于主键值索引？如果表中没有唯一主键或索引，keyset游标将变为static游标
--dynamic:书面意思为动态的，即可将源表的变化反应至游标中，update delete insert操作的任何修改。
--满足游标搜索条件时会为该行记录添加指针，反之则删除，可最大保证数据的一致性
--fast_forward：对read_only forward_only优化。不能用于scroll和update项
--scroll_locks：在服务器向游标添加行时在源表的每行放置一个锁定，保证对游标的update和delete操作成功执行。并发处理时避免使用该选项
--optimistic:在数据读入游标后，如游标中的某行发生变化，对游标的任何定位update或delete操作都将失败。不能同fast_forward同时使用
--type_warning：在游标类型被修改为与用户定义类型不同时，dbms将发送一个警告。


--注意：在用declare cursor创建游标时：未指定：read_only optimistic scroll_locks选项
--如果select语句不支持更新 游标为read_only类型
--static和fast_forward     游标为read_only类型
--dynamic和keyset          游标为：optimistic


--@@cursor_rows：全局变量确定游标的行数
--@@cursor_rows值说明：
--值为-m:表示源表读入游标的处理仍在进行，m表示当前游标中的数据行数
--值为-1:表明该游标为dynamic动态游标，反应源表变化，游标数据不确定。
--值为0: 表明无符合条件数据或游标已关闭
--值为n: 表明游标从源表读入数据结束，n为游标中已有数据记录的行数。


--使用@@cursor_rows
declare wm1_cursor scroll cursor 
for
select * from teacher where sex='女'
open wm1_cursor
print '所有女教师记录:'+cast(@@cursor_rows as char(4)) +'条'
close wm1_cursor
--注意需指明游标为scroll，如没有则为动态游标。
deallocate wm1_cursor 
deallocate wm_cursor


--@@fetch_status 全局变量检测fetch操作状态
--在使用fetch语句从游标读取数据，应检查@@fetch_status，确定fetch操作是否成功
--@@fetch_status有以下返回值：
--值为:0  Fetch命令成功执行
--值为:-1 Fetch命令执行失败，或行数据超出游标数据结果集
--值为:2  Fetch所读取的数据已经不存在。
--值为:9  Fetch光标未执行行提取操作。
open wm1_cursor
declare @a_no int,@a_name char(10),@a_cno int,@a_sal int,
@ab_name char(12),@a_sex char(2),@a_age int
fetch from wm1_cursor
into @a_no,@a_name,@a_cno,@a_sal,@ab_name,@a_sex,@a_age
while @@FETCH_STATUS=0
begin
print '教工号:'+cast(@a_no as char(4))+'姓名:'+@a_name+'课程号:'+
cast(@a_cno as char(4))+'工资:'+cast(@a_sal as char(6))+'所在系:'+
@ab_name+'性别:'+@a_sex+'年龄:'+cast(@a_age as char(2))
fetch from wm1_cursor
into @a_no,@a_name,@a_cno,@a_sal,@ab_name,@a_sex,@a_age
end
close wm1_cursor
select * from teacher where sex='女'
--注意：如果工资中有null值时则会因赋值不成功有空白行


--游标的关闭与释放,语法：
--deallocate cursor_name
--deallocate 命令可以释放和关闭游标，而close命令仅关闭游标。
--注意：在使用begin transaction从游标读取一条数据记录，以commit transaction 
--或者rollback transaction结束事务处理时。SQL将自动关闭游标。commit:提交
--commit transcation :标志一个成功的隐性事务或显性事务的结束，提交事务并永久保存在数据库中
--使用事务结束后不关闭游标，可设置参数：cursor_close_on_commit设为off
--语法： set cursor_colse_on_commit off


--游标变量
--在SQL中，游标可以看作是一种变量类型，即游标变量有两种方法定义游标变量
--1：先创建一个游标,然后用SET语句将游标赋值给游标变量
declare wm_cursor scroll cursor
for 
select * from teacher where sex='女'
declare @t_cursor cursor
set @t_cursor=wm_cursor
--2:直接将创建游标语句放置在游标变量的赋值语句中
declare @tm_cursor cursor
--定义游标变量
set @tm_cursor=scroll cursor
for
select * from teacher where sex='女'
--定义游标结果集


create procedure teacherinfo_cursor @t_dname varchar(16),
@teacher_cursor cursor varying output
--vary:变化的 
--在存储过程中：定义游标类型的变量为输出函数:output,必须在之前使用varying关键字。
as 
set @teacher_cursor=CURSOR forward_only static 
for
select * from teacher where dname=@t_dname
open @teacher_cursor
--创建一存储过程使用游标变量
--drop procedure teacherinfo_cursor
declare @mycursor cursor
exec teacherinfo_cursor '计算机',@teacher_cursor=@mycursor output
fetch next from @mycursor
while(@@FETCH_STATUS=0)
begin
fetch next from @mycursor
end
deallocate @mycursor
deallocate tcinfo_cursor


--使用系统存储过程管理游标
--系统过程：
--sp_cursor_list：显示当前作用域内的游标及其属性
--sp_describe_cursor:显示游标属性  describe:描述，形容；描绘
--sp_describe_cursor_tables:显示游标引用的基础表
--sp_describe_cursor_columns:显示游标结果集中数据列的属性


--sp_cursor_list语法：
sp_cursor_list [@cursor_return=] cursor_variable_name output,
[@cursor_scope=] cursor_scope
--scope:范围；余地；视野；眼界；导弹射程
--cursor_variable_name为创建游标变量，存储了当前作用域内的游标及其属性。 cursor_scope为游标作用域。
--cursor_scope取值：1：返回所有的local游标 2：返回所有的global游标 3：返回所有的local和global游标


declare @report cursor
exec sp_cursor_list @cursor_return=@report output,
@cursor_scope=2
fetch next from @report
while(@@FETCH_STATUS=0)
begin
fetch next from @report
end
deallocate @report
--查询当前表中的游标