Detailed analysis of the usage of SQL cursors
This article mainly explains the detailed analysis of the usage of SQL cursors, the content is clear, interested friends can learn, I believe it will be helpful after reading.
Type:
1. Ordinary cursors only have NEXT operations
two。 Scroll cursors have many operations
1. Ordinary cursor
DECLARE @ username varchar (20), @ UserId varchar (100) DECLARE cursor_name CURSOR FOR-- define cursor SELECT TOP 10 UserId,UserName FROM UserInfo ORDER BY UserId DESCOPEN cursor_name-- Open cursor FETCH NEXT FROM cursor_name INTO @ UserId,@username-- grab the next row of cursor data WHILE @ @ FETCH_STATUS = 0 BEGIN PRINT 'user ID:'+@UserId+'' + 'user name:' + @ username FETCH NEXT FROM cursor_name INTO @ UserId @ username ENDCLOSE cursor_name-- close cursor DEALLOCATE cursor_name-- release cursor
Results:
User ID:zhizhi user name: Deng Hongzhi
User ID:yuyu user name: Wei Yu
User ID:yujie user name: Li Yujie
User ID:yuanyuan user name: Wang Mengyuan
User ID:YOUYOU username: lisi
User ID:yiyiren user name: Ren Yi
User ID:yanbo user name: Wang Yanbo
User ID:xuxu user name: Chen Jiaxu
User ID:xiangxiang user name: Li Qingxiang
User ID:wenwen user name: Wei Wenwen
two。 Scroll cursor
-- SET NOCOUNT ONDECLARE C SCROLL CURSOR FOR with SCROLL option-- SCORLL, there are more cursor operations (scroll cursors) SELECT TOP 10 UserId,UserName FROM UserInfo ORDER BY UserId DESCOPEN C FETCH LAST FROM C-- data for the last row, and the current behavior specifies row FETCH ABSOLUTE 4 FROM C-- the fourth row of data starting from the first row, and specifies the current behavior where n can be positive or negative, n > 0, flip down, n
