Typical Cursor Applications in PL/SQL

Cursors can be used in many applications where data is processed sequentially; for example, migrating data from a legacy system to an Oracle database or loading data into a data warehouse. For instance, a company might need to build a data warehouse where it can process information about marketing, inventory, and customers to understand the market potential and improve its sales. To populate the data warehouse, the company would have to upload the information from all the different data sources into a temporary set of tables on a regular basis. The data within the temporary tables would have to be validated before being loaded into the data warehouse tables.
This is an ideal application for the use of cursors. Once the data is loaded into temporary tables, a cursor can be used to select each record, validate it, and then insert it into the data warehouse table.

A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function. A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:

1. The nested cursor is explicitly closed by the user
2. The parent cursor is reexecuted
3. The parent cursor is closed
4. The parent cursor is cancelled
5. An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)

The following restrictions apply to CURSOR expressions:

1. If the enclosing statement is not a SELECT statement, nested cursors can appear only as REF CURSOR arguments of a procedure.
2. If the enclosing statement is a SELECT statement, nested cursors can also appear inthe outermost select list of the query specification or in the outermost select list of another nested cursor.
3. Nested cursors cannot appear in views.
4. You cannot perform BIND and EXECUTE operations on nested cursors

The following example shows the use of a CURSOR expression in the select list of a query:

SELECT department_name, CURSOR(SELECT salary, commission_pct
FROM employees e
WHERE e.department_id = d.department_id)
FROM departments d;

The next example shows the use of a CURSOR expression as a function argument. The example begins by creating a function in the sample OE schema that can accept the REF CURSOR argument.