Populating Index-by VARCHAR2 Tables
Using Collections; Index-By Tables
4-40 Oracle Database 2 Day Developer's Guide
Defining Index-by Tables
Now that you have declared your cursors, you can use the
%ROWTYPE
attribute to
create the index-by
PLS_INTEGER
tables
employees_jobs
and
jobs
, as shown in
Example 413 Creating index-by PLS_INTEGER tables based on the cursor structure
TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
INDEX BY PLS_INTEGER;
employees_jobs employees_jobs_type;
TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
INDEX BY PLS_INTEGER;
jobs jobs_type;
To create a table that is indexed by a
VARCHAR2
, such as the
job_titles
index-by
table of
job_id
, use the definition of these types from the original table,
hr.jobs
, as
Example 414 Creating index-by VARCHAR2 tables
TYPE job_titles_type IS TABLE OF hr.jobs.job_title%TYPE
INDEX BY hr.jobs.job_id%TYPE;
job_titles job_titles_type;
Populating Index-by PLS_INTEGER Tables; BULK COLLECT
If your work requires referencing a large quantity of data as local PL/SQL variables,
the
BULK COLLECT
clause is much more efficient than looping through a result set
one row at a time. When you query only some columns, you can store all the results
for each column in a separate collection variable. When you query all the columns of a
table, you can store the entire result set in a collection of records.
With the index-by
PLS_INTEGER
employees_jobs
and
jobs
tables, you can now
open the cursor and use
BULK COLLECT
to retrieve data, as shown in
Example 415 Populating index-by PLS_INTEGER tables through BULK COLLECT
OPEN employees_jobs_cursor;
FETCH employees_jobs_cursor BULK COLLECT INTO employees_jobs;
CLOSE employees_jobs_cursor;
OPEN jobs_cursor;
FETCH jobs_cursor BULK COLLECT INTO jobs;
CLOSE jobs_cursor;
Populating Index-by VARCHAR2 Tables
Once the
jobs
table contains data, use the
FOR ... LOOP
, as shown in
VARCHAR2
table,
job_titles
:
Example 416 Populating index-by VARCHAR2 tables
FOR i IN 1..jobs.COUNT() LOOP
job_titles(jobs(i).job_id) := jobs(i).job_title;
END LOOP;
