Simple cursor traversal of data

A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with a t_ SQL selection statement because it is a result set (it can be zero or one or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared.

There are four basic steps to use a cursor: declare the cursor, open the cursor, extract data, and close the cursor.

1. Declare a cursor

Like other variables, we can also define the access type of the cursor: Global, shared, instance, or local. We recommend that you use the same naming conventions as other variables. Declare my_cursor cursor keyset for select * from Info

Declare mermercursor cursor
Select acct_no, name, balance from customer where province = "Beijing ";

2. Open the cursor
Open customercursor;

3. extract data
Fetch statement to obtain data. A fetch statement can put a recordProgramVariable specified by the Member. After a cursor has been declared and opened, we can put the data into any variable. In the fetch statement, you can specify the cursor name and the target variable name.

-- Declare local variables
Declare @ ID int, @ name varchar (20), @ address varchar (20)
-- Locate the record at the specified position
Fetch absolute 56488 from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the relative position record of the current record
Fetch relative-88 from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the previous record
Fetch prior from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the last record
Fetch next from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate the first record
Fetch first from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address
-- Locate to the end record
Fetch last from my_cursor into @ ID, @ name, @ address
Select @ ID as ID, @ name as name, @ address as address

Note: If the message "extraction type · cannot be used only for the cursor" is displayed, declare mycursor cursor scroll for · (fast_forward: only for the cursor; scroll: Scroll cursor)

Example:

Declare mycursor cursor scroll for select * From test1

Declare @ row1 int, @ row2 char (10), @ row3 char (10), @ row4 char (10)

Open mycursor

Fetch next from mycursor into @ row1, @ row2, @ row3, @ row4

While (@ fetch_status = 0)
Begin

Insert into test1 (ID, a, B, c)
Values (@ row1, @ row2, @ row3, @ row4)

Fetch next from mycursor into @ row1, @ row2, @ row3, @ row4

End
Close mycursor
Deallocate mycursor

(The example is to copy the data in the test1 table once !)

(@ Fetch_status returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened by the connection. 0 fetch statement succeeded;-1 fetch statement failed or this row is not in the result set;-2 extracted row does not exist; so use @ fetch_status = 0 to determine whether the cursor has reached the last row)

Advanced applications:

To modify the information of the row where the current cursor is located, perform the following operations:
Update Table1
Set balance = 1000
Where current of yourcursor;
Delete the current row as follows:
Delete from Table1
Where current of yourcursor;

when declaring a cursor: declare my_cursor cursor scroll dynamic for ·
scroll indicates that the cursor pointer can be freely moved (otherwise, only forward ), dynamic indicates that the cursor can be read and written (otherwise, the cursor is read-only)