SQL cursor usage methods SQL CURSOR usage

1. Why cursors are used: one of the main reasons for using cursors is to convert the set operation into a single record processing mode. When data is retrieved from a database in SQL language, the result is placed in an area of memory, and the result is often a collection of multiple records. The cursor mechanism allows users to access these records row-by-line within SQL Server, displaying and processing these records at the user's own will.

2. How to use cursors: Generally, the use of cursors follows the following general steps: (1) declares a cursor. 　　 Associate a cursor with the result set of a T-SQL statement. 　　 (2) Open the cursor. 　　 (3) Manipulate data using cursors. (4) Close the cursor.

2.1. DECLARE CURSOR DECLARE CURSOR statement SQL-92 standard syntax format: DECLARE cursor name [insensitive] [SCROLL] cursor for sql-statement Eg: Declare Mycrsrvar Cursor for Select * from Tbmydata

2.2 Opening the cursor open Mycrsrvar when the cursor is opened, the row pointer will point to the 1th row of the cursor set, and if you want to read the 1th row of data in the cursor set, you must move the row pointer so that it points to line 1th. For this example, you can read the 1th row of data using the following operation: Fetch first from e1cursor or fetch NEXT from e1cursor

2.3 Manipulating data using cursors The following example uses the @ @FETCH_STATUS to control cursor activity in a while loop/* The operation of reading data using a cursor is as follows. */DECLARE e1cursor cursor/* Declares a cursor, default is forward_only cursor */For SELECT * from C_example open e1cursor/* OPEN cursor */F ETCH Next from E1cursor/* reads the 1th row of data */while @ @FETCH_STATUS = 0/* Controls cursor activity with while loop * * BEGIN FETCH NEXT from E1cursor /* The rest of the row data will be read in the loop */END close e1cursor/* Close cursor */deallocate e1cursor/* Delete cursor */declare @id int, @name Varch   AR (20);   DECLARE cur cursor fast_forward for select Id,name from A;   Open cur;   FETCH NEXT from cur to @id, @name;   While @ @fetch_status =0 Begin--Do what you have to do fetch next from cur into @id, @name;   End Close cur; DEALLOCATE cur;

　　

2.4 Close cursor close cursor by using the close statement closed {{[GLOBAL] cursor name} | cursor variable name} Use the deallocate statement to delete the cursor with the syntax in the following format: deallocate {{[GLO BAL] Cursor name} | @ cursor variable name 3. The concise syntax for the fetch operation is as follows: Fetch [NEXT | PRIOR | First | 　　 　　Last] from {cursor name | @ cursor variable name} [into @ variable name [, ...]] Parameter description: Next takes down a row of data and puts the next line as the current row (increment). Because the row pointer is before the 1th row of the cursor after the cursor is opened, the first fetch next operation obtains the 1th row of data in the cursor set. 　　 Next is the default cursor extraction option. into @ variable name [,...] puts the column data of the extraction operation into the local variable. Each variable in the list is associated from left to right with the corresponding column in the cursor result set. The data type of each variable must match the data type of the corresponding result column or the implicit conversion supported by the result column data type. The number of variables must be the same as the number of columns in the cursor selection list.

--------------------------------------------------------------------------------------------------------------- -----------------

After each fetch operation, it is common to look at the status value in the global variable @ @FETCH_STATUS to determine if the fetch operation was successful. There are three status values for this variable: • 0 indicates that the FETCH statement was executed successfully. 1 indicates that the FETCH statement failed, such as moving the row pointer beyond the result set. 　　2 indicates that the fetched row does not exist. Because the @ @FETCH_STATU is a global variable, all cursors on a connection may affect the value of the variable.   Therefore, after executing a FETCH statement, you must test the value of the variable before executing another FETCH statement on the other cursor to make the correct judgment. Declare @ variable Name 1 variable type 1 Declare @ variable name 2 variable type 2 Declare cur cursor fast_forward for select field name 1, field name 2 from table name 1; Open cur; FETCH NEXT from cur to @ variable name 1,@ variable name 2; While @ @fetch_status =0 begin Update Table name 2 SET field name [email protected] Variable name 1 whare field name [email protected] Variable name 2 fetch NEXT from cur in To @ variable name 1,@ variable name 2; End Close cur; DEALLOCATE cur; Turn off the prompt to close the confirmation fetch

SQL cursor usage method SQL Cursor usage method (GO)