A cursor is an object that allows you to individually process rows from the result set returned by a SELECT statement. Next, we will look at cursors supported in the Transact-SQL language. These are server-side cursors that exist as objects on the database server side. There are also client cursors that are used to create client database applications.

The literature notes that row-by-row processing of a data set using a cursor in the vast majority of cases is significantly slower than similar actions performed by SQL tools for processing multiple rows. Therefore, it is recommended to use cursors only in cases where describing the required actions through operations with sets of rows is clearly ineffective or even impossible.

Working with a cursor usually involves next steps:

• cursor declaration;
• opening cursor;
• reading in value variables attributes from the first cursor record;
• moving around the cursor (usually in a loop) and processing cursor entries;
• closing cursor;
• freeing memory allocated to the cursor.

A cursor is declared using the DECLARE statement, the format of which is shown below. It should be noted that in SQL Server this operator supports both syntax ISO standard SQL (the version of the standard is not specified in the documentation), and syntax using a set of Transact-SQL CURSOR language extensions

FOR select_statement

Extended Transact-SQL syntax:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

Specifying the GLOBAL keyword means that the declared cursor is available in any job batch, trigger, or stored procedure that runs within current connection with the server. The cursor is implicitly released only if the connection is broken.

A "local" cursor, whether created by default or by explicitly specifying LOCAL, is only available in the job batch, stored procedure, or trigger in which it was created. This cursor is implicitly released when the batch, stored procedure, or trigger completes execution. The exception is when the cursor is passed through an OUTPUT parameter of a stored procedure. Then the cursor is freed when all variables referencing it are freed or when it goes out of scope.

FORWARD_ONLY means that you can only “move” along the cursor forward (only the FETCH NEXT command is available, see below), i.e. Each entry in the cursor can be processed at most once. If FORWARD ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, then the cursor operates as a DYNAMIC cursor (see below). If neither FORWARD_ONLY or SCROLL is specified, and if none of the STATIC, KEYSET, or DYNAMIC keywords is specified, then the default is FORWARD_ONLY.

SCROLL means that you can “move” around the cursor in any direction (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE are available in the FETCH statement). The SCROLL option cannot be specified with the FAST_FORWARD option. STATIC, KEYSET, and DYNAMIC cursors have a default value of SCROLL.

STATIC means the cursor is not updatable. The resulting data set of such a cursor is retrieved from the database and stored in the database for temporary objects tempdb. Changes to the tables that serve as the basis for the cursor will not be reflected in the cursor after this.

KEYSET - y of this type cursor, a set of key values ​​identifying the selected records is stored in a temporary table. As you move through the cursor, the values ​​of non-key attributes are retrieved from the corresponding tables, so changes to non-key columns will be visible when you move the cursor. If the row that is in the cursor has already been removed from the table by the time it is fetched by the FETCH operator, the service variable @@ FETCH_STATUS will return the value -2. Rows added to tables after the cursor is opened are not visible in the cursor. If the query that generates the cursor involves at least one table that does not have a unique index, the cursor of type KEYSET is converted to type STATIC.

DYNAMIC is the most resource-intensive cursor type that displays all data changes made in the rows of the result set, including newly inserted rows. The data values, order, and row membership in each selection may vary. FETCH ABSOLUTE cannot be used with dynamic cursors.

FAST_FORWARD is the fastest cursor type, allowing you to move from one line to another only “forward”. This is the default cursor type (when optional keywords are omitted). It is equivalent to a cursor declared with the FORWARD_ONLY and READ_ONLY parameters.

READ_ONLY – defines a “read-only” cursor: changes to the database cannot be made through such a cursor.

SCROLL_LOCKS means that SQL Server locks rows as they are read into the cursor, ensuring that they can be updated or deleted through that type of cursor.

A cursor declared with the OPTIMISTIC keyword does not require a row lock and allows data to be modified. If changes to the base table occurred after data was read into the cursor, attempting to modify that data through the cursor results in an error.

