SQL基本操作（一）：关于游标Cursor

use master 
go
create database xscj
go
use xscj
go
create table xs
(学号 char(6) not null primary key,
 姓名 char(8),
 专业名 char(10),
 性别 bit default 1,
 出生日期 smalldatetime,
 总学分 tinyint,
 备注 text)
go
create table kc
(课程号 char(3) not null primary key,
 课程名 char(16),
 开课学期 tinyint default 1,
 check(开课学期>=1 and 开课学期<=8),
 学时 tinyint,
 学分 tinyint)
go
create table xs_kc
(学号 char(6) not null,
 课程号 char(3) not null,
 primary key(学号,课程号),
 成绩 tinyint,
 学分 tinyint)
go
insert into xs values('001101','王林','计算机',1,'1980-2-10',50,null)
insert into xs values('001102','程明','计算机',1,'1981-2-1',50,null)
insert into xs values('001103','王燕','计算机',0,'1979-10-6',50,null)
insert into xs values('001104','伟严严','计算机',1,'1980-8-26',50,null)
insert into xs values('001106','李芳芳','计算机',1,'1980-11-20',50,null)
insert into xs values('001107','李明','计算机',1,'1980-5-1',54,'提前修完《数据结构》，并获学分')
insert into xs values('001108','林一帆','计算机',1,'1979-8-5',52,'已提前修完一门课')
insert into xs values('001109','张强明','计算机',1,'1978-8-11',50,null)
insert into xs values('001110','张蔚','计算机',0,'1981-7-22',50,'三好学生')
insert into xs values('001111','赵琳','计算机',0,'1980-3-18',50,null)
insert into xs values('001113','严红','计算机',0,'1979-7-13',48,'有一门功课不及格，待补考')


insert into xs values('001201','李红庆','通信工程',1,'1979-5-11',40,'有一门不及格，待补考')
insert into xs values('001202','孙详欣','通信工程',1,'1979-6-11',42,null)
insert into xs values('001203','孙研','通信工程',1,'1978-6-10',42,null)
insert into xs values('001204','吴薇华','通信工程',0,'1978-6-1',42,null)
insert into xs values('001206','刘燕敏','通信工程',0,'1979-9-20',42,null)
insert into xs values('001210','罗林琳','通信工程',0,'1979-5-1',44,'已提前修完一门，并获得学分')
insert into xs values('001216','李计','通信工程',1,'1978-3-9',42,null)
insert into xs values('001218','马琳琳','通信工程',0,'1980-10-9',42,null)
insert into xs values('001220','王玉民','通信工程',1,'1980-3-18',42,null)
insert into xs values('001221','王林','通信工程',0,'1979-11-13',42,null)
insert into xs values('001241','王敏','通信工程',0,'1980-1-30',50,'转专业学习')
 insert into kc values('101','计算机基础',1,80,5)
 insert into kc values('102','程序设计与语言',2,68,4)
 insert into kc values('206','离散数学',4,68,4)
 insert into kc values('208','数据结构',5,68,4)
 insert into kc values('209','操作系统',6,68,4)
 insert into kc values('210','计算机原理',5,85,5)
 insert into kc values('212','数据库原理',7,68,4)
 insert into kc values('301','计算机网络',7,51,3)
 insert into kc values('302','软件工程',7,51,3)
 insert into kc values('304','算法',7,51,3)
 insert into kc values('305','软件设计',7,51,3)
insert into xs_kc values('001101','101',80,null)
insert into xs_kc values('001101','102',78,null)
insert into xs_kc values('001101','206',76,null)
insert into xs_kc values('001103','101',76,null)
insert into xs_kc values('001103','102',72,null)
insert into xs_kc values('001103','206',70,null)
insert into xs_kc values('001106','101',65,null)
insert into xs_kc values('001106','102',71,null)
insert into xs_kc values('001106','206',85,null)
insert into xs_kc values('001104','101',90,null)
insert into xs_kc values('001104','102',84,null)
insert into xs_kc values('001104','206',65,null)
insert into xs_kc values('001102','102',78,null)
insert into xs_kc values('001102','206',78,null)
insert into xs_kc values('001107','101',78,null)
insert into xs_kc values('001107','102',80,null)
insert into xs_kc values('001107','206',68,null)
insert into xs_kc values('001108','101',85,null)
insert into xs_kc values('001108','102',64,null)
insert into xs_kc values('001108','206',87,null)
insert into xs_kc values('001109','101',66,null)
insert into xs_kc values('001109','102',83,null)
insert into xs_kc values('001109','206',70,null)
insert into xs_kc values('001110','101',95,null)
insert into xs_kc values('001110','102',90,null)
insert into xs_kc values('001110','206',89,null)
insert into xs_kc values('001111','101',91,null)
insert into xs_kc values('001111','102',70,null)

select * from xs
select * from kc
select * from xs_kc
--以下是一个符合SQL-92的游标声明
declare xs_cur1 cursor
        for select 学号,姓名,性别,出生时间,总学分 from xs where 专业名='计算机'
        for read only
--以下是一个T-SQL扩展游标声明
declare xs_cur2 cursor dynamic
        for select 学号,姓名,总学分 from xs where 专业名='计算机'
        for update of 总学分
--定义游标xs_cur3 然后打开游标,输出其行数
declare xs_cur3 cursor  local  scroll  scroll_locks
        for select 学号,姓名,总学分 from xs 
        for update of 总学分
--打开游标
open xs_cur3
--游标相应的操作
select '游标xs_cur3数据行数'=@@cursor_rows
--关闭游标
close xs_cur3
--从游标xs_cur1中提取数据
declare xs_cur1 cursor
        for select 学号,姓名,性别,出生日期,总学分 from xs where 专业名='计算机'
        for read only 
--打开游标
open xs_cur1
--提取数据(由于xs_cur1)是只进游标,所以只能使用next提取数据
fetch next from xs_cur1
--关闭游标
close xs_cur1
--从游标xs_cur2中提取数据（xs_cur2是动态游标,可以前滚，后滚，除absolute以外都可以提取）
declare xs_cur2 cursor dynamic
        for select 学号,姓名,总学分 from xs where 专业名='计算机'
        for update of 总学分
--打开游标
open xs_cur2
--读取游标第一行
fetch first from xs_cur2
--读取下一行
fetch next from xs_cur2
--读取上一行
fetch prior from xs_cur2
--读取最后一行
fetch last from xs_cur2
--读取当前行的上两行
fetch relative -2 from xs_cur2
--读取当前行的下三行
fetch relative 3 from xs_cur2
select 'fetch执行情况'=@@fetch_status
--关闭游标
close xs_cur2