Lab 9.1 Cursor Manipulation | Oracle PL[s]SQL by Example

Lab Objectives

After this Lab, you will be able to:

Make Use of Record Types

Process an Explicit Cursor

Make Use of Cursor Attributes

Put It All Together

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 :

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:

An implicit cursor is automatically declared by Oracle every time an SQL statement is executed. The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor.
An explicit cursor is defined by the program for any query that returns more than one row of data. That means the programmer has declared the cursor within the PL/SQL code block. This declaration allows for the application to sequentially process each row of data as it is returned by the cursor.

Implicit Cursor

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:

Any given PL/SQL block issues an implicit cursor whenever an SQL statement is executed, as long as an explicit cursors 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 in the database; the implicit cursor fulfills this need.
The most recently opened cursor is called the 'SQL%' cursor.

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.

An implicit cursor cannot tell you how many rows were affected by an update. SQL%ROWCOUNT returns numbers of rows updated. It can be used as follows:

 SET SERVEROUTPUT ON BEGIN    UPDATE student       SET first_name = 'B'     WHERE first_name LIKE 'B%';    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END;

Consider the following example of an implicit cursor.

FOR EXAMPLE

 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.

Explicit 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 the cursor . This initializes the cursor into memory.
Opening the cursor . The previously declared cursor can now be opened; memory is allotted.
Fetching the cursor . Previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.
Closing the cursor . Previously declared, opened, and fetched cursor must now be closed to release memory allocation.

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

The naming conventions that are used in the Oracle Interactive Series advise you always to name a cursor as c_cursorname. By using a c_ in the beginning of the name, it will always be clear to you that the name is referencing a cursor.

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.

FOR EXAMPLE

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>

Cursor names follow the same rules of scope and visibility that apply to the PL/SQL identifiers. Because the name of the cursor 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.

Record Types

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

FOR EXAMPLE

 -- 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.

 record_name.item_name

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.

FOR EXAMPLE

 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.