TYPE_WARNING specifies that when a cursor is implicitly converted from the requested type to another (for example, the KEYSET to STATIC cursor conversion described above when there is no unique index on the table), a warning will be sent to the client.

Select_statement – ​​SELECT statement that forms the result set of the cursor.

The FOR UPDATE statement specifies the columns in the cursor to be updated. If OF column_name [, . . . n], then only the listed columns will be available for changes. If there is no list of columns, updating is possible for all columns, except in the case of a cursor declaration with the READ_ONLY parameter.

To open and fill the cursor, use the command

OPEN (( cursor_name) I @cursor_variable)

When opening, the cursor can be specified by name (cursor_name) or through a variable of type CURSOR (@cursor_variable). The GLOBAL parameter specifies that cursor_name is a global cursor.

To move through a cursor's data set and retrieve the data as variable values, use the FETCH statement:

FETCH [

(( cursor_name] I @cursor_variable]

The commands that determine the direction of movement along the cursor are described in table. 10.10. As noted earlier, depending on the type of cursor, some commands for a particular cursor may not be applicable.

It is important to note that if the cursor has just been opened, the first execution of FETCH NEXT results in a jump to the first record of the cursor.

Table 10.10

Navigating a cursor dataset

The @@FETCH_STATUS global variable allows you to find out the result of the last execution of the FETCH statement:

O – action completed successfully;

• -1 – the execution of the operator failed, or the line was outside the result set (the cursor ended);
• -2 – the selected row is missing, for example, if while working with a “change-sensitive” type cursor, the current record was deleted from the database.

The CLOSE statement closes an open cursor, freeing the memory used to store the data set. Retrieving data and moving around a closed cursor is impossible; to do this, it must be reopened.

CLOSE (( cursor_name)|@cursor_variable )

The DEALLOCATE statement removes the association between a cursor and its name or variable. If this is the last name or variable referencing the cursor, the cursor itself is deleted and any resources it uses are freed:

DEALLOCATE (( cursor_name] | @cursor_variable) Let's consider a simple example of using a cursor. Here, authors and titles of books published no earlier than 2000 are selected from the table, after which the data is displayed in a loop SELECT statements– each time one entry with its own title. Additional explanations are provided by comments in the code:

/*declare variables*/

DECLARE @auth varchar(50), @title varchar(50)

WHERE >= 2000

/*open the cursor and “run” through it, displaying the author and title using a separate SELECT statement*/

FETCH NEXT FROM cursorl INTO @auth, @title

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM cursorl INTO @auth, Stitle

/*close the cursor and release it*/

DEALLOCATE cursorl

As noted above, a variable of type CURSOR can be used instead of the cursor name. Below is similar code using these variables:

DECLARE South varchar(50), Stitle varchar(50)

/*declare a variable of type cursor*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECT Author, Title FROM dbo.Bookl

WHERE >= 2000

/*assign a value to a variable of type cursor*/

SET Scurl = cursorl

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM Scurl INTO South, Stitle

Hello fellow blog reader on Community.

I want to tell you about my recent experience cursor optimization in SQL Server.
The first thing you need to know is cursor is not good, but bad. Where it is possible to replace the cursor with INSERT SELECT or use a temporary table, this should be done (with rare exceptions). A cursor almost always means additional server resources and a sharp drop in performance compared to other solutions.
Secondly, sometimes you can’t do without a cursor – where you can’t do without a line-by-line pass through the selection result. In such cases, it is very important to create correctly desired type cursor – the one that corresponds to the problem being solved. General syntax The cursor declaration looks like this:

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 will focus on the first three lines of key parameters.
LOCAL or GLOBAL: if we want the cursor to be accessible to other procedures, functions, packages within our session, then GLOBAL - in this case we take care of deleting the cursor ourselves (DEALLOCATE command). In all other cases (i.e. in the overwhelming majority) - LOCAL. Attention, by default the GLOBAL cursor is created!
FORWARD_ONLY or SCROLL: if we want to move around the cursor like crazy, back and forth, then SCROLL, otherwise - FORWARD_ONLY. Attention, by default a SCROLL cursor is created!
STATIC or KEYSET, DYNAMIC, FAST_FORWARD: if we want to display actual information from the table (i.e., if after opening the cursor, we changed the information in one of the fields of the table and we want that when passing through the cursor, the required line of the cursor will already have updated information), then we use either KEYSET (if EVERY table participating in sample, has a unique index) or DYNAMIC (the slowest type). If we need a snapshot of the sample result after opening the cursor - STATIC(the fastest type - a copy of the sample result is copied to the tempdb database and we work with it). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – guys from the Internet write that STATIC takes longer to open (since a copy is created in tempdb), but it works faster, and FAST_FORWARD is the opposite. So if the number of records is large (as much as practice shows), then we use STATIC, otherwise we use FAST_FORWARD. Attention, by default a DYNAMIC cursor is created.

Thus, for a large number of records, in most cases my choice is:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen t

for a small number of records:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Now let's move on to practice (which actually prompted me to write this).
From time immemorial, when declaring a cursor, I used the DECLARE ... CURSOR LOCAL FOR... construction.
When developing integration with one very bad database, in which there is not a single index and not a single key, I used the same approach when declaring cursors as always. The sample of one cursor contained 225,000 records. As a result, the process of importing data from such a database took 15 hours 14 minutes!!! And although the import was primary (i.e., one-time), even normal testing of such an import would have required several days! After replacing the above construction when declaring a cursor with DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. the entire import process took... attention... 10 minutes 5 seconds!!! So the game is definitely worth the candle.
I would like to repeat that the ideal option is not to use cursors at all - for the MS SQL DBMS, the relational rather than the navigation approach is much more native.

1) The concept of a cursor
Interactive SQL does not differentiate between single-row and multi-row queries. Embedded SQL executes these queries differently. Single-line queries return one line and we have already covered them. When the result of a query is more than one row, embedded SQL must provide application program the ability to obtain query results line by line. Cursors are used for this. A cursor is a variable associated with a query. Its value is each row that matches the query. Like variables, cursors must be declared before they can be used. Unlike views, cursors are designed for line-by-line processing.

