Toward Dba[mssql] Detailed cursors

In the original: Toward the Dba[mssql Article] detailed cursor

Previous review: The previous bug introduced some of the less commonly used data filtering methods, this article detailed introduction downstream standard.

Concept

To put it simply, the function of a cursor is to store a result set, and to process the data of this result set on a per-syntax basis.

View

Because cursors can take the result set out of the process, it increases the burden on the server. Furthermore, the efficiency of using cursors is far more efficient than using the default result set, where the only packet sent from the client to the server is the packet containing the statement to be executed. When using a server cursor, each FETCH statement must be sent from the client to the server and then parsed and compiled into an execution plan on the server. Unless you want to perform very complex data operations on SQL Server.

Basic knowledge

I. SQL Server 2005 supports two methods for requesting cursors

1.transact-sql (support SQL-92);

2. Database application Programming Interface (API) cursor functions (ADO, OLE DB, ODBC) applications cannot mix these two methods of requesting cursors. ODBC also supports client-side cursors, which are cursors implemented on the client.

Two. Cursors can be divided into global cursors and local cursors depending on the scope. Global cursors can be used throughout the session, and local cursors are only executed in a single T-SQL batch, stored procedure, or trigger, and the local cursor is automatically deleted when the execution is complete.

Three. Basic operation of cursors, defining cursors, opening cursors, looping through cursors, closing cursors, and deleting cursors.

Grammar Basics (SQL-92)

Taking SQL-92 as an example

Initial data pps_app_infomation

DECLARE @Parm01 varchar DECLARE @Parm02 varchar DECLARE @Parm03 varchar (+) DECLARE Cur_pay insensitive CURSOR Forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation] OPEN Cur_payfetch Cur_pay into @Parm01, @Parm02, @Parm03WHILE @ @FETCH_STATUS = 0BEGINWAITFOR DELAY ' 00:00:01 ' PRINT @Parm 01+ ' __ ' [email protected]+ ' __ ' [email protected]fetch cur_pay into @Parm01, @Parm02, @Parm03ENDDEALLOCATE Cur_pay

Where Cur_pay is the cursor name insensitive is used to set whether the cursor uses a copy open cursor fetch loop read-in cursor deallocate delete cursor

Insensitive

For example, in the cursor reading process we will stay up late bug change to get up early bug see if add insensitive will be what difference

The previous insensitive keyword uses a copy of the data, and the latter one without insensitive is instant data

The sensitivity behavior of a cursor defines whether updates made to the horizontal snaplines (used to establish a cursor) are visible to the cursor. Sensitivity also defines whether updates can be made through cursors.

Scroll

Let's look at the scroll keyword again.

DECLARE @Parm01 varchar DECLARE @Parm02 varchar (DECLARE @Parm03 varchar) DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation] OPEN Cur_paybeginfetch last from Cur_pay into @Parm01, @Parm02, @Parm03 PRINT @Parm01 + ' __ ' [email protected]+ ' __ ' [email p Rotected]enddeallocate Cur_pay

The above program is OK for the successful output late night bug __maoya__06 1:32pm

If you remove the scroll keyword, you will be prompted

Msg 16911, Level 16, State 1, line 8th
Fetch: The last fetch type cannot be used with a forward-only cursor.

Scroll retrieves a specific row through a Transact-SQL Server cursor. If the scroll option is not specified in the DECLARE CURSOR statement of the SQL-92 style, next is the only supported fetch option. If the scroll option is specified in the SQL-92-style DECLARE CURSOR statement, all fetch options are supported.

FETCH syntax

In addition to the last parameter (which returns the final row in the cursor and takes it as the current row), the other

NEXT follows the current row and returns the result row, and the current row is incremented to the return row. If fetch next is the first fetch operation on the cursor, then the first row in the result set is returned with next as the default cursor extraction option.

PRIOR returns the result row immediately preceding the current row, and the current line is decremented to the return row. If fetch PRIOR is the first fetch operation on the cursor, no rows are returned and the cursor is placed before the first row.

First returns one row in the cursor and takes it as the current row.

