Handling Errors and Exceptions
Handling Errors and Exceptions
Developing and Using Stored Procedures 4-41
Iterating Through an Index-by Table
The structure
employees_jobs
is a dense index-by table, because it is indexed by a
PLS_INTEGER
. You can iterate through it simply by placing your operations within a
FOR ... LOOP
that counts from
1
through the
COUNT()
value of the table, as
. Note that the line in bold represents a direct look-up of
a value in the
job_titles
table.
Example 417 Iterating through an index-by PLS_INTEGER table
FOR i IN 1..employees_jobs.count() LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(employees_jobs(i).employee_id, 10)||
RPAD(employees_jobs(i).first_name, 15)||
RPAD(employees_jobs(i).last_name, 15)||
job_titles(employees(i).job_id));
END LOOP;
The structure
job_titles
is a sparse index-by table, indexed by a
VARCHAR2
. As
demonstrates, you can iterate through it within a
WHILE ... END
LOOP
using a pre-defined counter that is equal to the first key value, and the
NEXT()
value of the table. You will notice that the elements are naturally sorted in lexical order
of the index.
Example 418 Iterating through an index-by VARCHAR2 table
DECLARE i hr.jobs.job_id%TYPE := job_titles.FIRST();
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(
RPAD(job_titles(i).job_id, 10)||
job_titles(i).job_title);
i := job_titles.NEXT(i);
END LOOP;
Handling Errors and Exceptions
Error conditions, known as exceptions, are easy to detect and process within your
PL/SQL code. When an error occurs, it raises an exception by stopping normal
processing and transferring control to exception-handling code. This code is located at
the end of the PL/SQL block. In PL/SQL, the checks and calls to error routines are
performed automatically, with each exception having its own exception handler.
Predefined exceptions are raised automatically for certain common error conditions
that involve variables or database operations. You can also declare custom exceptions
for conditions that are errors with respect to your program, or as wrappers to existing
Oracle messages.
