|
The easiest and cleanest way to "densify" an associative array is to iterate through the contents with a WHILE loop, using NEXT (or PRIOR) to "skip" over undefined index values. You can also "shortcut" the process by checking to see if the collection is already dense; if the count of elements in the collection is equal to the highest index value minus the lowest plus one, it is dense.
|
|
I create the following package specification:
CREATE OR REPLACE PACKAGE plch_pkg
IS
TYPE list_t IS TABLE OF VARCHAR2 (200)
INDEX BY PLS_INTEGER;
PROCEDURE make_dense (
list_in IN list_t, list_out OUT list_t);
END;
/
The make_dense procedure accepts a collection (list_in) that contains at least one element and may be sparse (one or more index values between lowest and highest that are not defined). It returns a collection (list_out) that contains the same elements in the same order as list_in, but that is dense (all index values from lowest to highest are defined).
Which of the choices offer an implementation of make_dense that satisfies the above requirement?
|
Explanation of Result Icons
|
Choice 1 (11337)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (
list_in IN list_t, list_out OUT list_t)
IS
BEGIN
IF list_in.COUNT = list_in.LAST - list_in.FIRST + 1
THEN
list_out := list_in;
ELSE
FOR indx IN 1 .. list_in.COUNT
LOOP
list_out (indx) := list_in (indx);
END LOOP;
END IF;
END;
END;
/
This choice cleverly attempts to short-cut the process by checking to see if the collection is already dense (count = last - first + 1), but then it simply uses a FOR loop to cover over all the elements. If it were that simple, you could just write:
list_out := list_in;
but it isn't that simple. If the collection isn't dense, the FOR loop will "blow up" with a NO_DATA_FOUND exception when it hits an index value that is not defined.
|
|
|
|
|
Choice 2 (11338)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (
list_in IN list_t, list_out OUT list_t)
IS
l_index PLS_INTEGER := list_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
list_out (l_index) := list_in (l_index);
l_index := list_in.NEXT (l_index);
END LOOP;
END;
END;
This choice correctly uses the WHILE loop to go from first to last via next, thereby avoiding NO_DATA_FOUND errors. Unfortunately, it uses the same index value from list_in for list_out (l_index), so list_out ends up being just as sparse as list_in.
|
|
|
|
|
Choice 3 (11339)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (
list_in IN list_t, list_out OUT list_t)
IS
l_index PLS_INTEGER := list_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
list_out (list_out.COUNT + 1) := list_in (l_index);
l_index := list_in.NEXT (l_index);
END LOOP;
END;
END;
/
This choice correctly uses the WHILE loop to go from first to last via next, thereby avoiding NO_DATA_FOUND errors. This is the template you should use, generally, for iterating through a collection that may be sparse.
This choice could be enhanced with the check shown in the first choice, so that you don't iterate through a potentially large and already dense collection, and instead simply assign the in collection to the out collection.
|
|
|
|
|
Choice 4 (11340)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (
list_in IN list_t, list_out OUT list_t)
IS
BEGIN
list_out := dense (list_in);
END;
END;
/
Ah, if only....if only Oracle provided a built-in named DENSE to take care of this for us, but it does not.
|
|
|
|
|
Choice 5 (11341)
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
BEGIN
FOR indx IN list_in.FIRST .. list_in.LAST
LOOP
IF list_in.EXISTS (indx)
THEN
list_out (list_out.COUNT + 1) := list_in (indx);
END IF;
END LOOP;
END;
END;
/
This gets the job done, but it is a sub-optimal algorithm. For example, if the lowest index value is -200,000 and the highest index value is 200,000 (and nothing defined in between), then this loop will execute 400,001 times but only two elements will be copied over to list_out. Yuch!
|
|
|
|
Oracle PL/SQL supports all the usual sorts of datatypes, such as strings, numbers and dates, and then adds a number of its own more specialized or unique datatypes, including records and collections.
A composite data type stores values that have internal components. You can pass entire composite variables to subprograms as parameters, and you can access internal components of composite variables individually. Internal components can be either scalar or composite. You can use scalar components wherever you can use scalar variables. PL/SQL lets you define two kinds of composite data types: collection and record. You can use composite components wherever you can use composite variables of the same type.
In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE against a column in a relational table that is itself a collection.
Associative arrays are the oldest type of collection in PL/SQL. First introduced in Oracle7 as "PL/SQL Tables", they were renamed "index-by tables" in Oracle8, when nested tables and varrays were introduced. They were then renamed "associative arrays" in Oracle Database 9i Release 2, with the introduction of string-indexing.
This type of collection can only be used in PL/SQL code (not, as with nested tables, as the datatype of a column in a relational table).
Key features of associative arrays include:
-
Integer or string indexing
-
Negative indexes
-
Sparse collections (undefined index values between lowest and highest index values used)
Notice that even though list_out is defined as an OUT argument, we can still call methods like COUNT for that collection, even though it is "reading" the collection.
|
|
Median
|
282
|
50
|
357
|
130
|
80
|
|
Best
|
1
|
100
|
496
|
12
|
100
|
Use the following code to verify the scoring of all the choices in this quiz. If you feel that there is a bug in our code or you disagree with the results, submit an objection in the Commentary section.
CREATE OR REPLACE PACKAGE plch_pkg
IS
TYPE list_t IS TABLE OF VARCHAR2 (200)
INDEX BY PLS_INTEGER;
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t);
END;
/
CREATE OR REPLACE PROCEDURE plch_tester
IS
l_sparse plch_pkg.list_t;
l_dense plch_pkg.list_t;
BEGIN
l_sparse (-10000) := 'Whitetip';
l_sparse (0) := 'Bramble';
l_sparse (10000) := 'Sevengill';
plch_pkg.make_dense (l_sparse, l_dense);
FOR indx IN l_dense.FIRST .. l_dense.LAST
LOOP
DBMS_OUTPUT.put_line (indx || '-' || l_dense (indx));
END LOOP;
END;
/
/* Loop assumes already dense. */
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
BEGIN
IF list_in.COUNT = list_in.LAST - list_in.FIRST + 1
THEN
list_out := list_in;
ELSE
FOR indx IN 1 .. list_in.COUNT
LOOP
list_out (indx) := list_in (indx);
END LOOP;
END IF;
END;
END;
/
BEGIN
plch_tester;
END;
/
/* Use while loop, but wrong "target" index for list_out. */
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
l_index PLS_INTEGER := list_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
list_out (l_index) := list_in (l_index);
l_index := list_in.NEXT (l_index);
END LOOP;
END;
END;
/
BEGIN
plch_tester;
END;
/
/* Correct use of while loop */
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
l_index PLS_INTEGER := list_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
list_out (list_out.COUNT + 1) := list_in (l_index);
l_index := list_in.NEXT (l_index);
END LOOP;
END;
END;
/
BEGIN
plch_tester;
END;
/
/* Non-existent built-in */
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
BEGIN
list_out := dense (list_in);
END;
END;
/
BEGIN
plch_tester;
END;
/
/* Slow approach with FOR Loop */
CREATE OR REPLACE PACKAGE BODY plch_pkg
IS
PROCEDURE make_dense (list_in IN list_t, list_out OUT list_t)
IS
BEGIN
FOR indx IN list_in.FIRST .. list_in.LAST
LOOP
IF list_in.EXISTS (indx)
THEN
list_out (list_out.COUNT + 1) := list_in (indx);
END IF;
END LOOP;
END;
END;
/
BEGIN
plch_tester;
END;
/
/* Clean up */
DROP PACKAGE plch_pkg
/
DROP PROCEDURE plch_tester
/
|
|