ABSOLUTE {n | @nvar}
If n or @nvar is a positive number, the nth row starting from the cursor header is returned, and the return row becomes the new current row. If n or @nvar is negative, the nth row that starts at the end of the cursor is returned, and the return row becomes the new current row. If n or @nvar is 0, the row is not returned. n must be an integer constant, and the data type of the @nvar must be smallint, tinyint, or int.

RELATIVE {n | @nvar}
If n or @nvar is a positive number, it returns the nth row starting at the current line and changes the return row to the new current row. If n or @nvar is negative, the nth row before the current row is returned, and the return row becomes the new current row. If n or @nvar is 0, the current row is returned. When the first fetch is done on the cursor, the row is not returned if you specify FETCH RELATIVE if n or @nvar is set to a negative number or 0. n must be an integer constant, and the data type of the @nvar must be smallint, tinyint, or int.

Defining Global Cursors

FETCH NEXT from GLOBAL cur_pay into @Parm01, @Parm02, @Parm03

If GLOBAL is not specified, a local cursor is referred to.

READ only with UPDATE

(only the code that is not duplicated or necessary is summarized below)

DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation]    For READ only    OPEN cur_paybegin    

         UPDATE pps_app_infomation SET gamename = ' Stay up bug forupdate ' WHERE current of Cur_payprint @Parm01 + ' __ ' [email protected]+ ' __ ' [Email protected] END

Msg 16929, Level 16, State 1, line 9th
The cursor is read-only.
statement has been terminated. stay up late bug __maoya__06 1:32pm

DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation]    For UPDATE

MSG 1048, Level 15, State 1, line 7th
Cursor options for UPDATE and insensitive conflicts.

DECLARE cur_pay  SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion].[ DBO]. [Pps_app_infomation]    For update    OPEN cur_paybegin    FETCH last from Cur_pay into @Parm01, @Parm02, @Parm03     Update pps_app_infomatio n SET gamename = ' Stay up bug forupdate ' WHERE current of the Cur_payfetch last from Cur_pay into @Parm01, @Parm02, @Parm03 PRINT @Pa rm01+ ' __ ' [email protected]+ ' __ ' [email protected]end

(1 rows affected)
Stay up late bug forupdate__maoya__06 1:32pm

READ only does not allow location updates through cursors and does not hold locks on rows that make up the result set. Update is relative to read only, and update can be defined to an updatable column.

Syntax basics (SQL Server extended format)

Basic Data Ibid.

DECLARE @Parm01 varchar DECLARE @Parm02 varchar DECLARE @Parm03 varchar (+) DECLARE cur_pay  CURSOR GLOBAL- -the same as the SQL-92 format optional local native cursor scroll--optional forward_only Specifies that the cursor can only scroll from the first row to the last row of dynamic--and the previous row parameter association static represents a temporary copy Dynamic directly reflects modifications made to the result set when scrolling the cursor         --keyset means that except for the unique key to get the latest value Fast_forward performance-optimized For_ward readonly cursor optimistic-optional read_ Only ibid scroll_locks positioning updates and locking the current data optimistic want to be updated with optimistic locks but if the read data is updated it will cause the operation to fail type_warning-if the cursor is invisible from the requested type to another type, Sends a warning to the client forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation]    For Updateopen Cur_paybegin    FETCH last from Cur_pay into @Parm01, @Parm02, @Parm03 PRINT @Parm01 + ' __ ' [email protecte d]+ ' __ ' [email protected]enddeallocate cur_pay

The syntax is directly annotated in code, and the SQL-92-like section is not mentioned.

Cursor Application

Defining a Cursor variable cursor_variable_name

DECLARE @tcur cursorset @tcur = cursor for SELECT * from Pps_app_infomation

Opens cursor open {{[global]cursor_name}|cursor_variable_name}

Close Cursor Close{{[global]cursor_name}|cursor_variable_name}

Release Cursor Deallocate{{[global]cursor_name}|cursor_variable_name}

Gets the number of cursor rows @ @CURSOR_ROWS

DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation] OPEN Cur_paybeginprint @ @CURSOR_ROWSEND

Output 4

