SQL Server cursor usage (zt)

In this step, you must specify the attributes of the cursor and the result set as required. You can specify a cursor in two ways. Form 1 (ANSI 92) declare cursor_name [insensitive] [scroll] cursor for select_statement [For {read only | update] [of column_list]}] Form 2 declare cursor_name cursor [local | Global] [forward_only | scroll] [Static | keyset | dynamic] [read_only | scroll_locks | optimistic] For select_statement [For {read only | update] [of column_list]}] insensitive keywords specify the result set to be retrieved create a temporary copy, later data will be obtained from this temporary copy. If the data in the original base table changes during subsequent cursor processing, they are invisible to the cursor. This insensitive cursor does not allow data changes. The scroll keyword indicates that the cursor can be rolled up in any way. All fetch options (first, last, next, relative, and absolute) can be used in the cursor. If this option is ignored, the cursor can only scroll forward (next ). Select_statement indicates the result set created by the SQL statement. The statement compute, compute by, for browse, and into cannot be used in the selection statement of the cursor declaration. Read only indicates that data modification is not allowed in the cursor result set. The update keyword indicates that the result set of the cursor can be modified. Of column_list indicates the columns that can be modified in the result set. By default (using the update keyword), all columns can be modified. The local keyword indicates that the cursor is local and can only be used in the declared process. The Global keyword makes the cursor globally visible for the entire connection. The global cursor is available at any time when the connection is activated. The cursor is no longer available only when the connection ends. Forward_only indicates that the cursor can only scroll forward. Static and insensitive cursors are the same. Keyset indicates the order of the selected rows. SQL Server creates a temporary keyword set from the result set. If the non-Keyword columns of the database are modified, they are visible to the cursor. Because it is a fixed set of keywords, it is invisible to modify keyword columns or insert new columns. Dynamic indicates that the cursor will reflect all modifications to the result set. Scroll_lock is used to lock the modification or deletion of a cursor to ensure its operation is successful. Optimistic specifies which modifications or deletions made through the cursor will not succeed. Note: If the SELECT statement uses distinct, union, and group by statements and the selection contains an aggregate expression, the cursor is automatically insensitive. · If the base table does not have a unique index, the cursor is created as an insensitive cursor. · If the SELECT statement contains order by, and the column identified by order by is not a unique row identifier, the dynamic cursor is converted to a keyset cursor. If the keyset cursor cannot be opened, it is converted to an insensitive cursor. The same is true for a cursor defined using the SQL ANSI-92 syntax, but there is no insensitive keyword. Ii. Open a cursor. Open a cursor to create a result set. The cursor is defined by the declare statement, but its actual execution is through the open statement. Syntax: open {[Global] cursor_name} | cursor_variable_name} Global indicates a global cursor. Cursor_name is the name of the opened cursor.

Cursor_variable_name is the variable name of the referenced cursor. The variable should be of the cursor type. After the cursor is opened, the system variable @ cursor_rows can be used to check the number of rows in the result set. @ Cursor_rows indicates that the cursor is being asynchronously migrated. The absolute value (if @ cursor_rows is-5, the absolute value is 5) indicates the number of rows in the current result set. Asynchronous cursors allow users to access the cursors when the cursor is fully migrated. III. The value of a forward cursor can be moved and processed back and forth on each row in the result set during the process of taking the value from the cursor. If the cursor is defined as scrollable (the scroll keyword is used during Declaration), any row in the result set can be retrieved at any time. For non-scroll cursors, only the next row of the current row can be retrieved. The result set can be retrieved from local variables. The syntax of the FETCH Command is as follows: fetch [next | prior | first | last | absolute {n | @ nvar} | relative {n | @ nvar}] from [Global] cursor_name} | cursor_variable_name} [into @ variable_name] [, ...... N] Next indicates the value of the next row from the current row. Prior indicates the value of the first row from the current row. First is the first line of the result set. Last is the last row of the result set. Absolute n indicates the nth row in the result set. The number of rows can also be transmitted through a local variable. The row number starts from 0, so when n is 0, no rows can be obtained. Relative n indicates that the row to be retrieved is at the position of the First n rows or the last n rows of the current row. If the value is positive, the row to be retrieved is at the position of the First n rows of the current row. If the value is negative, the last n rows of the current row are returned. Into @ cursor_variable_name indicates the list of variables where the cursor column value is stored. The number of variables in the list should be the same as the number of variables used by the SELECT statement in the declare statement. The data type of the variable should also be the same as the data type of the selected column. The values in the variables remain until the next fetch statement is used. Every execution of fetch is stored in the system variable @ fetch_status. If fetch is successful, @ fetch_status is set to 0. @ Fetch_status-1 indicates that a part of the result set has been reached (for example, the row in the base table is deleted after the cursor is opened ). @ Fetch_status can be used to construct a cursor processing cycle. Example: declare @ INAME char (20), @ fname char (20) Open author_cur fetch first from author_cur into @ INAME, @ fname while @ fetch_status = 0 begin if @ fname = 'Albert 'print "found Albert ringer" else print "Other ringer" fetch next from author_cur into @ INAME, @ fname end IV. the close statement is used to close the cursor and release the result set. After the cursor is closed, you cannot perform the fetch operation again. If you still need to use the fetch statement, you need to re-open the cursor. Syntax: Close [Global] cursor_name | cursor_variable_name v. Release the cursor. The deallocate statement releases the lock applied to the data structure and cursor. Syntax: deallocate [Global] cursor_name | cursor_variable_name provides a complete example for the travel subject: use master go create procedure sp_buildindexes as declare @ tablename sysname, @ MSG varchar (100 ), @ cmd varchar (100) declare table_cur cursor for select name from sysobjects where type = 'U' open table_cur fetch next from table_cur into @ tablename while @ fetch_status = 0 begin if @ fetch_status =-2 continue select @ MSG = "Bu Ilding indexes for table "+ @ tablename + "..." Print @ MSG select @ cmd = "DBCC dbreindex ('" + @ tablename + "')" Exec (@ cmd) print "fetch next from table_cur into @ tablename end deallocate table_cur go the script below will execute sp_buildindexes use pubs go exec ap_buildindexes for the pubs database. Note: the above is an example of creating a user-defined system stored procedure. Temporary tables are created in tempdb. The temporary table names start. The scope of a temporary table is to create a connection to a temporary table. Because temporary tables cannot be shared between two connections. Once the connection is closed, the temporary tables will be discarded. If a temporary table is created in a stored procedure, the scope of the temporary table is in the stored procedure, or any stored procedure called by the stored procedure. To share a temporary table between connections, use a global temporary table. The global temporary table starts with the "#" symbol and remains in the database until SQL server restarts. Once these temporary tables are created, all users can access them. Permissions cannot be explicitly specified in the temporary table. Temporary tables provide the ability to store intermediate results. Sometimes, temporary tables can improve performance by breaking a complex Query into two queries. This can be achieved by first storing the results of the first query in a temporary table, and then using a temporary table in the second query. We recommend that you use a temporary table when a subset of a large table is used for multiple times. In this case, a subset of data is kept in the temporary table to be used in subsequent connections, which greatly improves performance. You can also create an index in a temporary table.