HomeOracle Page 2 - Associative Arrays in Oracle PL/SQL: Introduction
Associative Arrays in Oracle PL/SQL: a simple example - Oracle
In this series of articles, we shall concentrate on working with associative arrays in Oracle PL/SQL. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of much more advanced techniques using associative arrays with Oracle PL/SQL.
I shall explain the above example part by part. Let us start with the following statements first:
type year_type is table of number index by binary_integer; year_sales year_type;
Repeating from the previous section, the “year_type” is a user-defined data type which can hold a set (or table) of values (typically of type “number”), organized with a BINARY_INTEGER index.
The “year_sales” is a variable based on the data type “year_type.” Now you can store a huge amount of data (typically in the form of pairs) within the single variable “year_type.” The pair of data should now contain a “key” (of type BINARY_INTEGER) and a “value” (of type NUMBER). Further proceeding we have the following statements:
From the above three statements, you can simply consider that 1990, 1991 and 1992 are “keys” (of type BINARY_INTEGER). Similarly, we have 34000, 45000 and 43000 which are simply “values” (of type NUMBER). So, we added three pairs of data to a single variable, “year_sales.” That’s the trick. Further proceeding we have the following:
The first statement simply retrieves the “values” available at “keys” 1990, 1991 and 1992 (which are nothing but 34000, 45000 and 43000) and finally adds them into a new simple variable, “tot_sales” (of type NUMBER). The second statement simply displays the result.
The above example is the simplest on earth. Let’s try something a bit harder.