Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.