A MS-SQL cursor is a temporary database object that is used to rotate copies of data rows stored in a system Permanent Table, it can also point to the data row stored in the system Permanent Table. A cursor provides you with a method to operate data in a table on a row-by-row basis instead of a result set.
1.
How to use a cursor1) define the cursor statement declare <cursor Name> cursor for 2) create a cursor statement open <cursor Name> 3) extract the cursor column value, move the record pointer fetch <column Name List> from <cursor Name> [into <Variable list>] 4) use @ fetch_status to process rows in the cursor using the while loop 5) delete the cursor and release the statement close <cursor Name>/deallocate <cursor Name> 6) cursor application instance -- Define the cursor declare cur_depart cursor for select cdeptid, cdeptname from Department into @ deptid, @ deptname -- create the cursor open cur_depart -- move or extract the column Value Fetch from cur_depart into @ deptid, @ deptname -- use a loop to process the column value in the cursor while @ fetch_status = 0 begin print @ deptid, @ deptname fetch from cur_depart into @ deptid, @ deptnameend -- close/release the cursor close cur_includeallocate cur_depart simple process: Define the cursor declare mermercursor cursor forselect acct_no, name, balancefrom customerwhere province = "Beijing"; open the cursor customercursor; extract data -- set the loop lb_continue = truell_total = 0Do while lb_continuefetch customercursorinto: ls_acct_no,: ls_name,: ll_balance; If sqlca. sqlcode = 0 thenll_total + = ll_balanceelselb_continue = falseend ifloop close cursor close customercursor;
2.
Statement details and notes1)
Define a cursor statement
Declare <
Cursor Name> [insensitive] [scroll] cursor
For <select
Statement> [For {read only | update [of <
Column Name List>]}]U
InsensitiveDBMS creates temporary copies of query result set data (instead of directly referencing columns in real data rows in the database table ). The cursor is read only, that is, it cannot modify its content or the content of the underlying table; u
ScrollThe specified cursor can be selected as the current row by using any fetch option (first last prior next relative absolute. If this option is omitted, the cursor will only support moving down a single row (that is, only the fetch next of the cursor is supported); U
Select
StatementA standard SELECT statement that defines the cursor result set. Keywords compute, compute by, for browse, and into; u are not allowed in the <SELECT statement> declared by the cursor.
Read OnlyPrevent users who use a cursor from changing the cursor content by updating data or deleting rows; u
UpdateCreate a cursor column that can be updated and list the values that can be updated. If any column is included in the clause, only the column to be included can be updated. If only the specified Update (no column Name List) in the declare cursor statement is specified, the cursor will allow updating any or all of its columns. Declare cur_depart cursor for select * from department for update of cdeptid, cdeptname 2)
Statements for extracting cursor column values and moving record pointers
Fetch [next | prior | first | last | {absolute <
Row number >}| {relative <
Row number>}]
From <
Cursor Name> [into <
Variable list……>]Every time a fetch statement is executed, the DBMS moves to the next row in the cursor and obtains the column values in the cursor to the variables listed in. Therefore, the variables listed in the into clause of the fetch statement must correspond to the type and number of the list in the SELECT statement in the cursor definition. Only when the scroll parameter is used to define the cursor, the row location parameters (first, last, Prior, next, relative, and absolute) of the fetch statement can be used. If the fetch statement does not include the parameter next | prior | first | last, DBMS will execute the default fetch next; u
NextMove a row (record) down and backward; u
PriorMove up and forward a row (record); U
FirstMove to the first row (record) of the result set; u
LastMove to the last row (record) of the result set; u
Absolute nThe Nth row in the result set. If n is a positive value, the DBMS moves backward or downward from the header of the 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 u
Relative nMoves n rows from the current position of the pointer. If n is a positive value, 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 to N rows; fetch relative 2 from cur_depart into @ deptid, @ deptname 3)
Cursor-based location delete/update
StatementIf the cursor is updatable (that is, the read only parameter is not included in the definition of the cursor statement), you can use the cursor to delete/update rows from the source table of the cursor data, that is, the delete/update operation is based on the current position of the cursor. For example: -- delete the record declare cur_depart cursor for select cdeptid, cdeptname from Department into @ deptid, @ deptnameopen partition from cur_depart into @ deptid, @ brief from department where current of cur_depart -- Update the content of the current row declare cur_depart cursor for select cdeptid, cdeptname from Department into @ deptid, @ deptnameopen cur_departfetch from cur_depart into @ deptid, @ deptname update Department set cdeptid = '000000' + @ deptid where current of cur_depart
3.
Tips and notes for using cursors1) Use order by to change the order of the rows in the cursor. Note that only the columns in the select clause in the query can be used as the order by clause columns. This is different from the normal SELECT statement. 2) when the order by clause is used in a statement, the delete/update statement cannot be executed with a cursor. To solve this problem, first create an index on the original table, use this index when creating a cursor. For example, declare cur_depart cursorfor select cdeptid, cdeptname from department with index (idx_id) for update of cdeptid, cdeptname sorts tables by adding with index in the from clause; 3) the computed value can be included in the cursor as a column; 4) the number of rows in the cursor is determined by @ cursor_rows.
4.
Use System Process Management cursorAfter a cursor is created, you can use the system process to manage the cursor. The system process of the cursor mainly includes: sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns. 1)
Sp_cursor_listDisplays the cursor and its attributes in the current scope. The command format is:
sp_cursor_list
[ @cursor_return = ] cursor_variable_name OUTPUT，
[ @cursor_scope = ] cursor_scopeParameter :·[
@ Cursor_return =]
Cursor_variable_nameOutput: name of the declared cursor variable.
Cursor_variable_nameThe data type of is
Cursor, No default value. A cursor is a scroll, dynamic read-only cursor. ·[
@ Cursor_scope =]
Cursor_scope: Specify the cursor level to report.
Cursor_scopeThe data type of is
IntThere is no default value. It can be one of the following values.
| Value |
Description |
| 1 |
Report all local cursors. |
| 2 |
Report all global cursors. |
| 3 |
Report local and global cursors. |
Tip:Because sp_cursor_list is a system process containing the cursor type variable @ cursor_return and the output reserved word, the result set in the cursor variable @ cursor_return is different from the result set in the pub_cur cursor. 2)
Sp_describe_cursorReport the features of server cursors.
Sp_describe_cursor[
@ Cursor_return
=]
Output_cursor_variableOutput
{[
,[
@ Cursor_source =]
N 'local'
,[
@ Cursor_identity =]
N'
Local_cursor_name
']
| [
,[
@ Cursor_source =]
N 'global'
,[
@ Cursor_identity =]
N'
Global_cursor_name
']
| [
,[
@ Cursor_source =]
N 'variable'
,[
@ Cursor_identity =]
N'
Input_cursor_variable
']
}
Parameters:·[
@ Cursor_return
=]
Output_cursor_variableOutput: Declares the name of the cursor variable, which receives the cursor output.
Output_cursor_variableThe data type of is
Cursor, No default value. Call
Sp_describe_cursorCannot be associated with any cursor. The returned cursor is a rolling dynamic read-only cursor. ·[
@ Cursor_source =] {
N 'local'|
N 'global'|
N 'variable'}: Specify whether to use the name of the upstream game, the name of the global cursor, or the name of the cursor variable to specify the cursor that is currently being reported on it. The parameter is
Nvarchar (30). ·[
@ Cursor_identity =]
N'
Local_cursor_name
']: The name of the cursor created by the declare cursor statement with the local keyword or set as local by default.
Local_cursor_nameThe data type of is
Nvarchar (128). ·[
@ Cursor_identity =]
N'
Global_cursor_name
']: The name of the cursor created by the declare cursor statement with the Global keyword or global by default. It can also be opened by an ODBC application and then called
SqlsetcursornameThe name of the API server cursor that is named after the cursor.
Global_cursor_nameThe data type of is
Nvarchar (128).
·[
@ Cursor_identity =]
N'
Input_cursor_variable
']: Name of the cursor variable associated with the open cursor.
Input_cursor_variableThe data type of is
Nvarchar (128).
Tip:The command formats of sp_descride_cursor_tables and sp_describe_cursor_columms are the same as those of sp_describe_cursor.
5.
Cursor typeMs SQL Server supports three types of cursors: transact_ SQL, API server, and customer cursors. 1)
Transact_ SQL
CursorTransact_ SQL cursor is defined by the declare cursor syntax and is mainly used in transact_ SQL scripts, stored procedures, and triggers. Transact_ SQL cursors are mainly used on servers. transact_ SQL statements sent from clients to servers or transact_ SQL statements in batch processing, stored procedures, and triggers are managed. Transact_ SQL cursor does not support extracting data blocks or multiple rows. 2)
API
CursorAPI cursors support using cursors in ole db, ODBC, and db_library, and are mainly used on servers. Each client application calls the API cursor function, ms SQL Sever's OLE DB provider, ODBC drive, or db_library dynamic link library (DLL) all these client requests are sent to the server to process the API cursor. 3)
Customer cursorClient cursors are used only when result sets are cached on the client. In a client cursor, a default result set is used to cache the entire result set on the client. Client cursors only support static cursors rather than dynamic cursors. Because server cursors do not support all Transact-SQL statements or batch processing, client cursors are often used only as an aid for server cursors. Generally, server cursors support the vast majority of cursors. Because API cursor and transact-SQL cursor are used on the server side, they are also called server cursor and background cursor, while client cursor is called foreground cursor. In this chapter, we will focus on server (background) cursors. Select count (ID) from infoselect * from info -- clear all records truncate table infodeclare @ I intset @ I = 1 while @ I <1000000 begininsert into info values ('Justin '+ STR (@ I ), 'shenzhen '+ STR (@ I) set @ I = @ I + 1end
6.
Advantages of cursors and cursorsIn databases, cursor is a very important concept. A cursor provides a flexible means to operate the data retrieved from a table. In essence, A cursor is actually a mechanism that can extract a record from a result set that contains multiple data records. A cursor is always associated with a t_ SQL selection statement because it is a result set (it can be zero or one or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared. If you have written a program to process a file in C language, the cursor is like the file handle you get when you open the file. If the file is successfully opened, the file handle can represent the file. For a cursor, the principle is the same. The visible cursor can process the result set from the basic table in a similar way as the traditional program reads the flat file, so as to present the data in the table to the program in the form of a flat file. We know that the relational database management system is actually set-oriented. In ms SQL, there is no way to describe a single record in a table, unless you use the WHERE clause to limit that only one record is selected. Therefore, we must use a cursor to process a single record. Server, the cursor allows the application to perform the same or different operations on each row in the row result set returned by the SELECT statement, rather than performing the same operation on the entire result set at a time; it also provides the ability to delete or update table data based on the cursor position. In addition, it is the combination of the cursor as a collection-oriented database management system and row-oriented programming, enable communication between the two data processing methods.