2) Cursor declaration

DECLARE [{}] [[NO] SCROLL] CURSOR [{WITH|WITHOUT} HOLD] FOR [FOR {READ ONLY|UPDATE [OF ]}]

3) Keywords
. SENSITIVE|INSENSITIVE|ASENSITIVE– changes in the result set are visible | prohibited (fixed using a copy of the data set)|The DBMS itself decides whether to make a copy (operates by default).
. WITH|WITHOUT HOLD– leaves open | closes the cursor if a COMMIT statement is encountered.
. SCROLL– [prevents] extracting result rows in random order.
. FOR READ ONLY– defines a read-only cursor.
. FOR UPDATE OF– blocks only the specified columns from updating.

4) Declaring a cursor in SQL Server

DECLARE CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] FOR [FOR UPDATE [OF ]]

. STATIC– Defines a cursor that creates a temporary copy of data for use by the cursor. All queries against a cursor access the specified temporary table in the tempdb database, so changes to the base tables do not affect the data returned by samples for that cursor, and the cursor itself does not allow changes to be made.
. KEYSET– Indicates that the membership or order of rows in the cursor does not change after it is opened. A set of keys that uniquely identify rows is built into a table in the tempdb database called keyset.
. DYNAMIC– Defines a cursor that displays all data changes made to the rows of the result set when viewing this cursor. The data values, order, and row membership in each selection may vary. The ABSOLUTE selection option is not supported by dynamic cursors.
. FAST_FORWARD– Indicates a FORWARD_ONLY, READ_ONLY cursor that has performance optimization enabled. The FAST_FORWARD option cannot be specified with the SCROLL or FOR_UPDATE options.
. SCROLL_LOCKS– Indicates that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks rows as they are read into the cursor to ensure they are available for subsequent changes. The SCROLL_LOCKS option cannot be specified with the FAST_FORWARD or STATIC option.
. OPTIMISTIC– Indicates that positioned updates or deletes made through the cursor will fail if the row has been updated since the time it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. Instead, to determine whether the row has changed since it was read into the cursor, a comparison is made of the values ​​of the timestamp column (or checksums, if the table does not have a timestamp column). If a row has been modified, its positioned modification or deletion is not possible. The OPTIMISTIC option cannot be specified with the FAST_FORWARD option.

