Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 9.1 Cursor Manipulation

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:

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