Associative Arrays in Oracle PL/SQL: Introduction - Associative Arrays in Oracle PL/SQL: a simple example (Page 2 of 4 )
I already explained Associative Arrays in the previous section. Now, we shall work more practically. Let us consider the following example:
declare
type year_type is table of number index by binary_integer;
year_sales year_type;
tot_sales number;
begin
year_sales(1990) := 34000;
year_sales(1991) := 45000;
year_sales(1992) := 43000;
tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
dbms_output.put_line('Total sales: ' || tot_sales);
end;
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:
year_sales(1990) := 34000;
year_sales(1991) := 45000;
year_sales(1992) := 43000;
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:
tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
dbms_output.put_line('Total sales: ' || tot_sales);
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.
Next: Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop >>
More Oracle Articles
More By Jagadish Chatarji