5) Opening the cursor

6) Retrieving rows from the cursor

FETCH [{NEXT|PRIOR|FIRST|LAST|{ABSOLUTE|RELATIVE }}]
FROM INTO

7) Cursor positioning options
. NEXT|PRIOR|FIRST|LAST– to the next|previous|first|last line of the result set.
. RELATIVE ±N– to a line with a positive or negative offset relative to the current line.
. ABSOLUTE ±N– to a line with an explicitly specified absolute position number from the beginning or end of the cursor.

Note: SQL Server allows the integer variable @N instead of N.

8) Closing the cursor

9) Notes on cursors
. If the cursor contains more than one line, it is necessary to organize a loop for retrieving data from it, periodically checking to reach the last line.
. Unlike tables and views, cursor rows are ordered either explicitly using a section ORDER BY, or in accordance with the conventions adopted in a particular DBMS.
. Cursors are also used to select groups of rows from tables, which can be updated or deleted one at a time.
. For a cursor to be updatable, it must meet the same criteria as the view, that is, not contain sections UNION, ORDER BY, GROUP BY, DISTINCT.

10) Example for deleting data from a cursor

exec sql declare cursor Cur1 for select * from Customer
where Rating
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; ) – Take data to delete from the cursor
not_done:
exec sql close cursor Cur1; — Close the cursor
exit();

11) Example for increasing commissions

exec sql declare cursor CurCust for select * from SalesPeople
where SNum in (select SNum from Customer where Rating=300); — Define the cursor
exec sql open cursor CurCust; - Execute the cursor
while (sqlca.sqlcode==0) ( — Create a loop to update data in the table
exec sql fetch CurCust into:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople set Comm=Comm+.01 where current
of CurCust; ) – Take the data for updating from the cursor
exec sql close cursor CurCust; — Close the cursor

SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2

DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
FETCH NEXT FROM Cur1
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1
DEALLOCATE Cur1

The implementation of a cursor in a database resembles Java class, having a set of data and methods for processing them. Wherein sql cursor uses data as regular array. Cursors can be used in triggers, stored procedures and functions.

In accordance with the SQL standard, when working with cursors, the following basic actions are performed:

• cursor declaration;
• opening a cursor with reading data;
• line-by-line sampling of data from the cursor;
• changing row data using the cursor;
• closing the cursor, after which it becomes inaccessible;
• releasing the cursor, i.e. removing a cursor from memory because closing it does not necessarily free the memory associated with it.

In different implementations the definition cursor may have some differences. For example, sometimes it is necessary to explicitly free the memory allocated for a cursor. Once the cursor is freed, the memory associated with it is also freed. This makes it possible reuse cursor name. In other implementations, when the cursor is closed, memory is freed implicitly.

IN in some cases You can't do without using a cursor. However, if possible, you should avoid using a cursor and work with standard data processing commands: SELECT, UPDATE, INSERT, DELETE. This is due to the fact that cursors do not allow modification operations on the entire volume of data and the speed of performing data processing operations using a cursor is noticeably lower than that of standard means SQL.

If a program can change the data loaded into the cursor, then it is called modifiable. When talking about cursors, we should not forget about transaction isolation. One user modifies a record using a cursor, while another user reads that record using their own cursor. Moreover, he can change the same record, which makes it necessary to maintain data integrity.

Declaring a cursor

