What is the basic usage of cursor in SQL
This article mainly introduces "what is the basic use of cursor in SQL". In daily operation, I believe that many people have doubts about the basic use of cursor in SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is the basic use of cursor in SQL?" Next, please follow the editor to study!
Type:
1. Ordinary cursors only have NEXT operations
two。 Scroll cursors have many operations
1. Normal cursors DECLARE @ username varchar (20), @ UserId varchar (100) DECLARE cursor_name CURSOR FOR-define cursors SELECT TOP 10 UserId,UserName FROM UserInfo ORDER BY UserId DESCOPEN cursor_name-open cursors 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 the cursor DEALLOCATE cursor_name-- release the 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。 Scrolling cursors-- cursors with SCROLL option SET NOCOUNT ONDECLARE C SCROLL CURSOR FOR-- SCORLL, there are more cursor operations (scrolling 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
