Multi-Level Collections in Oracle 9i
Pages: 1, 2, 3, 4
Reporting on the Nested Table
We'll also need some code to extract the information from the nested table and show to a user of this application. The following cursor for loop retrieves the name of the person and his training log and then iterates through that log, displaying its contents. See Example 4 for the complete code.
begin
for v_row in (select first_name, training_log
from nested_training_logs)
loop
show (v_row.first_name);
for week in
v_row.training_log.first ..
v_row.training_log.last
loop
show (week);
for run in
v_row.training_log (week).first ..
v_row.training_log (week).last
loop
show (run);
show (v_row.training_log (week) (run).distance);
show (v_row.training_log (week) (run).pace);
end loop;
end loop;
end loop;
end;Notice that in the preceding code we see that appending each successive subscript to the variable representing the multi-level collection instance drills down into each successive layer in its structure.
If appropriate, this could be rewritten using bulk collect into local multi-level collection (with one extra level), thus:
declare
type first_name_tab_t is table of
nested_training_logs.first_name%type
index by binary_integer;
v_first_name_tab first_name_tab_t;
type training_logs_tab_t is table of training_log_t
index by binary_integer;
v_training_logs_tab training_logs_tab_t;
begin
select first_name, training_log
bulk collect into
v_first_name_tab, v_training_logs_tab
from nested_training_logs;
for j in
v_first_name_tab.first..
v_first_name_tab.last
loop
Show ( v_first_name_tab(j) );
for week in v_training_logs_tab(j).first..
v_training_logs_tab(j).last
loop
Show ( week );
for run in v_training_logs_tab(j)(week).first..
v_training_logs_tab(j)(week).last
loop
Show ( run );
Show ( v_training_logs_tab(j)(week)(run).distance );
Show ( v_training_logs_tab(j)(week)(run).pace );
end loop;
end loop;
end loop;
end;As you can see, we simply add another row specifier since we have an additional level of nesting:
v_training_logs_tab(j)(week)(run).distanceExample 4. Report on the contents of the nested table.
begin
for v_row in
( select first_name, training_log from nested_training_logs )
loop
dbms_output.put_line ( v_row.first_name );
for week in v_row.training_log.first..
v_row.training_log.last
loop
dbms_output.put_line ( '. week #' || to_char(week) );
for run in v_row.training_log(week).first..
v_row.training_log(week).last
loop
dbms_output.put_line
(
'. run #' || to_char(run) || ': '
|| lpad ( v_row.training_log(week)(run).distance, 3, ' ' )
|| ' /'
|| lpad ( v_row.training_log(week)(run).pace, 3, ' ' )
);
end loop;
end loop;
end loop;
end;Deriving a table function from the reporting logic to output a relational "view" At the heart of the innermost loop in the preceding section, we have the required information to populate a record corresponding to one row of the relational representation. (Writing a program to generate a report is a convenient way to test the logic before converting the code to a table function.) The conversion is relatively routine:
Surround the block with a create function statement and declare a ref cursor input parameter.
Define types for a record, and table of such records, according to the requirement and add a return declaration for the record type.
Add the
pipelinedkeyword.Declare local variables
v_in_rowandv_out_rowas records of the appropriate types.Reformulate the cursor loop (if it's not already coded this way) to use fetch
p_in_cursorintov_in_rowwith the corresponding exit condition (don't open it—this is done by the system when the table function is invoked).Replace the Show invocations with assignments for the elements of the target record.
Deliver the record as the actual parameter to pipe
row().Add close
p_in_cursorand return as the last executable statements.
With these changes in place (see Example 5), we can now conveniently perform ad hoc queries, such as that shown here:
select first_name, avg ( distance ) d, avg ( pace ) p
from
(
select first_name, distance, pace
from table
(
reln_training_logs_fn
(
cursor
(
select first_name, training_log
from nested_training_logs
)
)
)
)
group by first_name;Example 5. Table function to "view" the contents of the nested table as a relational table.
create or replace function reln_training_logs_fn
( p_nested_training_logs in sys_refcursor )
return my_types.reln_training_logs_tab_t
/*
The algorithm handles each row in isolation and thus
is amenable to the simplest form of parallelism
*/
parallel_enable (
partition p_nested_training_logs by any ) pipelined
is
v_in_row my_types.nested_training_log_row_t;
v_out_row my_types.reln_training_log_row_t;
begin
loop
fetch p_nested_training_logs into v_in_row;
exit when p_nested_training_logs%notfound;
for week in v_in_row.training_log.first..
v_in_row.training_log.last
loop
for run in v_in_row.training_log(week).first..
v_in_row.training_log(week).last
loop
v_out_row.first_name :=
v_in_row.first_name;
v_out_row.week :=
week;
v_out_row.run :=
run;
v_out_row.distance :=
v_in_row.training_log(week)(run).distance;
v_out_row.pace :=
v_in_row.training_log(week)(run).pace;
pipe row ( v_out_row );
end loop;
end loop;
end loop;
close p_nested_training_logs;
return;
end reln_training_logs_fn;
