Example 1. Defining and accessing a multi-level collection.

/* file multilevel_collections.sql */
 1  declare
 2     type bunch_of_pets_t is table of pet_t index by binary_integer;
 3     my_pets   bunch_of_pets_t;
 4  begin
 5     my_pets (1) :=
 6           pet_t (100, 'Mercury',
 7              vet_visits_t (
 8                 vet_visit_t ('01-Jan-2001', 'Clip wings'),
 9                 vet_visit_t ('01-Apr-2002', 'Check cholesterol'))
10           );
11     dbms_output.put_line (my_pets (1).name);
12     dbms_output.put_line (my_pets (1).petcare (2).reason);
13     dbms_output.put_line (my_pets.count);
14     dbms_output.put_line (my_pets(1).petcare.last);
15  end;

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Lines 5-10 assign an object of type pet_t to the first row in this associative table. As you can see, the syntax required when working with nested, complex objects of this sort can be quite intimidating. So let's "parse" the various steps required.

To instantiate an object of type pet_t, we must provide a tag number, name, and list of vet visits, which is a nested table. To provide a nested table of type vet_visits_t, we must call the associated constructor (of the same name). We can either provide a null or empty list, or initialize the nested table with some values. We do this in lines 8 and 9. Each row in the vet_visits_t collection is an object of type vet_visit_t, so again we must use the object constructor and pass in a value for each attribute (date and reason for visit).

Once the collection has been populated, we can access its data. We do this in lines 11-14. In line 11, we display the value of the name attribute of the pet object in row 1 of the my_pets associative table. In line 12, we display the value of the reason attribute of the vet visit object in row 2 of the nested table, which in turn resides in the first row of the my_pets associative table. Mmm. That's a mouthful, and it's a "line-full" of code:

dbms_output.put_line (my_pets(1).petcare(2).reason);

On lines 13 and 14, we demonstrate how you can use the collection methods (in this case, COUNT and LAST) on both outer and nested collections.

The output from running this script is:

Mercury
Check cholesterol
1
2

Let's now take a look at a more complex example of applying multi-level collections.

The "Runner's Training Logs" Scenario

Consider implementing a system to allow a running coach to maintain training logs for each of the runners under his guidance. Each runner is identified by first name and runs several times per week. A run is characterized by the distance and the average pace. The coach will want to monitor week-by-week variations and progress. Of course, many designs for the logical data model will work, but we consider just two here.

Single flat relational table:

create table reln_training_logs (
  first_name  varchar2(20) not null,
  week        number       not null,
  run         number       not null,
  distance    number       not null,
  pace        number       not null );

alter table reln_training_logs
  add constraint reln_training_logs_pk 
  primary key (first_name,week,run)
  using index;

Relational table with multi-level collection column:

create type run_t as object ( 
  distance number, pace number );

create type weeks_running_t is  
  varray(20) of run_t not null;

create type training_log_t is 
  varray(255) of weeks_running_t not null;

create table nested_training_logs (
  first_name varchar2(20) primary key,
  training_log training_log_t );

The reln_training_logs approach would be suitable if the typical access was for ad hoc queries across runners, and the nested_training_logs approach would be suitable if the typical access was to report all the information for each of a number of selected runners.

Example 2. Define the data structures.

create type run_t as object ( 
  distance number, pace number );
create type weeks_running_t is 
  varray(20) of run_t not null;
create type training_log_t is 
  varray(255) of weeks_running_t not null;

create or replace package my_types is
  type reln_training_log_row_t is record (
    first_name  varchar2(20),
    week        number,
    run         number,
    distance    number,
    pace        number );

  type cur_t                  is ref cursor
    /* 
      strong cursor type for table 
      function partitioning 
    */
    return reln_training_log_row_t;

  type reln_training_logs_tab_t is 
    table of reln_training_log_row_t;

  type nested_training_log_row_t is record (
    first_name  varchar2(20),
    training_log training_log_t );
  type nested_training_logs_tab_t is 
    table of nested_training_log_row_t;
end my_types;

create table nested_training_logs (
  first_name varchar2(20) primary key,
  training_log training_log_t );

create table nested_training_logs_2 (
  first_name varchar2(20) primary key,
  training_log training_log_t );

create table reln_training_logs (
  first_name  varchar2(20) not null,
  week        number       not null,
  run         number       not null,
  distance    number       not null,
  pace        number       not null );
alter table reln_training_logs
  add constraint reln_training_logs_pk primary key (
    first_name,week,run)
  using index;

