Working with PL/SQL collections is an important part of the Oracle Database 11g: Advanced PL/SQL exam for which I’m currently studying. This post serves as a refresher on the different types of collections that exist in the wonderful PL/SQL language.
Associative arrays
- Previously known as index-by tables or PL/SQL tables.
- Single-dimensional set of key-value pairs. Each key is unique, and is used to locate the corresponding value.
- Arbitrary key values, which means that the keys don’t need to be consecutive.
- Indexed by BINARY_INTEGER, PLS_INTEGER, VARCHAR2 or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG.
- BINARY_INTEGER and PLS_INTEGER are identical and can be used interchangeably since Oracle Database 10g.
- BULK COLLECT INTO can be used to populate the collection from a query result. The key type cannot be VARCHAR2 in this case.
- An associative array cannot be used as column type in a database table.
set serveroutput on declare type laat_dept_names is table of departments.department_name%type index by pls_integer; laa_dept_names laat_dept_names; l_index number; begin -- bulk collect into example -- select department_name -- bulk collect into laa_dept_names -- from departments; -- add new elements laa_dept_names(1) := 'Administration'; laa_dept_names(2) := 'Marketing'; laa_dept_names(3) := 'Purchasing'; laa_dept_names(4) := 'Human Resources'; -- change existing value associated with key value '4' laa_dept_names(4) := 'HR'; -- remove element laa_dept_names.delete(3); -- print elements l_index := laa_dept_names.first; while l_index is not null loop dbms_output.put_line(laa_dept_names(l_index) || ' (' || l_index || ')'); l_index := laa_dept_names.next(l_index); end loop; end; -- output: -- Administration (1) -- Marketing (2) -- HR (4)
Nested tables
- Single-dimensional array with an unbounded number of elements.
- Initially dense, but can become sparse, because you can delete elements from the collection.
- Dense = collection has no gaps between its elements.
- Sparse = collection can have gaps between its elements.
- Always indexed by an integer.
- A nested table can be used as column type in a database table.
set serveroutput on declare type lntt_dept_names is table of departments.department_name%type; lnt_dept_names lntt_dept_names; l_index number; begin -- initialise the collection with two values -- key values start with 1 and are consecutive lnt_dept_names := lntt_dept_names('Administration', 'Marketing'); -- extend the collection with two extra values lnt_dept_names.extend(2); -- add new elements lnt_dept_names(3) := 'Purchasing'; lnt_dept_names(4) := 'Human Resources'; -- change existing value associated with key value '4' lnt_dept_names(4) := 'HR'; -- remove element -- a nested table can become sparse lnt_dept_names.delete(3); -- print elements l_index := lnt_dept_names.first; while l_index is not null loop dbms_output.put_line(lnt_dept_names(l_index) || ' (' || l_index || ')'); l_index := lnt_dept_names.next(l_index); end loop; end; -- output: -- Administration (1) -- Marketing (2) -- HR (4)
The EXTEND method can accept different parameters:
- EXTEND appends one null element to a collection.
- EXTEND(n) appends n null elements to a collection.
- EXTEND(n, i) appends n copies of the ith element to a collection.
Varrays
- Varrays are very similar to nested tables, except for the fact that you must specify an upper bound.
- Varrays cannot become sparse, which means that you can’t use the DELETE method on varrays.
- A varray can be used as column type in a database table.
set serveroutput on declare type lvat_dept_names is varray(4) of departments.department_name%type; lva_dept_names lvat_dept_names; l_index number; begin -- initialise the collection with two values -- key values start with 1 and are consecutive lva_dept_names := lvat_dept_names('Administration', 'Marketing'); -- extend the collection with two extra values lva_dept_names.extend(2); -- add new elements lva_dept_names(3) := 'Purchasing'; lva_dept_names(4) := 'Human Resources'; -- change existing value associated with key value '4' lva_dept_names(4) := 'HR'; -- a varray can't become sparse -- lva_dept_names.delete(3); -- print elements l_index := lva_dept_names.first; while l_index is not null loop dbms_output.put_line(lva_dept_names(l_index) || ' (' || l_index || ')'); l_index := lva_dept_names.next(l_index); end loop; end; -- output: -- Administration (1) -- Marketing (2) -- Purchasing (3) -- HR (4)