In order for Oracle to process an 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, a pointer to the parsed representation of the statement (parsing an 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 will be 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. Two important features about the cursor are as follows :
Types of Cursors
There are two types of cursors:
In order to better understand the capabilities of an explicit cursor, you first need to run through the process of an implicit cursor. The process is as follows:
The Processing of an Implicit 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.
Consider the following example of an implicit cursor.
SET SERVEROUTPUT ON; DECLARE v_first_name VARCHAR2(35); v_last_name VARCHAR2(35); BEGIN SELECT first_name, last_name INTO v_first_name, v_last_name FROM student WHERE student_id = 123; DBMS_OUTPUT.PUT_LINE ('Student name: ' v_first_name' 'v_last_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('There is no student with student ID 123'); END;
It is important to note that 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 . Once the SELECT INTO statement completes, Oracle closes the implicit cursor.
Unlike implicit cursor, explicit cursor is defined by the program for any query that returns more than one row of data. So you need to process an explicit cursor as follows. First you declare a cursor. Next, you open earlier declared cursor. Next, you fetch earlier declared and opened cursor. Finally, you close the cursor.
The only means of generating an explicit cursor is for the cursor to be named in the DECLARE section of the PL/SQL block.
The advantages of declaring an explicit cursor over the indirect implicit cursor are that the explicit cursor gives more programmatic control to the programmer. Implicit cursors are less efficient than explicit cursors, and thus it is harder to trap data errors.
The process of working with an explicit cursor consists of the following steps:
Declaring a Cursor
Declaring a cursor defines the name of the cursor and associates it with a SELECT statement. The first step is to Declare the Cursor with the following syntax:
CURSOR c_cursor_name IS select statement
It is not possible to make use of a cursor unless the complete cycle of (1) declaring, (2) opening, (3) fetching, and finally (4) closing has been performed. In order to explain these four steps, the following examples will have code fragments for each step and finally will show you the complete process.
This is a PL/SQL fragment that demonstrates the first step of declaring a cursor. A cursor named C_MyCursor is declared as a select statement of all the rows in the zipcode table that have the item state equal to 'NY'.
DECLARE CURSOR C_MyCursor IS SELECT * FROM zipcode WHERE state = 'NY'; ... <code would continue here with Opening, Fetching and closing of the cursor>
A record is a composite data structure, which means that it is composed of more than one element. 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: (1) table-based, (2) cursor-based, (3) 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
-- ch09_1a.sql SET SERVEROUTPUT ON DECLARE vr_student student%ROWTYPE; BEGIN SELECT * INTO vr_student FROM student WHERE student_id = 156; DBMS_OUTPUT.PUT_LINE (vr_student.first_name' ' vr_student.last_name' has an ID of 156'); EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR(-2001,'The Student ' 'is not in the database'); END;
The variable vr_student is a record type of the existing database table student. That is, 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 cursors. When referencing elements of the record, you use the same syntax that you use with tables.
In order to define a variable that is based on a cursor record, the cursor must first be declared. In the following lab, you will start by declaring a cursor and then proceed with the process of opening the cursor, fetching from the cursor, and finally closing the cursor.
A table-based record is drawn from a particular table structure. Consider the following code fragment.
DECLARE vr_zip ZIPCODE%ROWTYPE; vr_instructor INSTRUCTOR%ROWTYPE;
Record vr_zip has structure similar to a row of the ZIPCODE table. Its elements are CITY , STATE , and ZIP . It is important to note that if CITY column of the ZIPCODE table has been defined as VARCHAR2(15), the attribute CITY of the vr_zip record will have the same datatype structure. Similarly, record vr_instructor is based on the row of the INSTRUCTOR table.