TYPE numbers_aat IS TABLE OF NUMBER
BEGIN
DBMS_OUTPUT.PUT_LINE (l_numbers (100));
END;
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names (100) := ‘Picasso’;
l_names (101) := ‘O”Keefe’;
l_names (102) := ‘Dali’;=
show_contents (l_names);
END;
/
initially set to the lowest defined index value.
If the collection is empty, both FIRST and
LAST will return NULL. The WHILE loop terminates when l_index is NULL. I then display
the name at the current index value and call
the NEXT method to get the next defined
index value higher than l_index. This function returns NULL when there is no higher
index value.
I call this procedure in the following block,
with a collection that is not sequentially
filled. It will display the three names without
raising NO_DATA_FOUND:
The following block will display three
artists’ names; note that the index values do
not need to start at 1.
When, however, you know for certain
that your collection is—and will always be—
densely filled, the FOR loop offers the simplest code for getting the job done. The procedure in Listing 2, for example, displays all
the strings found in a collection whose type
is defined in the DBMS_U TILI T Y package.
This procedure calls two methods: FIRS T
and LAST. FIRST returns the lowest defined
index value in the collection, and LAS T
returns the highest defined index value in
the collection.
If your collection may be sparse or you
want to terminate the loop conditionally, a
WHILE loop will be the best fit. The procedure in Listing 3 shows this approach.
In this procedure, my iterator (l_index) is
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names (-150) := 'Picasso';
l_names (0) := 'O''Keefe';
Take the Challenge!
Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in it. The quiz appears below and also at PL/SQL Challenge
( plsqlchallenge.com), a Website that offers online quizzes on the PL/SQL
language as well as SQL and Oracle Application Express.
Question
Which of the following blocks will display these three lines after execution:
Strawberry
Raspberry
Blackberry
a.
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names ( 1) := 'Strawberry';
l_names ( 10) := 'Blackberry';
l_names ( 2) := 'Raspberry';
FOR indx IN 1 .. l_names.COUNT
LOOP
DBMS_OUTPUT.put_line (l_names (indx));
END LOOP;
END;
/
b.
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names ( 1) := 'Strawberry';
l_names ( 10) := 'Blackberry';
l_names ( 2) := 'Raspberry';
indx := l_names.FIRST;
WHILE (indx IS NOT NULL)
LOOP
DBMS_OUTPUT.put_line (l_names (indx));
indx := l_names.NEXT (indx);
END LOOP;
END;
/
c.
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names ( 1) := 'Strawberry';
l_names ( 10) := 'Blackberry';
l_names ( 2) := 'Raspberry';
DECLARE
indx PLS_INTEGER := l_names.FIRST;
BEGIN
WHILE (indx IS NOT NULL)
d.
DECLARE
l_names DBMS_UTILITY.maxname_array;
BEGIN
l_names ( 1) := 'Strawberry';
l_names ( 10) := 'Blackberry';
l_names ( 2) := 'Raspberry';
FOR indx IN l_names.FIRST .. l_names.LAST
LOOP
DBMS_OUTPUT.put_line (l_names (indx));
END LOOP;
END;
