Database Reference
In-Depth Information
Now we loop through the open cursor, selecting each row. There will be
more about loops later in this chapter. This loop has three commands. The
first one places the next row from the cursor into the RARTIST record vari-
able. The second command causes the looping to end if the status of the
cursor is NOTFOUND (meaning there are no rows left to retrieve.) The
third line places a line on the screen that displays the artist name. This line
is executed only if there was a row retrieved from the cursor. The three lines
are repeated for every row retrieved from the cursor:
LOOP
FETCH CARTIST INTO RARTIST;
EXIT WHEN CARTIST%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(RARTIST.NAME);
END LOOP;
Do not forget to close your cursor! Explicit cursors should be closed as
soon as they are no longer needed to improve performance, prevent locking
issues, and ensure that cursor limits are not reached.
CLOSE CARTIST;
END;
/
SET SERVEROUTPUT OFF;
The execution of the pieces of the anonymous procedure looping
through the explicit cursor just described is shown in Figure 24.6.
There are other variations of how explicit cursors can be coded, but we
do not need to go into any further detail.
24.4.2
Implicit Cursors
Every SQL statement both in SQL and inside a PL/SQL block not declared
explicitly as a cursor is an implicit cursor. An implicit cursor is opened and
closed by SQL or PL/SQL and is used to process INSERT, UPDATE,
DELETE, and SELECT statements. A special type of implicit cursor exclu-
sive to PL/SQL is called a cursor FOR loop. A cursor FOR loop is an
implicit cursor on the basis that it does not require use of the OPEN,
FETCH, and CLOSE statements.
