A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL server and display and process these records as you wish.
1. Why cursor:
A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL server and display and process these records as you wish.
2. How to use a cursor:
Generally, using a cursor follows the following general steps:
(1) Declare the cursor. Associate the cursor with the result set of the T-SQL statement.
(2) Open the cursor.
(3) Use a cursor to operate data.
(4) Close the cursor.
2.1. Declare a cursor
Standard syntax format of the declare cursor statement SQL-92:
Declare cursor name [INSENSITIVE] [SCROLL] CURSOR
FOR SQL-statement
Eg:
Declare MycrsrVar Cursor
FOR Select * FROM tbMyData
2.2 Open cursor
OPEN MycrsrVar
When the cursor is opened, the row pointer will point to the cursor set before the 1st rows, if you want to read 1st rows of data in the cursor set, you must move the row pointer to 1st rows. In this example, you can use the following operations to read 1st rows of data:
Fetch first from E1cursor
Or fetch next from E1cursor
2.3 use a cursor to operate data
The following example uses @ FETCH_STATUS to control the cursor activity in a WHILE loop.
/* Perform the following operations to read data using a cursor. */
DECLARE E1cursor cursor/* declares the cursor. The default value is FORWARD_ONLY */
For select * FROM c_example
OPEN E1cursor/* OPEN cursor */
Fetch next from E1cursor/* read 1st rows of Data */
WHILE @ FETCH_STATUS = 0/* use a WHILE loop to control cursor activity */
BEGIN
Fetch next from E1cursor/* Other rows of data will be read in the loop body */
END
CLOSE E1cursor/* CLOSE the cursor */
DEALLOCATE E1cursor/* Delete cursor */
2.4 close the cursor
CLOSE the cursor using the CLOSE statement
CLOSE {[GLOBAL] cursor name} | cursor variable name}
Use the DEALLOCATE statement to delete a cursor. The syntax format is as follows:
DEALLOCATE {[GLOBAL] cursor name} | @ cursor variable name
3. The Concise syntax of the FETCH operation is as follows:
FETCH
[NEXT | PRIOR | FIRST | LAST]
FROM
{Cursor name | @ cursor variable name} [INTO @ variable name [,…] ]
Parameter description:
NEXT, remove the data of a row and use the NEXT row as the current row (incrementing ). Since the row pointer is directed to the cursor before the cursor is opened, the first execution of the fetch next operation will obtain 1st rows of data in the cursor set. NEXT is the default cursor extraction option.
INTO @ variable name [,…] Put the extracted column data in a local variable. The variables in the list are associated with the corresponding columns in the cursor result set from left to right. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.
--------------------------------------------------------------
After each FETCH operation, you usually need to check the status value in the global variable @ FETCH_STATUS to determine whether the FETCH operation is successful. The variable has three status values:
· 0 indicates that the FETCH statement is successfully executed.
-1 indicates that the FETCH statement fails. For example, moving the row pointer exceeded the result set.
-2 indicates that the extracted row does not exist.
Because @ FETCH_STATU is a global variable, all the cursors on a connection may affect the value of this variable. Therefore, after executing a FETCH statement, you must test the value of this variable before executing another FETCH Statement on another cursor to make a correct judgment.
========================================================== ========================================================== ==========
Problem description: There are two tables,
Table
Table B
Now, we want to get the O_Saloary in Table A and the_Salary in Table B, which is the current total salary and updated to Table.
You can use a view to connect tables.
Now let's take the SQL statement into consideration. The cursor is a good method.
Principle: The cursor extracts the corresponding dataset according to the specified requirements, and then processes data one by one.
1.1 cursor Concept
Cursor allows you to access the result set returned by SQL Server row by row. A major reason for using cursor is to convert the set operation into a single record processing method. After retrieving data from a database using SQL, the results are stored in a memory area, and the results are often a collection containing multiple records. The cursor mechanism allows you to access these records row by row in SQL server and display and process these records as you wish.
1.2 advantages of cursor
The following advantages can be obtained from the cursor definition. These advantages make the cursor play an important role in practical application:
1) Allow the program to perform the same or different operations on each row in the row set returned by the select statement, rather than the same operation on the entire row set.
2) provides the ability to delete and update rows in a table based on the cursor position.
3) cursors serve as a bridge between a collection-oriented database management system (RDBMS) and a row-oriented program design. These two processing methods are communicated through cursors.
1.3 Use of cursors
After talking about the advantages of this multi-game, we will unveil the secret of the cursor.
The order in which the cursor is used: Name cursor, open cursor, read data, close cursor, delete cursor.
1.3.1 declare a cursor
Simplest CURSOR Declaration: DECLARE <CURSOR Name> cursor for <SELECT statement>;
The select statement can be a simple query, or a complex successive query and nested query. 1.3.2 open the cursor.
It's very simple. We just opened the declared cursor mycursor.
OPEN mycursor
1.3.3 read data
FETCH [NEXT | PRIOR | FIRST | LAST] FROM {cursor name | @ cursor variable name} [INTO @ variable name [,…] ]
Parameter description:
NEXT, remove the data of a row and use the NEXT row as the current row (incrementing ). Since the row pointer is directed to the cursor before the cursor is opened, the first execution of the fetch next operation will obtain 1st rows of data in the cursor set. NEXT is the default cursor extraction option.
INTO @ variable name [,…] Put the extracted column data in a local variable.
The variables in the list are associated with the corresponding columns in the cursor result set from left to right.
The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the data type of the result column. The number of variables must be the same as the number of columns in the cursor selection list.
1.3.4 close the cursor
CLOSE mycursor
1.3.5 delete a cursor
DEALLOCATE mycursor
A specific example is provided: Declare @ id nvarchar (20) -- Define a variable to save the id number
Declare @ A float -- define variables to save values
Declare mycursor cursor for select * from tb_c -- specify a cursor for the obtained Dataset
Open mycursor -- open the cursor
Fetch next from mycursor into @ id, @ A -- start to capture the first data
While (@ fetch_status = 0) -- if there is always data in the dataset
Begin
Select tb_ B .name,(tb_ B .gz + @ A) from tb_ B where tb_ B .id = @ id -- start to do what you want (what updates, delete)
Fetch next from mycursor into @ id, @ A -- jump to the next data
End
Close mycursor -- close the cursor
Deallocate mycursor -- delete a cursor
========================================================== ========================================================== ==========
// Determine the cursor status
// The 0 fetch statement is successful.
//-1 The fetch statement fails or this row is not in the result set
//-2 The extracted row does not exist ================================ ========================================================== ======================== Declare @ id nvarchar (20) -- Define a variable to save the id number
Declare @ A float -- define variables to save values
Declare mycursor cursor for select * from tb_c -- specify a cursor for the obtained Dataset
Open mycursor -- open the cursor
Fetch next from mycursor into @ id, @ A -- start to capture the first data
While (@ fetch_status = 0) -- if there is always data in the dataset
Begin
Select tb_ B .name,(tb_ B .gz + @ A) from tb_ B where tb_ B .id = @ id -- start to do what you want (what updates, delete)
Fetch next from mycursor into @ id, @ A -- jump to the next data
End
Close mycursor -- close the cursor
Deallocate mycursor -- delete a cursor
===== Note: in SQLServer 2005, you can also write ====================================================== ========================
Declare @ id nvarchar (20) -- Define a variable to save the id number
Declare @ A float -- define variables to save values
Declare mycursor cursor for select * from tb_c -- specify a cursor for the obtained Dataset
Open mycursor -- open the cursor
Fetch mycursor into @ id, @ A -- start to capture the first data
While (@ fetch_status = 0) -- if there is always data in the dataset
Begin
Select tb_ B .name,(tb_ B .gz + @ A) from tb_ B where tb_ B .id = @ id -- start to do what you want (what updates, delete)
Fetch mycursor into @ id, @ A -- jump to the next data
End
Close mycursor -- close the cursor
Deallocate mycursor -- delete a cursor