Cursors must be declared before they can be used. The SQL standard uses the following syntax to create a cursor:

Declare cursor_name cursor for select_statement ])]

IN this expression the cursor is declared declare cursor with the name "cursor_name".

INSENSITIVE a static cursor is created that does not allow changes to be made. Additionally, changes made by other users are not displayed. If the INSENSITIVE keyword is missing, a dynamic cursor is created.

When using a keyword SCROLL the created cursor can be scrolled in any direction, allowing you to apply any selection commands. If this argument is omitted, then cursor will be sequential, i.e. its viewing will be possible only in one direction - from beginning to end.

Expression select_statement indicates a structure for reading information like select ... from ... . It must not contain the operator into, since cursor has its own operator fetch to fill variables with cursor data.

When specifying an argument FOR READ_ONLY a read-only cursor will be created and no modifications to the data will be allowed. A dynamic cursor can be declared as a read-only cursor, allowing changes made by another user to be displayed.

Creating a cursor with an argument FOR UPDATE allows you to make changes to data in the cursor either in specified columns or, in the absence of an argument OF column_name, in all columns.

You can declare multiple cursors in a subroutine. But each cursor must have a unique name. To open a cursor you must use the operator open which opens the previously declared cursor:

Cursor open

SQL defines the following syntax for opening a cursor "cursor open":

Open cursor_name;

Fetching data from a cursor, cursor fetch

The syntax for reading data from a cursor into some variables is as follows:

Fetch cursor_name into var_name [, var_name] ...;

Operator fetch selects open cursor data into variables located after into and moves the cursor to the next position.

Cursor close

Operator close closes cursor. If the operator is not explicitly specified, the cursor is closed automatically when the corresponding program block is closed.

Close cursor_name;

After closing, the cursor becomes inaccessible. When closing, all locks installed while the cursor was running are released. Only open cursors can be closed. A closed but not released cursor can be reopened. It is not allowed to close an unopened cursor.

Each DBMS has its own peculiarities of using a cursor.

Features of using cursors in Oracle

There are four cursor attributes in PL/SQL %FOUND, %NOTFOUND, %ISOPEN And %ROWCOUNT. Cursor attributes are declared like the %TYPE and %ROWTYPE operators, to the right of the cursor name.

%FOUND attribute

%NOTFOUND attribute

The %NOTFOUND attribute is the exact opposite of %FOUND.

%ISOPEN attribute

The %ISOPEN attribute only indicates whether the cursor is open or not.

%ROWCOUNT attribute

Attribute %ROWCOUNT is a numeric attribute that returns the number of rows read by the cursor at a particular point in time.

Example of a SQL cursor in an Oracle DBMS

Declare v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs cursor for select id, name, sum(comm) as comm from managers where data between "2014-11-01" and "2014-11-30" group by id, name; begin open crs; loop EXIT WHEN crs%NOTFOUND; FETCH crs into v_id, v_name, v_comm; insert into bonus(id, name, comm) values ​​(crs.id, crs.name, crs.comm); end loop; commit; close crs; end;

Features of using cursors in SQL server

Cursors used in MSSQL can be sequential or scrollable. Sequential allows you to select data in only one direction - from beginning to end. Scrollable cursors allow movement in both directions and allow you to jump to an arbitrary row in the cursor's result set.

SQL Server supports static, dynamic, sequential, and keyset-driven cursors.

In a static cursor design, information is stored as a snapshot at some point in time. Therefore, changes made to the database by another user are not visible. While the cursor is being opened, the server sets a lock on all rows included in its full result set. A static cursor does not change after creation and always displays the data set that existed at the time it was opened. If other users change the data included in the cursor in the source table, this will not affect the static cursor. It is impossible to make changes to a static cursor, so it always opens in read-only mode.

