For Oracle to process a SQL statement, it needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement and a pointer to the parsed representation of the statement. (Parsing a SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid.) In a query, the active set refers to the rows that are returned. A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. Cursors have two important features:
- Cursors allow you to fetch and process rows returned by a SELECT statement one row at a time.
- A cursor is named so that it can be referenced.
TYPES OF CURSORS
There are two types of cursors:
- Oracle automatically declares an implicit cursor every time a SQL statement is executed. The user is unaware of this and cannot control or process the information in an implicit cursor.
- The program defines an explicit cursor for any query that returns more than one row of data. This means that the programmer has declared the cursor within the PL/SQL code block. This declaration allows the application to sequentially process each row of data as the cursor returns it.
To better understand the capabilities of an explicit cursor, you first need to understand the process of an implicit cursor:
- Any given PL/SQL block issues an implicit cursor whenever a SQL statement is executed, as long as an explicit cursor does not exist for that SQL statement.
- A cursor is automatically associated with every DML (data manipulation) statement (UPDATE, DELETE, INSERT).
- All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
- An INSERT statement needs a place to receive the data that is to be inserted into the database; the implicit cursor fulfills this need.
- The most recently opened cursor is called the SQL cursor.
The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. During the processing of an implicit cursor, Oracle automatically performs the OPEN, FETCH, and CLOSE operations.
An implicit cursor can tell you how many rows were affected by an update. Cursors have attributes such as ROWCOUNT. SQL%ROWCOUNT returns the number of rows updated. It can be used as follows:
SET SERVEROUTPUT ON
SET first_name = ‘B’
WHERE first_name LIKE ‘B%’;
SET SERVEROUTPUT ON;
SELECT first_name, last_name
INTO v_first_name, v_last_name
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE (‘Student name: ‘||v_first_name||‘ ‘||v_last_name);
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘There is no student with student ID 123’);
Oracle automatically associates an implicit cursor with the SELECT INTO statement and fetches the values for the variables v_first_name and v_last_name. After the SELECT INTO statement completes, Oracle closes the implicit cursor.
The only means of generating an explicit cursor is to name the cursor in the DECLARE section of the PL/SQL block.
The advantage of declaring an explicit cursor over an indirect implicit cursor is that the explicit cursor gives the programmer more programmatic control. Also, implicit cursors are less efficient than explicit cursors, so it is harder to trap data errors.
The process of working with an explicit cursor consists of the following steps:
1. Declaring the cursor. This initializes the cursor into memory.
2. Opening the cursor. The declared cursor is opened, and memory is allotted.
3. Fetching the cursor. The declared and opened cursor can now retrieve data.
4. Closing the cursor. The declared, opened, and fetched cursor must be closed to release the memory allocation.
DECLARING A CURSOR
Declaring a cursor defines the cursor’s name and associates it with a SELECT statement. You declare a cursor using the following syntax:
CURSOR c_cursor_name IS select statement
You can’t use a cursor unless the complete cycle of declaring, opening, fetching, and closing has been performed.
Cursor names follow the same rules of scope and visibility that apply to the PL/SQL identifiers. Because the cursor name is a PL/SQL identifier, it must be declared before it is referenced. Any valid select statement can be used to define a cursor, including joins and statements with the UNION or MINUS clause.
A record is a composite data structure, which means that it is composed of one or more elements. Records are very much like a row of a database table, but each element of the record does not stand on its own. PL/SQL supports three kinds of records: table-based, cursor-based, and programmer-defined. A table-based record is one whose structure is drawn from the list of columns in the table. A cursor-based record is one whose structure matches the elements of a predefined cursor. To create a table-based or cursor-based record, use the %ROWTYPE attribute:
record_name table_name or cursor_name%ROWTYPE
SET SERVEROUTPUT ON
WHERE student_id = 156;
DBMS_OUTPUT.PUT_LINE(vr_student.first_name || ‘ ‘ || vr_student.last_name || ‘ has an ID of 156’);
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-2001, ‘The Student is not in the database’);
The variable vr_student is a record type of the existing database table student. In other words, it has the same components as a row in the student table. A cursor-based record is much the same, except that it is drawn from the select list of an explicitly declared cursor. When referencing elements of the record, you use the same syntax that you use with tables: