The goal of this article is to show the principal
features about the collections.
We will see how to declare, initialize and handle
collection with SQL and PL/SQL.
All the examples have been runned on a 10.1.0.2.0
database release.
This
is what the documentation says about collections:
“A collection
is an ordered group of elements, all of the same type. It is a general concept that
encompasses lists, arrays, and other familiar datatypes. Each element has a
unique subscript that determines its position in the collection.
PL/SQL offers these
collection types:
·
Index-by
tables, also known as associative
arrays, let you look up elements using arbitrary numbers and strings
for subscript values. (They are similar to hash tables in other
programming languages.)
·
Nested
tables hold an
arbitrary number of elements. They use sequential numbers as subscripts. You
can define equivalent SQL types, allowing nested tables to be stored in
database tables and manipulated through SQL.
·
Varrays (short for variable-size arrays) hold a fixed number
of elements (although you can change the number of elements at runtime). They
use sequential numbers as subscripts. You can define equivalent SQL types,
allowing varrays to be stored in database tables. They can be stored and
retrieved through SQL, but with less flexibility than nested tables.
Although collections can
have only one dimension, you can model multi-dimensional arrays by creating
collections whose elements are also collections.
To use collections in an
application, you define one or more PL/SQL types, then define variables of
those types. You can define collection types in a procedure, function, or
package. You can pass collection variables as parameters, to move data between
client-side applications and stored subprograms.
To look up data that is more
complex than single values, you can store PL/SQL records or SQL object types in
collections. Nested tables and varrays can also be attributes of object types.”
Index-by tables cannot be stored in database tables, so they are non-persistent.
You
cannot use them in a SQL statement and are available only in PL/SQL blocks.
Nested
tables and Varrays are persistent. You can use
the CREATE TYPE statement to create them in the database, you can read and
write them from/to a database column.
Nested
tables and Varrays must have been initialized before you can use them.
TYPE type_name IS TABLE OF element_type [NOT NULL];
With
nested tables declared within PL/SQL, element_type can
be any PL/SQL datatype except : REF CURSOR
Nested
tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:
q
BINARY_INTEGER, PLS_INTEGER
q
BOOLEAN
q
LONG, LONG RAW
q
NATURAL, NATURALN
q
POSITIVE, POSITIVEN
q
REF CURSOR
q
SIGNTYPE
q
STRING
Declare
TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
SQL
CREATE [OR
REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER ;
3.2 Varrays
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OFelement_type[NOT NULL];
size_limit is a positive integer
literal representing the maximum number of elements in the array.
PL/SQL
Declare
TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
SQL
CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];
INDEX BY key_type;
The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).
It can also be VARCHAR2 or
one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG
which is equivalent to declaring a key type of VARCHAR2(32760).
The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for
an associative array.
Declare
TYPE
TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER ;
Only Nested tables and varrays need
initialization.
To initialize a collection, you use
the “constructor” of the collection which name is the same as the collection.
4.1 Nested
tables
Declare
TYPE
TYP_NT_NUM IS TABLE OF NUMBER ;
Nt_tab
TYP_NT_NUM ;
Begin
Nt_tab :=
TYP_NT_NUM( 5, 10, 15, 20 ) ;
End ;
Declare
TYPE
TYP_V_DAY IS VARRAY(7) OF VARCHAR2(15) ;
v_tab
TYP_V_DAY ;
Begin
v_tab :=
TYP_NT_NUM(
‘Sunday’,’Monday’,’Tuesday’,’Wedneday’,’Thursday’,’Friday’,’Saturday’ ) ;
End ;
It is not required to initialize all the elements of a collection. You
can either initialize no element. In this case, use an empty constructor.
v_tab := TYP_NT_NUM() ; This collection is empty, which is different than a NULL collection (not initialized).
Declare
TYPE TYP_TAB
IS TABLE OF NUMBER INDEX BY PLS_INTEGER ;
my_tab TYP_TAB ;
Begin
my_tab(1) :=
5 ;
my_tab(2) :=
10 ;
my_tab(3) :=
15 ;
End ;
While the collection is not initialized (Nested tables and Varrays), it
is not possible to manipulate it.
You can test if a collection is initialized:
Declare
TYPE TYP_VAR_TAB is VARRAY(30) of varchar2(100) ;
tab1 TYP_VAR_TAB ; -- declared but not initializedBegin
If Tab1 IS NULL Then -- NULL collection, have to initialize it -- Tab1 := TYP_VAR_TAB('','','','','','','','','',''); End if ; -- Now, we can handle the collection --End ;
To access an element of a collection, we need to use a subscript value that indicates the unique element of the collection.
The subscript is of type integer or
varchar2.
Declare
Type TYPE_TAB_EMP IS
TABLE OF Varchar2(60) INDEX BY BINARY_INTEGER ;
emp_tab TYPE_TAB_EMP ;
i pls_integer ;
Begin
For i in 0..10 Loop
emp_tab( i+1 ) := 'Emp ' || ltrim( to_char( i ) ) ;
End loop ;
End ;
Declare
Type TYPE_TAB_DAYS IS
TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20) ;
day_tab TYPE_TAB_DAYS ;
Begin
day_tab( 'Monday' ) :=
10 ;
day_tab( 'Tuesday' ) :=
20 ;
day_tab( 'Wednesday' ) := 30 ;
End ;
It is possible to assign values of a collection to another collection if
they are of the same type.
Declare
Type TYPE_TAB_EMP IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ; Type TYPE_TAB_EMP2 IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER ; tab1 TYPE_TAB_EMP := TYPE_TAB_EMP( ... ); tab2 TYPE_TAB_EMP := TYPE_TAB_EMP( ... ); tab3 TYPE_TAB_EMP2 := TYPE_TAB_EMP2( ... ); Begin
tab2 := tab1 ; -- OK tab3 := tab1 ; -- Error : types not similar...
End ;
Until the 10g release, collections cannot be directly compared for
equality or inequality.
The 10g release allows to do some comparaisons between collections:
You can compare collections of same type to verify if they ar equals or
not equals
DECLARE
TYPE Colors IS TABLE OF VARCHAR2(64); primaries Colors := Colors('Blue','Green','Red'); rgb Colors := Colors('Red','Green','Blue'); traffic_light Colors := Colors('Red','Green','Amber');BEGIN
-- We can use = or !=, but not < or >.
-- 2 collections are equal even if the membersare not in the same order.
IF primaries = rgb THEN dbms_output.put_line('OK, PRIMARIES & RGB have same members.'); END IF; IF rgb != traffic_light THENdbms_output.put_line('RGB & TRAFFIC_LIGHT have different members');
END IF;END;
You can also apply some operators on the collections:
DECLARE
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
nt4 nested_typ := nested_typ(1,2,4);
reponse BOOLEAN;
combien NUMBER;
PROCEDURE verif(test BOOLEAN DEFAULT NULL, label IN VARCHAR2
DEFAULT NULL, quantity NUMBER DEFAULT NULL) IS
BEGIN
IF test IS NOT NULL THEN
dbms_output.put_line(label || ' -> ' || CASE test WHEN
TRUE THEN 'True' WHEN FALSE THEN 'False' END);
END IF;
IF quantity IS NOT NULL THEN
dbms_output.put_line(quantity);
END IF;
END;
BEGIN
reponse := nt1 IN (nt2,nt3,nt4); --
true, nt1 correspond to nt2
verif(test => reponse, label => 'nt1 IN (nt2,nt3,nt4)');
reponse := nt1 SUBMULTISET OF nt3; --
true, all elements correpond
verif(test => reponse, label => 'nt1 SUBMULTISET OF nt3');
reponse := nt1 NOT SUBMULTISET OF nt4; -- true
verif(test => reponse, label => 'nt1 NOT SUBMULTISET OF
nt4');
combien := CARDINALITY(nt3); --
number of elements of nt3
verif(quantity => combien);
combien := CARDINALITY(SET(nt3)); -- number of distinct elements
verif(quantity => combien);
reponse := 4 MEMBER OF nt1; -- false, no corresponding element
verif(test => reponse, label => '4 MEMBER OF nt1');
reponse := nt3 IS A SET; -- false,
nt3 have duplicated elements
verif(test => reponse, label => 'nt3 IS A SET' );
reponse := nt3 IS NOT A SET; --
true, nt3 have diplicated elements
verif(test => reponse, label => 'nt3 IS NOT A SET' );
reponse := nt1 IS EMPTY; -- false,
nt1 have elements
verif(test => reponse, label => 'nt1 IS EMPTY' );
END;
nt1 IN (nt2,nt3,nt4) -> True
nt1 SUBMULTISET OF nt3 -> True
nt1 NOT SUBMULTISET OF nt4 -> True
4
3
4 MEMBER OF nt1 -> False
nt3 IS A SET -> False
nt3 IS NOT A SET -> True
nt1 IS EMPTY -> False
We can use the following methods on a collection:
·
EXISTS
·
COUNT
·
LIMIT
·
FIRST and LAST
·
PRIOR and NEXT
·
EXTEND
·
TRIM
·
DELETE
A collection method
is a built-in function or procedure that operates on collections and is called
using dot notation.
collection_name.method_name[(parameters)]
Collection methods cannot be called
from SQL statements.
Only the EXISTS method can be used on
a NULL collection.
all other methods applied on a null collection raise the
COLLECTION_IS_NULL error.
6.1 EXISTS(index)
Returns TRUE if the index element exists in the collection, else it
returns FALSE.
Use this method to be sure you are doing a valid operation on the collection.
This method does not raise the SUBSCRIPT_OUTSIDE_LIMIT exception if used on an
element that does not exists in the collection.
If my_collection.EXISTS(10)
Then
My_collection.DELETE(10) ;
End if ;
6.2
COUNT
Returns the number of elements in a collection.
SQL> Declare
2 TYPE
TYP_TAB IS TABLE OF NUMBER;
3 my_tab
TYP_TAB := TYP_TAB( 1, 2, 3, 4, 5 );
4 Begin
5 Dbms_output.Put_line( 'COUNT = ' ||
To_Char( my_tab.COUNT ) ) ;
6 my_tab.DELETE(2) ;
7 Dbms_output.Put_line( 'COUNT = ' ||
To_Char( my_tab.COUNT ) ) ;
8 End ;
9 /
COUNT = 5
COUNT = 4
PL/SQL procedure successfully completed.
6.3
LIMIT
Returns the maximum number of elements that a varray can contain.
Return NULL for Nested tables and Index-by tables
SQL> Declare
2 TYPE TYP_ARRAY IS ARRAY(30) OF NUMBER ;
3 my_array TYP_ARRAY :=
TYP_ARRAY( 1, 2, 3 ) ;
4 Begin
5 dbms_output.put_line( 'Max array size is '
|| my_array.LIMIT ) ;
6 End;
7 /
Max array size is 30
6.4
FIRST and LAST
Returns the first or last subscript of a collection.