October 2005
Intermediate to advanced
454 pages
14h 44m
English
Oracle offers both numeric and cursor FOR loops . With the numeric FOR loop, you specify the start and end integer values, and PL/SQL does the rest of the work for you, iterating through each intermediate value, and then terminating the loop:
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
)
IS
BEGIN
FOR l_current_year IN start_year_in .. end_year_in
LOOP
display_total_sales (l_current_year);
END LOOP;
END display_multiple_years;The cursor FOR loop has the same basic structure, but, in this case, you supply an explicit cursor or SELECT statement in place of the low-high integer range:
PROCEDURE display_multiple_years (
start_year_in IN PLS_INTEGER
,end_year_in IN PLS_INTEGER
)
IS
BEGIN
FOR l_current_year IN (
SELECT * FROM sales_data
WHERE year BETWEEN start_year_in AND end_year_in)
LOOP
-- This procedure is now accepted a record implicitly declared
-- to be of type sales_data%ROWTYPE...
display_total_sales (l_current_year);
END LOOP;
END display_multiple_years;