Back up the following table:

SQL SERVER Reference: Explanation and example of cursors (Cursor)

Each cursor must have four components, these four key parts must conform to the following order;

1.DECLARE Cursors
2.OPEN Cursors
3. Fetch information from a cursor
4.CLOSE or deallocate cursors

Usually we use declare to declare a cursor declaring a cursor that mainly includes the following main elements:

Cursor name
Data sources (tables and columns)
Selection criteria
Properties (read-only or modifiable)
The syntax format is as follows: DECLARE cursor_name [insensitive] [SCROLL] Cursor
For select_statement
[For {READ only | UPDATE [of column_name [,... N]]}]

which
Cursor_name refers to the name of the cursor.

Insensitive

Indicates that MS SQL SERVER stores the data records selected by the cursor definition in a temporary table (built under the tempdb database). The read operation on the cursor is answered by a temporary table. Therefore, modifications to the base table do not affect the data that is extracted by the cursor, that is, the cursor does not change as the contents of the base table are changed, and the base table cannot be updated with the cursor. If you do not use the reserved word, updates and deletions to the base table will be reflected in the cursor.

Cursor pointer sketch

With:

1. Define a standard cursor: Declare mycursor CURSOR FOR SELECT * FROM Yuangong

2. Define a read-only cursor: Declare mycursor CURSOR FOR SELECT * FROM Yuangong for Read only

3. Define a writable cursor: Declare MYCURSOR1 cursor FOR SELECT * from Yuangong for update of

Name, sex, age, base salary, bonus, income tax, salary payable
Note: Scroll can only work on read-only cursors

4. Open cursor: Open cursor name

For example: Declare mycursor cursor FOR SELECT * FROM Yuangong
Open MyCursor

5. Fetching data from a cursor: Fetch, by default, before the pointer points to the first record

How to move a record pointer:
Next move down one record
Prior move up one record
First record
Last record
Absolute N Absolute record nth record

Fetching data syntax: FETCH NEXT | Prior | A | Last | Absolute n from cursor name [into variable names list]

6. Closing cursor: Close cursor name
The cursor is temporarily closed and can be opened again using open.

7. Release cursor: DEALLOCATE cursor name

Clears the cursor from memory. If you want to use it again, you must declare it again.

To determine the current cursor state:

8. @ @fetch_status If the return is 0, the current operation is successful. Otherwise it will fail.
0 FETCH statement succeeded.
-1 The FETCH statement failed or the row is not in the result set.
-2 The extracted row does not exist.

Example 1:

Use cursors to read all of the data from a student table: Declare @i INT
DECLARE @TN char (8), @FU char (20)
Declare mycursor cursor FOR select Sno,sname from student
Open MyCursor
Select @i = count (*) from student
While @ @fetch_status = 0 and @i > 1
BEGIN
FETCH NEXT from MyCursor into @TN, @FU
Set @i = @i-1
PRINT @TN + ' + @FU
End
Close MyCursor
Deallocate mycursor

Results:
s1001, Jack Dong.
s1002 Lucy Dong
s1003 Brezse Dong
s1004 Andy Liu
s1005 Jack Chen

Example 2:

Manipulate the read data through the cursor and output different results: Declare @s_name varchar, @c_name varchar (), @sc_core int
Declare my_cur cursor FOR select Sname,cname,scgrade
From student S, Course c, studentcourse sc WHERE s.sno = sc.sno and C.cno = Sc.cno
Open My_cur
Print Space (27) + ' 2007 Computer Professional Examination system '
FETCH NEXT from My_cur into @s_name, @c_name, @sc_core
While @ @fetch_status = 0
Begin
If @sc_core < 60
Begin
Print space + @s_name + @c_name + ': Fail '
End
Else
Begin
If @sc_core >= @sc_core < 70
Begin
Print space + @s_name + @c_name + ': Pass '
End
Else
Begin
If @sc_core >= @sc_core < 80
Begin
Print space + @s_name + @c_name + ': Good '
End
Else
Begin
Print space + @s_name + @c_name + ': Excellent '
End
End
End
FETCH NEXT from My_cur into @s_name, @c_name, @sc_core
End
Close My_cur
Deallocate my_cur

Results:
2007 Computer Professional Examination system
Jack Dong C + + programming: Pass
Jack Dong operating system: good
Lucy Dong C + + program design: Excellent
Lucy Dong Computer composition principle: good
Brezse Dong C + + program design: Excellent
Brezse Dong Object-oriented program design method: Fail
Andy Liu Operating system: failed
Andy Liu Computer Composition principle: excellent

Issues to be aware of when using cursors:
　
(1) While the use of cursors is flexible, direct manipulation of single data in a dataset can be achieved, but cursors affect system performance in the following ways:
-Use of cursors results in increased page locks and table locks
-Leads to increased network traffic
-Additional overhead for the server to process the corresponding instructions

(2) Optimization problems when using cursors:
-explicitly indicate the purpose of the cursor: for read only or for update
-Specify the modified column after the for update