Cursors. DECLARE CURSOR command general rules Transact sql cursors

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft included them in SQL Server for a reason so they must have a place where they can be used in an efficient manner.

solution

In some circle "s cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others.So, it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor-based processing is appropriate or not. started let's do the following:

Look at an example cursor
Break down the components of the cursor
Provide additional cursor examples
Analyze the pros and cons of cursor usage

How to Create a SQL Server Cursor

Creating a SQL Server cursor is a consistent process, so once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let's walk through the steps:

First, you declare your variables that you need in the logic.
Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
Third, you fetch a record from cursor to begin the data processing.
Fourth, is the data process that is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
Fifth, you fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
Sixth, once all of the data has been processed, then you close cursor.
As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model ","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @ name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

DECLARE statements - Declare variables used in the code block
SET\SELECT statements - Initialize the variables to a specific value
DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
- NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
OPEN statement - Open the cursor to begin data processing
FETCH NEXT statements - Assign the specific values from the cursor to the variables
- NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
WHILE statement - Condition to begin and continue data processing
BEGIN...END statement - Start and end of the code block
- NOTE - Based on the data processing multiple BEGIN...END statements can be used
Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
DEALLOCATE statement - Destroys the cursor

Additional SQL Server Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor-based logic:

Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:

Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
Serialized processing - If you have a need to complete a process in a serialized manner, cursors are a viable option.
Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor-based logic, but other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
Large data sets - With large data sets you could run into any one or more of the following:
- Cursor based logic may not scale to meet the processing needs.
- With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
- Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
- If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
- SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
- Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a
- Repeat a batch with the GO command
Next steps
- When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
- If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
- If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
- As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.

It may well happen that the response to a simple client request will be a selection of hundreds of thousands of rows, which is indigestible for most clients. In this case, the solution to the problem of interaction with clients is the use of cursors as a universal mechanism for exchanging data between the server and the client. Cursors work with the resulting data set (the result of query execution), giving users additional options for data processing:

Cursors allow you to work with table rows by specifying their serial number in the data set;

Cursors allow you to implement complex data modification operations, for example, when changing the value of a column requires repeatedly accessing the values of other columns.

Cursor lifecycle:

Cursor creation: DECLARE<имя курсора>[INSENSITIVE] [SCROLL] CURSOR FOR< SELECT -оператор>FOR ( READ ONLY | UPDATE )

Here, the INSENSITIVE keyword means that the cursor will be static (a snapshot of the data), while by default the cursor is created dynamic (the selection is made every time the row is accessed). The SCROLL keyword means that the cursor can be scrolled in any direction, otherwise the cursor is created "sequential".

Cursor opening: OPEN[GLOBAL]<имя курсора>. A cursor specified as GLOBAL is not automatically deleted when the procedure or package from which it was called terminates.

Readingdata : FETCH [[ NEXT | PRIOR | FIRST | last | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ]<имя курсора>[ INTO @ variable _ name , …]. SQL Server 2000 only allows one row to be read from a cursor. The FIRST keyword is to return the first row of the cursor; LAST - the last line of the cursor; NEXT - the next line after the current one, the returned line becomes the current one; PRIOR - the previous one before the current one; ABSOLUTE n - returns a string by its absolute serial number in the cursor; RELATIVE - n lines after the current one. The column data will be stored in each of the specified variables in the order in which they are listed.

Data change: executes an UPDATE command with syntax designed to work with cursors.

Deleting data: executes a DELETE command with syntax designed to work with cursors.

Cursor close: CLOSE[GLOBAL]<имя курсора>

Freeing the cursor: DEALLOCATE [ GLOBAL ]<имя курсора>

Cursor example:

DECLARE fo_curs CURSOR STATIC FOR

SELECT name_eng from fo ORDER BY name_eng

DECLARE @name varchar(50)

FETCH FIRST FROM fo_curs INTO @name

WHILE @@FETCH_STATUS=0

FETCH NEXT FROM fo_curs INTO @name

DEALLOCATE fo_curs

2.7. Ensuring data security and integrity in Microsoft SQL Server. Database management. Roles. Assigning rights to users (GRANT, DENY, REVOKE). Methods and technologies of data protection in SQL Server.

SQL Server Security and Administration. .

The main task of the DBMS is to ensure the integrity and consistency of data within the selected subject area. One of the factors preventing the system from solving this problem is the actions of users who accidentally or deliberately try to destroy the data structure or change the data itself. Therefore, the DBMS must be protected not only from physical failures, but also from users who are inadequate for the tasks being implemented. To do this, it is necessary to design and connect a security system to the database that will prevent users from performing actions that go beyond their authority.

Database management

To create a database using TSQL, the CREATE DATABASE command is used, but usually SQL Server Management Studio is used for this purpose. There are quite a few database operations defined in SQL Server: increasing (decreasing) file sizes, changing configuration (ALTER command), attaching and detaching, transferring ownership, changing name, viewing properties, and finally deleting (DROP DATABASE).

As with most database servers, there is a user in SQL Server with full administrative privileges - this is System Administrator or ‘sa". After the initial server installation, the sa password is empty. The user who creates a new database automatically becomes its owner ('dbo" - Data Base Owner). At the time of database creation, the "guest" user is also defined. If the user account is not explicitly mapped to a specific database user, the user is granted implicit access with using the guest name guest.Guest is usually forbidden.

