MacLochlainns Weblog

DECLARE
  /* Create a single column collection that is a list strings
     less than 8 characters in length and another of strings
     less than 20 characters in length. */
  TYPE DAY   IS TABLE OF VARCHAR2(8);
  TYPE verse IS TABLE OF VARCHAR2(20);
 
  /* Create variables that use the user-defined types:
  || =================================================
  ||  1. We give the variable a name of lv_day and lv_verse.
  ||  2. We assign a user-defined ADT (Attribute Data Type) collection.
  ||  3. We assign a list of value to the constructor of the list, which
  ||     allocates memory for each item in the comma-delimited list of
  ||     string.
  */
  lv_day   DAY   := DAY('first','second','third','fourth','fifth');
  lv_verse VERSE := verse('Partridge','Turtle Doves','French Hen'
                         ,'Calling Birds','Gold Rings');
 
BEGIN
  /*
  ||  Remove an element from each of the two lists, which makes the two
  ||  lists sparsely indexed. A sparsely indexed list has gaps in the
  ||  sequential index of the list.
  */
 
  lv_day.DELETE(3);
 
  /*
  ||   Loop through the list of days:
  ||  ===================================================j
  ||   1. A list created by a comma-delimited list is densely populated,
  ||      which means it has no gaps in the sequence of indexes.
  ||   2. A list created by any means that is subsequently accessed
  ||      and has one or more items removed is sparsely populated,
  ||      which means it may have gaps in the sequence of indexes.
  ||   3. A FOR loop anticipates densely populated indexes and fails
  ||      when trying to read a missing index, which is why you should
  ||      use an IF statement to check for the element of a list before
  ||      accessing it.
  ||   4. A COUNT method returns the number of elements allocated memory
  ||      in a list of values and the LAST method returns the highest
  ||      index value. The index value is alway an integer for user-defined
  ||      ADT (Attribute Data Type) collections, but may be a string for
  ||      an associative array or a PL/SQL list indexed by a string.
  ||   5. Removing an element from a list does not change the other
  ||      index values but does if you create an array (or varray), which
  ||      means COUNT OR LAST may cause the same type of error for a list
  ||      with a missing element.
  */
 
  FOR i IN 1..lv_day.LAST LOOP
 
    /*
    ||  Verify the index is valid.
    || ====================================================
    ||  You check whether the element is present in the
    ||  list.
    */
 
    IF lv_day.EXISTS(i) THEN
 
      /* Print the beginning of the stanza. */
      dbms_output.put_line('On the ['||lv_day(i)||'] of Christmas ...');
 
      /* Print the song. */
      FOR j IN REVERSE 1..i LOOP
        /* Check if the day exists. */
        IF lv_verse.EXISTS(j) THEN
          /* All but first and last verses. */
          IF j > 1 THEN
            dbms_output.put_line('-   ['||lv_verse(j)||']');
          /* The last verse. */
          ELSIF i = j THEN
            dbms_output.put_line('- A ['||lv_verse(j)||']'||CHR(10));
          /* Last verse. */
          ELSE
            dbms_output.put_line('and a ['||lv_verse(j)||']'||CHR(10));
          END IF;
        END IF;
      END LOOP;
    ELSE
      CONTINUE;
    END IF;
  END LOOP;
END;
/