Oracle Collections
Collections are powerful structures because they enable you to develop programs that manage large sets of data in memory. You can build collections of any SQL and PL/SQL datatype. Some collections onl work in PL/SQL where others work in both SQL and PL/SQL.
There are three types of collections that Oracle uses
VArray | They are densely populated arrays (no gaps in the index sequence) and behave like traditional programming arrays. They can be accessed by either SQL or PL/SQL, at creation they have a fixed size which cannot be changed. Varrays use sequential integers for there subscripts (index), however the subscript starts from 1 not 0. You would use a varray when the physical size of the collection is static and the collection may be used in tables. These are like the traditional arrays in other programming languages. |
Nested Tables | They are initially defined as densely populated arrays but become sparsely populated when records are deleted. They can be accessed by either SQL or PL/SQL, they can also be dynamically extended. Nested Tables use sequential integers for there subscripts (index), however the subscript starts from 1 not 0. You would use nested tables when the physical size is unknown due to run-time variations and when the type may be used in tables. These are like the traditional lists or bags in other programming languages. |
Associative Array | They are sparsely populated arrays which means the numbering does not have to be sequential, only unique. The subscript supports both unique integers or strings, they can grow dynamically but they can only be accessed via PL/SQL. Associative arrays use sequential or non-sequential integers or strings for there subscript. You would associative arrays when the physical size is unknow due to run-time variations and when the type will not be used in tables. These are like the traditional sets or maps in other programming languages. |
Varrays are single dimesional structures which can be used in table, record and object definitions, they can be accessed via both SQL and PL/SQL. Varrays have to be defined, declared and inialized, also remember that the subscript starts at 1 not 0.
VArray example | -- SQL Example for i in v_char_data.first .. v_char_data.last |
Complex example (using database table) |
create or replace type strings as varray(3) of varchar2(30 chars); create table individuals ( create table addresses ( insert into individuals values (individuals.nextval, 'Paul Valle', 'Mr'); create or replace type varray_nested_table is table of varchar2(30); -- Updating data address address_type; |
Nested Tables are single dimesional structures which can be used in table, record and object definitions, they can be accessed via both SQL and PL/SQL. Nested Tables have to be defined, declared and inialized, also remember that the subscript starts at 1 not 0. The size of a Nested table is only constrained with the available memory in the SGA.
Nested table example | -- SQL Example DECLARE -- PL/SQL Example for i in v_char_data.first .. v_char_data.last Note: extend is a method that adds a new empty element to a nested table, this must be called first. |
Complex Nested table example | see above in the varray section |
Associated Arrays are single dimesional structures which can be used in programming structures. They can only be accessed in PL/SQL, they have a different approach to Varrays and Nested tables
You cannot store an associative array directly in the database, so you need logic to store and retrieve the values in this type of collection with the database, it also cannot be use in SQL.
associative array example | -- SQL Example -- Define required collection data types. -- Declare a varray. -- Declare empty associative array. -- Print assigned output title. -- Print an indexed element from the array. |
Oracle 11g delivers collections set operators, they act and function like SQL set operators in select statements. They only work with varrays and nested tables because they require numeric index values. You chave to migrate associate arrays into varrays or nested tables in order to use them.
cardinality | counts the number of elements in a collection, the elements do not have to be unique |
empty | check whether a variable is null or not null |
member of | checks if the left operand is a member of the collection in the right operand |
multiset except | lets you find the elements remaining from the first set after removing any matching elements from the second set. |
multiset intersect | lets you find the intersection or matching values between two sets. |
multiset union | perform a union all operation on two collections. |
set | removes duplicates from a collection and thereby creates a set of unique values |
submultiset | compares the left operand against the right operand to determine if the left operand is a subset of the right operand. |
Examples |
|
Requires scripts for running below | CREATE OR REPLACE TYPE list IS TABLE OF NUMBER; / CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS |
cardinality | DECLARE -- Returns in |
empty | if <list> IS NOT EMPTY -- check to see if the list is empty then ........ end if; |
member of | DECLARE type list is table of varchar2(10); n varchar2(10) := 'One'; a list := ('One', 'Two', 'Three'); BEGIN if n MEMBER OF a then dbms_output.put_line('n is a member'); end if; END; / |
multiset except | DECLARE a list := list(1,2,3,4); b list := list(4,5,6,7); BEGIN dbms_output.put_line(format_list(a MULTISET EXCEPT b )); END; / -- Results in (1,2,3) -- only element 4 exists in both sets , thus removes 4 from -- the first set |
multiset intersect | DECLARE a list := list(1,2,3,4); b list := list(4,5,6,7); BEGIN dbms_output.put_line(format_list(a MULTISET INTERSECT b )); END; / -- Results in (4) -- only element 4 exists in both sets |
multiset union | DECLARE Note: you can use the distinct or set keywords to eliminate duplicates |
set | DECLARE a list := list(1,2,3,3,4,4,5,6,6,7); BEGIN dbms_output.put_line(format_list(SET(A)); END; / -- Results in (1,2,3,4,5,6,7) -- returns all elements excuding duplicates |
submultiset | DECLARE if NOT a SUBMULTISET c |
There are a number of operations that you can perform on a collection
EXISTS | check to see if an element in a collection exists |
collection.exists(index) |
COUNT | returns the number of elements |
collection.count |
LIMIT | returns the maximum number of entries in a VARRAY | collection.limit |
FIRST | returns the first element | collection.first |
LAST | returns the last element | collection.last |
NEXT | returns the next element in the collection | collection.next |
PRIOR | returns previous element in a collection | collection.prior |
EXTEND | extends the number of elements in a collection | collection.extend -- extend one element collection.extend(n) -- extend x elements collection.extend(n, I) -- extend n elements, copy l elements |
TRIM | removes elements from the end of a collection | collection.trim collection.trim(n) |
DELETE | removes elements from a collection |
collection.delete -- delete one element collection.delete(n) -- delete x elements collection.delete(m,n) -- delete from m to n |
The table below summaries the differences between the above collections
Associate Array | Nested Table | VARRAY | |
index type | Number or String | Number | Number |
fixed size declared | No | No | Yes |
spare index allowed | Yes | Yes | No |
store in database | No | Yes | Yes |
access individual elements in stored collection | n/a | Yes | No |
Executing SQL statements in programs causes a context switch between the PL/SQL and SQL engines, too many context switches and performance degrades, to reduce context switches we use bulk collect which lets us transfers rows between SQL and PL/SQL as collections. Normally developers usually tend to use the cursor for loops method for processing data, they declare a cursor, open it, fetch from it row by row in a loop and process the row they fetched.
There are two bulk collect attributes that you can use
%BULK_EXCEPTION(i) | lets you see whether or not a row encountered an error during a bulk insert, update or delete. |
%BULK_ROWCOUNT(i) | lets you see whether or not an element is altered by a bulk insert, update or delete. |
Here are the examples
Implicit bulk collection | select column1, column2 collect bulk into collection1, collection 2from table_name [where statements]; |
Explicit bulk collection | fetch cursor_name [parameter1, etc] bulk collect into collection1, collection2 [limit rows]; |
Bulk collect example | DECLARE |
Bulk collect example (limit rows) |
DECLARE cursor test_cursor select object_name from t_all_objects; type cl_type is table of test_cursor%rowtype; recl cl_type ; BEGIN open test_cursor; loop fetch test_cursor bulk collect into recl limit 2000; for i in 1 .. recl.count loop null; end loop; exit when test_cursor%notfound; end loop; END; |
The forall statement basically does the same as the bulk collect statement, the construct packages up multiple write statements and sends them off to the oracle database in a single message thus reducing overall performance of the whole operation.
indices of clause allows bulk operation on a sparse collection (where elements have been deleted) by removing the reference to specific elements.
values of clause allows values of one collection to be used as index pointers to another collection.
forall example | forall <index> IN lower_bound .. upper_bound |
forall example (exception capature) |
forall <index> IN lower_bound .. upper_bound save exceptions sql_statement; |
forall example (indices of) |
forall <index> INDICES OF lower_bound .. upper_bound sql_statement; |
forall example (values of) |
forall <index> VALUES OF lower_bound .. upper_bound sql_statement; |