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.
A collection type defined in a pl/sql block is a local type. It is available only in the block, and is stored in the database only if the block is in a standalone or package subprogram.
A collection type defined at schema level is a standalone type. You create it with the "CREATE TYPE Statement". It is stored in the database until you drop it with the "DROP TYPE Statement".
A collection type defined in a package specification is a public item. You can reference it from outside the package by qualifying it with the package name (package_name.type_name). It is stored in the database until you drop the package.
An associative array is a set of key-value pairs. Each key is a unique index,
used to locate the associated value with the syntax variable_name(index).
The data type of index can be either a string type (VARCHAR or VARCHAR2) or INTEGER. Indexes are stored in sort order, not creation order.
The operation of exporting a union array will replace the data type of the index with varchar2 to varchar.The operation of importing a union array will replace the data type of the index from varchar2 to varchar.
PERFORM is not supported for manipulating associative array and dbms_output.put_line is not supported for printing the elements of the associative array.
For example, define a type of associative array indexed by integer, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values (in sort order, not creation order).
DECLARE
-- Associative array indexed by integer:
TYPE population IS TABLE OF varchar -- Associative array type
INDEX BY integer; -- indexed by integer
city_population population; -- Associative array variable
i integer;
BEGIN
-- Add elements (key-value pairs) to associative array:
city_population('2') := 'Smallville';
city_population('5') := 'Midland';
city_population('3') := 'Megalopolis';
FOR i IN 1..6 LOOP
IF city_population.EXISTS(i) THEN
dbms_output.put_line ('city_population(' || i || ')' || ' exists');
ELSE
dbms_output.put_line ('city_population(' || i || ')' || ' does not exist');
END IF;
END LOOP;
END;
/
For example, defines a type of associative array indexed by string, declares a variable of that type, populates the variable with three elements, changes the value of one element, and prints the values.
DECLARE
-- Associative array indexed by varchar2(64):
TYPE population IS TABLE OF NUMERIC -- Associative array type
INDEX BY VARCHAR2(64); -- indexed by varchar2(64)
city_population population; -- Associative array variable
BEGIN
-- Add elements (key-value pairs) to associative array:
city_population('Smallville') := 2000;
city_population('Midland') := 750000;
city_population('Megalopolis') := 1000000;
raise info 'the count elements is %', city_population;
city_population('Midland') := 50000;
raise info 'the count elements is %', city_population;
END;
/
dbms_output.put_line is not supported for printing the elements of the associative array, so use raise to print.
In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.
When you retrieve a nested table value from the database into a pl/sql nested table variable,
pl/sql gives the rows consecutive indexes, starting at 1. Using these indexes,
you can access the individual rows of the nested table variable.
The syntax is variable_name(index). The indexes and row order of a nested table
might not remain stable as you store and retrieve the nested table from the database.
The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.
Example: Nested Table of Local Type
DECLARE
TYPE Roster IS TABLE OF VARCHAR(15); -- nested table type
-- nested table variable initialized with constructor:
names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
DBMS_OUTPUT.PUT_LINE('Initial Values:');
FOR i IN names2.FIRST .. names2.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names2(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------');
names2(5) := 'P Perez'; -- add one element
FOR i IN names2.FIRST .. names2.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names2(i));
END LOOP;
END;
/
Example: Nested Table of Standalone Type
CREATE TYPE names2 IS TABLE OF VARCHAR(15); DECLARE nt names2 ; BEGIN nt(1) = 'D Caruso'; nt(2) = 'J Hamil'; nt(3) = 'R Singh'; DBMS_OUTPUT.PUT_LINE(nt(2)); END; /
Example: Nested Table of Composite Type
create table t(a int,b text);
insert into t values(1,'1');
insert into t values(2,'2');
select dbms_output.serveroutput('t');
create or replace procedure p1 is
TYPE array_table IS TABLE OF t%rowtype;
a_table array_table := array_table();
BEGIN
SELECT t.* BULK COLLECT INTO a_table FROM t;
FOR i IN a_table.first..a_table.last LOOP
a_table(i).a := 123;
a_table(i).b := 'test';
dbms_output.put_line('a_table(i).a = '||a_table(i).a||', a_table(i).b = '||a_table(i).b);
END LOOP;
END;
/
Example: Using Nested Table Element as Insert Source
create table t(a int,b float,c number);
insert into t values(1,10,100);
insert into t values(2,20,200);
create or replace procedure p1 is
TYPE array_table IS TABLE OF t%rowtype;
a_table array_table := array_table();
BEGIN
SELECT t.* BULK COLLECT INTO a_table FROM t;
execute immediate 'truncate table t';
FOR i IN a_table.first..a_table.last LOOP
a_table(i).a := a_table(i).a * 100;
a_table(i).b := a_table(i).b * 100;
a_table(i).c := a_table(i).c * 100;
INSERT INTO t VALUES a_table(i);
END LOOP;
END;
/
Variable-sized array is an array whose element count can vary from zero (empty) to the declared maximum size, and overruns are also supported here.
To access the elements of a mutable array, use the syntax variable_name(index). When storing and retrieving mutable arrays in the database, their indexes and element order will remain stable.
As you add or delete elements, the amount of memory occupied by the mutable array variable can dynamically increase or decrease.
Currently, only local varrays are supported, not global varrays .
Example: varrays of local type
DECLARE
TYPE Roster IS VARRAY(10) OF VARCHAR(15); -- varray type
-- varray variable initialized with constructor:
names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
DBMS_OUTPUT.PUT_LINE('Initial Values:');
FOR i IN names2.FIRST .. names2.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names2(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------');
names2.extend;
names2(5) := 'P Perez'; -- add one element
FOR i IN names2.FIRST .. names2.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names2(i));
END LOOP;
END;
/
A collection method is a pl/sql subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use and your applications easier to maintain.
Table 42.1 shows collection methods.
Table 42.1. Collection methods
| Method | Description |
|---|---|
| DELETE | Deletes elements from collection. |
| EXTEND | Adds elements to end of nested table. |
| EXISTS | Returns TRUE if and only if specified element of nested table exists. |
| FIRST | Returns first index in collection. |
| LAST | Returns last index in collection. |
| COUNT | Returns number of elements in collection. |
The basic syntax of a collection method invocation is:
collection_name.method