Why cursors are used in pl sql

Example 1 : In this example, we will project all the employee name from emp table using a cursor-FOR loop. Skip to content.

Report a Bug. Previous Prev. Next Continue. Home Testing Expand child menu Expand. SAP Expand child menu Expand. Web Expand child menu Expand.

Must Learn Expand child menu Expand. This technique can be very fast, but also very memory-intensive. If you use it often, you might be able to improve your code by doing more of the work in SQL:. If you only need to loop once through the result set, use a FOR loop as described in the following sections.

This technique avoids the memory overhead of storing a copy of the result set. If you are looping through the result set to scan for certain values or filter the results into a smaller set, do this scanning or filtering in the original query instead.

If you are looping through the result set and running another query or a DML statement for each result row, you can probably find a more efficient technique. Perhaps the most common case of a query is one where you issue the SELECT statement, then immediately loop once through the rows of the result set.

The iterator variable for the FOR loop does not need to be declared in advance. When you use expressions rather than explicit column names, use column aliases so that you can refer to the corresponding values inside the loop:. You might want to specify a query in one place but retrieve the rows somewhere else, even in another subprogram. Or you might want to process some rows differently than others, and so need more than a simple loop.

Because explicit cursors are so flexible, you can choose from different notations depending on your needs. The following sections describe all the query-processing features that explicit cursors provide. In traditional database programming, you process query results using an internal data structure called a cursor. You refer to the fields of this record variable inside the loop. You can perform tests and calculations, display output, or store the results somewhere else.

It does a query to get the name and status of every index that you can access. The sequence of statements inside the loop is executed once for each row that satisfies the query. When you leave the loop, the cursor is closed automatically. The cursor is closed even if you use an EXIT or GOTO statement to leave the loop before all rows are fetched, or an exception is raised inside the loop.

IIf you need to reference the same query from different parts of the same procedure, you can declare a cursor that specifies the query, and process the results using a FOR loop.

The select list might contain an expression, such as a column plus a constant, or two columns concatenated together. If so, use a column alias to give unique names to the appropriate columns.

First, you initialize the cursor with the OPEN statement, which identifies the result set. This technique requires more code than other techniques such as the implicit cursor FOR loop. Its advantage is flexibility. You can:.

Process multiple rows in a single loop iteration, skip rows, or split the processing into more than one loop.

You must declare a cursor before referencing it in other statements. You give the cursor a name and associate it with a specific query. You can optionally specify parameters that you use in the WHERE clause instead of referring to local variables.

These parameters can have default values. Cursors and variables follow the same scoping rules. Naming cursors after database tables is possible but not recommended. A cursor can take parameters, which can appear in the associated query wherever constants can appear. The formal parameters of a cursor must be IN parameters; they supply values in the query, but do not return any values from the query.

As the example below shows, you can initialize cursor parameters to default values. You can pass different numbers of actual parameters to a cursor, accepting or overriding the default values as you please. Also, you can add new formal parameters without having to change existing references to the cursor. Cursor parameters can be referenced only within the query specified in the cursor declaration. The parameter values are used by the associated query when the cursor is opened.

Opening the cursor executes the query and identifies the result set, which consists of all rows that meet the query search criteria. An example of the OPEN statement follows:. Each fetch retrieves the current row and advances the cursor to the next row in the result set. For each column value returned by the query associated with the cursor, there must be a corresponding, type-compatible variable in the INTO list.

Any variables in the query are evaluated only when the cursor is opened. In the following example, each retrieved salary is multiplied by 2 , even though factor is incremented after every fetch:. To change the result set or the values of variables in the query, you must close and reopen the cursor with the input variables set to their new values.

However, you can use a different INTO list on separate fetches with the same cursor. Each fetch retrieves another row and assigns values to the target variables, as the following example shows:. If you fetch past the last row in the result set, the values of the target variables are undefined. When that happens, no exception is raised. For more information, see "Using Cursor Expressions". In the following example, you bulk-fetch from a cursor into two collections:.

Once a cursor is closed, you can reopen it, which runs the query again with the latest values of any cursor parameters and variables referenced in the WHERE clause. A subquery is a query usually enclosed by parentheses that appears within another SQL data manipulation statement.

The statement acts upon the single value or set of values returned by the subquery. For example:. This technique can avoid joins. You can use a subquery in place of a table name, in the FROM clause of a query. This technique lets you join a table with a small set of rows from another table, instead of joining the entire tables. Using a subquery in the FROM clause, the following query returns the number and name of each department with five or more employees:.

While a subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row. The following example returns the name and salary of each employee whose salary exceeds the departmental average. For each row in the table, the correlated subquery computes the average salary for the corresponding epartment. Instead of referring to local variables, you can declare a cursor that accepts parameters, and pass values for those parameters when you open the cursor. If the query is usually issued with certain values, you can make those values the defaults.

You can use either positional notation or named notation to pass the parameter values. The following example computes the total wages paid to employees in a specified department. Example Passing Parameters to Explicit Cursors. For example, here are several ways to open a cursor:.

Formal parameters declared with a default value do not need a corresponding actual parameter. If you omit them, they assume their default values when the OPEN statement is executed. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement.

You can use cursor attributes in procedural statements but not in SQL statements. Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set.

To be safe, you might want to use the following EXIT statement instead:. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. Table Cursor Attribute Values. Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. A cursor variable is more flexible because it is not tied to a specific query.

You can open a cursor variable for any query that returns the right set of columns. You pass a cursor variable as a parameter to local and stored subprograms.

Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. Or, you can pass cursor variables back and forth between a client and the database server through remote procedure calls.

Cursor variables are like pointers to result sets. In Example , we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table. Example Using a Cursor Expression. You can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. Cursor subqueries are often used with table functions, which are explained in "Setting Up Transformations with Pipelined Functions".

