PL/SQL Collections

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.

Syntax :

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
table_name type_name;

Example :


-- Associative array indexed by string
-- Associative array variable
 bal_list balance;
 name VARCHAR2(10);
 -- 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 
 ('Balance of ' || name || ' is ' || TO_CHAR(bal_list(name))); 
 name := bal_list.NEXT(name); 
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.

Example 2:

 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; 
 FOR n IN c_cust LOOP 
 counter := counter +1; 
 name_list(counter) :=; 

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.

Syntax :

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.

Example :

 TYPE names_table IS TABLE OF VARCHAR2(10); 
 names names_table; 
 marks grades; 
 total integer; 
 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; 
Total 4 Students
Student:AAA, Marks:98
Student:BBB, Marks:97
Student:CCC, Marks:78
Student:DDD, Marks:87

PL/SQL procedure successfully completed.

Collection Methods

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

Method Name 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.



Post your Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s