Detects the status of the fetch operation @ @FETCH_STATUS The return value 0 indicates that the FETCH statement executed successfully-1 indicates that the FETCH statement failed or the row no longer results set-2 means that the data information to be read does not exist

DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from [picpromotion]. [dbo]. [Pps_app_infomation] FETCH NEXT from Cur_pay OPEN cur_paybeginif (@ @FETCH_STATUS = 0) print (' FETCH statement succeeds ')    if (@ @FETCH_STATUS =-1) print (' FET CH statement failed or line not in result set ') if (@ @FETCH_STATUS =-2) print (' fetched row does not exist ') END

Msg 16917, Level 16, State 2, line 6th
The cursor is not open.
FETCH statement failed or row is not in result set

Cursor nesting

Use another cursor in the cursor. In general, the use of cursors has occupied the system resources, and then nested cursors will greatly affect the efficiency, this article is for reference only.

Add a datasheet pps_appconfig_infomation

DECLARE @Parm01 intdeclare @Parm02 varchar (DECLARE @Parm03 varchar) DECLARE @Parm04 varchar (+) DECLARE Cur_pay Insensitive SCROLL CURSOR forselect appid,gamename,createuser,createdate from Pps_app_infomationopen Cur_PayBEGIN   FETCH Next from Cur_payinto @Parm01, @Parm02, @Parm03, @Parm04   while (@ @FETCH_STATUS =0)    BEGIN PRINT (' Current game number ' +cast (@Parm01 as varchar (4)) + ' game name ' [email protected]        DECLARE Sub_cur CURSOR forselect wm_type,wm_text from pps_appconfig_infomation WHERE AppId = @Parm01DECLARE @Parm05 varchar (+) D Eclare @Parm06 varchar OPEN sub_curfetch next from Sub_curinto @Parm05, @Parm06 while (@ @FETCH_STATUS =0) BEGIN PRINT ( ' Current game type ' [email protected]+ ' Default soft text for ' [email protected] ' FETCH next from Sub_curinto @Parm05, @Parm06 endclose sub_ Curdeallocate Sub_curfetch Next from Cur_payinto @Parm01, @Parm02, @Parm03, @Parm04   endenddeallocate Cur_pay

The output result is

system stored procedures associated with cursors

Sp_cursor_list reports the properties of a server cursor that is currently open for a connection.

DECLARE cur_pay insensitive SCROLL CURSOR forselect appid,gamename,createuser,createdate from Pps_app_infomationopen Cur_pay   DECLARE @REPORT cursorbegin   fetch NEXT from Cur_pay while   (@ @FETCH_STATUS =0)    BEGIN        Fetch Next from Cur_pay   END   exec sp_cursor_list @cursor_return = @REPORT output, @cursor_scope = 3--Set 1 o'clock report all local cursor Settings 2 o'clock Report All global cursor Settings 3 o'clock report all local cursors and global cursors Endclose cur_paydeallocate Cur_pay

Sp_describe_cursor View the global properties of cursors are similar to sp_cursor_list.

DECLARE cur_pay insensitive SCROLL CURSOR forselect gamename,createuser,createdate from Pps_app_infomationopen cur_Pay< C0/>declare @REPORT cursorbegin   fetch NEXT from Cur_pay and   (@ @FETCH_STATUS =0)    BEGIN        FETCH next from Cur_pay   END   exec sp_describe_cursor @cursor_return = @REPORT output, @cursor_source =n ' globle ', @cur_identity = N ' cur_pay '         [email protected]_source optional n ' local ', n ' globle ', n ' variable ' select local, global, or variable [email protected]_identity View the cursor name Endclose cur_paydeallocate cur_pay

Operations in a relational database work on the entire rowset. The rowset returned by the SELECT statement includes all rows that satisfy the criteria in the WHERE clause of the statement. The complete rowset returned by the statement is called the result set. Applications, especially interactive online applications, do not always effectively handle the entire result set as a unit. These applications require a mechanism to process one or a subset of rows at a time. Cursors are an extension of the result set that provides this mechanism.

Toward Dba[mssql] Detailed cursors