Sounds and Music 

What is a sql cursor. Cursors. Modifying and deleting rows via cursors


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 associated current record pointer. 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, it's 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 retrieve 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 whether 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 whether 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.

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

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

Working with a cursor usually involves the following steps:

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

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

FOR select_statement

Extended Transact-SQL syntax:

DECLARE cursor_name CURSOR

FOR select_statement

]][;]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

To open and fill the cursor, use the command

OPEN (( cursor_name) I @cursor_variable)

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

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

FETCH [

(( cursor_name] I @cursor_variable]

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

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

Table 10.10

Navigating a Cursor Dataset

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

O – action completed successfully;

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

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

CLOSE (( cursor_name)|@cursor_variable )

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

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

/*declare variables*/

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

WHERE >= 2000

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

FETCH NEXT FROM cursorl INTO @auth, @title

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM cursorl INTO @auth, Stitle

/*close the cursor and release it*/

DEALLOCATE cursorl

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

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

/*declare a variable of type cursor*/

DECLARE Scurl CURSOR

DECLARE cursorl CURSOR FAST_FORWARD

SELECT Author, Title FROM dbo.Bookl

WHERE >= 2000

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

SET Scurl = cursorl

WHILE SSFETCH_STATUS = 0

FETCH NEXT FROM Scurl INTO South, Stitle

An explicit cursor is a SELECT command explicitly defined in the declaration section of a program. When you declare an explicit cursor, it is given a name. Explicit cursors cannot be defined for INSERT, UPDATE, MERGE, and DELETE commands.

By defining the SELECT command as an explicit cursor, the programmer has control over the major stages of retrieving information from the Oracle database. It determines when to open the cursor (OPEN), when to select rows from it (FETCH), how many rows to select, and when to close the cursor using the CLOSE command. Information about the current state of the cursor is available through its attributes. It is this high granularity of control that makes explicit cursors an invaluable tool for the programmer.

Let's look at an example:

1 FUNCTION jealousy_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR jealousy_cur 5 IS 6 SELECT location FROM friends 7 WHERE NAME = UPPER (NAME_IN); 8 8 jealousy_rec jealousy_cur%ROWTYPE; 9 retval NUMBER; 10 BEGIN 11 OPEN jealousy_cur; 13 12 FETCH jealousy_cur INTO jealousy_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 CLOSE jealousy_cur; 26 22 RETURN retval; 23 EXCEPTION 24 WHEN OTHERS THEN 25 IF jealousy_cur%ISOPEN THEN 26 CLOSE jealousy_cur; 27 END IF; 28 END;

The next few sections discuss each of these operations in detail. The term "cursor" in them refers to explicit cursors, unless the text explicitly states otherwise.

Declaring an Explicit Cursor

To be able to use an explicit cursor, it must be declared in the declaration section of the PL/SQL block or package:

CURSOR cursor_name [ ([ parameter [, parameter...]) ] [ RETURN specification_reEirn ] IS SELECT_command ];

Here the cursor name is the name of the declared cursor; spiifiction_te?it - optional RETURN section; KOMaHdaSELECT - any valid SQL SELECT command. Parameters can also be passed to the cursor (see the “Cursor Parameters” section below). Finally, after the SELECT...FOR UPDATE command, you can specify a list of columns to update (also see below). After the declaration, the cursor is opened with the OPEN command, and rows are retrieved from it using the FETCH command.

Some examples of explicit cursor declarations.

  • Cursor without parameters. The resulting set of rows from this cursor is the set of company IDs selected from all the rows in the table:
CURSOR company_cur IS SELECT company_id FROM company;
  • Cursor with parameters. The resulting rowset of this cursor contains a single row with the company name corresponding to the value of the passed parameter:
CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;
  • Cursor with RETURN clause. The resulting rowset of this cursor contains all the data in the employee table for department ID 10:
CURSOR emp_cur RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE department_id = 10;

Cursor name

An explicit cursor name must be up to 30 characters long and follow the same rules as other PL/SQL identifiers. The cursor name is not a variable - it is the identifier of the pointer to the request. The cursor name is not assigned a value and cannot be used in expressions. The cursor is used only in the OPEN, CLOSE and FETCH commands, and to qualify the cursor attribute.

Declaring a cursor in a package

Explicit cursors are declared in the declaration section of a PL/SQL block. A cursor can be declared at the package level, but not within a specific package procedure or function. An example of declaring two cursors in a package:

PACKAGE book_info IS CURSOR titles_cur IS SELECT title FROM books; CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

The first titles_cur cursor returns only book titles. The second, books_cur , returns all rows of the books table in which the book names match the pattern specified as the cursor parameter (for example, "All books containing the string 'PL/SQL'"). Note that the second cursor uses a RETURN section, which declares the data structure returned by the FETCH command.

The RETURN section can contain any of the following data structures:

  • A record defined from a data table row using the %ROWTYPE attribute.
  • An entry defined from another, previously declared cursor, also using the %rowtype attribute.
  • A programmer-defined entry.

The number of expressions in the cursor selection list must match the number of columns in the table_name%ROWTYPE, Kypcop%ROWTYPE, or record type record. The data types of the elements must also be compatible. For example, if the second element of the select list is of type NUMBER, then the second column of the entry in the RETURN section cannot be of type VARCHAR2 or BOOLEAN.

Before moving on to a detailed examination of the RETURN section and its advantages, let's first understand why it might be necessary to declare cursors in a package? Why not declare an explicit cursor in the program in which it is used - in a procedure, function or anonymous block?

The answer is simple and convincing. By defining a cursor in a package, you can reuse the query specified in it without repeating the same code in different places applications. Implementing the request in one place simplifies its modification and code maintenance. Some time savings are achieved by reducing the number of requests processed.

It's also worth considering creating a function that returns a cursor variable based on REF CURSOR . The calling program fetches rows through a cursor variable. For more information, see the "Cursor Variables and REF CURSOR" section.

When declaring cursors in reusable packages, there is one important thing to consider. All data structures, including cursors, declared at the “package level” (not inside a specific function or procedure), retain their values ​​throughout the session. This means that the batch cursor will remain open until you explicitly close it, or until the session ends. Cursors declared in local blocks are automatically closed when those blocks complete.

Now let's look at the RETURN section. One interesting thing about declaring a cursor in a package is that the header of the cursor can be separated from its body. This header, more reminiscent of a function header, contains information that the programmer needs to work: the name of the cursor, its parameters and the type of data returned. The body of the cursor is the SELECT command. This technique is demonstrated in new version declarations of the books_cur cursor in the book_info package:

PACKAGE book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; END; PACKAGE BODY book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

All characters before the IS keyword form a specification, and after IS comes the cursor body. Splitting the cursor declaration can serve two purposes.

  • Hiding information. The cursor in the package is a “black box”. This is convenient for programmers because they don't have to write or even see the SELECT command. It is enough to know what records this cursor returns, in what order and what columns they contain. A programmer working with the package uses the cursor like any other ready-made element.
  • Minimum recompilation. By hiding the query definition in the body of the package, changes to the SELECT command can be made without changing the cursor header in the package specification. This allows code to be improved, corrected, and recompiled without recompiling the package specification, so that programs that depend on that package will not be marked as invalid and will not need to be recompiled.

Opening an Explicit Cursor

Using a cursor starts with defining it in the declarations section. Next, the declared cursor must be opened. The syntax for the OPEN statement is very simple:

OPEN cursor_name [ (argument [, argument...]) ];

Here, cursorname is the name of the previously declared cursor, and argument is the value passed to the cursor if it is declared with a list of parameters.

Oracle also supports FOR syntax when opening a cursor, which is used for both cursor variables (see "Cursor Variables and REF CURSOR" section) and embedded dynamic SQL.

When PL/SQL opens a cursor, it executes the query it contains. In addition, it identifies the active data set - the rows of all tables participating in the query that match the WHERE criterion and the join condition. The OPEN command does not retrieve data - that is the job of the FETCH command.

Regardless of when the first data fetch occurs, Oracle's data integrity model ensures that all fetch operations return data in the state at which the cursor was opened. In other words, from opening to closing the cursor, when retrieving data from it, the insert, update, and delete operations performed during this time are completely ignored.

Moreover, if the SELECT command contains a FOR UPDATE section, all rows identified by the cursor are locked when the cursor is opened.

If you try to open a cursor that is already open, PL/SQL will throw the following error message:

ORA-06511: PL/SQL: cursor already open

Therefore, before opening the cursor, you should check its state using the attribute value %isopen:

IF NOT company_cur%ISOPEN THEN OPEN company_cur; ENDIF;

The attributes of explicit cursors are described below in the section dedicated to them.

If the program executes FOR loop using a cursor, this cursor does not need to be opened explicitly (fetching data, closing). The PL/SQL engine does this automatically.

Fetching data from an explicit cursor

The SELECT command creates a virtual table - a set of rows defined by a WHERE clause with columns defined by a list of SELECT columns. Thus, the cursor represents this table in the PL/SQL program. The primary purpose of a cursor in PL/SQL programs is to select rows for processing. Fetching cursor rows is done with the FETCH command:

FETCH cursor_name INTO record_or_variable_list;

Here, cursor name is the name of the cursor from which the record is selected, and the record or variable list is the PL/SQL data structures into which the next row of the active recordset is copied. Data can be placed in a PL/SQL record (declared with the %ROWTYPE attribute or TYPE declaration) or in variables (PL/SQL variables or bind variables - such as in Oracle Forms elements).

Examples of explicit cursors

The following examples demonstrate different ways data samples.

  • Fetching data from a cursor into a PL/SQL record:
DECLARE CURSOR company_cur is SELECT ...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec;
  • Fetching data from a cursor into a variable:
FETCH new_balance_cur INTO new_balance_dollars;
  • Fetching data from a cursor into a PL/SQL table row, variable, and Oracle Forms bind variable:
FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

Data fetched from a cursor should always be placed in a record declared under the same cursor with the %ROWTYPE attribute; Avoid selecting lists of variables. Fetching into a record makes the code more compact and flexible, allowing you to change the fetch list without changing the FETCH command.

Sampling after processing the last row

Once you open the cursor, you select lines from it one by one until they are all exhausted. However, you can still issue the FETCH command after this.

Oddly enough, PL/SQL does not throw an exception in this case. He just doesn't do anything. Since there is nothing else to select, the values ​​of the variables in the INTO section of the FETCH command are not changed. In other words, the FETCH command does not set these variables to NULL.

Explicit cursor column aliases

The SELECT statement in the cursor declaration specifies the list of columns it returns. Along with table column names, this list can contain expressions called calculated or virtual columns.

A column alias is an alternative name specified in the SELECT command for a column or expression. By defining appropriate aliases in SQL*Plus, you can display the results of an arbitrary query in human-readable form. In these situations, aliases are not necessary. On the other hand, when using explicit cursors, calculated column aliases are needed in the following cases:

  • when retrieving data from a cursor into a record declared with the %ROWTYPE attribute based on the same cursor;
  • when a program contains a reference to a calculated column.

Consider the following query. The SELECT command selects the names of all companies that ordered goods during 2001, as well as the total amount of orders (assuming the default formatting mask for the current database instance is DD-MON-YYYY):

SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

Running this command in SQL*Plus will produce the following output:

COMPANY_NAME SUM(INV_AMT)
ACME TURBO INC. 1000
WASHINGTON HAIR CO. 25.20

As you can see, the column header SUM (INV_AMT) is not well suited for a report, but it is fine for simply viewing data. Now let's run the same query in a PL/SQL program using an explicit cursor and add a column alias:

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM company C, invoice I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; END;

Without the alias, I won't be able to reference the column in the comp_rec record structure. If you have an alias, you can work with a calculated column just like you would with any other query column:

IF comp_rec.total_sales > 5000 THEN DBMS_OUTPUT.PUT_LINE (" You have exceeded your credit limit of $5000 by " || TO_CHAR (comp_rec.total_sales - 5000, "$9999")); ENDIF;

When selecting a row into a record declared with the %ROWTYPE attribute, the calculated column can only be accessed by name - because the structure of the record is determined by the structure of the cursor itself.

Closing an explicit cursor

Once upon a time in childhood we were taught to clean up after ourselves, and this habit remained with us (although not everyone) for the rest of our lives. It turns out that this rule plays an extremely important role in programming, and especially when it comes to managing cursors. Never forget to close your cursor when you no longer need it!

CLOSE command syntax:

CLOSE cursor_name;

Below are some important tips and considerations related to closing explicit cursors.

  • If a cursor is declared and opened in a procedure, be sure to close it when you are done with it; otherwise your code will leak memory. In theory, a cursor (like any data structure) should be automatically closed and destroyed when it goes out of scope. Typically, when exiting a procedure, function, or anonymous block, PL/SQL actually closes all open cursors within it. But this process is resource-intensive, so for efficiency reasons, PL/SQL sometimes delays identifying and closing open cursors. Cursors of type REF CURSOR, by definition, cannot be closed implicitly. The only thing you can be sure of is that when the "outermost" PL/SQL block completes and control is returned to SQL or another calling program, PL/SQL will implicitly close all cursors opened by that block or nested blocks. except REF CURSOR . The article "Cursor reuse in PL/SQL static SQL" from the Oracle Technology Network provides a detailed analysis of how and when PL/SQL closes cursors. Nested anonymous blocks are an example of a situation in which PL/SQL does not implicitly close cursors. Interesting information For more information on this topic, see Jonathan Gennick's article “Does PL/SQL Implicitly Close Cursors?”
  • If a cursor is declared in a package at the package level and is open in some block or program, it will remain open until you explicitly close it or until the session ends. Therefore, after finishing working with a batch level cursor, you should immediately close it with the CLOSE command (and by the way, the same should be done in the exceptions section):
BEGIN OPEN my_package.my_cursor; ... Working with the cursor CLOSE my_package.my_cursor; EXCEPTION WHEN OTHERS THEN IF mypackage.my_cursor%ISOPEN THEN CLOSE my_package.my_cursor; ENDIF; END;
  • The cursor can only be closed if it was previously open; otherwise, an INVALID_CURS0R exception will be thrown. The cursor state is checked using the %ISOPEN attribute:
IF company_cur%ISOPEN THEN CLOSE company_cur; ENDIF;
  • If there are too many open cursors left in the program, the number of cursors may exceed the value of the OPEN_CURSORS database parameter. If you receive an error message, first make sure that the cursors declared in the packages are closed when they are no longer needed.

Explicit Cursor Attributes

Oracle supports four attributes (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) to obtain information about the state of an explicit cursor. An attribute reference has the following syntax: cursor%attribute

Here cursor is the name of the declared cursor.

The values ​​returned by explicit cursor attributes are shown in Table. 1.

Table 1. Explicit Cursor Attributes

The values ​​of cursor attributes before and after performing various operations with them are shown in Table. 2.

When working with explicit cursor attributes, consider the following:

  • If you try to access the %FOUND, %NOTFOUND, or %ROWCOUNT attribute before the cursor is opened or after it is closed, Oracle throws an INVALID CURSOR exception (ORA-01001).
  • If the first time the FETCH command is executed, the resulting rowset is empty, the cursor attributes return the following values: %FOUND = FALSE , %NOTFOUND = TRUE , and %ROWCOUNT = 0 .
  • When using BULK COLLECT, the %ROWCOUNT attribute returns the number of rows retrieved into the given collections.

Table 2. Cursor Attribute Values

Operation %FOUND %NOTFOUND %ISOPEN %ROWCOUNT
Before OPEN Exception
ORA-01001		 Exception
ORA-01001		 FALSE Exception
ORA-01001
After OPEN NULL NULL TRUE 0
Before the first FETCH sample NULL NULL TRUE 0
After the first sample
FETCH		 TRUE FALSE TRUE 1
Before subsequent
FETCH		 TRUE FALSE TRUE 1
After subsequent FETCH TRUE FALSE TRUE Depends on data
Before the last FETCH sample TRUE FALSE TRUE Depends on data
After the last FETCH sample TRUE FALSE TRUE Depends on data
Before CLOSE FALSE TRUE TRUE Depends on data
After CLOSE Exception Exception FALSE Exception

The use of all these attributes is demonstrated in the following example:

Previous blogs have repeatedly provided examples of using procedure parameters and functions. Parameters are a means of passing information to and from a program module. When used correctly, they make modules more useful and flexible.

PL/SQL allows you to pass parameters to cursors. They perform the same functions as the parameters of the software modules, as well as several additional ones.

  • Expanding cursor reuse capabilities. Instead of hard-coding the values ​​that define the data selection conditions into the WHERE clause, you can use parameters to pass new values ​​into the WHERE clause each time the cursor is opened.
  • Troubleshooting Cursor Scope Issues. If the query uses parameters instead of hard-coded values, the resulting set of cursor rows is not tied to a specific program variable or block. If your program has nested blocks, you can define a cursor at the top level and use it in nested blocks with variables declared in them.

The number of cursor parameters is unlimited. When OPEN is called, all parameters (except those that have default values) must be specified for the cursor.

When does a cursor require parameters? The general rule here is the same as for procedures and functions: if the cursor is expected to be used in different places and with different values ​​in the WHERE section, a parameter should be defined for it. Let's compare cursors with and without the parameter. Example of a cursor without parameters:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM Jokes;

The cursor's result set includes all entries in the joke table. If we only need a certain subset of rows, the WHERE section is included in the query:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM jokes WHERE category = "HUSBAND";

To perform this task, we did not use parameters, and they are not needed. In this case, the cursor returns all rows that belong to a specific category. But what if the category changes every time you access this cursor?

Cursors with parameters

Of course, we wouldn't define a separate cursor for each category—that would be completely inconsistent with how data-driven application development works. We only need one cursor, but one for which we could change the category - and it would still return the required information. And the best (although not the only) solution to this problem is to define a parameterized cursor:

PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Cursor with a list of parameters consisting of || from a single string parameter. */ CURSOR joke_cur (category_in IN VARCHAR2) IS SELECT name, category, last_used_date FROM Joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Now, when opening the cursor, an argument is passed to it */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec;

Between the cursor name and the IS keyword there is now a list of parameters. The hard-coded HUSBAND value in the WHERE clause has been replaced by a reference to the UPPER parameter (category_in). When you open the cursor, you can set the value to HUSBAND , husband or HuSbAnD - the cursor will still work. The name of the category for which the cursor should return joke table rows is specified in the OPEN statement (in parentheses) as a literal, constant, or expression. When the cursor is opened, the SELECT command is parsed and the parameter is associated with the value. The resulting set of rows is then determined and the cursor is ready for fetching.

Opening a cursor with options

A new cursor can be opened indicating any category:

OPEN joke_cur(Jokes_pkg.category); OPEN joke_cur("husband"); OPEN joke_cur("politician"); OPEN joke_cur (Jokes_pkg.relation || "-IN-LAW");

Cursor parameters are most often used in the WHERE clause, but they can be referenced elsewhere in the SELECT statement:

DECLARE CURSOR joke_cur (category_in IN ARCHAR2) IS SELECT name, category_in, last_used_date FROM joke WHERE category = UPPER (category_in);

Instead of reading the category from the table, we simply substitute the category_in parameter into the select list. The result remains the same because the WHERE clause restricts the sample category to the parameter value.

Cursor parameter scope

The scope of a cursor parameter is limited to that cursor. A cursor parameter cannot be referenced outside of the SELECT command associated with the cursor. The following PL/SQL snippet does not compile because program_name is not a local variable in the block. This is a formal cursor parameter that is defined only inside the cursor:

DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_name:= "THE BREATHING MUMMY"; /* Invalid link */ OPEN scariness_cur (program_name); .... CLOSE scariness_cur; END;

Cursor Parameter Modes

The syntax for cursor parameters is very similar to that of procedures and functions - except that cursor parameters can only be IN parameters. Cursor parameters cannot be set to OUT or IN OUT modes. These modes allow values ​​to be passed and returned from procedures, which makes no sense for a cursor. There is only one way to get information from the cursor: fetching a record and copying the values ​​from the list of columns in the INTO section

Default Parameter Values

Cursor parameters can be assigned default values. An example of a cursor with a default parameter value:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;

Because the emp_id_in parameter has a default value, it can be omitted in the FETCH command. In this case, the cursor will return information about the employee with code 0.

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:

  1. First, you declare your variables that you need in the logic.
  2. Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
  3. Third, you fetch a record from cursor to begin the data processing.
  4. 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.
  5. 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.
  6. Sixth, once all of the data has been processed, then you close cursor.
  7. 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

Recommended Reading

Learn more about SQL Server Cursors and alternatives:

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 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.

Cursor in SQL, this is an area in database memory that is designed to store the latest SQL statement. If the current statement is a database query, a string of query data called the current value, or current row, is also stored in memory. cursor. The specified area in memory is named and accessible to application programs.

According to the SQL standard when working with cursors the following main ones can be identified actions:

  • creation or cursor declaration ;
  • opening the cursor, those. filling it with data that is stored in multi-level memory;
  • selection from cursor And change using it data lines;
  • closing the cursor, after which it becomes inaccessible to user programs;
  • freeing the cursor, i.e. deletion cursor as an object, because it closing does not necessarily free the memory associated with it.

SQL Server supports three type of cursors:

  • cursors SQL is used primarily within triggers, stored procedures, and scripts;
  • cursors servers act on the server and implement software interface applications for ODBC, OLE DB, DB_Library;
  • cursors client 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.

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 change rows of data using a cursor;
  • CLOSE – closing the cursor ;
  • DEALLOCATE – freeing the cursor, i.e. deleting the cursor as an object.

Cursor Declaration

In the SQL standard for creating cursor The following command is provided:

<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ])]