The user who created the object in the database automatically becomes the owner of the object, and no one, including dbo and sa , can use the object until the owner grants them rights to it. But in order for the user to create an object, the database owner must first grant him the appropriate rights.

Role allows you to combine users who perform the same functions to simplify administration. Roles are built-in and custom. Built-in roles are implemented at the server level and at the database level. Below is a table of built-in roles for a database:

db_owner. Has all rights in the database

db_accessadmin. Can add or remove users

db_securityadmin. Manages all permissions, objects, roles and users

db_ddladmin. Can execute all DDL commands except GRANT, DENY, REVOKE

db_backupoperator. Can execute archiving commands. data

db_datareader. Can view. any data in any table

db_datawriter. Maybe a modification. any data in any table

db_denydatareader. Prohibited view love data in any tables

db_denydatawriter. Prohibit modifying any data in any tables

Assign rights to users. The foundation of SQL Server security is (1) accounts; (2) users (users); (3) roles; (4) groups.

When a user connects to SQL Server, the actions they can perform are determined by the rights granted to them as a user and as a member of a role. Rights are granted by the DBMS administrator, the owner of the database, or the owner of a specific database object. Database rights can be divided into three categories: (1) rights to access database objects; (2) rights to execute TSQL commands; (3) implicit rights. The server allows you to transfer ownership from one user to another.

The following commands are used to manage user permissions to access database objects:

GRANT( ALL |< вид действия >,…}

