第十二章 游标

【例12.1】声明名称为cursor_fruit的游标，输入语句如下。
USE test;
GO
DECLARE cursor_fruit CURSOR FOR   
SELECT f_name, f_price FROM fruits ;

【例12.2】打开上例中声明的名称为cursor_fruit的游标，输入语句如下。
USE test;
GO
OPEN  cursor_fruit ; 

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

【例12.4】关闭名称为cursor_fruit的游标，输入语句如下。
CLOSE  cursor_fruit; 

【例12.5】使用DEALLOCATE语句释放名称为cursor_fruit的变量，输入语句如下。
USE test;
GO
DEALLOCATE cursor_fruit;

【例12.6】声明名称为@VarCursor的游标变量，输入语句如下。
USE test;
GO
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               --释放游标

【例12.7】创建游标cursor_variable，将fruits表中记录的f_name，f_price值赋给变量@fruitName和@fruitPrice，并打印输出，输入语句如下。
USE test;
GO
DECLARE @fruitName VARCHAR(20), @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 '编号为的供应商提供的水果种类和价格为：'
PRINT '类型：' +'    价格：' 
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

【例12.8】声明名称为Cursor_order的游标，对fruits表中的记录按照价格字段降序排列，输入语句如下。
USE test;
GO
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

【例12.9】声明整型变量@sID=101，然后声明一个对fruits表进行操作的游标，打开该游标，使用FETCH...NEXT方法来获取游标中的每一行的数据，如果获取到的记录的s_id字段值与@sID值相同，将s_id=@sID的记录中的f_price字段修改为11.1，最后关闭并释放游标，输入语句如下。
USE test;
GO
DECLARE @sID INT              --声明变量
DECLARE @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;

【例12.10】使用游标删除fruits表中s_id=102的记录，输入语句如下。
USE test;
GO
DECLARE @sID INT              --声明变量
DECLARE @ID INT =102
DECLARE cursor_delete CURSOR FOR
SELECT s_id FROM fruits ;
OPEN cursor_delete
FETCH NEXT FROM cursor_delete INTO @sID
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @sID = @ID
BEGIN
DELETE FROM fruits WHERE s_id=@ID
END
FETCH NEXT FROM cursor_delete INTO @sID
END
CLOSE cursor_delete
DEALLOCATE cursor_delete 
SELECT * FROM fruits WHERE s_id = 102;

【例12.11】打开一个全局游标，并使用sp_cursor_list报告该游标的属性，输入语句如下。
USE test
GO
--声明游标
DECLARE testcur CURSOR  FOR
SELECT f_name
FROM test.dbo.fruits
WHERE f_name LIKE 'b%'
--打开游标
OPEN testcur

--声明游标变量
DECLARE @Report CURSOR

--执行sp_cursor_list存储过程，将结果保存到@Report游标变量中
EXEC sp_cursor_list @cursor_return = @Report OUTPUT,@cursor_scope = 2

--输出游标变量中的每一行.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END

--关闭并释放游标变量
CLOSE @Report
DEALLOCATE @Report
GO

--关闭并释放原始游标
CLOSE testcur
DEALLOCATE testcur
GO

【例12.12】打开一个全局游标，并使用sp_describe_cursor报告该游标的属性，输入语句如下。
USE test
GO
--声明游标
DECLARE testcur CURSOR  FOR
SELECT f_name
FROM test.dbo.fruits
--打开游标
OPEN testcur
--声明游标变量
DECLARE @Report CURSOR

--执行sp_describe_ cursor存储过程，将结果保存到@Report游标变量中
EXEC sp_describe_cursor @cursor_return = @Report OUTPUT,
@cursor_source=N'global',@cursor_identity = N'testcur'

--输出游标变量中的每一行.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END

--关闭并释放游标变量
CLOSE @Report
DEALLOCATE @Report
GO

--关闭并释放原始游标
CLOSE testcur
DEALLOCATE testcur
GO

【例12.13】打开一个全局游标，并使用sp_describe_cursor_columns报告游标所使用的列，输入语句如下。
USE test
GO
--声明游标
DECLARE testcur CURSOR  FOR
SELECT f_name
FROM test.dbo.fruits
--打开游标
OPEN testcur
--声明游标变量
DECLARE @Report CURSOR

--执行sp_describe_cursor_columns存储过程，将结果保存到@Report游标变量中
EXEC master.dbo.sp_describe_cursor_columns
    @cursor_return = @Report OUTPUT
    ,@cursor_source = N'global' 
    ,@cursor_identity = N'testcur';

--输出游标变量中的每一行.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END

--关闭并释放游标变量
CLOSE @Report
DEALLOCATE @Report
GO

--关闭并释放原始游标
CLOSE testcur
DEALLOCATE testcur
GO

【例12.14】打开一个全局游标，并使用 sp_describe_cursor_tables 报告游标所引用的表，输入语句如下。
USE test
GO
--声明游标
DECLARE testcur CURSOR  FOR
SELECT f_name
FROM test.dbo.fruits
WHERE f_name LIKE 'b%'
--打开游标
OPEN testcur

--声明游标变量
DECLARE @Report CURSOR

--执行sp_describe_cursor_tables存储过程，将结果保存到@Report游标变量中
EXEC sp_describe_cursor_tables
      @cursor_return = @Report OUTPUT,
      @cursor_source = N'global', @cursor_identity = N'testcur'

--输出游标变量中的每一行.
FETCH NEXT from @Report
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   FETCH NEXT from @Report
END

--关闭并释放游标变量
CLOSE @Report
DEALLOCATE @Report
GO

--关闭并释放原始游标
CLOSE testcur
DEALLOCATE testcur
GO