SQL游标语法及举例

游标的定义：
每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序；
1.DECLARE 游标
2.OPEN 游标
3.从一个游标中FETCH 信息
4.CLOSE 或DEALLOCATE 游标
通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容：

游标名字
数据来源（表和列）
选取条件
属性（仅读或可修改）
其语法格式如下：
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
其中：
cursor_name
指游标的名字。
INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
使用OUTER JOIN；
所选取的任意表没有索引；
将实数值当作选取的列。
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再
重开游标。
select_statement
是定义结果集的SELECT 语句。应该注意的是，在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。
READ ONLY
表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE 语句的WHERE CURRENT OF 子句中，不允许对该游标进行引用。
UPDATE [OF column_name[,…n]]
定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。当游标被成功创。

 

我们来举一个例子：利用SQL游标知识，把各系的系主任工资改为所在系的平均工资。

 

解答：

1.首先创建一个数据库，语句为：create database testCursor;

2.并使用这个数据库，语句为：use testCursor;

3.创建两个表，一个为部门信息，另一个为老师信息。语句为：

create table dept(did varchar(20) not null,

 dname varchar(50) default '',

 dean varchar(20) not null,

 primary key (did));

 

 create table prof(pid varchar(20) not null,

 pname varchar(50) default '',

 age int(11) default '0',

 did varchar(20) not null,

 sal decimal,

 primary key (pid));

4.准备测试数据，即将部分数据导入两个表中。语句为：

insert into dept values('1', 'test1', '1111');

insert into dept values('2', 'test2', '1112');

insert into dept values('3', 'test3', '1113');

 

insert into prof values('1111', 'prof1', 21, '1', 100);

insert into prof values('1112', 'prof2', 23, '2', 103);

insert into prof values('1113', 'prof3', 24, '3', 106);

insert into prof values('1114', 'prof4', 25, '1', 101);

insert into prof values('1115', 'prof5', 21, '2', 109);

insert into prof values('1116', 'prof6', 29, '2', 105);

insert into prof values('1117', 'prof7', 20, '3', 101);

insert into prof values('1118', 'prof8', 21, '3', 120);

insert into prof values('1119', 'prof9', 25, '3', 102);

5.编写SQL脚本，将各系的系主任工资改为所在系的平均工资。此地方举例子，编写存储过程来实现，具体代码为：

CREATE PROCEDURE prof_sal_update()

begin

declare @avgsals decimal, @vardid varchar(20);

declare curr1 cursor;

for select did, avg(sal) as avgsal from prof group by did order by did;

open curr1;

fetch curr1 into @avgsals, @vardid;

while (@@fetch_status=0)

begin

    update prof set prof.sal=@avgsals where prof.did=@vardid and prof.pid in (select dept.dean from dept where dept.did=@vardid);

    fetch next from curr1 into @avgsals, @vardid;

end

close curr1;

deallocate curr1;

end

 

实现细节说明：

首先查询出每个部门的平均工资，利用语句“select did, avg(sal) as avgsal from prof group by did order by did;”实现；

其次，要更新相应的系主任的工资，就要获得当前系编号，以及当前系平均工资，利用语句“fetch curr1 into @avgsals, @vardid;”实现；

再次，有了当前系编号，以及当前系平均工资，就更新对应当前系的系主任的工资利用语句“update prof set prof.sal=@avgsals where prof.did=@vardid and prof.pid in (select dept.dean from dept where dept.did=@vardid);”实现；

最后，可能不只一个系，则加个循环结构实现。利用如下语句实现：

while (@@fetch_status=0)

begin

    ……

    fetch next from curr1 into @avgsals, @vardid;

end