Transaction processing is an Oracle feature, available through all programming languages, that lets multiple users work on the database concurrently, and ensures that each user sees a consistent version of data and that all changes are applied in the right order. You usually do not need to write extra code to prevent problems with multiple users accessing data concurrently.

Oracle uses locks to control concurrent access to data, and locks only the minimum amount of data necessary, for as little time as possible. You can request locks on tables or rows if you really do need this level of control. You can choose from several modes of locking such as row share and exclusive. For information on transactions, see Oracle Database Concepts. The COMMIT statement ends the current transaction, making any changes made during that transaction permanent, and visible to other users.

A block can contain multiple transactions, and a transaction can span multiple blocks. Example illustrates a transaction that transfers money from one bank account to another.

It is important that the money come out of one account, and into the other, at exactly the same moment. Otherwise, a problem partway through might make the money be lost from both accounts or be duplicated in both accounts. If a network or machine fails during the commit, the state of the distributed transaction might be unknown or in doubt. This option specifies the priority with which the redo information generated by the commit operation is written to the redo log.

For information about distributed transactions, see Oracle Database Concepts. If you make a mistake, such as deleting the wrong row from a table, a rollback restores the original data.

If you cannot finish a transaction because an exception is raised or a SQL statement fails, a rollback lets you take corrective action and perhaps start over. Example inserts information about an employee into three different database tables. Savepoints let you roll back part of a transaction instead of the whole transaction. The number of active savepoints for each session is unlimited. Example marks a savepoint before doing an insert.

In that case, you roll back to the savepoint, undoing just the insert. When you roll back to a savepoint, any savepoints marked after that savepoint are erased.

The savepoint to which you roll back is not erased. A simple rollback or commit erases all savepoints. If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent, but you can only roll back to the most recently marked savepoint. Savepoint names are undeclared identifiers. Reusing a savepoint name within a transaction moves the savepoint from its old position to the current point in the transaction.

This means that a rollback to the savepoint affects only the current part of your transaction, as shown in Example If the statement fails, Oracle rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction. If the statement raises an unhandled exception, the host environment determines what is rolled back.

Oracle can also roll back single SQL statements to break deadlocks. Oracle signals an error to one of the participating transactions and rolls back the current statement in that transaction. Before executing a SQL statement, Oracle must parse it, that is, examine it to make sure it follows syntax rules and refers to valid schema objects. Errors detected while executing a SQL statement cause a rollback, but errors detected while parsing the statement do not. You should explicitly commit or roll back every transaction.

If you do not commit or roll back a transaction explicitly, the client environment determines its final state. Read-only transactions are useful for running multiple queries while other users update the same tables.

During a read-only transaction, all queries refer to the same snapshot of the database, providing a multi-table, multi-query, read-consistent view.

Other users can continue to query or update data as usual. A commit or rollback ends the transaction. In Example a store manager uses a read-only transaction to gather order totals for the day, the past week, and the past month. The totals are unaffected by other users updating the database during the transaction. By default, Oracle locks data structures for you automatically, which is a major strength of the Oracle database: different applications can read and write to the same data without harming each other's data or coordinating with each other.

You can request data locks on specific rows or entire tables if you need to override default locking. Explicit locking lets you deny access to data for the duration of a transaction. An example follows:. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock.

All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. For a workaround, see "Fetching Across Commits".

For example, the following query locks rows in the employees table but not in the departments table:. Row share locks allow concurrent access to a table; they prevent other users from locking the entire table for exclusive use. Table locks are released when your transaction issues a commit or rollback. The lock mode determines what other locks can be placed on the table. For example, many users can acquire row share locks on a table at the same time, but only one user at a time can acquire an exclusive lock.

While one user has an exclusive lock on a table, no other users can insert, delete, or update rows in that table. A table lock never keeps other users from querying a table, and a query never acquires a table lock.

Only if two different transactions try to modify the same row will one transaction wait for the other to complete. Select the rowid of each row into a UROWID variable, then use the rowid to identify the current row during subsequent updates and deletes.

The extra space needed for read consistency is not released until the cursor is closed, which can slow down processing for large updates. An autonomous transaction is an independent transaction started by another transaction, the main transaction.

Autonomous transactions do SQL operations and commit or roll back, without committing or rolling back the main transaction. For example, if you write auditing data to a log table, you want to commit the audit data even if the operation you are auditing later fails; if something goes wrong recording the audit data, you do not want the main operation to be rolled back.

Figure shows how control flows from the main transaction MT to an autonomous transaction AT and back again. Figure Transaction Control Flow.

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back. More important, autonomous transactions help you build modular, reusable software components.

You can encapsulate autonomous transactions within stored procedures. A calling application does not need to know whether operations done by that stored procedure succeeded or failed. In this context, the term routine includes. You can code the pragma anywhere in the declarative section of a routine.

But, for readability, code the pragma at the top of the section. Example marks a packaged function as autonomous. You cannot use the pragma to mark all subprograms in a package or all methods in an object type as autonomous. Only individual routines can be marked autonomous. Example Declaring an Autonomous Function in a Package.

Example Declaring an Autonomous Standalone Procedure. Example marks a database trigger as autonomous. Example Declaring an Autonomous Trigger. Although an autonomous transaction is started by another transaction, it is not a nested transaction:.

It does not depend on the main transaction. Record variables can be defined based on tables and views. Record variables can also be defined based on cursors. When a record variable is defined based on a cursor, the record member name is actually the column name and the column alias of the SELECT statement. In order to simplify the data processing of display cursors, it is recommended that developers use record variables to store cursor data.

For example:. This cursor does not need to be declared, opened, and closed. As with the display cursor, variables with keyword into receive data with the same data type as the list. A quick way to use cursor for loop and display cursor.