游标的使用笔记

USE sample_db;
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,&＃39;apple&＃39;,5.8),
        (102,&＃39;blackberry&＃39;,6.8),
        (105,&＃39;orange&＃39;,4.5),
        (102,&＃39;banana&＃39;,3.5),
        (103,&＃39;lemon&＃39;,8.0),
        (104,&＃39;grape&＃39;,7.6),
        (101,&＃39;melon&＃39;,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.读取游标中的数据

--【例】使用名称为cursor_fruit的光标&＃xff0c;检索fruits表中的记录&＃xff0c;输入如下&＃xff1a;
USE sample_db;
GO
FETCH NEXT  FROM cursor_fruit 
WHILE  &＃64;&＃64;FETCH_STATUS&＃61;0
BEGIN
    FETCH NEXT  FROM cursor_fruit
END;

--4.关闭关闭名称为cursor_fruit的游标

CLOSE cursor_fruit

--5.释放游标

DEALLOCATE cursor_fruit;

--游标的运用

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

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

--声明名称为cursor_order的游标&＃xff0c;对fruits表中的记录按照价格字段降序排列&＃xff0c;输入语句如下&＃xff1a;
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 &＃64;&＃64;FETCH_STATUS&＃61;0
FETCH NEXT FROM cursor_order
CLOSE cursor_order
DEALLOCATE cursor_order;

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

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