SQL 游标cursor的运用 ╰半橙微兮° 2022-08-22 12:28 167阅读 0赞 游标的运用 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; ------------释放游标; ![这里写图片描述][20160527222448860] 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; ![这里写图片描述][20160527222458438] [20160527222448860]: /images/20220722/a6b9d8809d974f529c883d71d3dd0ce3.png [20160527222458438]: /images/20220722/a6640d29d5e64d4fb3945a461f563369.png
还没有评论,来说两句吧...