CodePudding user response:

CodePudding user response:

CodePudding user response:

CodePudding user response:

 
To access and change relation data 


Transact - SQL cursor 
Transact - SQL cursor is mainly used in stored procedures, triggers and Transact - SQL script, they make the content of the result set for other Transact - SQL statement is also available, 

Used in the stored procedure or trigger Transact - SQL cursor typical process for: 

Statement to Transact - SQL variable contains the cursor returned data, result set columns for each declare a variable, declare variables is large enough to hold the return value of column, and statements can be obtained from the column data type conversion in a recessive manner of data types, 


Use the DECLARE CURSOR statement to Transact - SQL CURSOR is associated with a SELECT statement, DECLARE CURSOR statement define the characteristics of the CURSOR at the same time, such as the name of the CURSOR and whether the CURSOR is read-only or only into the characteristics, 


Use the OPEN statement execution SELECT statement and generate cursor, 


Using the FETCH INTO statements extract a single row, and transfer of data in each column to specify variables, then other Transact - SQL statement to refer to these variables to access has been extracted data values, Transact - SQL does not support line extraction block, 


The end of the cursor, use the CLOSE statement, CLOSE the cursor can release some resources, such as the cursor result set and the current row locking, but if you want to send a OPEN statement, then the cursor structure can still be used for processing, because the cursor still exists, at this point is not to use the name of the cursor, DEALLOCATE statement is released in full assigned to the resources of the cursor, including the name of the cursor, the cursor is released, must use the DECLARE statement to regenerate the cursor, 
Monitor Transact - SQL cursor activity 
Can use sp_cursor_list system stored procedure to get the current connection visible cursor list, use sp_describe_cursor, sp_describe_cursor_columns and sp_describe_cursor_tables to determine the characteristics of the cursor, 

After the cursor open @ @ CURSOR_ROWS function or by sp_cursor_list or sp_describe_cursor returned CURSOR_ROWS column will point out the rows in the cursor, 

After each FETCH statement execution, @ @ FETCH_STATUS updated to reflect the final state of extraction, also can return from the sp_describe_cursor FETCH_STATUS column to get the state information, @ @ FETCH_STATUS report cursor in the state, such as beyond the first line and last line extraction, @ @ FETCH_STATUS for connection is global, and connection cursor open by each extract reset, if you have to understand the status in the future, you need to perform another statement in connection, before the @ @ FETCH_STATUS stored in a user variables, even if the next statement is not FETCH, also may be INSERT, UPDATE or DELETE those statements, which can trigger contains reset @ @ FETCH_STATUS FETCH statement triggers, sp_describe_cursor returned FETCH_STATUS column to specify cursor is certain, not affected by the reference to other cursor FETCH statement, but sp_describe_cursor will FETCH statement by reference to the same cursor, so must pay attention to when using, 

After completion OF the FETCH cursor will locate in the line, the extracted line called the CURRENT line, if there is no statement as read-only cursor cursor, you can perform with the WHERE the CURRENT OF cursor_name clause OF the UPDATE or DELETE statement to modify the CURRENT line, 

By DECLARE CURSOR Transact - SQL statement given the name of the CURSOR can be global or local, global the name of the CURSOR is located on the same connection can be made of any the batch, stored procedure or trigger references, the local name of the CURSOR cannot be declared CURSOR batch, stored procedure or trigger is referenced, triggers and stored procedures in the local cursors and thus can avoid unintentional references from external stored procedures or triggers, 

Use cursor variable 
Microsoft? SQL Server? 2000 also support the cursor variable data type, the cursor can have two kinds of method that is associated with a cursor variable: 

/* Use DECLARE @ local_variable, DECLARE CURSOR and SET. */
DECLARE @ MyVariable CURSOR 

DECLARE MyCursor CURSOR FOR 
The SELECT LastName FROM Northwind. Dbo. Employees 

The SET @ MyVariable=MyCursor 

/* Use DECLARE @ local_variable and SET */
DECLARE @ MyVariable CURSOR 

The SET @ MyVariable=CURSOR SCROLL KEYSET FOR 
The SELECT LastName FROM Northwind. Dbo. Employees 

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull