I’ve written about collections before; but usually in the context of solving some other problem rather than focusing on them specifically. I’ll begin with a quick review of some of those previous topics and then dig into the syntax details of working with collection types.
Previous Examples
Using the COLLECT operator we can create a collection of varchar2 values that could then be converted into a CLOB value. This is useful when the varchar2 limit of LISTAGG is reached and the overflow option won’t satisfy your needs.
When writing various implementations of an unusual aggregation, using a collection as the input parameter to a user-defined aggregate greatly simplified the queries needed to solve the original question. In other solutions I also used collections as a means of passing an arbitrary number of parameters to a routine.
While answering another friend’s question I explored numerous possible solutions to illustrate methods of approaching the problem and showing him a variety of syntax. This included instantiating collections explicitly within some of the solutions so characters of words could be checked for membership within either of the collections. These methods were used in two of the three-article series: here and here.
Collection Types
In the previous articles I made frequent use of nested tables. I often create generic types as follows:
CREATE OR REPLACE TYPE vctab AS TABLE OF VARCHAR2(4000); CREATE OR REPLACE TYPE numtab AS TABLE OF NUMBER;
Alternately, if I wanted a collection with a maximum size I could use a varying array type. If I had a use for one, I would likely be creating it with a shorter varchar2 size limit to correspond to some expected range of data values.
CREATE OR REPLACE TYPE vc_varray AS VARRAY(10) OF VARCHAR2(50); CREATE OR REPLACE TYPE num_varray AS VARRAY(10) OF NUMBER;
While varray types are legal and have uses, nested tables are more commonly used in SQL context. Not only are nested tables more flexible, but the SQL conditions, operators, and functions for collections only support nested table types.
Creating Collections
As seen in the articles above, a nested table collection can be created using an object construct the COLLECTION function, or a pl/sql function returning a collection type.
select collect(empno) from emp;
select vctab('abc','def','xyz') from dual;
When using COLLECT it may be necessary to cast the collection to a defined type in order to pass it to a procedure or function.
select cast(collect(empno) as numtab) from emp;
PL/SQL functions may return a collection type as well, some commonly used functions are those found in the DBMS_XPLAN package, such as DBMS_XPLAN.DISPLAY.
You can also use object constructors to create a varray or pl/sql routines to return a varray variable..
select vc_varray('abc','def','xyz') from dual;
As this article is SQL-focused, I will explore the PL/SQL options more in another article, but some uses can be seen in the articles linked to above.
Using Collections
One of the most common uses for a collection type is to query from it as if it were a table. Prior to 12c, doing so required the use of the TABLE function, but as of 12c, the collections can be queried directly.
select * from vctab('abc','def','xyz');
select * from table(vctab('abc','def','xyz'));
select * from vc_varray('abc','def','xyz');
select * from table(vc_varray('abc','def','xyz'));
Nested Table Conditions
Nested Table collections have a few conditions recognized in SQL; but varrays do not have such conditions available to them.
These SQL conditions include:
- Is (not) a Set
Checks if the elements of the collection are unique, empty collections are still sets
- Is (not) Empty
Checks if the collection has zero elements, a collection of null values is not an empty set
- (not) Member
Checks if a given value is an element of a set, as with most null conditions check if null is a member of a collection returns null, even if the collection does contain a null value.
- (not) Submultiset
Checks if every member of one collection is also a member of another collection, except for null values
The sample query below shows all of the conditions in use with an assortment of collections.
SQL> SELECT set_elements,
2 CASE WHEN t IS A SET
3 THEN 'Yes' ELSE 'No' END is_a_set,
4 CASE WHEN t IS EMPTY
5 THEN 'Yes' ELSE 'No' END is_empty,
6 CASE WHEN 'a' MEMBER OF t
7 THEN 'Yes' ELSE 'No' END a_is_a_member,
8 CASE WHEN t SUBMULTISET OF vctab('a','b','c','d','e')
9 THEN 'Yes' ELSE 'No' END subset_of_abcde
10 FROM (SELECT 'a,b,c,d,e' set_elements, vctab('a','b','c','d','e') t FROM DUAL
11 UNION ALL
12 SELECT 'd,e,c', vctab('d','e','c') FROM DUAL
13 UNION ALL
14 SELECT 'a,b,a,b', vctab('a','b','a','b') FROM DUAL
15 UNION ALL
16 SELECT '~empty~', vctab() FROM DUAL
17 UNION ALL
18 SELECT '~NULL~', CAST(NULL AS vctab) FROM DUAL);
SET_ELEMENTS IS_A_SET IS_EMPTY A_IS_A_MEMBER SUBSET_OF_ABCDE
------------ -------- -------- ------------- ---------------
a,b,c,d,e Yes No Yes Yes
d,e,c Yes No No Yes
a,b,a,b No No Yes No
~empty~ Yes Yes No Yes
~NULL~ No No No No
Nested Table Operators
Nested tables also have operators similar to those used with sql result sets. I have used the TABLE function in the example queries below; but it is not necessary in 12c and above.
- MULTISET EXCEPT
Similar to the MINUS operator between two query results
SQL> SELECT *
2 FROM TABLE(
3 vctab('a','b','c','d','e')
4 MULTISET EXCEPT
5 vctab('d','e','c','f','g')
6 );
COLUMN_VALUE
--------------------------
a
b
- MULTISET INTERSECT
Similar to the INTERSECT operator between two query results
SQL> SELECT *
2 FROM TABLE(
3 vctab('a','b','c','d','e')
4 MULTISET INTERSECT
5 vctab('d','e','c','f','g')
6 );
COLUMN_VALUE
---------------------
c
d
e
- MULTISET UNION
Similar to the UNION operator between two query results except the default is UNION ALL rather than UNION DISTINCT which is the opposite of the normal sql query operator.
SQL> SELECT *
2 FROM TABLE(
3 vctab('a','b','c','d','e')
4 MULTISET UNION
5 vctab('d','e','c','f','g')
6 );
COLUMN_VALUE
------------------
a
b
c
d
e
d
e
c
f
g
Nested Table Functions
Nested tables also come with a unique set of functions.
- SET
The set function removes duplicate values from a collection, turning it into a proper set. We see this by applying the function to the result of the previous union query.
SQL> SELECT *
2 FROM TABLE(
3 SET(
4 vctab('a','b','c','d','e')
5 MULTISET UNION ALL
6 vctab('d','e','c','f','g')
7 )
8 );
COLUMN_VALUE
---------------
a
b
c
d
e
f
g
- POWERMULTISET
Returns a collection consisting of all non-empty subsets of the collection
SQL> SELECT *
2 FROM TABLE(
3 POWERMULTISET(
4 vctab('a','b','c')
5 )
6 );
COLUMN_VALUE
-----------------------
VCTAB('a')
VCTAB('b')
VCTAB('a', 'b')
VCTAB('c')
VCTAB('a', 'c')
VCTAB('b', 'c')
VCTAB('a', 'b', 'c')
- POWERMULTISET_BY_CARDINALITY
Returns a collection consisting of all subsets of the collection with a given number of elements.
SQL> SELECT *
2 FROM TABLE(
3 POWERMULTISET_BY_CARDINALITY(
4 vctab('a','b','c','d'),3
5 )
6 );
COLUMN_VALUE
-----------------------
VCTAB('a', 'b', 'c')
VCTAB('a', 'b', 'd')
VCTAB('a', 'c', 'd')
VCTAB('b', 'c', 'd')
This concludes the native SQL functionality for nested tables and varrays. I’ll cover more use cases of collections with PL/SQL and declaring and using table columns using these types in future articles.