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 representa-tion
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
1. Cursors allow you
to fetch and process rows returned by a SE-LECT
statement, one row
at a time.
2. A cursor is named
so that it can be referenced.
TYPES OF CURSORS
There are two types
of cursors: (1) 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. (2) 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 cursor does
not exist for that SQL
statement.
• A cursor is automatically
associated with every DML (Data Ma-nipulation)
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 Customers
SET Cust_name = 'B'
WHERE Cust_name LIKE 'B%';
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
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:
1. DECLARING the cursor. This
initializes the cursor into mem-ory.
2. OPENING the cursor. The
previously declared cursor can now
be opened; memory is allotted.
3. FETCHING the cursor. The
previously declared and opened cur-sor
can now retrieve data; this is
the process of fetching the
cursor.
4. CLOSING the cursor. The
previously declared, opened, and
fetched cursor must now be
closed to release memory alloca-tion.
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 follow-ing
syntax:
CURSOR c_cursor_name IS select
statement
The naming conventions that are used in the
Oracle Interactive Series
advise you to always 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 have been
performed. In order to explain
these four steps, the following examples
will have code fragments for
each step and then will show you the com-plete
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 bookings
WHERE Cust_no = 701;
...
<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 iden-tifier,
it must be declared before it is
referenced. Any valid select state-ment
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
DECLARE
vr_customer Customers%ROWTYPE;
BEGIN
SELECT *
INTO vr_customer
FROM Customers
WHERE Cust_no = 701;
DBMS_OUTPUT.PUT_LINE
(vr_customer.Cust_name||' '||' has an ID of 701');
EXCEPTION
WHEN no_data_found
THEN
RAISE_APPLICATION_ERROR(-2001,'The
Customer '||
'is not in the
database');
END;
The variable vr_customer is a record type of
the existing database table
Customers. 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 el-ements
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.
8.1 OPENING A CURSOR
The next step in controlling an
explicit cursor is to open it. When the
Open cursor statement is
processed, the following four actions will take
place automatically:.
1. The variables (including bind variables) in the WHERE
clause
are examined.
2. Based on the values of the
variables, the active set is deter-mined
and the PL/SQL engine executes
the query for that cur-sor.
Variables are examined at
cursor open time only.
3. The PL/SQL engine identifies
the active set of data—the rows
from all involved tables that
meet the WHERE clause criteria.
4. The active set pointer is set
to the first row.
The syntax for opening a cursor
is:
OPEN cursor_name;
A pointer into the active set is also
established at the cursor open time.
The pointer determines which row is the
next to be fetched by the cur-sor.
More than one cursor can be open at a time.
PROCESSING
AN EXPLICIT CURSOR
FETCHING ROWS IN A CURSOR
After the cursor has been
declared and opened, you can then retrieve data
from the cursor. The process of
getting the data from the cursor is re-ferred
to as fetching the cursor. There are two
methods of fetching a cur-sor,
done with the following
command:
FETCH cursor_name INTO PL/SQL
variables;
or
FETCH cursor_name INTO PL/SQL
record;
When the cursor is fetched the
following occurs:
1. The fetch command is used to
retrieve one row at a time from
the active set. This is
generally done inside a loop. The values
of each row in the active set
can then be stored into the corre-sponding
variables or PL/SQL record one
at a time, performing
operations on each one
successively.
1. After each FETCH, the active
set pointer is moved forward to
the next row. Thus, each fetch
will return successive rows of
the active set, until the
entire set is returned. The last FETCH
will not assign values to the
output variables; they will still
contain their prior values.
FOR EXAMPLE
DECLARE
CURSOR c_customer IS
SELECT *
FROM customers;
vr_customer c_customer%ROWTYPE;
BEGIN
OPEN c_customer;
LOOP
FETCH c_customer INTO vr_customer;
EXIT WHEN
c_customer%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vr_customer.cust_name||
' '||vr_customer.Cust_no);
END LOOP;
...
The lines in italics have not
yet been covered but are essential for the
code to run correctly
CLOSING A CURSOR
Once all of the rows in the
cursor have been processed (retrieved), the
cursor should be closed. This
tells the PL/SQL engine that the program is
finished with the cursor, and
the resources associated with it can be freed.
The syntax for closing the
cursor is:
CLOSE cursor_name;
Table 8.1 lists the attributes
of a cursor, which are used to determine the
result of a cursor operation
when fetched or opened.
a) Now
that you know cursor attributes, you can use one of these
to exit the loop within the code you developed in
the previous ex-ample.
Are you able to make a fully executable block now?
If not, ex-plain
why.
Cursor attributes can be used
with implicit cursors by using the prefix
SQL, for example: SQL%ROWCOUNT.
Cursor Attribute Syntax Explanation
%NOTFOUND cursor_name%NOTFOUND A Boolean
attribute that re-turns TRUE if the previous FETCH did not return a row, and
FALSE if it did.
%FOUND cursor_name%FOUND A Boolean
attribute that re-turns TRUE if the previous FETCH returned a row, and FALSE if
it did not.
%ROWCOUNT cursor_name%ROWCOUNT # of
records fetched from a cursor at that point in time.
%ISOPEN cursor_name%ISOPEN A Boolean
attribute that re-turns TRUE if cursor is open, FALSE if it is not. 3 PUTTING IT
ALL TOGETHER
If you use a SELECT INTO syntax
in your PL/SQL block, you will be creat-ing
an implicit cursor. You can
then use these attributes on the implicit
cursor.
FOR EXAMPLE
SET SERVEROUTPUT ON
DECLARE
v_Cust_name
Customers.Cust_name%type;
BEGIN
SELECT Cust_name
INTO v_Cust_name
FROM customers
WHERE Cust_no = 701;
IF SQL%ROWCOUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE(v_cust_name
||' has a '||
'id of 701');
ELSIF SQL%ROWCOUNT = 0
THEN
DBMS_OUTPUT.PUT_LINE('The customer
701 is '||
' not in the database');
ELSE
DBMS_OUTPUT.PUT_LINE('Stop
harassing me');
END IF;
END;
Here is an example
of the complete cycle of declaring, opening, fetching,
and closing a
cursor including use of cursor attributes.
DECLARE
v_Cust_no Customers.Cust_no%TYPE;
CURSOR c_customer IS
SELECT Cust_no
FROM Customers
WHERE Cust_no < 710;
BEGIN
OPEN c_customer;
LOOP
FETCH c_customer INTO v_Cust_no;
EXIT WHEN c_customer%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Customer ID : '||v_Cust_no);
END LOOP;
CLOSE c_customer;
EXCEPTION
WHEN OTHERS
THEN
IF c_customer%ISOPEN
THEN
CLOSE c_customer;
END IF;
END;