Shulou(Shulou.com)06/01 Report--

The concept of cursor

A cursor is a data access mechanism, which is a database object that accesses and manipulates data in behavioral units in a given result set.

Benefits of cursors: data can be processed row by row allowing specific rows located in the result set to get a row from the current result set

The current row of the result set can be modified

Cursors in T-SQL are defined in MSDN as follows:

DECLARE cursor_name CURSOR [LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,... n] [ ]-- the default scope is GLOBAL-- default Forward_Only, which means that cursors can only be read from the beginning of the dataset to the end of the dataset. FETCH NEXT is the only option, while SCROLL supports cursors in any direction in the defined dataset. Or any position shift-STATIC KEYSET DYNAMIC and FAST_FORWARD choose one of these four keywords is the relationship between the table data reflected by the cursor data set and the data read by the cursor STATIC means that when the cursor is created, a copy of the data set contained in the SELECT statement after the FOR will be created and stored in the tempdb database, and any changes to the underlying table data will not affect the contents of the cursor. DYNAMIC is the opposite of STATIC, and when the underlying database changes, the contents of the cursor are reflected, and in the next fetch, the data content changes. KEYSET can be understood as a compromise between STATIC and DYNAMIC. Store the only primary key that can determine each row in the result set of the cursor into tempdb. When any row in the result set is changed or deleted, @ @ FETCH_STATUS can't detect the newly added data for-2. FAST_FORWARD can be understood as an optimized version of FORWARD_ONLY. Forward _ ONLY executes a static plan, while FAST_FORWARD chooses dynamic plan or static plan according to the situation. In most cases, FAST_FORWARD performs slightly better than FORWARD_ONLY.-- READ_ONLY SCROLL_LOCKS OPTIMISTIC choosing one or three READ_ONLY means that declared cursors can only read data, and cursors cannot do any update operations. SCROLL_LOCKS is the other extreme, locking all data read into cursors to prevent other programs from making changes to ensure the absolute success of updates. OPTIMISTIC is a relatively good choice. OPTIMISTIC does not lock any data. When the data needs to be updated in the cursor, if the underlying table data is updated, the update of the data in the cursor is not successful. If the underlying table data is not updated, the table data in the cursor can be updated.

Type of cursor

Static cursors: adopenstatic does not detect changes in data rows

Dynamic cursors: adopendynamic reflects changes in all data rows

Forward cursor only: adopenforwardonly does not support scrolling

Keyset cursors: adopenstatic can reflect modifications, but not quasi-group reflect insertions and deletions

Order in which cursors are used

Define cursor declare

Open the cursor open

Use cursor fetch

Close the cursor close

Release the cursor deallocate

Declare curTest cursor

Scroll for select Title from dbo.Course

Open curTest-Open the cursor

Fetch curTest

Declare @ Name varchar (50)

Fetch first from curTest into @ Name

Print 'course:' + @ Name

While @ @ FETCH_STATUS=0

Begin

Fetch next from curTest into @ Name

Print 'course:' + @ Name

End

Close curTest-closes the cursor

Deallocate curTest-release cursors

Some optimization suggestions for cursors

If you can avoid using cursors, try not to use cursors.

Be sure to turn it off and release it after use.

Try not to define cursors on large amounts of data.

Try not to update data on cursors.

Try not to use parameters such as insensitive, static and keyset to define cursors

If possible, try to use the FAST_FORWARD keyword to define cursors

If only the data is read, and only the FETCH NEXT option is used when reading, it is best to use the FORWARD_ONLY parameter

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.