SQLSERVER2005 cursor Application and detailed description

SQLSERVER2005 cursor Application and detailed description
/*
Output cursor Parameters
The output cursor parameter is used to pass the local cursor of the stored procedure back to the calling batch, stored procedure, or trigger.

First, you create the following procedure to declare and open a cursor on the titles table:

Use pubs
If exists (select name from sysobjects
where name = ' Titles_cursor ' and type = ' P '
drop procedure Titles_cursor
Go
CREATE PROCEDURE titles_cursor @titles_cursor cursor varying output
As
Set @titles_cursor = cursor
Forward_only Static for
SELECT *
From titles

Open @titles_cursor
Go

Next, execute a batch, declare a local cursor variable, perform the procedure above to assign the cursor to a local variable, and then extract the row from the cursor.

Use pubs
Go
DECLARE @mycursor cursor
exec titles_cursor @titles_cursor = @mycursor output
while (@ @fetch_status = 0)
Begin
FETCH NEXT from @mycursor
End
Close @mycursor
DEALLOCATE @mycursor
Go

About SQL Server cursors

cursor (cursor) It enables users to access the result set returned by SQL Server line-by-row. One of the main reasons for using cursors (cursor) is to convert a collection operation to a single record-handling method. When you retrieve data from a database tutorial in SQL language, the results are placed in an area of memory, and the result is often a collection of multiple records. The cursor mechanism allows users to access these records line by row in SQL Server, and to display and process the records according to the user's own wishes.

1.2 Advantages of Cursors

The following advantages can be obtained from the cursor definition, which makes the cursor play an important role in practical applications:

1) allows the program to perform the same or different actions on each row in the rowset returned by the query statement select, rather than performing the same operation on the entire rowset.

2 provides the ability to delete and update rows in a table based on the cursor position.

3 the cursor actually serves as a bridge between the set-oriented database management system (RDBMS) and the line-oriented programming, so that the two approaches are communicated through cursors.

1.3 Use of Cursors

Speaking of the merits of this multiple-cursor, we are now in person to uncover the mysterious veil of the cursor.

Use the order of cursors: Reputation cursors, open cursors, read data, close cursors, delete cursors.

1.3.1 Declaration cursor

Simplest cursor declaration: Declare < cursor name >cursor for<select statement >;

Where the SELECT statement can be a simple query, or a complex, successive query and nested query

Example: [Table 2 addsalary for example]

Declare mycursor cursor FOR SELECT * FROM addsalary so I declare a cursor on the table addsalary MyCursor

"Advanced Notes"

Declare < cursor name > [insensitive] [scroll] cursorfor<select statement > Here I say a downstream standard. [Insensitive] and [scroll]

in intermediate applications