MSSQL Cursor Use experience _mssql

Cursors provide you with a method of data in an action table that is based on a row-by-line basis instead of processing the entire result set at a time.
1. How to use Cursors
1) Define the cursor sentence Declare < cursors name > Cursor for
2 Create a cursor sentence Open < name >
3 Extract Cursor column value, move record pointer fetch < column list > from < cursor name > [into < variable list]
4 Use the While loop to process rows in a cursor using the @ @Fetch_Status
5 Delete the cursor and release the statement close < cursor name >/deallocate < cursor name >
6 Cursor Application Example
--Defining cursors
Declare Cur_depart Cursor
For the 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
--using cyclic processing of column values in cursors
While @ @Fetch_Status =0
Begin
Print @DeptID, @DeptName
Fetch from Cur_depart into @DeptID, @DeptName
End
--Close/release cursors
Close Cur_depart
Deallocate Cur_depart
2. Details and notes of the statement
1) Define a cursor sentence
Declare < cursor name > [insensitive] [Scroll] Cursor
For <select statement > [for {Read only | Update [< list]}]
The insensitive DBMS creates a temporary copy of the query result set data (instead of using a direct reference to a column in a real row of data in a database table). The cursor is read only, that is, the contents of the content or the underlying table cannot be modified;
SCROLL specifies that the cursor supports selecting any of its rows as the current row by using any fetch option (the Prior Next relative absolute). If omitted, the cursor will support only the move down line (that is, only fetch Next for cursors);
The SELECT statement defines a standard SELECT statement for a cursor result set. Keyword COMPUTE, COMPUTE by, for BROWSE, and into are not allowed within the <select statement > of the cursor Declaration;
Read only prevents users using cursors from changing the contents of the cursor by updating data or deleting rows;
Update creates a cursor column that can update the cursor and lists the values that can be updated. If any column is included in the clause, only columns that are listed can be updated. If you specify only update (no column name list) in the DECLARE CURSOR statement, the cursor will allow you to update any or all of its columns.
Declare Cur_depart Cursor
For the Select * from Department for Update of Cdeptid,cdeptname
2) Extract the cursor column value, move the record pointer statement
Fetch [Next | Prior | A | Last | {Absolute < line #} | {Relative < line number}]
From < cursor name > [into < variable list ...]
Each time the FETCH statement is executed, the DBMS moves to the next row in the cursor and gets the column values in the cursor into the variables listed in into. Therefore, the variables listed in the INTO clause of the FETCH statement must correspond to the type and number of lists in the SELECT statement in the cursor definition;
You can use the row positioning parameters of the FETCH statement (the Prior next relative absolute) only if you are using the scroll parameter when defining the cursor, if you do not include the parameter next in the FETCH statement | Prior | A | Last,dbms will perform the default fetch Next;
Next move one line down and back (record);
Prior move up and forward one row (record);
First move to row one (record) of the result set;
The last row to move to the result set (record);
Absolute n moves to the nth row in the result set. If n is positive, the DBMS moves backwards or downwards from the first 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 positive, 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 n rows;
Fetch relative 2 from Cur_depart into @DeptID, @DeptName
3 positioning delete/update statements based on cursors
If the cursor is updatable (that is, the read only argument is not included in the definition of a cursor sentence), you can delete/update the row from the source table of the cursor data, that is, the operation delete/update the current position based on the cursor pointer;
Example:
--Deletes the record for the current row
Declare Cur_depart Cursor
For the 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 contents of the current line
Declare Cur_depart Cursor
For the Select cdeptid,cdeptname from Department into @DeptID, @DeptName
Open Cur_depart
Fetch from Cur_depart into @DeptID, @DeptName
Update Department Set cdeptid= ' 2007 ' + @DeptID Where Current of Cur_depart
3. Cursor usage tips and attention
1 To change the order of the rows of the cursor by using orders by. It should be noted here that only columns appearing in the SELECT clause in the query can be columns of an ORDER BY clause, unlike the ordinary SELECT statement;
2 when the ORDER BY clause is used in the statement, the cursor cannot be used to perform the positional delete/update statement, and how to resolve the problem, first create the index on the original table, and specify this index to use when creating the cursor, for example:
Declare Cur_depart Cursor
For Select Cdeptid,cdeptname from Department with INDEX (idx_id)
For Update of Cdeptid,cdeptname
By adding the with index in the FROM clause, the table is sorted by index.
3 in a cursor can contain a computed value as a column;
4 Determine the number of rows in the cursor using @ @Cursor_Rows;
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 to @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 to @NIAN
Close GetYear
Deallocate getyear
Return @NIANS
End