create table reln_training_logs_2 (
  first_name  varchar2(20) not null,
  week        number       not null,
  run         number       not null,
  distance    number       not null,
  pace        number       not null );
alter table reln_training_logs_2
  add constraint reln_training_logs_2_pk primary key (
    first_name,week,run)
  using index;

Example 3. Procedure to populate the nested table.

create or replace procedure populate_nested_training_logs is
  v_training_log   training_log_t;
begin
  v_training_log := 
    training_log_t ( weeks_running_t ( run_t ( 0, 0 ) ) );

  v_training_log(1) :=
    weeks_running_t
      ( 
        run_t (  1,  6 ), 
        run_t (  7,  7 ),
        run_t (  3,  6 ),
        run_t (  9,  9 ),
        run_t (  3,  6 ),
        run_t ( 18, 10 )
      );

  v_training_log.extend;
  v_training_log(2) :=
    weeks_running_t
      ( 
        run_t ( 5, 7 ), 
        run_t ( 9, 8 ),
        run_t ( 3, 7 ),
        run_t ( 9, 9 ),
        run_t ( 3, 7 )
      );

  v_training_log.extend;
  v_training_log(3) :=
    weeks_running_t
      ( 
        run_t ( 5, 7 ), 
        run_t ( 9, 8 ),
        run_t ( 3, 7 ),
        run_t ( 9, 9 ),
        run_t ( 3, 7 )
      ) ;

  insert into nested_training_logs ( 
    first_name, training_log ) values
      ( 'fred', v_training_log );

  v_training_log := 
    training_log_t ( weeks_running_t ( run_t ( 0, 0 ) ) );

  v_training_log(1) :=
    weeks_running_t
      ( 
        run_t ( 2, 10 ), 
        run_t ( 3, 11 ),
        run_t ( 3, 11 ),
        run_t ( 4, 12 )
      );

  v_training_log.extend;
  v_training_log(2) :=
    weeks_running_t
      ( 
        run_t ( 1, 10 ), 
        run_t ( 2, 11 ),
        run_t ( 3, 12 ),
        run_t ( 2, 10 ),
        run_t ( 1,  9 ),
        run_t ( 4, 12 )
      );

  insert into nested_training_logs ( 
    first_name, training_log ) values
      ( 'sid', v_training_log );
end populate_nested_training_logs;

Reporting on the Nested Table

Example 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:

1. Surround the block with a create function statement and declare a ref cursor input parameter.

2. Define types for a record, and table of such records, according to the requirement and add a return declaration for the record type.

3. Add the pipelined keyword.

4. Declare local variables v_in_row and v_out_row as records of the appropriate types.

5. Reformulate the cursor loop (if it's not already coded this way) to use fetch p_in_cursor into v_in_row with the corresponding exit condition (don't open it—this is done by the system when the table function is invoked).

6. Replace the Show invocations with assignments for the elements of the target record.

7. Deliver the record as the actual parameter to pipe row().

8. Add close p_in_cursor and return as the last executable statements.

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;

Writing a Table Function to Output a Nested "View" from the Relational Representation

Suppose it had been decided to implement the persistent storage of the collection as a relational representation. It's still possible to view it as if it were the nested table representation by using a table function. The simplest design would use nested PL/SQL cursor loops, thus:

for each distinct runner… ; 
   for each distinct week for that runner… ; 
      for each run for that week for that runner 
          add the object to represent the run 
          to the column in the collection for that week;
   when done with that week add the column 
      for the whole week to the "plane" 
    of the collection for that runner's log; 
when done with that runner, 
    pipe the record representing the name 
    and the training log collection.

To make the table function more general, it needs to have a ref cursor input parameter to be invoked with a SELECT having two levels of nested CURSOR subqueries corresponding to the preceding nested PL/SQL loops. An alternative is to design the function to accept a "flat" SELECT. The latter approach requires slightly more elaborate coding of the function logic (to explicitly detect the next week and the next runner) but makes the resulting function substantially more user-friendly, and so it was selected for implementation in this illustration. To make the example richer with respect to understanding table functions, parallelization declarations are added to ensure that all the rows for a particular runner go consecutively to the same slave, and that for that runner the input rows are ordered by week and then run. (The algorithm depends on these assumptions.)

Example 6. Table function to "view" the contents of the relational table as a nested table.

