游标的运用
1、使用游标变量:
USE test2
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; ------------释放游标;
2、使用游标为变量赋值:
DECLARE @fruitsName VARCHAR(23), @FruitsPrice DECIMAL(6,2); ---声明两个变量;
DECLARE cursor_fruits CURSOR FOR
SELECT f_name,f_price FROM fruits ORDER BY f_price DESC; ----将结果集降序排序;
OPEN cursor_fruits;
FETCH NEXT FROM cursor_fruits INTO @fruitsName,@FruitsPrice; ---给变量赋值;
PRINT '水果的种类和价格:';
PRINT '名称'+' 价格 ';
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @fruitsName + ' '+STR(@FruitsPrice,6,2); ---价格保留6位数,最多保留两位小数;
FETCH NEXT FROM cursor_fruits INTO @fruitsName,@FruitsPrice;
END
CLOSE cursor_fruits;
DEALLOCATE cursor_fruits;