Nested Sets in Oracle (2)


Following the creation of the CATEGORY table as described in Nested Sets in Oracle (1), I came up against how to load the data. The example given in Managing Hierarchical Data in MySQL was to manually work out the links.

Load the Data

Of course, my way of doing things is far more complicated as can be seen in the following script:

TRUNCATE TABLE category;
INSERT INTO category ( name, parent ) VALUES ( 'ELECTRONICS', NULL ); 
INSERT INTO category ( name, parent ) VALUES ( 'PORTABLE ELECTRONICS',
  ( SELECT category_id FROM category WHERE name = 'ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'MP3 PLAYERS',
  ( SELECT category_id FROM category WHERE name = 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'FLASH',
  ( SELECT category_id FROM category WHERE name = 'MP3 PLAYERS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'CD PLAYERS',
  ( SELECT category_id FROM category WHERE name = 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( '2 WAY RADIOS',
  ( SELECT category_id FROM category WHERE name = 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'TELEVISIONS',
  ( SELECT category_id FROM category WHERE name = 'ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'LCD',
  ( SELECT category_id FROM category WHERE name = 'TELEVISIONS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'TUBE',
  ( SELECT category_id FROM category WHERE name = 'TELEVISIONS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'PLASMA',
  ( SELECT category_id FROM category WHERE name = 'TELEVISIONS' ) ); 
COMMIT;

Note: This loading script relies on the uniqueness of the NAME column within the CATEGORY table. Thus the following query can return only zero (0) or one (1) row of one (1) column:

SELECT category_id FROM category WHERE name = 'TELEVISIONS';

Bug with Loading with an Invalid Parent Name

The problem with this script occurs when zero (0) rows are returned and a NULL value is inserted into the PARENT column of the CATEGORY table. This is done silently – no error is returned.

One solution is to provide a function to validate the parent name and return a valid CATEGORY_ID value:

CREATE OR REPLACE FUNCTION
  get_category_id(
    p_name IN VARCHAR2
  )
  RETURN NUMBER
  AS
  
  -- Returns the ID of a CATEGORY with a name matching the
  -- passed parameter.
  -- A NULL input returns a NULL
  
  l_category_id
    NUMBER
    NOT NULL
    := 0;

BEGIN

  IF p_name IS NULL THEN
    RETURN NULL;
  END IF;
  
  SELECT
      category_id
    INTO
      l_category_id
    FROM
      category
    WHERE
      name = p_name
  ;

  RETURN l_category_id;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR( -20002, 'Name (' || p_name ||
         ') not defined in CATEGORY table.' );
    WHEN OTHERS THEN
      RAISE;

END get_category_id;

Load the Data with a Function

The complexity of data checking has now been moved into a function and RI constraints. The new data loading script is:

TRUNCATE TABLE category;
INSERT INTO category ( name, parent ) VALUES ( 'ELECTRONICS', NULL ); 
INSERT INTO category ( name, parent ) VALUES ( 'PORTABLE ELECTRONICS',
  get_category_id( 'ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'MP3 PLAYERS',
  get_category_id( 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'FLASH',
  get_category_id( 'MP3 PLAYERS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'CD PLAYERS',
  get_category_id( 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( '2 WAY RADIOS',
  get_category_id( 'PORTABLE ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'TELEVISIONS',
  get_category_id( 'ELECTRONICS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'LCD',
  get_category_id( 'TELEVISIONS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'TUBE',
  get_category_id( 'TELEVISIONS' ) ); 
INSERT INTO category ( name, parent ) VALUES ( 'PLASMA',
  get_category_id( 'TELEVISIONS' ) ); 
COMMIT;

This is better than the first script in that it more readable, but it still has the complexity of SQL coding.

Unloading the Data as INSERT Statements

The following SQL will unload the table to create a data load script:

SELECT
    'INSERT INTO category ( name, parent ) VALUES ( ''' ||
    name ||
    ''', ' ||
    DECODE(
       parent,
         NULL, 'NULL',
         '( get_category_id( ''' || 
         PRIOR name ||
         ''' )'
    ) ||
    ' );'
      AS sql_stmt
  FROM
    category o
  START WITH
    parent IS NULL
  CONNECT BY
    parent = PRIOR category_id
/

This SQL walks down the tree from the node where the PARENT column is NULL (‘ELECTRONICS’) and visiting each node. The PRIOR function returns values from the parent node. Once you understand the SQL, you get an idea how much simpler this is in Oracle than in MySQL.

Advertisements

2 thoughts on “Nested Sets in Oracle (2)

  1. Nested Sets in Oracle (3) « Yet Another OCM

  2. Nested Sets in Oracle (4) « Yet Another OCM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s