Oracle PL/SQL Programming: A Developer's Workbook

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? FOR indx IN the_best.FIRST .. the_best.LAST LOOP DBMS_OUTPUT.PUT_LINE (the_best(indx)); END LOOP; 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; 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; /* assume ...