Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
29-28.	The matches are: 1 and d 2 and b 3 and c 4 and a
Q:
29-29.	There are several possible ways to improve the program. The following optimization eliminates the nested loops by accumulating the schedule in reverse, resulting in a program that is both faster and easier to understand (note that there are several versions on the book’s web page): /* Filename on web page: presvalue.sql */ PROCEDURE build_lease_schedule IS pv_total_lease NUMBER (9) := 0; one_year_pv NUMBER (9) := 0; BEGIN fixed_count := 0; var_count := 0; FOR year_count IN REVERSE 1 .. 20 LOOP one_year_pv := pv_of_fixed (year_count) + pv_of_variable (year_count); pv_total_lease := pv_total_lease + one_year_pv; pv_table (year_count) := pv_total_lease; END LOOP; END;
Q:
29-30.	You can create an anonymous PL/SQL block within an IF statement to declare and use memory only when necessary: PROCEDURE only_as_needed (...) IS BEGIN IF <condition> THEN DECLARE big_string VARCHAR2(32767) := ten_minute_lookup (...); big_list list_types.big_strings_tt; BEGIN use_big_string (big_string); Process_big_list (big_list); END; ELSE /* Nothing big going on here */ ... END IF; END;
Q:
29-31.	You can use native dynamic SQL to easily change the status of a trigger: /* Filename on web page: settrig_status.sp */ CREATE OR REPLACE PROCEDURE settrig (tab ...