PL/SQL Collections

In this chapter, we will discuss the Collections in PL/SQL. A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collection types −

• Index-by tables or Associative array
• Nested table
• Variable-size array or 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
Variablesize array (Varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level	Yes

We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss the PL/SQL tables.

Both types of PL/SQL tables, i.e., the index-by tables and the nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

An index-by table (also called an associative array) 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.

An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name, the keys of which will be of the subscript_type and associated values will be of the element_type

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

Example

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

When the above code is executed at the SQL prompt, it produces the following result −

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Example

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+  

DECLARE 
   CURSOR c_customers 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_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/ 

When the above code is executed at the SQL prompt, it produces the following result −

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Nested Tables

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects −

• 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, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

A nested table is created using the following 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.

A nested table can be stored in a database column. It can further be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example

The following examples illustrate the use of nested table −

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('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   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; 
/  

When the above code is executed at the SQL prompt, it produces the following result −

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed. 

Example

Elements of a nested table can also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as −

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/ 

When the above code is executed at the SQL prompt, it produces the following result −

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed. 

• 100% Practical Training

• Hands
  On
  Training

• 100%
  Job
  Gurantee

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose −

S.No	Method Name & Purpose
1	EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNT Returns the number of elements that a collection currently contains.
3	LIMIT Checks the maximum size of a collection.
4	FIRST Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
5	LAST Returns the last (largest) index numbers in a collection that uses the integer subscripts.
6	PRIOR(n) Returns the index number that precedes index n in a collection.
7	NEXT(n) Returns the index number that succeeds index n.
8	EXTEND Appends one null element to a collection.
9	EXTEND(n) Appends n null elements to a collection.
10	EXTEND(n,i) Appends n copies of the ith element to a collection.
11	TRIM Removes one element from the end of a collection.
12	TRIM(n) Removes n elements from the end of a collection.
13	DELETE Removes all elements from a collection, setting COUNT to 0.
14	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.
15	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.

Collection Exceptions

The following table provides the collection exceptions and when they are raised −

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

PL/SQL Collections Training Venue:

Are you located in any of these areas - Adyar, Mylapore, Nandanam, Nanganallur, Nungambakkam, OMR, Pallikaranai, Perungudi, Ambattur, Aminjikarai, Adambakkam, Anna Nagar, Anna Salai, Ashok Nagar, Besant Nagar, Choolaimedu, Chromepet, Medavakkam, Porur, Saidapet, Sholinganallur, St. Thomas Mount, T. Nagar, Tambaram, Teynampet, Thiruvanmiyur, Thoraipakkam,Vadapalani, Velachery, Egmore, Ekkattuthangal, Guindy, K.K.Nagar, Kilpauk, Kodambakkam, Madipakkam, Villivakkam, Virugambakkam and West Mambalam.

Our Adyar office is just few kilometre away from your location. If you need the best PL/SQL Collections, driving couple of extra kilometres is worth it!

Related Trainings

Interested to be a trainer?

jobs@greenstech.in

For corporate Training

corp@greenstech.in