How to use cursors in sql server (create, open, read, close, delete)

　1. Problems encountered

　In fact, it is not a big problem O(∩_∩)O: Sometimes we may want to directly process the select result set in batch processing or storage process. At this time , we need a database object that allows us to process each row of records one by one.

　 2. The concept of cursor

　 To solve the above problem, we can use a database object called "cursor".

　 Cursor (Cursor) can be regarded as a data type, which can be used to traverse the result set, equivalent to a pointer, or a subscript in an array. It has the following methods for processing result sets:

Positioning a certain row in the result set

Searching for a row or part of a row from the position of the current result set

Modifying the data of the current row in the result set

3. Cursor The usage method (create, open, read, close, delete)

　　【Create Cursor】

　 is somewhat similar to the method of defining various data types, but be careful not to add "@" (actually there are also "cursor type variables" ", the usage is almost the same as "cursor", and the @ symbol is used when defining). The following is the statement that defines the cursor:

　 declare cursor name cursor [local|global] [forward_only|scroll]

　 for

　 select query statement

Cursors are divided into two types: local cursors and global cursors. local means local cursor, global Represents a global cursor (default value, can be omitted). When forward_only (default value, can be omitted) is specified, the cursor is forward-only, which means that records can only be extracted from beginning to end. If you need to jump back and forth between rows, you need to specify scroll.

　【Use Cursor】

　 There is no point in just creating a cursor but not using it. Let's take the simplest example to demonstrate the steps after creating a cursor:

　　--[Create Cursor]

　declare C1 cursor for select xingming from yiren

　declare @xingming varchar(20)

--[Open cursor]

　open C1

　 --[Read cursor]

　fetch next from C1 into @xingming --The characteristic of while is that it needs to be written once first

　while(@@FETCH_STATUS=0)

begin

　print 'Name:'+@xingming

　fetch next from C1 into @xingming

　end

　--[Close cursor]

　close C1

　--[Delete cursor]

　deallocate C1

Cursor Is the usage method similar to whle(rs.next()){} in Java? In fact, when rs.next() is executed, it directly moves backward in the result set. If it does not reach the end of the result set, , the loop body will still be executed. The same is true for using a cursor here. When the value of @@FETCH_STATUS is 0, the cursor has not yet reached the end. When it is no longer 0, the cursor reaches the end and exits the loop.

　Fetch next from cursor name into variable name list is a fixed form method of reading the cursor content. When the query statement selects multiple fields, you also need to use this sentence to assign values ​​to multiple variables when reading. So write it as a list of variable names.

　　【Global cursor and scroll cursor】

　 mentioned earlier global cursor and scroll cursor, here is an example:

　if(CURSOR_STATUS('global','CURSOR_2')!=-3) deallocate CURSOR_2

　declare CURSOR_2 cursor scroll --global scroll cursor

　for select xingming, niche, xingbie from yiren

　 --the first T-SQL batch starts

　open CURSOR_2

　declare @seq int,

　@xingming varchar(20) ,@nicheng varchar(50),@xingbie nchar

　set @seq=4

　fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie

　if(@@FETCH_STATUS=0)

　begin

print '+cast(@seq as varchar)+' artist is: '+@xingming

　print case @xingbie when 'male' then 'him' when 'female' then 'her' end

　+' The nickname is: '+@nicheng

　end

　close CURSOR_2

　　--The second T-SQL batch starts

　open CURSOR_2

　declare @seq int,

　@xingming varchar(2 0),@ nicheng varchar(50),@xingbie nchar

　set @seq=5 --Divided into two batches, @seq needs to be defined again

　fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie

　if( @@FETCH_STATUS=0)

　begin

　print '+cast(@seq as varchar)+' artist is: '+@xingming

　print case @xingbie when 'male' then 'him' when 'female ' then 'she' end

　+' nickname is: '+@nicheng

　end

　close CURSOR_2

　go

　--Delete the cursor in the third batch

　deallocate CURSOR_2

　When scroll is turned on After the option, fetch can be used to read next (move back), prior (move forward), first (first line), last (last line), absolute (position to an absolute line with a numerical value), relative (position to a relative line with a numerical value). OK) .

　Once a global cursor is defined, it will always exist, so every batch can see it. It won't go away until you use deallocate to remove it. CURSOR_STATUS('global','CURSOR_2') can check its status.

　【Nesting of Cursors】

　 Since it greatly affects system performance, just take a brief look at it.

　if(CURSOR_STATUS('global','CURSOR_3')!=-3) deallocate CURSOR_3

　declare CURSOR_3 cursor for

　select yanchuid from yanchu

　open CURSOR_3

　declare @ycid int

　fetch next from CURSOR_3

　 into @ycid

　while(@@FETCH_STATUS=0)

　begin

　print 'The artist participating in the '+cast(@ycid as varchar)+'th performance is:'

　declare CURSOR_4 cursor for

　select xingming from yiren where yirenid in

　　(select yirenid from yanchuyiren where yanchuid=@ycid)

　　——This sentence uses a subquery, and you can actually nest a cursor

　declare @xingming varchar(50)

　open CURSOR_4

　　fetch next from CURSOR_4 into @xingming

　while(@@FETCH_STATUS=0)

　begin

　print @xingming

　　fetch next from CURSOR_4 into @xingming

　end

　　close CURSOR_4

　deallocate CURSOR_4

fetch next from CURSOR_3

　into @ycid

　print ''

　end

　close CURSOR_3

　deallocate CURSOR_3

　【Cursor variable】

　Cursor variables are truly treating cursors as data types a method to use, The difference between a cursor variable and a cursor object is whether there is @. When creating a cursor variable, first declare @cursor variable name cursor, and then set @cursor variable name=cursor for select statement.

　　declare @c1 CURSOR

　　set @c1=cursor for select xingming from yiren

　　open @c1

　　declare @xingming varchar(50)

　　fetch next from @c1 into @xingming

　print @xingming

　close @c1

　deallocate @c1

　IV. Precautions for Cursors

　【Disadvantages of Cursors】

　Using a cursor will take out the result set and process it one by one, which increases the burden on the server. Furthermore, using cursors is far more efficient. Not as efficient as using the default result set. Therefore, if you can't use the cursor, try not to use it.

　【Additional explanation of cursor】

　When we just open a cursor, it does not point to the first record, but to the front of the first record. We can use a book as an analogy. The cursor can not only point to the records in the record set (each page of the book content), but also point to places where there are no records outside the record set (the cover and back cover of the book).

　 @@fetch_status has three values: 0 means that fetch is executed normally, -1 means that fetch exceeds the result set, and -2 means that the row pointed to by fetch no longer exists.

　5. Modify the paging query stored procedure, use the cursor

　Modify the first branch to the following code:

　if @currentpage >1

　begin

　if @currentpage>@totalpages

　begin

　set @ currentpage = @totalpages

　end

　declare @start int,@count int

　set @count = 0

　set @start = @currentpage*@pagesize+1

　set @sql='declare cursor_1 cursor scroll for select * from '

　+@tablename+' order by '+@idname

　exec(@sql)

　open cursor_1

　fetch relative @start,@pagesize from cursor_1

　while @@fetch_status=0

　begin

set @count = @count+1

　fetch next from cursor_1

　if @count=@pagesize-1

　break

　end

　close cursor_1

　deallocate cursor_1

　end

　And remove the

　Exec(@sql)

　That’s it. If this sentence is not removed, this sentence will be executed one more time at the end of the stored procedure, thus generating the @cursor_1 cursor again by mistake.