A dynamic cursor requires additional network overhead and software resources. When using dynamic cursors, a complete copy of the data is not created, but selections from the source tables are performed only when the user accesses certain data. During the fetch, the server locks the rows, and any changes the user makes to the cursor's full result set will be visible in the cursor. However, once the cursor has fetched data, changes made by another user will no longer be reflected in the cursor.

A cursor controlled by a set of keys has properties between static and dynamic. Records are identified at the time of sampling and thus changes are tracked. This type of cursor is useful when implementing backward scrolling. In this case, data additions and deletions are not visible until the information is updated and the cursor selects new version records if changes have been made to them.

Static cursors are best used for information processing systems, i.e. for reporting systems or for statistical and analytical purposes. A static cursor is better at fetching large amounts of data. In systems for electronic purchases or reservations of objects (seats, tickets), it is necessary to dynamically perceive updated information as changes are made. In such cases, a dynamic cursor is used. In these applications, the amount of data transferred is typically small and accessed at the individual record level.

Sequential cursors do not allow data to be fetched in the reverse direction, only from the beginning to the end of the cursor. A sequential cursor does not store a set of all data rows. They are read from the database as soon as a selection is made in the cursor, which allows dynamically reflecting all changes made by users to the database using the INSERT, UPDATE, DELETE commands. The cursor reads the most recent data state.

Cursor Declaration

Declare cursor_name cursor for SELECT_statement ]]

When using a keyword LOCAL A local cursor will be created that is visible only within the block, trigger, stored procedure, or user-defined function. Keyword GLOBAL, defines a global cursor that exists until the current connection is closed.

Operator FORWARD_ONLY defines a sequential cursor that allows data to be retrieved only in the direction from the first row to the last. When using the operator SCROLL a scrollable cursor is created that allows data to be accessed in any order and in any direction.

The cursor type is determined by the operators:

• STATIC - creating a static cursor;
• DYNAMIC - creating a dynamic cursor;
• KEYSET - creating a key cursor.

If for cursor READ_ONLY specify argument FAST_FORWARD, then the created cursor will be optimized for quick access to the data. This argument cannot be used in conjunction with arguments FORWARD_ONLY And OPTIMISTIC.

If the cursor is created with the operator OPTIMISTIC, then it is prohibited to change or delete rows that were changed after the cursor was opened.

When specifying an argument TYPE_WARNING the server will report an implicit cursor type change if it is incompatible with the SELECT query.

Retrieving data from a cursor, fetch

Immediately after opening the cursor, you can get its contents using the following command:

When using the operator FIRST the first row of the cursor's result set will be returned, which becomes the current row. When specified LAST will be refunded last line cursor. It also becomes the current line.

When specifying an operator NEXT the row immediately after the current one in the result set will be returned. This line becomes the current line. Default command FETCH uses exactly this method of fetching rows.

When specifying an operator PRIOR the line before the current one will be returned. This line becomes the current line.

Operator ABSOLUTE (line_number | @line_number_variable) returns a row by its absolute ordinal number in the complete result set of the cursor. The line number can be specified using a constant or as the name of a variable in which the line number is stored. The variable must be an integer data type. Both positive and negative values ​​are indicated. When specifying a positive value, the string is counted from the beginning of the set, while a negative value is counted from the end. The selected line becomes the current line. If a null value is specified, no row is returned.

Argument RELATIVE (number of rows | @variable number of rows) returns the line offset the specified number of lines after the current one. If you specify a negative number of rows, the row that is the specified number of rows before the current one will be returned. Specifying a null value will return the current row. The returned row becomes the current row.

To open a global cursor, precede its name by specifying keyword GLOBAL. The cursor name can also be specified using a variable.

In expression INTO @variable_name [,...n] a list of variables is defined in which the corresponding column values ​​of the returned row will be stored. The order of the variables must match the order of the columns in the cursor, and the data type of the variable must match the data type in the cursor column.

Changing and deleting data using a cursor

To change data using a cursor, you must issue an UPDATE command in the following format:

In one operation, the values ​​of several columns of the current cursor row can be changed, but they all must belong to the same table.