( ON (<имя таблицы или представления>} [(<имя столбца>,…)]

| ON (< имя хранимой процедуры >}

| ON (< имя пользовательской функции >}

TO ( PUBLIC |<имя объекта системы безопасности>,…}

[ AS<имя группы> | <имя роли>]

– assign rights to users, where

ALL - the user is granted all possible permissions, otherwise specify

<вид действия>– rights to actions available to the user, namely:

SELECT - for viewing, for a table column and for a table (view)

INSERT - for adding, for the table (view) as a whole

UPDATE - for change, for a table column and for a table (view)

DELETE - for deletion, for the table (view) as a whole

EXECUTE - to execute stored procedures

REFERENCES - the ability to refer to the specified object (to be included in the foreign key).

<имя объекта системы безопасности>– SQL Server accounts, Windows domain users; PUBLIC - for all users.

WITH GRANT OPTION - Allows the currently granted user to assign access rights to the object to other users.

AS<имя группы> | <имя роли>– participation of a user in a role that is given the opportunity to grant rights to other users.

GRANT SELECT ON authors TO public

GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom

GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION

GRANT SELECT ON Plan_Data TO Jack AS Accounting

Jack is not in the Accounting role, but someone in that role can grant the right

DENY( ALL |< вид действия >,…}

( ON (<имя таблицы или представления>} [(<имя столбца>,…)]

| ON (<имя хранимой процедуры>}

| ON (<имя пользовательской функции>}

TO ( PUBLIC |<имя объекта системы безопасности>,…}

– access denied users to database objects. CASCADE revokes rights not only from this user, but also from everyone to whom he granted rights.

Example (on disable command execution TSQL):

DENY CREATE TABLE TO Jack CASCADE

Command REVOKE used to implicitly deny access to database objects. The syntax is the same as the DENY command. Implicit denying is similar to denying access, with the difference that it only takes effect at the level at which it is defined. Example: The user Jack , who is a member of the GoodUsers role, has been granted access to the XFiles table. If the GoodUsers role denies access to this table with REVOKE, the user Jack can still access this table, since the rights for him are explicitly defined. If you apply REVOKE to him personally, he will lose the right to access XFiles.

Permissions granted to roles are inherited by their members. If a user is granted access to an object through membership in one role, but denied in another, then the access conflict is always resolved in favor of a denial.

Data protection technologies in MS SQL Server

1.Mechanism checkpoints– checkpoints that are generated after ~60 s to write updated pages to disk (the checkpoint can be forced by the CHECKPOINT command).

2. Built-in and external mechanisms for checking the integrity of the database (launched automatically or, like the DBCC utility - Database Consistency Checker - manually).

3. Physical duplication (if allowed) of database files by means of the operating system (including the mechanism of mirrored hard disks).

4. Backing up databases and transaction logs - by writing a database dump to a backup device (magnetic tape or hard disk).

5. Replication - the possibility of duplicating information through its periodic (in some cases - synchronous) transfer from one SQL server to another.

6. Encryption of traffic between the client and the server, as well as encryption of codes used to work with database objects (stored procedures, triggers, etc.)

The cursor is defined. A description of its types and behavior is given: static, dynamic, sequential and key cursors. The principles of cursor control are described: creating and opening a cursor, reading data, closing a cursor. Cursor programming examples are given.

Cursor Concept

A relational database query typically returns multiple rows (records) of data, but the application processes only one record at a time. Even if it deals with several rows at the same time (for example, outputting data in the form of spreadsheets), their number is still limited. In addition, when modifying, deleting, or adding data, the unit of work is the row. 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 a region in database memory that is dedicated to storing the last SQL statement. If the current statement is a database query, the query data string, called the current value, or current cursor line, is also stored in memory. The specified area in memory is named and available to application programs.

Typically, cursors are used to select a subset of the information stored in a database. One cursor line can be checked by the application at a time. Cursors are often used in SQL statements embedded in 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;
cursor opening, i.e. filling it with data that is stored in multi-level memory;
fetch from cursor and changing data rows with it;
cursor close, after which it becomes inaccessible to user programs;
freeing the cursor, i.e. deleting the cursor as an object, since closing it does not necessarily release the memory associated with it.

In different implementations, the definition of the cursor may have some differences. So, for example, sometimes the developer must explicitly release the memory allocated for the cursor. After freeing the cursor the memory associated with it is also freed. This makes it possible to reuse his name. In other implementations, cursor close memory is deallocated implicitly. Immediately after recovery, it becomes available for other operations: opening another cursor etc.

In some cases, the use of 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 you to perform modification operations on the entire amount 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 kinds of cursors:

SQL cursors are mainly used inside 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 operations by reducing the wastage of network operations.

Different types of multi-user applications require different types of parallel data access. Some applications need immediate access to information about changes to the database. This is typical for ticket reservation systems. In other cases, for example, in statistical reporting systems, the stability of data is important, because if they are constantly modified, programs will not be able to effectively display information. Different applications need different implementations of cursors.

In the SQL Server environment, the types of cursors differ in terms of the capabilities they provide. The cursor type is determined at the stage of its creation and cannot be changed. Some cursor types can detect changes made by other users to rows included in the result set. However, SQL Server tracks changes to such rows only at the stage of accessing the row and does not allow changes to be modified after the row has already been read.

Cursors fall into two categories: consecutive and scrollable. Sequential allow you to select data in only one direction - from the beginning to the end. Scrollable Cursors provide more freedom of action - you can move in both directions and jump to an arbitrary line of the result set of the cursor. If the program is able to modify the data pointed to by the cursor, it is called scrollable and modifiable. Speaking of cursors, one should not forget about the isolation of transactions. When one user modifies a record, another reads it with its own cursor, and moreover, it can modify the same record, which makes it necessary to maintain data integrity.

SQL Server supports static , dynamic , consecutive 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 cursor opening the server acquires a lock on all rows included in its complete result set. Static cursor does not change after creation and always displays the dataset 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 You can't make changes, so it always opens in read-only mode.

Dynamic cursor keeps the data alive, but it 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 fetched the data , they will not be reflected in the cursor .

Cursor controlled by a set of keys, lies 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 for implementing scrolling back so that row additions and deletions are not visible until the information is updated, and the driver selects a new version of the entry if changes have been made to it.

Sequential cursors do not allow data fetching in the reverse direction. The user can only select rows from the beginning to the end of the cursor. Sequential 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 you to dynamically reflect all changes made by users to the database 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 "warehouse" systems: applications for reporting systems or for statistical and analytical purposes. Besides, static cursor better than others copes with sampling a large amount of data. On the contrary, in systems of electronic purchases or reservations of tickets it is necessary to dynamically perceive updated information as changes are made. In such cases, use dynamic cursor. In these applications, the amount of data transferred is usually small, and access to them is carried out at the level of rows (individual records). Group access is very rare.

Cursor control in MS SQL Server environment

Cursor control implemented by executing the following commands:

DECLARE - create or cursor declaration;
OPEN- cursor opening, i.e. filling it with data;
FETCH- fetch from cursor and changing data rows with a cursor;
CLOSE- cursor close;
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 not present, an dynamic cursor.

By specifying the SCROLL keyword, the generated cursor can be scrolled in any direction, allowing any selection commands to be applied. If this argument is omitted, the cursor will be consistent, i.e. its viewing will be possible only in one direction - from the beginning to the end.

The SELECT statement specifies the body of the SELECT query, which is used to determine the result set of cursor rows.

Specifying the FOR READ_ONLY argument creates a read-only cursor and does not allow any modifications to the data. It is distinct from static, although the latter also does not allow data to be changed. As a read-only cursor can be declared dynamic cursor, which will display changes made by another user.

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 argument column_name , 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 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 a cursor outside of 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 sequential 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 generated cursor will be optimized for fast data access. This argument cannot be used in conjunction with the FORWARD_ONLY and OPTIMISTIC arguments.

A cursor created with the OPTIMISTIC argument prevents modification and deletion of rows that have been modified since cursor opening.

With the TYPE_WARNING argument, the server will inform the user of an implicit cursor type change if it is incompatible with a SELECT query.

Opening the cursor

For cursor opening and filling it with data from the specified when creating the SELECT query cursor, the following command is used:

After cursor opening the associated SELECT statement is executed, the output of which is stored in layered memory.

Fetching data from a cursor

Right after cursor opening you can select its content (the result of the corresponding query) with the following command:

Specifying FIRST will return the very first row of the full cursor 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 row in the complete result set. Now it becomes current. By default, the FETCH command uses this method of fetching rows.

The PRIOR keyword returns the line before the current one. She becomes current.

Argument ABSOLUTE (line_number | @line_number_variable) returns a row by its absolute ordinal in the cursor 's full result set . The line number can be specified using a constant or as the name of a variable that stores the line number. The variable must have an integer data type. Both positive and negative values are indicated. If you specify a positive value, the string is counted from the beginning of the set, a negative value - from the end. The selected line becomes the current line. If null is specified, no string is returned.

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

To open global cursor, it requires 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 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. 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 the cursor, you must issue an UPDATE command in the following format:

Multiple columns of the current row of the cursor can be modified in one operation, but they must all 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.

Cursor close

After closing, the cursor becomes inaccessible to program users. When closing, all locks set during its operation are removed. Closing can only be applied to open cursors. closed but not freed cursor can be re-opened. It is not allowed to close an unopened cursor.

Freeing the Cursor

Cursor close optionally releases the memory associated with it. In some implementations, you must explicitly deallocate it with the DEALLOCATE statement. After freeing the cursor memory is also freed, and the cursor name can be reused.

To control when the end of the cursor is 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 row 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 ID, 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 " [email protected]+ "Company"+ @firm PRINT @message SELECT @message="Product Name Purchase Date Price" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Item.Name, Trade.Date, Item.Price*Trade.Quantity AS Cost FROM Item INNER JOIN Transaction ON Goods. ItemCode=Deal.ItemCode WHERE Deal.CustomerCode [email protected] _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=" " [email protected]+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @ [email protected][email protected] 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 customer-- 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 customers from Moscow and their total cost.

Example 13.7. Develop a scrollable cursor for clients from Moscow. If the phone number starts with 1, remove the customer 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 " Client List" DECLARE klient_cursor CURSOR GLOBAL SCROLL KEYSET FOR SELECT Company, Last Name, Phone FROM Client WHERE City ="Moscow" ORDER BY Firm, LastName FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Client" [email protected]+ "Firm" [email protected]" Phone "+ @tel PRINT @message -- if phone number starts with 1 -- delete client with that number IF @tel LIKE '1%' DELETE Client WHERE CURRENT OF klient_cursor ELSE -- go to next client FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- in first entry, replace first digit in -- phone number with 4 UPDATE Client SET Phone='4' + RIGHT( @tel,LEN(@tel)-1)) WHERE CURRENT OF klient_cursor SELECT @message="Client " [email protected]+" 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 a procedure. The procedure returns a data set - a list of goods.

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 @ [email protected] _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

Rebeca M. Riordan "Cursors in Transact-SQL"

Internet University of Information Technology

http://www.INTUIT.ru

Training course: "Programming in Microsoft SQL Server 2000"

A cursor is a special temporary SQL object designed for use in programs and stored procedures. With it, you can loop through the result set of query rows, individually reading and processing each of its rows. In stored procedures, you can use cursors to perform complex calculations that are difficult to express using the syntax of the SELECT statement. The large theoretical material of the lesson is complemented by very good examples. In particular, the application of the CURSOR_STATUS function, the description of the @@CURSOR_ROWS and @@FETCH_STATUS variables, and much more are considered.

You will learn:

declare a cursor;
open cursor;
close cursor;
release the cursor;
use a simple FETCH command;
fetch string into variables;
select a string by its absolute position;
select a row by its relative position;
perform positional modification;
perform positional deletion;
use the @@CURSOR_ROWS global variable to determine the number of rows in the cursor set;
use the @@FETCH_STATUS global variable to determine the results of the FETCH command;
use the CURSOR_STATUS function to query the cursor status.

One of the characteristic properties of relational databases is that actions are performed on sets of rows. A set may be empty or contain only one row, but it is still considered a set. This is a necessary and useful property for relational operations, but it can sometimes be inconvenient for applications.

For example, since there is no way to point to a particular row in a set, presenting the rows one at a time to the user can be difficult. Even though the extensions provided by Transact-SQL to the standard SQL language allow much greater programming power, there are still operations that are difficult, time consuming, or even impossible to perform based on set principles.

To deal with situations like this, SQL provides cursors. A cursor is an object that points to a particular row in a set. Depending on the nature of the cursor you have created, you can move the cursor within the set and modify or delete the data.

The concept of cursors

Microsoft SQL Server actually supports two different types of cursors: Transact-SQL cursors and API cursors (application programming interface cursors). API cursors are created within an application that uses Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC, or DB-Library. Each of these APIs supports slightly different functionality and uses a different syntax. We won't discuss the Cursors API in detail here; if you plan to use them, please refer to the relevant documentation for the API and the programming language you intend to use.

Transact-SQL cursors are created using the DECLARE CURSOR command. Both the cursor object and the rowset it points to must exist on the server. Such cursors are called server cursors. If you are using a server cursor from an application connected to SQL Server over a network, each cursor operation requires two-way network communication. Cursor API libraries that support server cursors also support a client cursor that exists on the client system and caches the strings it processes on the client.

The set of rows pointed to by the cursor is determined using the SELECT command. When creating a Transact-SQL cursor, there are several restrictions on the SELECT statement:

the SELECT command cannot return multiple result sets;

the SELECT command cannot contain an INTO clause to create a new table;

the SELECT command cannot contain a COMPUTE or COMPUTE BY clause used to aggregate results. (However, it may contain aggregation functions, such as AVG.)

Cursor characteristics

Transact-SQL supports several different types of cursors. Figuring out the various characteristics of each of the cursors is a rather tedious task, but it can be made easier if we take into account for each type of cursor three more or less independent characteristics: the ability to reflect changes in the source data, the ability to scroll through multiple rows, and the ability to modify multiple lines.

Reflection of change

The ability of a cursor to reflect changes in data is called cursor sensitivity. Suppose you have created a cursor for a statement:

SELECT * FROM Oils WHERE Left(OilName, 1) = "B" The Aromatherapy database will return four rows, as shown in Figure 1. If, while you are using the cursor, someone adds a Description value for the Bergamot element, or adds a string for the Bayberry element, what happens to the set of strings your cursor points to?

Rice. 1. The Aromatherapy database contains four rows beginning with the letter B.

When you create your cursor, two kinds of sensitivity can be independently defined: changes to which rows are included in the set (set membership) and whether changes are reflected in the original rows.

scrolling

The second characteristic of the cursor is the ability to scroll both forward and backward, or only forward. There is an age-old programming dilemma here: speed versus flexibility. Sequential cursors (forward-only) are much faster, but have less flexibility.

Update

The last characteristic used to classify cursors is the ability to update rows by the cursor. Again, read-only cursors are usually more performant but less flexible.

Cursor types

Transact-SQL supports four different types of cursors: static, key, dynamic, and quick access or "firehose" cursors. Each cursor type stores different data about the rows it points to, and each cursor type has different combinations of the characteristics discussed in the previous section.

Static cursors

A static cursor takes a snapshot of the data specified by the SELECT statement and stores it in the tempdb database. It does not "feel" changes to the structure or data values, and since any modifications will only be reflected in the copy, this cursor is always opened in read-only mode. Static cursors, however, can be declared sequential or scrollable.

Key Cursors

The key cursor copies only those columns into tempdb that uniquely identify each row. To be able to declare a key cursor, each table included in the definition of the SELECT statement must have a unique index that specifies the set to be copied - the key.

Key cursors can be either modifiable or read-only. They can also be scrollable or sequential.

Membership in a key cursor is fixed at the time the cursor is declared. If during the open state of the cursor a string is added that satisfies the selection condition, it will not be added to the set. In our previous example, where LEFT(OilName, 1) = "B" was used as the filter condition, a new row with the value of the OilName field "Bayberry" will not be added to the rows related to the scope of the cursor.

Similarly, if a change is made to a string that would then not satisfy the set membership condition, such as changing "Basil" to "Kumquat", the string will still remain a member of the set. Even if a row is deleted, it is still a member of the set, but SQL Server returns NULL for all column values.

Although the cursor's set membership remains fixed after the cursor is opened, changes to the data values made to the source tables are nonetheless reflected. For example, changing the value of the Description field for the string Bergamot will be returned by the cursor. However, changes to keyset values are only reflected in cursors if they occur within the cursor. Continuing with the previous example, if the value of the OilName field was changed from "Basil" to "Kumquat" inside the cursor, the cursor would return "Kumquat". If the change was made by another user, the cursor will still return "Basil".

Advice. As we will see in the next section, creating a cursor and opening a cursor are different operations. To update the contents of a key cursor, you can close and reopen it.

Dynamic Cursors

A dynamic cursor behaves as if the SELECT statement is re-executed each time the row is accessed. (Actually, things work a little differently, but this representation allows you to better judge the operation of dynamic cursors.) Dynamic cursors reflect changes related to both membership and values of the source data, regardless of whether these changes are made inside the cursor, or entered by another user.

There is one limitation with dynamic cursors: the SELECT statement used to define the cursor can only contain an ORDER BY clause if there is an index that includes the columns used in the ORDER BY clause. If you declare a key cursor using an ORDER BY clause that does not operate on an index, SQL Server converts the cursor to a key cursor.

Quick Access Cursors

SQL Server supports a special, optimized form of a non-scrollable, read-only cursor. This kind of cursor is declared using the FAST_FORWARD keyword, and is most commonly referred to as a "firehose" cursor.

Fire cursors are very effective, but there are two important limitations to their use. First, if you used text, ntext, or image columns and a TOP clause in a cursor definition SELECT statement, SQL Server converts the cursor to a key cursor.

Second, if the SELECT statement you used to define the cursor contains tables that have triggers and tables that don't, the cursor is converted to a static one. Triggers are Transact-SQL scripts that are automatically executed by the server when executed against a Data Manipulation Language (DML) statement table. We'll look at triggers in more detail in Lesson 29, but for now let's focus on the following point: if someone adds a trigger to one of the tables used by the cursor, your application may suddenly stop running because SQL Server converts a faster cursor to a slower one.

Using Cursors

Using cursors is like using local variables - you declare them, set a value, and then use them. However, unlike local variables, which are automatically destroyed when they go out of scope, you must explicitly free the rows used by the cursor and then destroy the cursor.

Creating Cursors

The first step in using a cursor is to create it. Transact-SQL cursors are created using the DECLARE CURSOR statement.

Attention! SQL Server supports two different methods for creating cursors: using SQL-92 syntax and using Transact-SQL syntax. The SQL-92 syntax conforms to the ANSI standard, but has less functionality than the Transact-SQL syntax discussed here.

The DECLARE CURSOR statement has the following syntax:

DECLARE cursor_name CURSOR

[visibility]

[scroll]

[lock]

FOR select_operator

Please note that all the parameters that define the characteristics of the cursor - visibility, type, etc. - are optional. The default values for these options are complex and may or may not specify how to interact with source records or views, or options for working with the database. To make the operator more convenient to read, it is better to explicitly set all the parameters you need. This way you will know exactly what you will get.

Cursor visibility is defined using the LOCAL or GLOBAL keywords, which have the same effect as the @local_table or @@global_table keywords when declaring temporary tables.

Advice. SQL Server will close and release the local cursor when it goes out of scope (visibility), but it's best to always do this explicitly.

The scroll option allows the use of the FORWARD_ONLY and SCROLL keywords, which specify, respectively, the ability to move only from the beginning to the end, or in any direction.

The type parameter specifies the type of cursor to create. Valid keywords are STATIC, KEYSET, DYNAMIC, and FAST_FORWARD. The type parameter FAST_FORWARD and the scroll parameter FORWARD_ONLY are mutually exclusive.

The lock option determines whether the rows can be modified by the cursor, and if so, if they can be modified by other users. If the READ_ONLY keyword is used, the cursor cannot make any changes to the original data. However, other users can modify the data, or you can do it yourself using the UPDATE statement. If SCROLL_LOCKS is specified as the lock parameter, updates can only be performed by the cursor. All other UPDATE statements, either within the same package or provided by other users, will fail.

The last lock option, OPTIMISTIC, allows row updates both inside and outside the cursor. This is the most flexible option, but there is always the possibility that a modification made by the cursor will fail if the line has been modified since it was read by the cursor.

The TYPE_WARNING parameter instructs SQL Server to send a warning message to the client if the cursor type is converted from the specified type to another type. This is possible if you declare a cursor that does not support the given SELECT statement.

The select_operator parameter specified in the FOR clause is required. It specifies the rows to be included in the cursor set.

The FOR UPDATE clause is optional. Cursors are modifiable by default unless READ_ONLY is set, but it's still a good idea to use this phrase to be sure of the result. You can use the OF clause of column_names to specify specific rows for which you allow modification. If you omit the OF section of column_names, the modification can be performed on all columns specified in the SELECT statement.

Cursor variables

Transact-SQL allows you to declare variables of type CURSOR. In this case, the standard DECLARE syntax does not create a cursor; you must explicitly set the variable for the cursor using the SET keyword.

DECLARE myCursor CURSOR

FOR SELECT OilName FROM Oils

DECLARE @myCursorVariable CURSOR

SET @myCursorVariable = myCursor

This syntax is useful if you want to create variables that can be assigned to different cursors. This may be required if you are creating a generic procedure to work with different result sets.

You can declare a cursor variable and then use it to create a cursor directly.

DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils

With this syntax, the cursor has no identifier and can only be referenced via a variable.

Opening the cursor

A cursor declaration creates a cursor object, but does not create a set of records that the cursor will manipulate (the cursor set). The cursor set is not created until you open the cursor. After the rather complicated syntax of the DECLARE CURSOR statement, the syntax of the statement seems quite transparent:

OPEN cursor_or_variable

The GLOBAL keyword helps avoid conflicts: if a cursor declared with the LOCAL keyword and a cursor declared with the GLOBAL keyword have the same identifier, references to the cursor will default to the local cursor unless you use the GLOBAL keyword. As with other similar cases, it's best to explicitly specify the keyword if you're opening a global cursor.

Cursor close

When you have finished using the cursor, you must close it. The CLOSE statement releases the resources used to maintain the cursor set and also releases any locks held on rows if you used the SCROLLOCKS option on the DECLARE statement. The syntax of the CLOSE command is almost identical to the syntax of the OPEN statement - only the keyword changes:

CLOSE cursor_or_variable

Freeing the Cursor

The last statement in the cursor creation sequence is the DEALLOCATE statement. Its syntax is also simple:

DEALLOCATE cursor_or_variable

However, there is a subtlety here: the DEALLOCATE statement deletes an identifier or cursor variable, but it does not necessarily delete the cursor itself. The cursor itself is not deleted until all identifiers referring to it are either freed or expire (when out of scope). Consider the following examples:

Create a cursor DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Create a cursor variable DECLARE @cursorVariable CURSOR -- Create a set of cursor entries OPEN myCursor -- Assign a variable to a cursor SET @cursorVariable = myCursor -- Release a cursor DEALLOCATE myCursor

After the cursor is freed, the identifier myCursor is no longer associated with the cursor set, but because the cursor set is still referenced by the @cursorVariable variable, the cursor and cursor set are not freed. Unless you explicitly free the cursor variable as well, the cursor and cursor set will exist until the variable expires.

Manipulating Strings with the Cursor

Cursors by themselves wouldn't be of any interest if you couldn't do things with them. Transact-SQL supports three different cursor commands: FETCH, UPDATE, and DELETE.

The FETCH command retrieves the specified string from the set of cursor rows. In its simplest form, the FETCH command has the following syntax:

FETCH cursor_or_variable

This record format returns the line at the cursor position (the current line).

Use a simple FETCH command

Navigate to the SQL 2000 Step by Step folder in the root directory, highlight the script named SimpleCursor, and click the Open button.
Query Analyzer will load the script into the Query window.

Advice. You may have noticed that this script takes longer than the corresponding SELECT statement. The fact is that creating and opening a cursor requires additional time. Never use a cursor if a SELECT statement is enough to complete the task.

The FETCH command can not only return a string directly, but also allows you to store the values from the returned column in variables. To store the results of a FETCH command in a variable, use the following syntax:

FETCH cursor_or_variable INTO variable_list

The variable_list is a comma-separated list of variable identifiers. Before executing the FETCH command, you must declare variables. The varlist must contain a variable for each column appearing in the SELECT statement that defines the cursor. The data type of the variable must either match or be compatible with the data type of the column.

Select lines with writing it to variables

In all of the previous examples, the FETCH statement was used to return the current row. The syntax of the FETCH statement also provides a number of keywords for specifying another row. When using these keywords, the FETCH statement will return the given row, and make it the current row.

These keywords allow you to specify an absolute position within the cursor set. The keywords FIRST and LAST return the first and last rows respectively, while ABSOLUTE n specifies the row n rows from the beginning (if n is positive) or from the end (if n is negative) of the cursor's record set. The value of n can be expressed as a constant (3) or as a variable (@theRow).

Select rows by their absolute position

Highlight the script named FetchAbsolute and click the Open button. Query Analyzer will load the script into the Query window.

In addition to keywords that allow you to retrieve rows by their absolute position, the FETCH statement provides three keywords that allow you to retrieve rows by their position relative to the current row. The FETCH NEXT operator returns the next row, the FETCH PRIOR operator returns the previous row, and the FETCH RELATIVE n operator returns the row n lines from the current row. Like the FETCH ABSOLUTE n statement, the FETCH RELATIVE n statement can specify rows before the current row if n is negative and rows after the current row if n is positive.

Select rows by their relative position

Highlight the script named FetchRelative and click the Open button. Query Analyzer will load the script into the Query window.

If the cursor is of type FORWARD_ONLY or PAST_FORWARD, only the NEXT keyword can be used to indicate the position. In fact, if the cursor is one of these types, the NEXT keyword is not needed. SQL Server assumes that each FETCH statement is actually a FETCH NEXT statement.

Use the FETCH NEXT statement for a fast cursor

Highlight the script named FetchFirehose and click the Open button. Query Analyzer will load the script into the Query window.

Click the Run Query button on the Query Analyzer toolbar. Query Analyzer will execute the query.

Modifying and deleting rows through cursors

If your cursor is modifiable. Changing the initial values in the cursor set is quite simple. There is a special form of the WHERE clause that supports modification through a cursor:

UPDATE table_or_view SET list_to_modify WHERE CURRENT OF cursor_or_variable

This is called a positional update. Transact-SQL also supports positional deletion, which has the following notation:

DELETE table_or_view WHERE CURRENT OF cursor_or_variable

Perform a positional update

Highlight the script named PositionedUpdate and click the Open button. Query Analyzer will load the script into the Query window.

Click the Execute Query button on the Query Analyzer toolbar. Query Analyzer will execute the query. Note that two grid panels are displayed. The first one is created by the FETCH statement and contains the initial contents of the columns. The second is the result of the SELECT statement and contains the value of the Description field after modification.

Monitoring Transact-SQL Cursors

Transact-SQL provides two global variables and a function to help you control the operation and state of your cursor. The variable @@CURSOR_ROWS returns the number of rows in the set of the last cursor opened on the connection. The values returned by @@CURSOR_ROWS are shown in Table 1.

The @@FETCH_STATUS variable returns information about the execution of the last FETCH command. Table 2 shows the values returned by the @@FETCH_STATUS variable.

Finally, Transact-SQL provides the CURSOR_STATUS function. This function has the following syntax:

CURSOR_STATUS(type, cursor_or_variable) Type can be "local", "global", or "variable" and cursor_or_variable is the identifier of the cursor or cursor variable to be retrieved. The results returned by the CURSOR_STATUS function are shown in Table 3.

Use cursor monitoring features

Highlight the StatusFunctions script and click the Open button. Query Analyzer will load the script into the Query window.

print version

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

It is noted in the literature 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 sets of rows. Therefore, cursors are recommended to be used only in cases where the description of the required actions through operations with sets of rows is clearly inefficient or even impossible.

Working with a cursor usually involves the following steps:

cursor declaration;
opening cursor;
reading attribute values into variables from the first cursor entry;
moving over the cursor (usually in a loop) and processing cursor entries;
close cursor;
freeing the 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 statement supports both the syntax of the ISO SQL standard (the version of the standard is not specified in the documentation) and the syntax using the CURSOR set of Transact-SQL language extensions.

FOR select_statement

Extended Transact-SQL Syntax:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

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

A "local" cursor, created by default or when LOCAL is explicitly specified, is available only in the job batch, stored procedure, or trigger in which it was created. Such a cursor is implicitly freed when the batch, stored procedure, or trigger completes execution. The exception is when the cursor is passed through an output parameter (OUTPUT) of a stored procedure. Then the cursor is released when all the variables referring to it are released or when the "scope" is exited.

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 behaves like a DYNAMIC cursor (see below). If none of the FORWARD_ONLY or SCROLL options are specified, and if none of the STATIC, KEYSET, or DYNAMIC keywords are specified, the FORWARD_ONLY option is set by default.

SCROLL means that you can "move" over the cursor in any direction (in the FETCH operator, FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE are available). The SCROLL option cannot be specified with the FAST_FORWARD option. STATIC, KEYSET, and DYNAMIC cursors default to SCROLL.

STATIC means the cursor is not updated. 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 displayed in the cursor after that.

KEYSET - for this type of cursor, a set of key values that identify the selected records is stored in a temporary table. When moving over the cursor, the values of non-key attributes are retrieved from the corresponding tables, so changes in non-key columns will be visible when working with the cursor. If the row in the cursor has already been removed from the table by the time it is fetched by the FETCH statement, the @@ FETCH_STATUS service variable will return the value -2. Rows added to tables after the cursor was opened are not visible in the cursor. If the query that generates the cursor uses at least one table that does not have a unique index, the KEYSET cursor is converted to STATIC.

DYNAMIC is the most resource-intensive cursor type that displays all data changes made to rows in the result set, including newly inserted rows. The data values, order, and membership of rows in each sample can change. You cannot use FETCH ABSOLUTE 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 options.

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 a cursor of this type.

A cursor declared with the OPTIMISTIC keyword does not request a row lock and allows data to be modified. If changes to the underlying table occur after the data is read into the cursor, an attempt to modify that data through the cursor results in an error.

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

Select_statement is a SELECT statement that generates the result set of the cursor.

The FOR UPDATE statement specifies which columns to update in the cursor. 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 when the cursor is declared with the READ_ONLY parameter.

To open and fill the cursor, use the command

OPEN ((cursor_name) I @cursor_variable)

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

The FETCH statement is used to move through the cursor dataset and retrieve the data as variable values:

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 will jump to the first entry in the cursor.

Table 10.10

Navigating the Cursor Dataset

The @@FETCH_STATUS global variable lets you know the result of the last execution of the FETCH statement:

О – the action was completed successfully;

-1 – statement execution failed, or the string was outside the limits of the result set (cursor ended);
-2 – there is no selectable row, for example, if the current record was deleted from the database during the work with the "change-sensitive" type cursor.

The CLOSE statement closes an open cursor, freeing the memory used to store the dataset. Selecting data and moving over a closed cursor is not possible - for 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 it 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 look at a simple example of using a cursor, where authors and titles of books published after 2000 are selected from a table, and the data is looped through the SELECT statements, each time one record with its own title. given by comments in the code:

/*declaring variables*/

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

WHERE >= 2000

/*open the cursor and "run" it, displaying the author and title in a separate SELECT statement*/

FETCH NEXT FROM cursor INTO @auth, @title

WHILESSFETCH_STATUS=0

FETCH NEXT FROM cursor INTO @auth, Stitle

/* close the cursor and release it */

DEALLOCATE cursorl

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

DECLARE Sauth varchar(50), Stitle varchar(50)

/*declaring a cursor type variable*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECT Author, Title FROM dbo.Bookl