Development and Programming
Working with Collections in Oracle
Oracle PL/SQL provides the datatypes Table and VARRAY to allow you declare index-by tables, nested tables and variable-size arrays:
In this topic, you learn how those types let you reference and manipulate collections of data as whole objects.
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique, either by using the primary key from a SQL table, or by concatenating strings together to form a unique value.
For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:
TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
country_population('Greenland') := 100000;
country_population('Iceland') := 750000;
howmany := country_population('Greenland');
continent_population('Australia') := 30000000;
continent_population('Antarctica') := 1000; -- Creates new entry
continent_population('Antarctica') := 1001; -- Replaces previous value
which := continent_population.FIRST; -- Returns 'Antarctica'
-- as that comes first alphabetically.
which := continent_population.LAST; -- Returns 'Australia'
howmany := continent_population(continent_population.LAST);
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.
Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.
PL/SQL nested tables are like one-dimensional arrays. Within the database, they can be considered one-column database tables. Oracle stores the rows of a nested table in no particular order. But, when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.
Nested tables differ from arrays in two important ways:
Items of type VARRAY are called varrays. A varray has a maximum size, which you must specify in its type definition. To reference an element in a varray, you use standard subscripting syntax. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, Grade(3) references the third element in varray Grades.
In the above example, the current upper bound for varray Grades is 7, but you can extend it to 8, 9, 10, and so on. Thus, a varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.
© 2019 Digcode.com. All rights reserved.