游标使用

一、什么是“游标（Cursor）”？

    游标是SQL 的一种数据访问机制 ，游标是一种处理数据的方法。主要用于存储过程，触发器和 T_SQL脚本中，它们使结果集的内容可用于其它T_SQL语句。

    众所周知，使用SQL的select查询操作返回的结果是一个包含一行或者是多行的数据集，如果我们要对查询的结果再进行查询，比如（查看结果的第一行、下一行、最后一行、前十行等等操作）简单的通过select语句是无法完成的，因为这时候索要查询的结果不是数据表，而是已经查询出来的结果集。游标就是针对这种情况而出现的。

我们可以将“ 游标 ”简单的看成是结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览我需要的数据。
下面我们先新建一个表并插入数据，结合表数据库来学习游标

二、游标的优点

 SELECT 语句返回的是一个结果集，但有时候应用程序并不总是能对整个结果集进行有效地处理，游标便提供了这样一种机制，它能从包括多条记录的结果集中每次提取一条记录，游标总是与一跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。使用游标具有一下优点：

(1).允许程序对由SELECT查询语句返回的行集中的每一次执行相同或不同的操作，而不是对整个集合执行同一个操作。

(2).提供对基于游标位置中的行进行删除和更新的能力。

(3).游标作为数据库管理系统和应用程序设计之间的桥梁，将两种处理方式连接起来。

三、建表语句，插入数据

建表：

create TABLE fruits(
    f_id int IDENTITY(1,1) PRIMARY KEY,--水果id
    s_id int not null,        --供应商id
    f_name varchar(255) not null,--水果名称
   f_price decimal(8,2) not null --水果价格
);

插入数据：
insert into fruits (s_id,f_name,f_price) 
        values
        (101,'apple',5.8),
        (102,'blackberry',6.8),
        (105,'orange',4.5),
        (102,'banana',3.5),
        (103,'lemon',8.0),
        (104,'grape',7.6),
        (101,'melon',10.5);

四、游标定义及操作

1.声明名称为cursor_fruit的游标

USE sample_db;
GO
DECLARE cursor_fruit CURSOR FOR
SELECT  f_name,f_price FROM fruits;

2.打开游标

OPEN cursor_fruit;

3.读取游标中的数据

 打开游标之后就可以读取游标中的数据了，FETCH命令可以读取游标中的某一行数据。FETCH的语法如下：

ETCH 
[ [ NEXT | PRIOR | FIRST | LAST 

         | ABSOLUTE { n | @nvar }

         | RELATIVE { n | @nvar }
    ]
FROM
]
{ { [GLOBAL ] cursor_name } | @cursor_variable_name}
[ INTO @variable_name [ ,...n ] ]

NEXT:紧跟当前行返回结果行，并且当前行递增为返回行，如果FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。

PRIOR：返回紧邻当前行前面的结果行，并且当前行递减为返回行，如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

FIRST：返回游标中的第一行并将其作为当前行。

LAST:返回游标中的最后一行并将其作为当前行。

ABSOLUTE { n | @nvar }：如果n或@nvar为正，则返回从游标头开始向后n行的第n行，并将返回行变成新的当前行。如果n或@nvar为负，则返回从游标末尾开始向前的n行的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。n必须是整数常量，并且@nvar的数据类型必须为int、tinyint或smallint.

RELATIVE { n | @nvar }：如果n或@nvar为正，则返回从当前行开始向后的第n行。如果n或@nvar为负，则返回从当前行开始向前的第n行。如果n或@nvar为0，则返回当前行,对游标第一次提取时，如果在将n或@nvar设置为负数或0的情况下指定FETCH RELATIVE,则不返回行，n必须是整数常量，@nvar的数据类型必须是int、tinyint或smallint.

GLOBAL：指定cursor_name是全局游标。

cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，则cursor_name指的是全局游标，否则cursor_name指的是局部游标。

@cursor_variable_name：游标变量名，引用要从中进行提取操作的打开的游标。

INTO @variable_name [ ,...n ]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型相匹配，或是结果集列数据类型所支持的隐士转换。变量的数目必须与游标选择列表中的列数一致。

【例】使用名称为cursor_fruit的光标，检索fruits表中的记录，输入如下：
USE sample_db;
GO
FETCH NEXT  FROM cursor_fruit 
WHILE  @@FETCH_STATUS=0
BEGIN
    FETCH NEXT  FROM cursor_fruit
END;

4.关闭游标

CLOSE cursor_fruit

5.释放游标

DEALLOCATE cursor_fruit;

五、游标的使用案例

1.使用游标变量

DECLARE @varCursor Cursor  --声明游标变量
DECLARE cursor_fruit CURSOR FOR    --创建游标
SELECT  f_name,f_price FROM fruits;
OPEN cursor_fruit  --打开游标
SET  @varCursor=cursor_fruit  --为游标变量赋值
FETCH NEXT FROM @varCursor   --从游标变量中读取值
WHILE @@FETCH_STATUS=0   --判断FETCH语句是否执行成功
BEGIN
    FETCH NEXT FROM @varCursor   --读取游标变量中的数据
END
CLOSE @varCursor    --关闭游标
DEALLOCATE @varCursor;    --释放游标 

2.用游标为变量赋值

创建游标cursor_variable,将fruits表中的记录f_name,f_price值赋给变量@fruitName和@fruitPrice，并打印输出。
DECLARE @fruitName varchar(50),@fruitPrice DECIMAL(8,2)
DECLARE cursor_variable CURSOR FOR
SELECT f_name,f_price FROM fruits
WHERE s_id=101;
OPEN cursor_variable
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
PRINT '编号为101的供应商提供的水果种类和价格为：'
WHILE @@FETCH_STATUS=0
BEGIN 
    PRINT @fruitName+' '+STR(@fruitPrice,8,2)
FETCH NEXT FROM cursor_variable
INTO @fruitName,@fruitPrice
END
CLOSE cursor_variable
DEALLOCATE cursor_variable;

3.用ORDER BY子句改变游标中的执行顺序

--声明名称为cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下：
DECLARE cursor_order CURSOR FOR
SELECT f_id,f_name,f_price FROM fruits
ORDER BY f_price DESC
OPEN cursor_order
FETCH NEXT FROM cursor_order
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;

4.用游标修改数据

【例】声明整型变量@sid=101,然后声明一个对fruits表进行操作的游标，打开该游标，
--使用FETCH NEXT方法来获取游标中的每一行的数据，
--如果获取到的记录的s_id的字段值与@sid值相同，将s_id=@sid的记录中的f_price修改为12.2，最后关闭释放游标，输入如下：
DECLARE @sid INT,@id INT =101
DECLARE cursor_fruit CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_fruit
FETCH NEXT FROM cursor_fruit INTO @sid
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid=@id
BEGIN 
    UPDATE fruits SET f_price=11.1 WHERE s_id=@id
END
FETCH NEXT FROM cursor_fruit INTO @sid
END
CLOSE cursor_fruit
DEALLOCATE cursor_fruit;
SELECT * FROM fruits where s_id=101;

5.使用游标删除数据

【例】使用游标删除fruits表中s_id=102的记录，如下
DECLARE @sid1 INT,@id1 int=102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO @sid1
WHILE @@FETCH_STATUS=0
BEGIN
    IF @sid1=@id1
BEGIN 
    DELETE FROM fruits where s_id=@id1
END
FETCH NEXT FROM cursor_delete INTO @sid1
END
CLOSE cursor_delete
DEALLOCATE cursor_delete;
SELECT * FROM fruits where s_id=102;