O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required