Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "how to define and use cursors in SQL Server". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to define and use cursors in SQL Server" can help you solve the problem.

Cursors are line-oriented and can make developers lazy and lazy to think about implementing certain functions in a collection-oriented query.

In terms of performance, cursors eat more memory, reduce available concurrency, consume bandwidth, lock resources, and, of course, more code. Use a metaphor to explain why cursors take up more resources. When you withdraw money from an ATM, is it more efficient to withdraw 1000 at a time, or 10 times?

Cursors are a very evil existence, and the use of cursors is often 2-3 times slower than using set-oriented methods, and this proportion increases when cursors are defined in large amounts of data. If possible, try to use while, subqueries, temporary tables, functions, table variables, etc., instead of cursors. Remember, cursors are always your last choice, not your first choice.

Since cursors have so many shortcomings, why learn cursors?

There are some cursors in the existing system, and our query must be implemented through cursors.

As an alternative, you can use cursors when you still cannot implement certain queries using while, subqueries, temporary tables, table variables, self-built functions, or other ways.

Definition syntax for cursors:

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] [;] I. define cursors

In T-SQL, defining a cursor can be very simple or relatively complex, depending on the parameters of the cursor. The parameter setting of the cursor depends on how well you know the principle of the cursor.

A cursor can actually be understood as a pointer defined on a particular dataset, and we can control this pointer to traverse the dataset, or just point to a specific row, so cursors are defined on datasets that start with SELECT.

Cursors are divided into cursor types and cursor variables.

Cursor variables can be assigned in two ways: when defining and defining first and then assigning values. Defining a cursor variable is the same as defining other local variables by adding "@" before the cursor.

Note that if you define a global cursor, you can only assign values directly at definition time, and you cannot precede the cursor name with "@".

The two definitions are as follows:

-- assign directly after definition declare test_Cursor cursor for select * from Person;-- first define and then assign declare @ TEST_Cursor2 cursor;set @ TEST_Cursor2 = cursor for select * from Person

Parameter explanation:

1. Choose between LOCAL and Global

If you do not specify a cursor scope, the default scope is GLOBAL.

-- Global cursors, cross-GLOBALdeclare test_Cursor cursor global for select * from Person;-- local cursors, cross-LOCALdeclare test_Cursor2 cursor local for select * from Person;go-- end the above scope open test_Cursor;open test_Cursor2 with GO This line of code reports that the cursor does not exist, so it is understandable that the local cursor does not span a batch. After the batch processing, it will be implicitly released and cannot call 2, FORWARD_ONLY or SCROLL in other batches to choose one of the two.

FORWARD_ONLY means that cursors can only be read from the beginning of the dataset to the end of the dataset, and FETCH NEXT is the only option. The default is Forward_Only.

SCROLL supports cursors to move in any direction or location in a defined dataset.

-- No parameters. Default is Forward_Onlydeclare test_Cursor cursor for select * from Person;-- + Forward_Onlydeclare test_Cursor2 cursor forward_only for select * from Person;-- + SCROLLdeclare test_Cursor3 cursor scroll for select * from Person;open test_Cursor;open test_Cursor2;open test_Cursor3;fetch last from test_Cursor;-error fetch: extraction type last cannot be used with forward-only cursors. Fetch last from test_Cursor2;-error fetch: the extraction type last cannot be used with forward-only cursors. Fetch last from test_Cursor3;-correct execution 3. Classification of cursors: STATIC, KEYSET, DYNAMIC and FAST_FORWARD

These four keywords are the relationship between the table data reflected in the data set of the cursor and the data read out by the cursor.

STATIC: when the cursor is created, a copy of the dataset contained in the SELECT statement after the FOR will be created and stored in the tempdb database, and any changes to the data in the underlying table will not affect the contents of the cursor.

DYNAMIC: the exact opposite of STATIC, when the underlying database changes, the contents of the cursor are reflected, and in the next fecth, the data contents change.

KEYSET: it can be understood as a compromise between STATIC and DYNAMIC. The only primary key that can determine each row in the result set of the cursor is stored in tempdb. When any row in the result set is changed or deleted, @ @ FETCH_STATUS will not be able to detect the newly added data.

FAST_FORWARD: can be understood as an optimized version of FORWARD_ONLY. FORWARD_ONLY executes static plans, while FAST_FORWARD chooses dynamic or static plans on a case-by-case basis. In most cases, FAST_FORWARD performs slightly better than FORWARD_ONLY.

4. Choose between READ_ONLY, SCROLL_LOCKS and OPTIMISTIC

READ_ONLY: this means that declared cursors can only read data, and cursors cannot do any update operations.

SCROLL_LOCKS: at the other extreme, all data read into the cursor is locked to prevent other programs from making changes to ensure the absolute success of the update.

OPTIMISTIC: a relatively good choice. OPTIMISTIC does not lock any data. When you need to update data 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.

5. For Update [of column_name,....]: defines the updatable columns in the cursor. Open the cursor

When the cursor is defined, the cursor needs to be opened and used, and it only takes one line of code to open the cursor:

OPEN test_Cursor

Note that when global and local cursor variables have duplicate names, local variable cursors are opened by default.

Use cursors 1. Use cursors to extract data

The use of cursors is divided into two parts, one is to manipulate the direction of the cursor in the dataset, and the other is to manipulate some or all of the rows pointed to by the cursor.

Six kinds of mobile navigation are supported, which are:

First line (FIRST)

Last line (LAST)

Next line (NEXT)

Previous line (PRIOR)

Jump directly to a line (ABSOLUTE (n))

Skip a few lines relative to the current (RELATIVE (n))

For example:

Declare test_Cursor cursor scroll for select name from Person;open test_Cursor;declare @ c nvarchar (10);-- take the next line fetch next from test_Cursor into @ c Tinci @ c Tinci-- take the last line fetch last from test_Cursor into @ c Ting print @ c Tory print-take the first line fetch first from test_Cursor into @ c Ting print @ c-take the previous line fetch prior from test_Cursor into @ c Ting print @ c Ting from test_Cursor into @ c, take the third line -- compared to the previous line fetch relative-1 from test_Cursor into @ c

For cursors that do not specify the SCROLL option (or forward-only cursors if not specified), only NEXT values are supported.

Cursors are often used with global variables @ @ FETCH_STATUS and WHILE loops for the purpose of traversing the dataset in which the cursor resides.

When a Fetch statement is executed, @ @ Fetch_Status may have three values:

0MagneFetch statement succeeded.

The-1:Fetch statement failed or the row is not in the result set.

-2: the extracted row does not exist.

Total cursor records @ @ CURSOR_ROWS

For example:

Declare test_Cursor cursor fast_forward for select id, name from Person;open test_Cursor;declare @ id int;declare @ name nvarchar (10); fetch next from test_Cursor into @ id, @ name;while @ @ FETCH_STATUS = 0 begin print @ id; print @ name; fetch next from test_Cursor into @ id, @ name; end;close test_Cursor;deallocate test_Cursor;2, delete data with cursor updates

Cursor modifies the current row data syntax:

Update base table name Set column name = value [,...] Where Current of cursor name

Cursor deletes the current number of rows according to the syntax:

Delete base table name Where Current of cursor name

For example:

-1. Declare the cursor declare orderNum_03_cursor cursor scroll for select OrderId, userId from bigorder where orderNum = 'ZEORD003402';--2. Open the cursor open orderNum_03_cursor;--3. Declare the variables declare @ OrderId int, @ userId varchar (15);-- 4. Locate the row fetch first from orderNum_03_cursor into @ OrderId, @ userId;-- the number of variables in into must be the same as the number of columns in the cursor query result set while @ @ fetch_status = 0-- extracted successfully, proceed to the next data extraction operation begin if @ OrderId = '123' where current of orderNum_03_cursor;-- modify the current row end If @ OrderId = 154074 begin delete bigorder where current of orderNum_03_cursor;-- delete the current line end; fetch next from orderNum_03_cursor into @ OrderId, @ userId;-- move the cursor end;close orderNum_03_cursor;deallocate orderNum_03_cursor; 4, close the cursor

After using the cursor, be sure to turn it off, just one line of code: CLOSE+ cursor name

Close test_ Cursorv. Release cursors

When the cursor no longer needs to be used, release the cursor with only one line of code: DEALLOCATE+ cursor name

Deallocate test_ Cursor6. Some optimization suggestions for cursors

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

Be sure to close and release 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

This is the end of the introduction to "how cursors in SQL Server are defined and used". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.