PL/SQL - Cursor
A cursor in the context of Oracle.
A cursor is a SQL datatype.
A cursor is a pointer to a private SQL area that stores information about processing a specific:
- or DML statement.
The cursor data are session-specific and therefore are stored in the private SQL area. A cursor contains:
- the rows
- and processing information in order to manipulate the rows.
Oracle does not return Result Sets but a pointer. A cursor can be passed between:
- the RDBMS and the client,
- or between PL/SQL and Java in the database.
It can also be returned from a query or a stored procedure.
The result of a SQL statement is a cursor.
When the same SQL text statement (the parent) is executed several time in different conditions, you will get several child cursors.
The parent cursor stores the sql text of the cursor.
different conditions can be caused by:
- different bind variable
- session parameter (CURSOR_SHARING,optimizer_mode, …)
Different conditions leads to a different execution plan that leads to different child cursors.
Cursor variable parameters are pointers. Therefore, if a subprogram assigns one cursor variable parameter to another, they refer to the same memory location. (Aliasing in Oracle term).
A cursor is not a PL/SQL variable but a SQL one, you cannot therefore assign values to a cursor.
To access data returned by a query, you define CURSORS and REF CURSORS where:
- CURSORS (Explicit Cursors) contain query results and metadata.
- A REF CURSOR (or CURSOR variable) data type contains a reference to a cursor.
Unlike an explicit cursor, which always refers to the same query work area, a cursor variable can refer to different work areas. You cannot use a cursor variable where a cursor is expected, or vice versa.
A cursor can take IN parameters (the constraint NOT NULL can not be imposed)
DECLARE CURSOR c1 (low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS SELECT * FROM departments WHERE department_id > low AND department_id < high;
You use three commands to control a cursor:
- and CLOSE
Opening the cursor executes the query and retrieve the result set (all rows that meet the query) and set the pointer before the first row. The FETCH statement will retrieve the first row.
For cursors declared using the FOR UPDATE clause, the OPEN statement also locks those rows.
DECLARE CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary > 2000; BEGIN OPEN C1;
- execute FETCH repeatedly until all rows have been retrieved (A FETCH statement retrieve the data of the next row)
- or use the BULK COLLECT clause to fetch all rows at once.
The cursor resources are released with a CLOSE statement.
A cursor attribute can be appended to the name of a cursor or cursor variable.
- %NOTFOUND, Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.
- %ROWCOUNT. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns always 0.
Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
- VSQL_WORKAREA displays information about work areas used by SQL cursors.