SQL Server cursor statement usage:
Copy codeThe Code is as follows: -- declare a cursor
DECLARE MyCursor CURSOR
For select top 5 FBookName, FBookCoding FROM TBookInfo // defines a cursor called MyCursor, which stores data after select
-- Open a cursor
OPEN MyCursor // OPEN This dataset
-- Loop a cursor
DECLARE @ BookName nvarchar (2000), @ BookCoding nvarchar (2000)
Fetch next from MyCursor INTO @ BookName, @ BookCoding // move the cursor to the first data, extract the first data and store it in the Variable
WHILE @ FETCH_STATUS = 0 // continue the loop if the previous operation is successful
BEGIN
Print 'name' + @ BookName
Fetch next from MyCursor INTO @ BookName, @ BookCoding // continue to raise the NEXT line
END
-- Close the cursor
CLOSE MyCursor
-- Release resources
DEALLOCATE MyCursor
Eg:Copy codeThe Code is as follows: create table #
(
Id varchar (20 ),
Name varchar (20)
)
Insert into # a select 1, 'jack'
Insert into # a select 2, 'join'
Insert into # a select 3, 'make'
Declare mycursor cursor
For select * from #
Open mycursor
Declare @ id varchar (20), @ name varchar (20)
Fetch next from mycursor into @ id, @ name
While @ fetch_status = 0
Begin
Select @ id, @ name
Fetch next from mycursor into @ id, @ name
End
Close mycursor
Deallocate mycursor
Cursors consume a lot in row-level operations. SQL queries are based on datasets. Therefore, generally, when a query can use a dataset, use a dataset instead of a large amount of cursor data. This is a performance killer.