Principle of SQL Server database cursor

Principle of cursor

In general, SQL query results are results set of multiple records, while advanced languages can only process one record at a time. The cursor mechanism is used to read and process multiple records at a time. In this way, the operation on the set is converted to the processing of a single record. The procedure for using a cursor is as follows:

1. Declare the cursor. The SELECT statement is not executed when the cursor is declared.
Declare <cursor Name> cursor for <SELECT statement>;
 
2. Open the cursor. When you open a cursor, you actually execute the corresponding SELECT statement to read the query results to the buffer zone. At this time, the cursor is active and the Pointer Points to the first record of the query result set.
Open <cursor Name>;
 
3. Push the cursor pointer and read the current record. Use the fetch statement to forward the cursor pointer to a record and read the current record in the buffer to the variable. The fetch statement is usually used in a cyclic structure. The fetch statement is executed cyclically to retrieve rows in the result set one by one for processing. In many databases, the cursor pointer can be moved at any step, not just forward the cursor pointer to a row.
Fetch <cursor Name> into <variable 1>, <variable 2>...
 
4. Close the cursor. Close the cursor with the close statement to release the buffer and other resources occupied by the result set. When the cursor is closed, it is no longer associated with the original query result set. However, the cursor can be opened again, which is associated with the new query results.
Close <cursor Name>;