A collection is an ordered group of elements, all of the same data type and are called elements. Each element is addressed by a unique subscript that represents its position in the collection. You access each element by its unique subscript.
PL/SQL has three collection types,
- Associative array (or index-by table)
- Nested table
- Variable-size array (varray)
Oracle documentation provides the following characteristics for each type of collections
|Collection Type||Number of Elements||Subscript Type||Dense or Sparse||Where Created||Can Be Object Type Attribute|
|Associative array (or index-by table)||Unbounded||String or integer||Either||Only in PL/SQL block||No|
|Nested table||Unbounded||Integer||Starts dense, can become sparse||Either in PL/SQL block or at schema level||Yes|
|Variable-size array (varray)||Bounded||Integer||Always dense||Either in PL/SQL block or at schema level||Yes|
Unbounded – there is no limit to the number of elements in the collection.
Dense means that the collection has no gaps between elements – every element between the first and last element is defined and has a value (which can be NULL).
We have already discussed varray in the chapter ‘PL/SQL – Arrays’.
Associative Arrays (Index-By Tables)
An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string.
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name;
DECLARE -- Associative array indexed by string TYPE balance IS TABLE OF NUMBER INDEX BY VARCHAR2(20); -- Associative array variable bal_list balance; name VARCHAR2(10); BEGIN -- adding elements to the table bal_list('AAA') := 2000; bal_list('BBB') := 5000; bal_list('CCC') := 10000; bal_list('DDD') := 8000; -- printing the table name := bal_list.FIRST; WHILE name IS NOT null LOOP dbms_output.put_line ('Balance of ' || name || ' is ' || TO_CHAR(bal_list(name))); name := bal_list.NEXT(name); END LOOP; END; /
Balance of AAA is 2000 Balance of BBB is 5000 Balance of CCC is 10000 Balance of DDD is 8000 PL/SQL procedure successfully completed.
DECLARE CURSOR c_cust is select name from customers; TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; name_list c_list; counter integer :=0; BEGIN FOR n IN c_cust LOOP counter := counter +1; name_list(counter) := n.name; dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); END LOOP; END; /
Customer(1):AAA Customer(2):BBB Customer(3):CCC Customer(4):DDD Customer(5):EEE Customer(6):FFF PL/SQL procedure successfully completed.
Nested Table Collections
A nested table is like a one-dimensional array with an arbitrary number of elements.
A nested table differs from an array in these important ways:
- An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
- An array is always dense (that is, it always has consecutive subscripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.
TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name;
This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.
DECLARE TYPE names_table IS TABLE OF VARCHAR2(10); TYPE grades IS TABLE OF INTEGER; names names_table; marks grades; total integer; BEGIN names := names_table('AAA', 'BBB', 'CCC', 'DDD'); marks:= grades(98, 97, 78, 87); total := names.count; dbms_output.put_line('Total '|| total || ' Students'); FOR i IN 1 .. total LOOP dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); end loop; END; /
Total 4 Students Student:AAA, Marks:98 Student:BBB, Marks:97 Student:CCC, Marks:78 Student:DDD, Marks:87 PL/SQL procedure successfully completed.
A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain.
The following table lists the methods and their purpose
|EXISTS(n)||Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.|
|COUNT||Returns the number of elements that a collection currently contains.|
|LIMIT||Checks the maximum size of a collection.|
|FIRST||Returns the first (smallest) index numbers in a collection that uses the integer subscripts.|
|LAST||Returns the last (largest) index numbers in a collection that uses the integer subscripts.|
|PRIOR(n)||Returns the index number that precedes index n in a collection.|
|NEXT(n)||Returns the index number that succeeds index n.|
|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.|
|TRIM||Removes one element from the end of a collection.|
|TRIM(n)<||Removes n elements from the end of a collection.|
|DELETE||Removes all elements from a collection, setting COUNT to 0.|
|DELETE(n)||Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.|
|DELETE(m,n)||Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.|