SQL cursor usage)

SQL cursor usage (reproduced)

I. The cursor consists of two parts:
1. The cursor result set is a set of rows returned by the SELECT statement that defines the cursor.
2. pointer to a row in the Set

Ii. cursor processing process:
Declare statement Declaration
Open with open statement
Use the fecth statement to extract data from the cursor
Determines whether it is null. If it is null, no is returned. If it is not null, yes is returned.
Close with close
Release with deallocate

1. Declared cursor:
Declare cursor name [insensitive] [scroll] cursor for select statement [For {read only | update [of column [,... n]}]
Description: insensitive defines a cursor to create a temporary copy of data that will be used by the cursor.
Scroll specifies that all the extraction items (first, last, Prior, next, relative, absolute) can be used. The default value is next.
Compute, compute by, for browse, and into cannot be used in select statements.

2. Open the cursor:
Open {[Globa] cursor name} | cursor variable
Note: Global is a global cursor.

3. Extract rows from open cursors

Iii. cursor type
Const adopenforwardonly = 0
Forward cursor, which is the default and provides the fastest running performance. Use it to open recordset and get all results from the right to the end. It does not support backward scrolling and only allows one-way moving between results.

Const adopenkeyset = 1
A static cursor indicates the data status in the table when the cursor is opened for the first time. The cursor cannot identify whether the data rows in the underlying table have been updated, deleted, or added to new data. However, unlike the continent logo that can only be moved forward, the static cursor can scroll before and after the result.

Const adopendynamic = 2
The keyboard-driven cursor can be used to query some changes of the underlying data rows in the table, but not all. In particular, it can accurately reflect whether data has been updated. However, it cannot identify whether other users have deleted data rows (the deleted data rows will leave holes in the recordset ). The keyboard-driven cursor supports scrolling between results.

Const adopenstatic = 3
Dynamic cursors are the most abundant types of cursors. When the cursor is opened, You can query any table changes made by other users and scroll.

Lock type
Const adlockreadonly = 1
The default lock type. Read-Only locks allow multiple users to read the same data at the same time, but do not change the data.

Const adlockpessimistic = 2
Open the data object in a pessimistic locking mode. This method is assumed that other users will access the data when you edit the record. Once you edit the record, other users cannot access the data.

Const adlockoptimistic = 3
Open Data Objects in Optimistic Locking mode. This method assumes that no other user accesses the data when you edit the record. Before the change is completed, other users cannot access the record.

Const adlockbatchoptimistic = 4
This type is used when multi-row batch processing is performed for updating

Vi. Integrated instances
Use SQL Server cursor statements to print reports
Declare authors_cursor cursor
Select au_id, au_fname, au_lname
From authors
Where State = "ut"
Order by au_id

Open authors_cursor

Fetch next from authors_cursor
Into @ au_id, @ au_fname, @ au_lname

While @ fetch_status = 0
Begin
Print ""
Select @ message = "----- books by Author:" +
@ Au_fname + "" + @ au_lname

Print @ message

-- Declare an inner cursor based
-- On au_id from the outer cursor.

Declare titles_cursor cursor
Select T. Title
From titleauthor Ta, titles t
Where ta. title_id = T. title_id and
Ta. au_id = @ au_id -- variable value from the outer cursor

Open titles_cursor
Fetch next from titles_cursor into @ title

If @ fetch_status <> 0
Print "<no books>"

While @ fetch_status = 0
Begin

Select @ message = "" + @ title
Print @ message
Fetch next from titles_cursor into @ title

End

Close titles_cursor
Deallocate titles_cursor

-- Get the next author.
Fetch next from authors_cursor
Into @ au_id, @ au_fname, @ au_lname
End

Close authors_cursor
Deallocate authors_cursor
Go

0 0

0

(Please Article Make comments)