Using Cursors and Loops in Transact-SQL. Cursors in MySQL stored procedures Cursors in sql server
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 batch job or stored procedure that called it.
In the following simple DB2 example, we will declare a cursor that looks up department numbers, department names, and manager numbers in the 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.)
IT APPLIES TO: SQL Server (since 2008)Base SQL data Azure SQL Data WarehouseParallel Data Warehouse
Defines attributes of a Transact-SQL server cursor, such as view properties and the query used to build the result set on which the cursor operates. The DECLARE CURSOR statement supports both ISO standard syntax and syntax that uses the Transact-SQL language extension set.
ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] [;] Transact-SQL Extended Syntax 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 ] ] ] [;]
cursor_name
cursor_name
INSENSITIVE
tempdb; thus, changes to the underlying tables are not reflected in the data returned by this cursor's selections, and this cursor is not changeable. When using ISO syntax, unless the INSENSITIVE option is specified, committed updates and deletes made to the base tables appear in subsequent selections.
SCROLL
Indicates that all sampling options are available (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). If the ISO DECLARE CURSOR statement does not specify a SCROLL option, only the NEXT fetch option is supported. The SCROLL option cannot be specified with the FAST_FORWARD option.
select_statement
A standard SELECT statement that specifies the result set of a cursor. The FOR BROWSE and INTO keywords are not allowed in select_statement cursor declaration.
select_statement conflict with a cursor of the requested type.
READ ONLY
Update ]
column_name [,
.. .n] is specified, only the listed columns allow changes. If the UPDATE statement is used without a list of columns, then the update is possible for all columns.
cursor_name
The Transact-SQL name of the specific server cursor. cursor_name must follow the rules for identifiers.
LOCAL
Indicates that the cursor is local to the package, stored procedure, or trigger in which it was created. The cursor name is only valid within this area. A cursor can be referenced by package local variables, stored procedures, triggers, or the output parameter of a stored procedure. The OUTPUT parameter is used to pass a local cursor to the calling package, stored procedure, or trigger, which can then assign the parameter to a cursor variable for subsequent access to the cursor after the stored procedure completes. The cursor is implicitly released when the batch, stored procedure, or trigger completes execution, unless the cursor was passed to the OUTPUT parameter. If the cursor was passed to the OUTPUT parameter, the cursor is released when all variables referencing it are freed or when the scope is exited.
GLOBAL
Indicates that the cursor is global to the connection. The cursor name can be used by any stored procedure or package that runs on the connection. The cursor is implicitly released only if the connection is broken.
FORWARD_ONLY
Specifies that the cursor can only be viewed from the first line to the last. Only the FETCH NEXT fetch option is supported. If FORWARD_ONLY is specified without the STATIC, KEYSET, or DYNAMIC keywords, the cursor behaves as a DYNAMIC cursor. If neither the FORWARD_ONLY argument nor the SCROLL argument is specified, the default is the FORWARD_ONLY argument unless the STATIC, KEYSET, or DYNAMIC keywords are present. STATIC, KEYSET, and DYNAMIC cursors have a default value of SCROLL. Unlike database APIs such as ODBC and ADO, FORWARD_ONLY mode is supported by the following Transact-SQL cursors: STATIC, KEYSET, and DYNAMIC.
STATIC
Defines a cursor that creates a temporary copy of data for use by the cursor. All queries to the cursor access the specified temporary table in tempdb; thus, changes to the underlying tables are not reflected in the data returned by this cursor's selections, and this cursor is not changeable.
KEYSET
Indicates that the membership or order of the rows in the cursor is unchanged when it is opened. A set of keys that uniquely identify rows is built into the table in tempdb called keys.
Changes to non-key values in base tables made by the cursor owner or committed by other users are displayed when the cursor owner views it. Changes made by other users are not reflected (changes cannot be made using the Transact-SQL server cursor). If a row is deleted, attempting to fetch rows returns @@FETCH_STATUS -2. Updates to key values from across cursor boundaries are similar to deleting an old row and then inserting a new row. The row with the new values is not visible and attempts to fetch the row with the old values return @@FETCH_STATUS -2. Updates are visible immediately if they are made through the cursor using the WHERE CURRENT OF clause.
DYNAMIC
Defines a cursor that displays all data changes made to rows in the result set while viewing this cursor. 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.
READ_ONLY
Prevents changes made through this cursor. The WHERE CURRENT OF clause cannot reference a cursor in an UPDATE or DELETE statement. This option takes precedence over the default cursor refresh feature.
SCROLL_LOCKS
Indicates that positioned updates or deletes made using the cursor are guaranteed to succeed. SQL Server locks rows as they are read into the cursor to ensure that those rows are available for subsequent changes. The SCROLL_LOCKS option cannot be specified with the FAST_FORWARD or STATIC option.
OPTIMISTIC
Specifies that positioned updates or deletes made using the cursor will fail if the row has been updated since it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. Comparisons are used instead timestamp column values or checksums, if not in the table timestamp column to determine whether the row has changed since it was read into the cursor. If the row has been modified, then attempts at a positioned update or delete will fail. The OPTIMISTIC option cannot be specified with the FAST_FORWARD option.
TYPE_WARNING
Specifies that a warning will be sent to the client if the cursor is implicitly converted from one requested type to another.
select_statement
A standard SELECT statement that specifies the result set of a cursor. The keywords COMPUTE, COMPUTE BY, FOR BROWSE and INTO are not allowed in select_statement cursor declaration.
SQL Server implicitly converts the cursor to another type if the clauses in select_statement conflict with a cursor of the requested type. For more information, see Implicit Cursor Conversions.
FOR UPDATE ]
Defines the columns in the cursor to be updated. If OF column_name [,
... n] is provided, only the listed columns allow changes. If the UPDATE statement is used without a column list, then the update is possible for all columns unless the READ_ONLY concurrency option was specified.
The DECLARE CURSOR statement defines attributes of a Transact-SQL server cursor, such as view properties and the query used to build the result set on which the cursor operates. The OPEN statement populates the result set, and the FETCH statement returns a row from it. The CLOSE statement clears the current result set associated with the cursor. The DEALLOCATE statement releases resources used by the cursor.
The first form of the DECLARE CURSOR statement uses ISO syntax to specify cursor parameters. The second form of the DECLARE CURSOR statement uses extensions to the Transact-SQL language that allow you to define cursors using the same types as those used in the cursor functions of database APIs such as ODBC and ADO.
These two forms cannot be mixed. If you specify SCROLL or omitting keywords before the CURSOR keyword, you cannot use keywords between the CURSOR and also for select_statement keywords. When specifying keywords between the CURSOR, as well as for select_statement keywords, you cannot specify SCROLL or INSENSITIVE before the CURSOR keyword.
If you use Transact-SQL syntax for the DECLARE CURSOR statement and do not specify the READ_ONLY, OPTIMISTIC, or SCROLL_LOCKS options, the following default value is assumed.
If the SELECT statement does not support updates (or has insufficient permissions, or is accessing remote tables that do not support updates, etc.), the cursor is set to READ_ONLY.
STATIC and FAST_FORWARD cursors default to READ_ONLY.
DYNAMIC and KEYSET cursors default to OPTIMISTIC.
Cursors can only be referenced by other Transact-SQL statements. Database API functions cannot reference cursors. For example, once a cursor is declared, OLE DB, ODBC, or ADO functions and methods cannot refer to its name. Cursor rows cannot be selected using the corresponding API functions and methods; For this purpose, you must use Transact-SQL FETCH statements.
The following stored procedures can be used to define the properties of a cursor after it has been declared.
Variables can be used as part select_statement, in which the cursor is declared. The values of cursor variables do not change after it is declared.
By default, DECLARE CURSOR permissions are granted to all users who have SELECT permission on the views, tables, and columns used by the cursor.
You cannot use cursors or triggers on a table with a clustered columnstore index. This limitation does not apply to nonclustered indexes; You can use cursors and triggers on a table with a nonclustered columnstore index.
A. Using a simple cursor and syntax
The result set created when you open this cursor includes all the rows and columns of the table. This cursor can be updated, all updates and deletions are represented in the selection for this cursor. FETCH``NEXT is fetch only because the SCROLL parameter was not specified.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
B. Using nested cursors to display a report
The following example uses nested cursors to display a complex report. An internal cursor is declared for each provider.
SET NOCOUNT ON ;
DECLARE @vendor_id int , @vendor_name nvarchar ( 50 ), @message varchar ( 80 ), @product nvarchar ( 50 ); PRINT" -------- Vendor Products Report --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: "+ @vendor_name PRINT @message -- Declare an inner cursor based
-- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 PRINT "<
Cursor is a link to the contextual memory area. In some implementations of the SQL programming language (Oracle, Microsoft SQL Server) - the result set obtained when executing a query and the current record pointer associated with it. I would say that a cursor is a virtual table that represents an alternative data storage. In this case, the cursor allows you to access its data as if it were the data of a regular array.
Cursors are used in stored procedures. Enough theory, let's look at an example:
We have a database (the database is a little not good, this is one of my laboratory work, but our database teacher insisted on such a structure)
/*bank information*/
CREATE TABLE `bank` (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
PRIMARY KEY (`BankId`))ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin" ;
/*data about deposits */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY(`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*data about investors*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "" ,
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY(`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"
Let's say we need to receive each bank in turn and perform some actions with it, the following query could help us with this
Select `bank`.* FROM `bank` LIMIT NUMBER OF THE_RECORD_WE NEED,1. Thus, using LIMIT WE NEED_RECORD NUMBER, 1, we extract each record in a loop from the bank table and perform the actions we need with it, while increasing the value of WE NEED_RECORD NUMBER by 1. Now we will do the same but using a cursor
Begin
/* variables where we extract data */
Declare vBankId integer ;
Declare vBankName VARCHAR(50);
Declare vAddress VARCHAR(50);
Declare vPhone VARCHAR (50);
/* hadler variable - a*/
Declare done integer default 0;
/*Cursor declaration*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER purpose, which will be explained below*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* open cursor */
Open BankCursor;
/*retrieve data*/
WHILE done = 0 DO
we take the actions we need
END WHILE ;
/*closing the cursor*/
Close BankCursor;
END ;* This source code was highlighted with Source Code Highlighter.
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Message: No data - zero rows fetched, selected, or processed
SQLSTATE: 02000 fires when the end of the cursor is reached, or when select or update returns an empty string.
The next line we declared the cursor DECLARE cursor_name CURSOR FOR select_statement;
Open the cursor Open cursor_name;
Then, until we reach the end of the cursor (WHILE done = 0 DO), we extract the data and process it.
You must close the cursor before exiting the stored procedure. Close cursor_name;
It doesn't seem complicated. But there are many pitfalls associated with SQLSTATE "02000".
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
we do some actions
END WHILE ;* This source code was highlighted with Source Code Highlighter.
Everything is fine and correct from a syntax point of view. But from a logical point of view, no. It may happen that depositors have not opened accounts in some bank, then for Select (ContributeAmount) INTO vContributeAmountSUM FROM bank distribution where BankId = vBankId limit 1; SQLSTATE: 02000 will fire, the done variable will be set to 1, and the while loop will end earlier than we expected. This can be avoided by doing the following
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
end if ;
we do some actions
END WHILE ;* This source code was highlighted with Source Code Highlighter.
With the first request, we checked whether there are contributions (if there are none, then vContributeAmountSUM == 0) and only if there are any, we retrieve the data.
Now let's say we need to remove the total amount in accounts in different banks for each client
Declare ClientSummCursor Cursor for Select sum
Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`), `bankdistribution`.`ClientId` FROM `bankdistribution` Inner Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by `bankdistribution`. `ClientId`;Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* check if there really are deposits in this bank */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
we do some actions.
END WHILE ;* This source code was highlighted with Source Code Highlighter.
The same situation may arise when the data in the ClientSummCursor cursor ends earlier than the data in the BankCursor, SQLSTATE: 02000 is triggered, the done variable is set to 1, and the while loop ends earlier than we expected. This can be avoided by doing the following
Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId,vBankName,vAddress,vPhone;
/* extract for the bank the amount of any of its deposits */
Select Count(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* check if there really are deposits in this bank */
if (vContributeAmountSUM > 0) then
/* extract for the bank the amount of any of its deposits */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if ;
/* before extracting data from the second cursor, remember the sqlstate state */
SET old_status = done;
/* extract the data we need */
FETCH ClientSummCursor INTO vSum,vClientId;
/* check whether the data was retrieved and whether sqlstate 0200 failed */
if (done = 0) then
we do some actions.
end if ;
/* before the end of the while, restore the value of the done variable */
set done = old_status;
END WHILE ;* This source code was highlighted with Source Code Highlighter.
Thank you to everyone who has read this far, I hope this will be useful to someone.
The implementation of a cursor in a database resembles a Java class that has a set of data and methods for processing it. Wherein sql cursor uses the data as a 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 to reuse the cursor name. In other implementations, when the cursor is closed, memory is freed implicitly.
In some cases, you cannot 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 ])]
This expression declares a cursor 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:
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 the last line of the cursor will be returned. 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, you must specify a keyword before its name 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 definition of the cursor is given. A description of its types and behavior is provided: static, dynamic, sequential and key cursors. The principles of cursor control are described: creating and opening a cursor, reading data, closing a cursor. Examples of cursor programming are given.Cursor concept
A query against a relational database typically returns multiple rows (records) of data, but the application only processes one record at a time. Even if it deals with several rows at the same time (for example, displaying data in the form of spreadsheets), their number is still limited. In addition, when modifying, deleting, or adding data, the work unit is the series. In this situation, the concept of a cursor comes to the fore, and in this context, the cursor is a pointer to a row.
A cursor in SQL is an area in database memory that is designed to hold the last SQL statement. If the current statement is a database query, a row of query data called the current value, or current cursor line, is also stored in memory. The specified area in memory is named and accessible to application programs.
Typically, cursors are used to select from a database a subset of the information stored in it. At any given time, one cursor line can be checked by the application program. Cursors are often used in SQL statements, built into application programs written in procedural languages. Some of them are implicitly created by the database server, while others are defined by programmers.
In accordance with the SQL standard, when working with cursors, the following main actions can be distinguished:
- creation or cursor declaration;
- opening cursor, i.e. filling it with data that is stored in multi-level memory;
- selection from cursor and changing data rows with it;
- closing the cursor, after which it becomes inaccessible to user programs;
- freeing the cursor, i.e. deleting the cursor as an object because closing it does not necessarily free the memory associated with it.
The definition of a cursor may vary slightly across implementations. For example, sometimes a developer must explicitly free the memory allocated for a cursor. After release the cursor its associated memory is also freed. This makes it possible to reuse his name. In other implementations when closing the cursor freeing memory occurs implicitly. Immediately after recovery, it becomes available for other operations: opening another cursor etc.
In some cases, using a cursor is unavoidable. However, if possible, this should be avoided and work with standard data processing commands: SELECT, UPDATE, INSERT, DELETE. In addition to the fact that cursors do not allow modification operations on the entire volume of data, the speed of performing data processing operations using a cursor is noticeably lower than that of standard SQL tools.
Implementation of cursors in MS SQL Server environment
SQL Server supports three types of cursors:
- SQL cursors are used primarily within triggers, stored procedures, and scripts;
- server cursors operate on the server and implement the application programming interface for ODBC, OLE DB, DB_Library;
- Client cursors are implemented on the client itself. They fetch the entire result set of rows from the server and store it locally, which speeds up data processing by reducing wasted time spent on network operations.
Different types of multi-user applications require different types of parallel access to data. Some applications require immediate access to information about changes to the database. This is typical for ticket reservation systems. In other cases, such as statistical reporting systems, data stability is important because if it is constantly being modified, programs will not be able to display information effectively. Different applications need different implementations of cursors.
In SQL Server, cursor types vary in the capabilities they provide. The cursor type is determined at the stage of its creation and cannot be changed. Some types of cursors can detect changes made by other users to rows included in the result set. However, SQL Server only tracks changes to such rows while the row is being accessed and does not allow changes to be modified once the row has already been read.
Cursors are divided into two categories: sequential and scrollable. Consecutive allow you to select data in only one direction - from beginning to end. Scrollable cursors provide greater freedom of action - it is possible to move in both directions and jump to an arbitrary row of the cursor's result set. If the program is able to modify the data that the cursor points to, it is called scrollable and modifiable. Speaking of cursors, we should not forget about transaction isolation. When one user modifies a record, another reads it using their own cursor, and moreover, he can modify the same record, which makes it necessary to maintain data integrity.
SQL Server supports static, dynamic, sequential and controlled by a set of keys.
In the scheme with static cursor information is read from the database once and stored as a snapshot (as of some point in time), so changes made to the database by another user are not visible. For a while opening the cursor the server sets a lock on all rows included in its full result set. Static cursor does not change after creation and always displays the data set that existed at the time of its opening.
If other users change the data included in the cursor in the source table, this will not affect the static cursor.
IN static cursor It is not possible to make changes, so it always opens in read-only mode.
Dynamic cursor maintains data in a “live” state, but this requires network and software resources. Using dynamic cursors a complete copy of the source data is not created, but a dynamic selection is performed from the source tables only when the user accesses certain data. During the fetch, the server locks the rows, and any changes the user makes to the full result set of the cursor will be visible in the cursor. However, if another user has made changes after the cursor has fetched the data, they will not be reflected in the cursor.
Cursor controlled by a set of keys, is in the middle between these extremes. Records are identified at the time of sampling, and thus changes are tracked. This type of cursor is useful when implementing scrolling back - then additions and deletions of rows are not visible until the information is updated, and the driver selects a new version of the record if changes have been made to it.
Sequential cursors are not allowed to fetch data in the reverse direction. The user can only select rows from the beginning to the end of the cursor. Serial cursor does not store a set of all rows. They are read from the database as soon as they are selected in the cursor, which allows all changes made by users to the database to be dynamically reflected using INSERT, UPDATE, DELETE commands. The cursor shows the most recent state of the data.
Static cursors provide a stable view of the data. They are good for information "warehousing" systems: applications for reporting systems or for statistical and analytical purposes. Besides, static cursor copes better than others with sampling large amounts of data. In contrast, electronic purchasing or ticket reservation systems require dynamic perception of updated information as changes are made. In such cases it is used dynamic cursor. In these applications, the amount of data transferred is typically small and accessed at the row (individual record) level. Group access is very rare.
Cursor management in MS SQL Server environment
Cursor control implemented by executing the following commands:
- DECLARE - creation or cursor declaration;
- OPEN – opening cursor, i.e. filling it with data;
- FETCH selection from cursor and changing data rows using the cursor;
- CLOSE - closing the cursor;
- DEALLOCATE – freeing the cursor, i.e. deleting the cursor as an object.
Cursor Declaration
The SQL standard provides the following command to create a cursor:
Using the INSENSITIVE keyword will create static cursor. Data changes are not allowed, in addition, changes made by other users are not displayed. If the INSENSITIVE keyword is missing, a dynamic cursor.
When you specify the SCROLL keyword, the created cursor can be scrolled in any direction, allowing you to use any selection commands. If this argument is omitted, the cursor will be consistent, i.e. its viewing will be possible only in one direction - from beginning to end.
The SELECT statement specifies the body of the SELECT request, which determines the resulting set of rows for the cursor.
Specifying FOR READ_ONLY creates a read-only cursor and does not allow any modifications to the data. It differs from static, although the latter also does not allow data to be changed. Can be declared as a read-only cursor dynamic cursor, which will allow changes made by another user to be displayed.
Creating a cursor with a FOR UPDATE argument allows you to execute in the cursor data change either in the specified columns or, in the absence of the OF column_name argument, in all columns.
In the MS SQL Server environment, the following syntax for the cursor creation command is accepted:
<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ]]
Using the LOCAL keyword will create a local cursor that is visible only within the scope of the package, trigger, stored procedure, or user-defined function that created it. When a package, trigger, procedure, or function terminates, the cursor is implicitly destroyed. To pass the contents of the cursor outside the construct that created it, you must assign an OUTPUT argument to its parameter.
If the GLOBAL keyword is specified, a global cursor is created; it exists until the current connection is closed.
Specifying FORWARD_ONLY creates serial cursor; Data can only be sampled in the direction from the first row to the last.
Specifying SCROLL creates scrollable cursor; Data can be accessed in any order and in any direction.
Specifying STATIC creates static cursor.
Specifying KEYSET creates a key cursor.
Specifying DYNAMIC creates dynamic cursor.
If you specify the FAST_FORWARD argument for a READ_ONLY cursor, the created cursor will be optimized for fast data access. This argument cannot be used in conjunction with the FORWARD_ONLY or OPTIMISTIC arguments.
A cursor created with the OPTIMISTIC argument prevents modification or deletion of rows that were modified after opening the cursor.
By specifying the TYPE_WARNING argument, the server will inform the user of an implicit change to the cursor type if it is incompatible with the SELECT query.
Opening the cursor
For opening the cursor and filling it with data from the SELECT query specified when creating the cursor, use the following command:
After opening the cursor The associated SELECT statement is executed, the output of which is stored in multi-level memory.
Retrieving data from a cursor
Right after opening the cursor you can select its contents (the result of executing the corresponding query) using the following command:
Specifying FIRST will return the very first row of the cursor's complete result set, which becomes the current row.
Specifying LAST returns the most recent row of the cursor. It also becomes the current line.
Specifying NEXT returns the row immediately after the current one in the full result set. Now it becomes current. By default, the FETCH command uses this method for fetching rows.
The PRIOR keyword returns the row before the current one. It becomes current.
Argument ABSOLUTE (line_number | @line_number_variable) returns a row by its absolute ordinal number in the cursor's complete result set. 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 that is 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 global cursor, you must specify the GLOBAL keyword before its name. The cursor name can also be specified using a variable.
In design INTO @variable_name [,...n] a list of variables is specified in which the corresponding column values of the returned row will be stored. The order of specifying 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. If the INTO construct is not specified, then the behavior of the FETCH command will resemble the behavior of the SELECT command - the data is displayed on the screen.
Changing and deleting data
To make changes using a cursor, you must issue an UPDATE command in the following format:
Several columns of the current cursor row can be changed in one operation, 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 line set current in the cursor will be deleted.
Closing the cursor
After closing, the cursor becomes inaccessible to program users. When closed, all locks installed during its operation are removed. Closure can only be applied to open cursors. Closed but not freed cursor may be reopened. It is not allowed to close an unopened cursor.
Release the cursor
Closing the cursor does not necessarily free the memory associated with it. Some implementations must explicitly deallocate it using the DEALLOCATE statement. After release the cursor Memory is also freed, making it possible to reuse the cursor name.
To control whether the end of the cursor has been reached, it is recommended to use the function: @@FETCH_STATUS
The @@FETCH_STATUS function returns:
0 if the fetch was successful;
1 if the fetch failed due to an attempt to fetch a line outside the cursor;
2 if the fetch failed due to an attempt to access a deleted or modified row.
DECLARE @id_kl INT, @firm VARCHAR(50), @fam VARCHAR(50), @message VARCHAR(80), @nam VARCHAR(50), @d DATETIME, @p INT, @s INT SET @s=0 PRINT "Shopping list" DECLARE klient_cursor CURSOR LOCAL FOR SELECT Client Code, Company, Last Name FROM Client WHERE City="Moscow" ORDER BY Company, Last Name OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ "Company "+ @firm PRINT @message SELECT @message="Product name Purchase date Cost" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Product.Name, Transaction.Date, Product.Price* Transaction.Quantity AS Cost FROM Product INNER JOIN Transaction ON Product. Product Code=Transaction.Product Code WHERE Transaction.Customer Code=@id_kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "No purchases" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" "+@nam+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @s=@s+@p FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Total cost "+ CAST(@s AS CHAR(6)) PRINT @message -- move to next client-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Example 13.6. Cursor for displaying a list of goods purchased by clients from Moscow and their total cost.
Example 13.7. Develop a scrollable cursor for clients from Moscow. If the phone number starts with 1, delete the client with that number and in the first cursor entry replace the first digit in the phone number with 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT "List of clients" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Firm, Last name, Phone FROM Client WHERE City ="Moscow" ORDER BY Company, Last name FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client "+@fam+ " Company "+@firm " Phone "+ @tel PRINT @message -- if the phone number starts with 1, -- delete the client with that number IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- move to the next client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- in the first entry, replace the first digit in the phone number with 4 UPDATE Client SET Phone='4' + RIGHT(@ tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client "+@fam+" Firm "+ @firm "Phone "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Example 13.7. Scrollable cursor for clients from Moscow.
Example 13.8. Usage cursor as an output parameter of the procedure. The procedure returns a data set - a list of products.
Calling the procedure and printing data from the output cursor is carried out as follows:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @cur=@my_cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n END CLOSE @my_cur DEALLOCATE @my_cur
