1. Definition of Collection
“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.
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.”
2. Persistent and non-persistent collections
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.
3. Declarations
3.1 Nested tables
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:
BINARY_INTEGER, PLS_INTEGER
BOOLEAN
LONG, LONG RAW
NATURAL, NATURALN
POSITIVE, POSITIVEN
REF CURSOR
SIGNTYPE
STRING
PL/SQL
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)
OF element_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) ;
3.3 Index-by tables
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 ;
4. Initalization
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 ;
4.2 Varrays
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).
4.3 Index-by tables
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 ;
5. Handle the collection
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 initialized
Begin
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 ;
Comparing collections
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 THEN
dbms_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
6. Methods
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.
“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.
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.”
2. Persistent and non-persistent collections
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.
3. Declarations
3.1 Nested tables
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:
BINARY_INTEGER, PLS_INTEGER
BOOLEAN
LONG, LONG RAW
NATURAL, NATURALN
POSITIVE, POSITIVEN
REF CURSOR
SIGNTYPE
STRING
PL/SQL
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)
OF element_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) ;
3.3 Index-by tables
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 ;
4. Initalization
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 ;
4.2 Varrays
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).
4.3 Index-by tables
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 ;
5. Handle the collection
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 initialized
Begin
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 ;
Comparing collections
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 THEN
dbms_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
6. Methods
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.
|
