Database Reference
In-Depth Information
The next thing we should deal with is retrieving data from the database
from within PL/SQL.
24.4
Retrieving Data in PL/SQL
What is a cursor? A
is a temporary area in memory used to store the
results of a query. Oracle Database calls the area of memory in which a cur-
sor is temporarily placed a Work Area. In programming terms, a cursor is a
pointer to an address in memory, a chunk of memory. Query and DML
command results are placed into and processed in cursors during execution.
In PL/SQL, cursors can be created as programming data structures. Cursors
can be used for queries returning one or many rows and can be of two
types: implicit and explicit cursors. An implicit cursor is declared automati-
cally by PL/SQL, and an explicit cursor is declared by the programmer. An
explicit cursor gives more control to the programmer.
cursor
24.4.1
Explicit Cursors
An explicitly declared cursor allows more programmer access to a cursor, for
each row in that cursor, using the cursor OPEN, FETCH, and CLOSE
commands.
What is an explicit cursor? Let's explain it in steps: the first step is to
declare a cursor. This example names the cursor CARTIST. The query
(SELECT * FROM ARTIST) retrieves rows of data and places them in the
cursor's memory area. At the same time, we declare a RECORD type to
contain each row retrieved. We use the record type called RARTIST, seen
before in this chapter, to retrieve each row of data from the cursor.
SET SERVEROUTPUT ON;
DECLARE
CURSOR CARTIST IS SELECT * FROM ARTIST;
RARTIST ARTIST%ROWTYPE;
The second step is to open the cursor. This parses the query and loads
the first portion of rows into the cursor in preparation for retrieval by the
program:
BEGIN
OPEN CARTIST;
