Multi-Level Collections in Oracle 9i
Pages: 1, 2, 3, 4

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.

We'll look at code to populate and to report on the nested_training_logs table. In our previous article we described the powerful table function feature, new in Oracle 9i. We'll see an interesting application of table functions in this example that allows us to "view" nested_training_logs as reln_training_logs and to "view" reln_training_logs as nested_training_logs. By writing each with a ref cursor input parameter, we can conveniently test that the result of two successive transformations is identical to the starting data.

Populating the Nested Table

Before we can work with data in our collections, we need to fill them up. The following statements rely on data structures for which the code is shown in Example 2. The full code for this population step is shown in Example 3. The code has this general shape:

-- Initialize the collections
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 ( 18, 10 ));

-- Extend and populate in the database table.
v_training_log.extend;

...

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

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;

Pages: 1, 2, 3, 4

Next Page