10-13. Navigating Collections
Problem
You need a routine to display sales totaled by region, which is stored in a collection of numbers, but the collection is indexed by a character string. Using a LOOP from 1 to the maximum size will not work.
Solution
Use the FIRST and LAST method to traverse the collection allowing PL/SQL to supply the proper index values. In this example, sales amounts are stored in a TABLE indexed by a string.
DECLARE
TYPE ntype IS TABLE OF NUMBER INDEX BY VARCHAR2(5);
nlist ntype;
idx VARCHAR2(5);
total integer := 0;
BEGIN
nlist('North') := 100;
nlist('South') := 125;
nlist('East') := 75;
nlist('West') := 75;
idx := nlist.FIRST;
LOOP
EXIT WHEN idx is null; DBMS_OUTPUT.PUT_LINE ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach 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.