To delete data using a cursor, use the DELETE command in the following format:

As a result, the current line in the cursor will be deleted.

Freeing memory, deallocate

To remove a cursor from memory, use the command

Deallocate cursor_name;

@@FETCH_STATUS attribute

To determine the presence of rows in the cursor, you should use a global variable @@FETCH_STATUS, which takes a non-zero value if there are no more rows in the cursor. If the set of rows has not yet been exhausted, then @@FETCH_STATUS is equal to zero.

Example of a cursor in SQL server

Declare @company varchar(50), @manager varchar(50), @message varchar(256); declare crs_clients cursor local for select company, manager from customers where city = "Moscow" order by company, manager; print "List of clients"; open crs_clients; fetch next from crs_clients into @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Company " + @company + " manager " + @manager; print @message; -- move to the next record fetch next from crs_clients into @company, @manager; end; close crs_clients; deallocate crs_clients;

The DECLARE CURSOR command allows you to retrieve records from a table row by row for manipulation. This allows for row-by-row processing instead of the traditional dataset processing that SQL does.

As a very first approximation, the following steps are used when working with the cursor.

The cursor is created with the DECLARE command. The cursor is opened with the OPEN command.

Operations with the cursor are performed using the FETCH command. The cursor is closed with the CLOSE command.

The DECLARE CURSOR command specifies a SELECT statement. Each row returned by the SELECT statement can be retrieved and processed individually. The following Oracle example declares a cursor in a declaration block along with several other variables. After this, in the subsequent BEGIN...END block, the cursor is opened, a selection is made from it, and the cursor is closed.

CURSOR title_price_cursor IS SELECT title, price FROM titles

WHERE price IS NOT NULL; title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);

OPEN title_price_Cursor;

FETCH title_price_cur-sor INTO title_price_val;

new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES

(title_price_val.title, new_price) CLOSE title_price_cursor; END;

Because this example uses PL/SQL, we won't explain much of the code in this book. However, the DECLARE block clearly shows the cursor declaration. In an executable PL/SQL block, the cursor is initialized with the OPEN command, the values ​​are retrieved with the FETCH command, and finally the cursor is closed with the CLOSE command.

The SELECT statement is the basis of the cursor, so it is good practice to test it thoroughly before including it in the DECLARE CURSOR statement. The SELECT statement can operate on the underlying table or view. Therefore, read-only cursors can work with non-updatable views. A SELECT statement can contain clauses such as ORDER BY, GROUP BY, and HAVING as long as these clauses do not update the source table. If the cursor is defined as FOR UPDATE, then it is recommended to remove such clauses from the SELECT statement.

Local cursors are often used as output parameters to stored procedures. Therefore, you can define and populate a cursor in a stored procedure and pass it to the calling batch job or stored procedure.

In the next simple example for DB2 we will declare a cursor that looks up department numbers, department names and manager numbers in admin_group "XO1".

DECLARE dept_cursor CURSOR

FOR SELECT dept_nbr, dept_name, mgr_nbr

WHERE admin_group="X01"

ORDER BY d"ept_name ASC, dept_nbr DESC, mgr_nbr DESC;

The following Microsoft SQL Server example declares and opens a cursor for the publishers table. The cursor selects the first record that matches the SELECT statement from the publishers table and inserts it into another table. It then moves on to the next record, then the next, until all records have been processed. Finally, the cursor is closed and memory is freed (the DEALLOCATE command is only used in Microsoft SQL Server).

DECLARE @publisher_name VARCHAR(20)

DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"

FETCH NEXT FROM pub_cursor INTO publisher_name

WHILE @s>FETCH_STATUS=0

INSERT INTO foreign_publishers VALUES("j>publisher_name)

CLOSE pub_cursor DEALLOCATE pub_cursor

In this example, you can see the cursor moving through a set of records. (This example is only intended to demonstrate the idea, since in reality there is The best way solution to this problem, namely the INSERT, SELECT instruction.)