Cursors in MySQL stored procedures. DECLARE CURSOR command general rules Ms sql cursors example
Cursor in SQL, an area in database memory that is dedicated to storing the last 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 the application programming interface 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 the INSENSITIVE keyword is missing, a dynamic cursor.
When you specify the SCROLL keyword, the created cursor can be scrolled in any direction, allowing you to 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 of the SELECT query, which determines the result 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.
Specifying LAST returns the most recent row 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.
1) The concept of a cursor
Interactive SQL does not differentiate between single-row and multi-row queries. Embedded SQL executes these queries differently. Single-line queries return one line and we have already covered them. When the result of a query is more than one row, embedded SQL must allow the application to retrieve the query results row by row. Cursors are used for this. A cursor is a variable associated with a query. Its value is each row that matches the query. Like variables, cursors must be declared before they can be used. Unlike views, cursors are designed for line-by-line processing.
2) Cursor declaration
DECLARE [{}] [[NO] SCROLL] CURSOR [{WITH|WITHOUT} HOLD] FOR [FOR {READ ONLY|UPDATE [OF ]}]
3) Keywords
. SENSITIVE|INSENSITIVE|ASENSITIVE– changes in the result set are visible | prohibited (fixed using a copy of the data set)|The DBMS itself decides whether to make a copy (operates by default).
. WITH|WITHOUT HOLD– leaves open | closes the cursor if a COMMIT statement is encountered.
. SCROLL– [prevents] extracting result rows in random order.
. FOR READ ONLY– defines a read-only cursor.
. FOR UPDATE OF– blocks only the specified columns from updating.
4) Declaring a cursor in SQL Server
DECLARE CURSOR [LOCAL|GLOBAL] [FORWARD_ONLY|SCROLL] [STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] FOR [FOR UPDATE [OF ]]
. STATIC– Defines a cursor that creates a temporary copy of data for use by the cursor. All queries against a cursor access the specified temporary table in the tempdb database, so changes to the base tables do not affect the data returned by samples for that cursor, and the cursor itself does not allow changes to be made.
. KEYSET– Indicates that the membership or order of rows in the cursor does not change after it is opened. A set of keys that uniquely identify rows is built into a table in the tempdb database called keyset.
. DYNAMIC– Defines a cursor that displays all data changes made to the rows of the result set when viewing this cursor. The data values, order, and row membership in each selection may vary. The ABSOLUTE selection option is not supported by dynamic cursors.
. FAST_FORWARD– Indicates a FORWARD_ONLY, READ_ONLY cursor for which performance optimization is enabled. The FAST_FORWARD option cannot be specified with the SCROLL or FOR_UPDATE options.
. SCROLL_LOCKS– Indicates that positioned updates or deletes made through the cursor are guaranteed to succeed. SQL Server locks rows as they are read into the cursor to ensure they are available for subsequent changes. The SCROLL_LOCKS option cannot be specified with the FAST_FORWARD or STATIC option.
. OPTIMISTIC– Indicates that positioned updates or deletes made through the cursor will fail if the row has been updated since the time it was read into the cursor. SQL Server does not lock rows as they are read into the cursor. Instead, a comparison is made of the values of the timestamp column (or checksums if the table does not have a timestamp column) to determine whether the row has changed since it was read into the cursor. If a row has been modified, its positioned modification or deletion is not possible. The OPTIMISTIC option cannot be specified with the FAST_FORWARD option.
5) Opening the cursor
6) Retrieving rows from the cursor
FETCH [{NEXT|PRIOR|FIRST|LAST|{ABSOLUTE|RELATIVE }}]
FROM INTO
7) Cursor positioning options
. NEXT|PRIOR|FIRST|LAST– to the next|previous|first|last line of the result set.
. RELATIVE ±N– to a line with a positive or negative offset relative to the current line.
. ABSOLUTE ±N– to a line with an explicitly specified absolute position number from the beginning or end of the cursor.
Note: SQL Server allows the integer variable @N instead of N.
8) Closing the cursor
9) Notes on cursors
. If the cursor contains more than one line, it is necessary to organize a loop for retrieving data from it, periodically checking to reach the last line.
. Unlike tables and views, cursor rows are ordered either explicitly using a section ORDER BY, or in accordance with the conventions adopted in a particular DBMS.
. Cursors are also used to select groups of rows from tables, which can be updated or deleted one at a time.
. For a cursor to be updatable, it must meet the same criteria as the view, that is, not contain sections UNION, ORDER BY, GROUP BY, DISTINCT.
10) Example for deleting data from a cursor
exec sql declare cursor Cur1 for select * from Customer
where Rating
// print (@f1+’ ‘+convert(Varchar(5),@f2))
exec sql delete from Customer
where current of Cur1; ) – Take data to delete from the cursor
not_done:
exec sql close cursor Cur1; — Close the cursor
exit();
11) Example for increasing commissions
exec sql declare cursor CurCust for select * from SalesPeople
where SNum in (select SNum from Customer where Rating=300); — Define the cursor
exec sql open cursor CurCust; - Execute the cursor
while (sqlca.sqlcode==0) ( — Create a loop to update data in the table
exec sql fetch CurCust into:Id_num, :SalesPerson, :Loc, :Comm;
exec sql update SalesPeople set Comm=Comm+.01 where current
of CurCust; ) – Take the data for updating from the cursor
exec sql close cursor CurCust; — Close the cursorSELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
DECLARE Cur1 SCROLL CURSOR FOR SELECT S.Name, MAX(S.City) AS City, SUM(O.Amt) AS Amt FROM SalesPeople S INNER JOIN Orders O ON S.SNum=O.SNum GROUP BY S.Name ORDER BY 2
OPEN Cur1
FETCH NEXT FROM Cur1
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1
DEALLOCATE Cur1
It may well happen that the response to a simple client request will be a sample 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 to use cursors as a universal mechanism for exchanging data between the server and the client. Cursors work with the result set of data (the result of a query), giving users additional data processing capabilities:
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 life cycle:
Creating a cursor: 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 (fetching is carried out every time the row is accessed). The SCROLL keyword means that the cursor can be scrolled in any direction, otherwise the cursor is created as a "sequential" cursor.
Opening cursor: OPEN [GLOBAL]<имя курсора>. A cursor specified as GLOBAL is not automatically deleted when the procedure or package from which it was called ends.
Readingdata : FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ]<имя курсора>[INTO@variable_name,...]. SQL Server 2000 allows you to read just one row from a cursor. The FIRST keyword is to return the first row of the cursor; LAST – last line of the cursor; NEXT – the next line after the current one, the returned line becomes the current one; PRIOR – previous before current; ABSOLUTE n – returns a line by its absolute sequence 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 they are listed.
Change data: executes an UPDATE command with syntax designed for working with cursors.
Deleting data: executes a DELETE command with syntax designed to work with cursors.
Closing the cursor: CLOSE [GLOBAL]<имя курсора>
Release the cursor: DEALLOCATE [GLOBAL]<имя курсора>
An example of using a cursor:
DECLARE fo_curs CURSOR STATIC FOR
SELECT name_rus from fo ORDER BY name_rus
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 for data protection in SQL Server.
SQL Server Security and Administration. .
The main task of a 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 intentionally try to destroy the data structure or change the data itself. Consequently, 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 beyond their authority.
Database management
To create a database using TSQL, use the CREATE DATABASE command, but usually the capabilities of SQL Server Management Studio are 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).
Like most database servers, SQL Server has a user with full administrative rights - 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 the database is created, the user "guest" is also defined. If the user account is not explicitly mapped to a user of a specific database, the user is granted implicit access with using the guest name guest.Guest is usually prohibited.
The user who creates an object in the database automatically becomes its owner, and no one, including dbo and sa, can use that object until the owner assigns them rights to it. But in order for a user to create an object, the database owner must first grant him the appropriate rights.
Role allows you to combine users performing the same functions to simplify administration. Roles can be built-in or custom. Built-in roles are implemented at the server level and at the database level. Below is a table of built-in database roles:
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. Archiver can execute commands. data
db_datareader. Maybe viewing. 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
Assigning rights to users. The basis of SQL Server security is (1) accounts; (2) users; (3) roles; (4) groups.
When a user connects to SQL Server, the actions he can perform are determined by the rights granted to him as a user and a member of a role. Rights are granted by the DBMS administrator, the database owner, or the owner of a specific database object. Rights in the database 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<имя группы> | <имя роли>]
– assigning rights to users, Where
ALL – the user is granted all possible permissions, otherwise specify
<вид действия>– rights to actions available to the user, namely:
SELECT – for view, for table column and for table (view)
INSERT – to add, for the table (view) as a whole
UPDATE – for change, for a table column and for a table (view)
DELETE – to delete, for the table (view) as a whole
EXECUTE – to execute stored procedures
REFERENCES – the ability to refer to a specified object (include it as part of a foreign key).
<имя объекта системы безопасности>– SQL Server accounts, Windows domain users; PUBLIC – for all users.
WITH GRANT OPTION - allows the user who is currently granted rights to assign access rights to the object to other users.
AS<имя группы> | <имя роли>– participation of a user in a role that is given the ability 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 a member of the Accounting role, but someone in that role can grant permission
DENY(ALL |< вид действия >,…}
( ON (<имя таблицы или представления>} [(<имя столбца>,…)]
| ON (<имя хранимой процедуры>}
| ON (<имя пользовательской функции>}
TO ( PUBLIC |<имя объекта системы безопасности>,…}
– access denial users to database objects. CASCADE revokes rights not only from this user, but also from everyone to whom he granted rights.
Example (on command prohibition TSQL):
DENY CREATE TABLE TO Jack CASCADE
Team REVOKE used to implicitly deny access to database objects. The syntax is the same as the DENY command. Implicit denial is similar to denying access, except that it only applies at the level at which it is defined. Example: The user Jack, who is a member of the GoodUsers role, has been granted access rights to the XFiles table. If REVOKE is denied for the GoodUsers role to access this table, Jack can still access this table because the rights for him are explicitly defined. If you use REVOKE personally for him, 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 denial.
Data protection technologies in MS SQL Server
1.Mechanism checkpoints– checkpoints that are generated after ~60 s to write updated pages to disk (a 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 using the operating system (including the mechanism of mirrored hard drives).
4. Backing up databases and transaction logs - by writing a database dump to a backup device (magnetic tape or hard drive).
5. Replication – the ability to duplicate information by periodically (in some cases, synchronously) transferring it from one SQL server to another.
6. Encryption of traffic between the client and server, as well as encryption of codes used to work with database objects (stored procedures, triggers, etc.)
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 with 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 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 with RETURN clause. The resulting rowset of this cursor contains all the data in the employee table for department ID 10:
Cursor name
An explicit cursor name must be up to 30 characters long and follow the same rules as others 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 defined in it without repeating the same code in different places in the application. Implementing the query 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 the new version of the books_cur cursor declaration 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. section.
If a program executes a FOR loop using a cursor, the cursor does not need to be opened (fetched, closed) explicitly. 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 to sample data.
- Fetching data from a cursor into a PL/SQL record:
- Fetching data from a cursor into a variable:
- Fetching data from a cursor into a PL/SQL table row, variable, and Oracle Forms bind variable:
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 the 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. For some interesting 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):
- 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 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 attempt 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 given examples of using parameters procedures 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 parameters are used in a query instead of hard-coded values, the resulting set of cursor rows is not tied to a specific program or block variable. 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 a 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 with 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.