Using Collections: Index-By Tables
Using Collections; Index-By Tables
4-38 Oracle Database 2 Day Developer's Guide
END IF;
END LOOP;
END eval_loop_control;
6.
In the
emp_eval
Body pane, add
eval_everyone
procedure, which retrieves a
result set that contains all employees in the company. Note that its code is similar
to that of procedure
eval_department
PROCEDURE eval_everyone AS
-- declaring the REF CURSOR type
emp_cursor emp_refcursor_type;
BEGIN
OPEN emp_cursor FOR SELECT * FROM employees;
-- start creating employee evaluations in a specific department
DBMS_OUTPUT.PUT_LINE('Determining the number of necessary evaluations');
eval_loop_control(emp_cursor); -- call to process the result set
DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
CLOSE emp_cursor;
END eval_everyone;
7.
In the
emp_eval
pane, compile and save
emp_eval
specification.
The following message appears in the Messages-Log pane:
EMP_EVAL Compiled
8.
In the
emp_eval
body pane, compile and save
emp_eval
body.
The following message appears in the Messages-Log pane:
EMP_EVAL Body Compiled
Using Collections; Index-By Tables
Another group of user-defined datatypes available in PL/SQL is a collection, which is
Oracle's version of one-dimensional arrays. A collection is a data structure that can
hold a number of rows of data in a single variable. In contrast to a record, which holds
only one row of data of different types, the data in a collection must be of the same
type. In other programming languages, the types of structures represented by
collections are called arrays.
Collections are used to maintain lists of information and can significantly improve
your application's performance because they allow direct access to their elements.
There are three types of collection structures: index-by tables, nested tables, and
variable arrays.
An index-by table is the most flexible and generally best-performing collection
type for use inside PL/SQL programs.
A nested table is appropriate for large collections that an application stores and
retrieves in portions.
A VARRAY is appropriate for small collections that the application stores and
retrieves in their entirety.
See Also:
Oracle Database PL/SQL Language Reference for information on the
syntax of cursor variables
Oracle Database PL/SQL Language Reference for information on the
syntax of cursor attributes