Using the INSENSITIVE keyword will create static cursor. Data changes are not allowed, in addition, are not displayed changes, made by other users. If keyword INSENSITIVE missing, created dynamic cursor.



When you specify the SCROLL keyword, the created cursor can be scrolled in any direction, allowing you to apply any commands samples. If this argument is omitted, then cursor it turns out consistent, i.e. its viewing will be possible only in one direction - from beginning to end.

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

Specifying the FOR READ_ONLY argument creates cursor"read-only" and no modification of the data is allowed. It is different from static, although the latter also does not allow you to change data. Can be declared as a read-only cursor dynamic cursor, which will allow you to display changes, made by another user.

Creation cursor with the FOR UPDATE argument allows you to execute in 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 creation command is accepted cursor:

<создание_курсора>::= DECLARE cursor_name CURSOR FOR SELECT_statement ]]

Using the LOCAL keyword will create a local cursor, which 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 completes cursor is implicitly destroyed. To transfer content cursor outside the construct that created it, you must assign its parameter an OUTPUT argument.

If the GLOBAL keyword is specified, a global cursor; it exists until the current connection is closed.

Specifying FORWARD_ONLY creates serial cursor ; sample data can only be processed in the direction from the first line 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 for cursor READ_ONLY specify the FAST_FORWARD argument then created cursor will be optimized for fast data access. This argument cannot be used in conjunction with the FORWARD_ONLY or OPTIMISTIC arguments.

IN cursor, created with the OPTIMISTIC argument, is prohibited change And deleting rows that were changed after opening the cursor.

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

Opening the cursor

For opening the cursor and filling it with data from the one specified during creation cursor The SELECT query uses the following command:

OPEN ((cursor_name) |@cursor_variable_name)

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:

FETCH [ FROM ]((cursor_name)| @cursor_variable_name) ]

Specifying FIRST will return the very first row of the complete result set cursor, which becomes the current line.

When LAST is specified, the most last line 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. samples lines.

The PRIOR keyword returns the row before the current one. It becomes current.

ABSOLUTE (row_number | @row_number_variable) returns the row by its absolute index number in the complete result set 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.

The RELATIVE argument (number of rows | @variable number of rows) returns the row that is the specified number of rows 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. Name cursor can also be specified using a variable.

The INTO @variable_name [,...n] construction specifies a list of variables in which the corresponding column values ​​of the returned string will be stored. The order of the variables must match the order of the columns in cursor, and the data type of the variable is the data type in the column cursor. 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.