create or replace function nested_training_logs_fn
  ( p_reln_training_logs  my_types.cur_t )
  return my_types.nested_training_logs_tab_t
  /* 
    The algorithm depends on assuming that it receives 
    rows ordered by first_name, week, then run, and that 
    all the rows for a particular runner go consecutively 
    to the same slave. These declarations ensure this and 
    remove the need for an ORDER BY clause in the SELECT 
    that's used to invoke this function.
  */
  order p_reln_training_logs by ( first_name, week, run )
  parallel_enable ( 
    partition p_reln_training_logs by range ( first_name ) )
  pipelined
is
  g_in_row              my_types.reln_training_log_row_t;
  g_out_row             my_types.nested_training_log_row_t;
  g_weeks_running       weeks_running_t;
  g_training_log        training_log_t;
  g_first_time          boolean := true;
  g_got_a_row           boolean;
  g_new_week            boolean;
  g_new_runner          boolean;
  g_current_first_name  reln_training_logs.first_name%type;
  g_prev_first_name     reln_training_logs.first_name%type;
  g_current_week        reln_training_logs.week%type;
  g_prev_week           reln_training_logs.week%type;

  procedure get_next_row is begin
    fetch p_reln_training_logs into g_in_row;
    g_got_a_row := not p_reln_training_logs%notfound;
    if g_got_a_row
    then
      case g_first_time
        when true then
          g_first_time := false;
          g_new_runner := false;
          g_new_week   := false;
        else
          g_new_runner := 
            g_prev_first_name <> g_in_row.first_name;
          g_new_week := case g_new_runner
                          when true then true
                          else           
                            g_prev_week <> g_in_row.week
                        end;
      end case;
      g_prev_first_name := g_in_row.first_name;
      g_prev_week       := g_in_row.week;
    end if;
    return;
  end get_next_row;

  function got_next_runner return boolean is begin
    g_current_first_name := g_in_row.first_name;
    g_new_runner := false;
    return g_got_a_row;
  end got_next_runner;

  function got_next_week return boolean is begin
    g_current_week := g_in_row.week;
    g_new_week := false;
    return ( not g_new_runner ) and g_got_a_row;
  end got_next_week;

  function got_next_run return boolean is begin
    return ( not g_new_week ) and g_got_a_row;
  end got_next_run;

  procedure new_training_log is begin
    g_training_log := null;
  end new_training_log;

  procedure new_weeks_running is begin
    g_weeks_running := null;
  end new_weeks_running;

  procedure store_this_run is begin
    if g_weeks_running is null
    then
      g_weeks_running := 
        weeks_running_t ( run_t ( 0, 0 ) );
    else
      g_weeks_running.extend;
    end if;
    g_weeks_running ( g_in_row.run ):=
      run_t ( g_in_row.distance, g_in_row.pace );
  end store_this_run;

  procedure store_this_weeks_running is begin
    if g_training_log is null
    then
      g_training_log := 
        training_log_t ( 
        weeks_running_t ( run_t ( 0, 0 ) ) );
    else
      g_training_log.extend;
    end if;
    g_training_log ( g_current_week ):= g_weeks_running;
  end store_this_weeks_running;

  procedure output_this_runner is begin
    g_out_row.first_name   := g_current_first_name;
    g_out_row.training_log := g_training_log;
  end output_this_runner;

begin
  get_next_row();
  while got_next_runner()
  loop
    new_training_log;
    while got_next_week()
    loop
      new_weeks_running;
      while got_next_run()
      loop
        store_this_run;
        get_next_row();
      end loop;
      store_this_weeks_running;
    end loop;
    output_this_runner; pipe row ( g_out_row ); 
  end loop;
  close p_reln_training_logs;
  return;
end nested_training_logs_fn;

In Summary

Example 7. An "end-to-end" test.

truncate table nested_training_logs;
execute populate_nested_training_logs

truncate table reln_training_logs;
insert into reln_training_logs
  (
    select *
      from table
        (
          reln_training_logs_fn 
           (
             cursor
               (
                 select first_name, training_log 
                   from nested_training_logs
               )
           )
        )
  );

truncate table nested_training_logs_2;
insert into nested_training_logs_2
  (
    select *
      from table
        (
          nested_training_logs_fn
            (
              cursor ( select * from  reln_training_logs )
            )
        )
  );

truncate table reln_training_logs_2;
insert into reln_training_logs_2
  (
    select *
      from table
        (
          reln_training_logs_fn 
           (
             cursor
               (
                 select first_name, training_log 
                   from nested_training_logs_2
               )
           )
        )
  );

select *
  from reln_training_logs_2
minus
select *
  from reln_training_logs;

select *
  from reln_training_logs
minus
select *
  from reln_training_logs_2;