MSSQL cursor usage experience

A cursor provides you with a method to operate data in a table on a row-by-row basis instead of a result set.
1. How to use a cursor
1) define the Cursor statement Declare <Cursor Name> Cursor
2) create a cursor statement Open <cursor Name>
3) extract the cursor column value and move the record pointer Fetch <column Name List> From <cursor Name> [Into <Variable list>]
4) use @ Fetch_Status to process rows in the cursor using the While LOOP
5) Delete the cursor and release the statement Close <cursor Name>/Deallocate <cursor Name>
6) cursor application instance
-- Define a cursor
Declare cur_Depart Cursor
For Select cDeptID, cDeptName From Department into @ DeptID, @ DeptName
-- Create a cursor
Open cur_Depart
-- Move or extract column values
Fetch From cur_Depart into @ DeptID, @ DeptName
-- Process the column values in the cursor cyclically
While @ Fetch_Status = 0
Begin
Print @ DeptID, @ DeptName
Fetch From cur_Depart into @ DeptID, @ DeptName
End
-- Close/release the cursor
Close cur_Depart
Deallocate cur_Depart
2. Statement details and notes
1) define a cursor statement
Declare <Cursor Name> [Insensitive] [Scroll] Cursor
For <Select statement> [FOR {Read Only | Update [OF <column Name List>]}]
Insensitive DBMS creates a temporary copy of the query result set data (instead of directly referencing columns in real data rows in the database table ). The cursor is Read Only, that is, its content or the content of the underlying table cannot be modified;
Scroll specifies that the cursor can be selected as the current row by using any Fetch option (First Last Prior Next Relative Absolute. If this option is omitted, the cursor only supports moving down a single row (that is, only the Fetch Next of the cursor is supported );
The Select statement defines the standard SELECT statement of the cursor result set. Keywords COMPUTE, compute by, for browse, and INTO are not allowed in the <Select statement> declared BY the cursor;
Read Only prevents users who use a cursor from changing the cursor content by updating data or deleting rows;
Update: Create a cursor column that can be updated and list values that can be updated. If any column is included in the clause, only the column to be included can be updated. If only the specified UPDATE (no column Name List) in the Declare Cursor statement is specified, the Cursor will allow updating any or all of its columns.
Declare cur_Depart Cursor
For Select * From Department For Update OF cDeptID, cDeptName
2) extract cursor column values and move record pointer statements
Fetch [Next | Prior | First | Last | {Absolute <row number >}| {Relative <row number >}]
From <cursor Name> [Into <Variable list...>]
Every time a Fetch statement is executed, the DBMS moves to the next row in the cursor and obtains the column values in the cursor to the variables listed in. Therefore, the variables listed in the Into clause of the Fetch statement must correspond to the type and number of the list in the Select statement in the cursor definition;
The First Last Prior Next Relative Absolute statement can be used only when the Scroll parameter is used to define the cursor ); if the Fetch statement does not include the parameter Next | Prior | First | Last, the DBMS executes the default Fetch Next;
Next move one row down and backward (record );
Prior moves up and forward a row (record );
First moves to the First row (record) of the result set );
Last move to the Last row (record) of the result set );
The Nth row in the result set that Absolute n moves. If n is a positive value, the DBMS moves backward or downward from the header of the result set to row n. If n is a negative number, the DBMS moves n rows forward or up from the bottom of the result set;
Fetch Absolute 2 From cur_Depart Into @ DeptID, @ DeptName
Relative n moves n rows from the current position of the pointer. If n is a positive value, the DBMS moves the row pointer back or down to row n. If n is a negative number, the DBMS moves the row Pointer Forward or up to n rows;
Fetch Relative 2 From cur_Depart Into @ DeptID, @ DeptName
3) cursor-based DELETE/UPDATE statements
If the cursor is updatable (that is, the Read Only parameter is not included in the definition of the cursor statement), you can use the cursor to DELETE/UPDATE rows from the source table of the cursor data, DELETE/UPDATE operations based on the current position of the cursor;
Example:
-- Delete the record of the current row
Declare cur_Depart Cursor
For Select cDeptID, cDeptName From Department into @ DeptID, @ DeptName
Open cur_Depart
Fetch From cur_Depart into @ DeptID, @ DeptName
Delete From Department Where current of cur_Depart
-- Update the content of the current row
Declare cur_Depart Cursor
For Select cDeptID, cDeptName From Department into @ DeptID, @ DeptName
Open cur_Depart
Fetch From cur_Depart into @ DeptID, @ DeptName
Update Department Set cDeptID = '000000' + @ DeptID Where current of cur_Depart
3. Tips and notes for using cursors
1) Use Order By to change the Order of the rows in the cursor. Note that only the columns in the Select clause in the query can be used as the Order by clause column, which is different from the normal Select statement;
2) When the Order By clause is used in the statement, you cannot use a cursor to execute the DELETE/UPDATE statement. To solve this problem, first create an index on the original table, use this index when creating a cursor. For example:
Declare cur_Depart Cursor
For Select cDeptID, cDeptName From Department With INDEX (idx_ID)
For Update Of cDeptID, cDeptName
By adding With Index to the From clause, you can use the Index to sort the table;
3) The cursor can contain the calculated value as a column;
4) use @ Cursor_Rows to determine the number of rows in the cursor;
Alter function SEL_KEYAR (@ yearnum int, @ f_k_lessonid VARCHAR (15 ))
Returns varchar (8000)
AS
BEGIN
DECLARE @ nian varchar (8000), @ nums int, @ nians varchar (8000)
SET @ NUMS = 1
Declare getyear cursor for select f_year FROM t_kejianol WHERE f_k_lessonid = @ f_k_lessonid group by f_year order by f_year DESC
OPEN GETYEAR
Fetch next from getyear into @ NIAN
WHILE @ FETCH_STATUS = 0
BEGIN
IF @ YEARNUM = @ NUMS
SET @ NIANS = ISNULL (@ NIANS + ',', '') + RTRIM (@ NIAN)
SELECT @ NUMS = @ NUMS + 1
Fetch next from getyear into @ NIAN
-- Fetch absolute 3 from getyear into @ NIAN
END
CLOSE GETYEAR
DEALLOCATE GETYEAR
-- PRINT @ NIANS
RETURN @ NIANS
END
Alter function SEL_KEYAR (@ yearnum int, @ f_k_lessonid VARCHAR (15 ))
Returns varchar (8000)
AS
BEGIN
DECLARE @ nian varchar (8000), @ nians varchar (8000)
Declare getyear cursor for select f_year FROM t_kejianol WHERE f_k_lessonid = @ f_k_lessonid group by f_year order by f_year ASC
OPEN GETYEAR
Fetch absolute @ yearnum from getyear into @ NIAN
CLOSE GETYEAR
DEALLOCATE GETYEAR
RETURN @ NIANS
END