Expert

6-23.

My son keeps track of every year’s favorite birthday presents in a database table. To display the contents of that table, he loads them into an index-by table, using the year as the row number:

/* Filename on web page: presents.sql */
CREATE TABLE birthday (
   party_date DATE,
   fav_present VARCHAR2(100));

INSERT INTO birthday VALUES
   ('01-OCT-92', 'TEENAGE MUTANT NINJA TURTLE');
INSERT INTO birthday VALUES
   ('01-OCT-98', 'GAMEBOY POKEMON');

DECLARE
   TYPE name_tt IS TABLE OF birthday.fav_present%TYPE
      INDEX BY BINARY_INTEGER;
   the_best name_tt;
BEGIN
   FOR rec IN (
      SELECT TO_NUMBER (TO_CHAR (party_date, 'YYYY')) indx,
             fav_present
        FROM birthday)
   LOOP
      the_best (rec.indx) := rec.fav_present;
   END LOOP;

That’s all well and good, but what he really wants to do is display those favorite presents and savor the moments of his recent past. The following loops demonstrate different ways he could see this information. Which is the best approach to take and what is wrong with each of the others?

  1. FOR indx IN the_best.FIRST .. the_best.LAST
LOOP
   DBMS_OUTPUT.PUT_LINE (the_best(indx));
END LOOP;
  2. IF the_best.COUNT > 0
THEN
   FOR indx IN the_best.FIRST .. the_best.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE (the_best(indx));
   END LOOP;
END IF;
  3. IF the_best.COUNT > 0
THEN
   FOR indx IN the_best.FIRST .. the_best.LAST
   LOOP
      IF the_best.EXISTS (indx)
      THEN
         DBMS_OUTPUT.PUT_LINE (the_best(indx));
      END IF;
   END LOOP;
END IF;
  4. /* assume ...

Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial