What is Collection and What are different types of Collections
in ORACLE?
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:
- Variable-size array or Varray
- Index-by tables or Associative array
- Nested table
Associative
Array:
|
Type t is
Table of something index by pls_integer;
|
Nested
Table:
|
Type t is
table of something;
|
VARRAY:
|
Type t is
varray(123) of something;
|
PL/SQL - Varrays (Variable-Size Arrays)
PL/SQL
programming language provides a data structure called the VARRAY, which can
store a fixed-size sequential collection of elements of the same type. A varray
is used to store an ordered collection of data, but it is often more useful to
think of an array as a collection of variables of the same type.
A Varray (variable-size array) is an array whose number of elements
can vary from zero (empty) to the declared maximum size. To access an element
of a Varray variable, use the syntax Variable_Name
(subscript). The lower bound of subscript is 1; the upper bound
is the current number of elements. The upper bound changes as you add or delete
elements, but it cannot exceed the maximum size. When you store and retrieve a
Varray from the database, its subscripts and element order remain stable.
VGrades(10) – Maximum
Size = 10
D
|
E
|
A
|
F
|
A
|
A Varray type is created with the CREATE TYPE statement. You
must specify the maximum size and the type of elements stored in the Varray.
Using SQL:
The Basic syntax for creating a VRRAY type at the schema
level is:
CREATE OR REPLACE TYPE Varray_Type_name IS
VARRAY(n) of <Element_Type>
For Example:
CREATE Or REPLACE TYPE Namearray AS VARRAY(3)
OF VARCHAR2(10);
Type created.
Using PL/SQL:
The Basic syntax for creating a VRRAY type within a PL/SQL
block is:
TYPE Varray_Type_name IS VARRAY(n) of
<Element_Type>
For Example:
TYPE Namearray IS VARRAY(5) OF VARCHAR2(10);
Type Grades IS VARRAY(5) OF INTEGER;
Example 1: The following program illustrates using
Varrays:
DECLARE
type Namesarray IS VARRAY(5) OF
VARCHAR2(10);
type Grades IS VARRAY(5) OF
INTEGER;
Names Namesarray;
Marks Grades;
Total Integer;
BEGIN
Names := Namesarray('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 SQL prompt,
it produces the following result:
SQL> DECLARE
2 type Namesarray IS VARRAY(5) OF VARCHAR2(10);
3 type Grades IS VARRAY(5) OF INTEGER;
4 Names Namesarray;
5 Marks Grades;
6 Total Integer;
7 BEGIN
8 Names := Namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
9 Marks:= grades(98, 97, 78, 87, 92);
10 Total := Names.count;
11 Dbms_Output.Put_Line('Total '|| total || ' Students');
12 FOR i in 1 ..total LOOP
13 Dbms_Output.Put_Line('Student: ' || names(i) || '
14 Marks: ' || Marks(i));
15 END LOOP;
16 END;
17 /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.
NOTE:
• In Oracle environment,
the starting index for varrays is always 1.
• You can initialize the
Varray elements using the constructor method of the Varray Type, which has the
same name as the Varray.
• Varray are one-dimensional arrays.
• A Varray is
automatically NULL when it is declared and must be initialized before its
elements can be referenced.
Example 2: Elements of a varray
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 EMPLOYEE table stored in our database as:
Select * from EMP_TEST;SQL> SELECT * From EMP_TEST;
ID NAME SALARY STATUS DEPTNO MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
115 Akhil 70
102 Ankit 8000 Active 20 101
104 Nikhil 69000 Active 10 101
105 Rajan 18000 Active 20 112
107 Karan 101000 Active 20 112
110 Sajal 88000 Active 10 101
103 Ravi 45000 InActive 30 104
106 Surya 67000 Active 30 104
108 Sam 99000 InActive 20 105
109 Jack 77000 Active 10 106
10 rows selected.
Following example makes use of cursor, which you will study
in detail in a separate chapter.
DECLARE
CURSOR C_Employees IS
SELECT Name FROM EMP_TESt;
Type Elist is Varray(10) OF
EMP_TEST.Name%Type;
Namelist Elist := Elist();
counter Integer :=0;
BEGIN
FOR i IN C_Employees LOOP
counter := counter + 1;
Namelist.Extend;
Namelist(counter) := i.Name;
Dbms_Output.Put_Line('Employee('||counter
||'):'||Namelist(counter));
END LOOP;
END;
When the above code is executed at SQL prompt, it produces the following result:
SQL> DECLARE
2 CURSOR C_Employees IS
3 SELECT Name FROM EMP_TESt;
4 Type Elist is Varray(10) OF EMP_TEST.Name%Type;
5 Namelist Elist := Elist();
6 counter Integer :=0;
7 BEGIN
8 FOR i IN C_Employees LOOP
9 counter := counter + 1;
10 Namelist.Extend;
11 Namelist(counter) := i.Name;
12 Dbms_Output.Put_Line('Employee('||counter ||'):'||Namelist(counter));
13 END LOOP;
14 END;
15 /Employee(1):Akhil
Employee(2):Ankit
Employee(3):Nikhil
Employee(4):Rajan
Employee(5):Karan
Employee(6):Sajal
Employee(7):Ravi
Employee(8):Surya
Employee(9):Sam
Employee(10):Jack
PL/SQL procedure successfully completed.
Let’s
see one more Example:
DECLARE
TYPE Foursome IS VARRAY(4) OF
VARCHAR2(15); /*VARRAY type*/
/*Varray variable initialized with
constructor:*/
Team Foursome :=
Foursome('John', 'Mary', 'Alberto', 'Juanita');
PROCEDURE Print_Team (heading
VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN 1..4 LOOP
DBMS_OUTPUT.PUT_LINE(i ||
'.' || team(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------');
END;
BEGIN
Print_Team('2001 Team:');
Team(3) := 'Pierre'; /*Change values of two elements*/
Team(4) := 'Yvonne';
Print_Team('2005 Team:');
/*Invoke constructor to assign
new values to varray variable:*/
Team := Foursome('Arun',
'Amitha', 'Allan', 'Mae');
Print_Team('2009 Team:');
END;
When the above code is executed at SQL prompt, it produces the following result:
SQL> DECLARE
2 TYPE Foursome IS VARRAY(4) OF VARCHAR2(15); /*VARRAY type*/
3 /*Varray variable initialized with constructor:*/
4 Team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
5
6 PROCEDURE Print_Team (heading VARCHAR2) IS
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE(heading);
9 FOR i IN 1..4 LOOP
10 DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
11 END LOOP;
12 DBMS_OUTPUT.PUT_LINE('------------------');
13 END;
14
15 BEGIN
16 Print_Team('2001 Team:');
17 Team(3) := 'Pierre'; /*Change values of two elements*/
18 Team(4) := 'Yvonne';
19 Print_Team('2005 Team:');
20 /*Invoke constructor to assign new values to varray variable:*/
21 Team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
22 Print_Team('2009 Team:');
23 END;
24 /2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
------------------
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
------------------
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
------------------
PL/SQL procedure successfully completed.
Index-By Table or Associative Array
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
whose keys will be of subscript_type
and associated values will be of Element_Type
TYPE Type_Name
IS TABLE OF Element_Type [NOT NULL] INDEX BY subscript_type;
Table_Name Type_Name;
Example 1: 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 SQL prompt, it produces the following result:
SQL> DECLARE
2 TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
3 Salary_list salary;
4 Name VARCHAR2(20);
5 BEGIN
6 /*Adding elements to the table*/
7 Salary_list('Rajnish') := 62000;
8 Salary_list('Minakshi') := 75000;
9 Salary_list('Martin') := 100000;
10 Salary_list('James') := 78000;
11 /*Printing the table*/
12 Name := Salary_list.FIRST;
13 WHILE Name IS NOT Null LOOP
14 Dbms_Output.Put_Line('Salary of ' || Name || ' is ' || TO_CHAR(Salary_list(Name)));
15 Name := Salary_list.NEXT(Name);
16 END LOOP;
17 END;
18 /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 2: 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 EMPLOYEE table
stored in our database as:
SQL> SELECT * From EMP_TEST;
ID NAME SALARY STATUS DEPTNO MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
115 Akhil 70
102 Ankit 8000 Active 20 101
104 Nikhil 69000 Active 10 101
105 Rajan 18000 Active 20 112
107 Karan 101000 Active 20 112
110 Sajal 88000 Active 10 101
103 Ravi 45000 InActive 30 104
106 Surya 67000 Active 30 104
108 Sam 99000 InActive 20 105
109 Jack 77000 Active 10 106
10 rows selected.
DECLARE
CURSOR C_employees IS Select Name from
EMP_TEST;
TYPE Elist IS TABLE of EMP_TEST.Name%type
INDEX BY binary_integer;
Namelist Elist;
Counter integer :=0;
BEGIN
FOR i IN C_employees LOOP
Counter := Counter +1;
Namelist(Counter) := i.name;
Dbms_Output.Put_Line('Employee('||counter|| '):'||Namelist(counter));
END LOOP;
END;
When the above code is executed at SQL prompt, it produces the following result:
SQL> DECLARE
2 CURSOR C_employees IS Select Name from EMP_TEST;
3 TYPE Elist IS TABLE of EMP_TEST.Name%type INDEX BY binary_integer;
4 Namelist Elist;
5 Counter integer :=0;
6 BEGIN
7 FOR i IN C_employees LOOP
8 Counter := Counter +1;
9 Namelist(Counter) := i.name;
10 Dbms_Output.Put_Line('Employee('||counter|| '):'||Namelist(counter));
11 END LOOP;
12 END;
13 /Employee(1):Akhil
Employee(2):Ankit
Employee(3):Nikhil
Employee(4):Rajan
Employee(5):Karan
Employee(6):Sajal
Employee(7):Ravi
Employee(8):Surya
Employee(9):Sam
Employee(10):Jack
PL/SQL procedure successfully completed.
Nested Tables or PL/SQL 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 declaration of an index-by table, but there is no
INDEX BY clause.
A
nested table can be stored in a database column and so it could 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 1: 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 SQL prompt,
it produces the following result:
SQL> DECLARE
2 TYPE Names_Table IS TABLE OF VARCHAR2(10);
3 TYPE Grades IS TABLE OF INTEGER;
4 Names Names_Table;
5 Marks Grades;
6 Total Integer;
7 BEGIN
8 Names := Names_Table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
9 Marks:= Grades(98, 97, 78, 87, 92);
10 Total := Names.count;
11 Dbms_Output.Put_Line('Total '|| total || ' Students');
12 FOR i IN 1 .. total LOOP
13 Dbms_Output.Put_Line('Student:'||names(i)||', Marks:' || marks(i));
14 End Loop;
15 END;
16 /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 2: Elements of a nested 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 EMPLOYEES table stored in our database as:
SQL> SELECT * From EMP_TEST;
ID NAME SALARY STATUS DEPTNO MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
115 Akhil 70
102 Ankit 8000 Active 20 101
104 Nikhil 69000 Active 10 101
105 Rajan 18000 Active 20 112
107 Karan 101000 Active 20 112
110 Sajal 88000 Active 10 101
103 Ravi 45000 InActive 30 104
106 Surya 67000 Active 30 104
108 Sam 99000 InActive 20 105
109 Jack 77000 Active 10 106
10 rows selected.
DECLARE
CURSOR C_employees IS SELECT
Name FROM EMP_TEST;
TYPE Elist IS TABLE of
EMP_TEST.Name%type;
Namelist Elist := Elist();
Counter integer :=0;
BEGIN
FOR i IN C_employees LOOP
Counter := Counter +1;
Namelist.Extend;
Namelist(Counter) := i.Name;
Dbms_Output.Put_Line('Employee('||counter||'):'||Namelist(Counter));
END LOOP;
END;
When the above code is executed at SQL prompt, it produces the following result:
SQL> DECLARE
2 CURSOR C_employees IS SELECT Name FROM EMP_TEST;
3 TYPE Elist IS TABLE of EMP_TEST.Name%type;
4 Namelist Elist := Elist();
5 Counter integer :=0;
6 BEGIN
7 FOR i IN C_employees LOOP
8 Counter := Counter +1;
9 Namelist.Extend;
10 Namelist(Counter) := i.Name;
11 Dbms_Output.Put_Line('Employee('||counter||'):'||Namelist(Counter));
12 END LOOP;
13 END;
14 /Employee(1):Akhil
Employee(2):Ankit
Employee(3):Nikhil
Employee(4):Rajan
Employee(5):Karan
Employee(6):Sajal
Employee(7):Ravi
Employee(8):Surya
Employee(9):Sam
Employee(10):Jack
PL/SQL procedure successfully completed.
Let’s
see one more Example:
DECLARE
TYPE Roster IS TABLE OF
VARCHAR2(15); /*Nested table type*/
/*Nested table variable
initialized with constructor*/
Names Roster := Roster('D
Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE Print_Names(heading
VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN names.FIRST ..
names.LAST LOOP /*For first to last element*/
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('-------------');
END;
BEGIN
Print_Names('Initial Values:');
Names(3) := 'P Perez'; /*Change value of one element*/
Print_Names('Current Values:');
Names := Roster('A Jansen', 'B
Gupta'); /*Change entire table*/
Print_Names('Current Values:');
END;
When the above code is executed at SQL prompt, it produces the following result:
SQL> DECLARE
2 TYPE Roster IS TABLE OF VARCHAR2(15); /*Nested table type*/
3 /*Nested table variable initialized with constructor*/
4 Names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
5 PROCEDURE Print_Names(heading VARCHAR2) IS
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(heading);
8 FOR i IN names.FIRST .. names.LAST LOOP /*For first to last element*/
9 DBMS_OUTPUT.PUT_LINE(names(i));
10 END LOOP;
11 DBMS_OUTPUT.PUT_LINE('-------------');
12 END;
13 BEGIN
14 Print_Names('Initial Values:');
15 Names(3) := 'P Perez'; /*Change value of one element*/
16 Print_Names('Current Values:');
17 Names := Roster('A Jansen', 'B Gupta'); /*Change entire table*/
18 Print_Names('Current Values:');
19 END;
20 /Initial Values:
D Caruso
J Hamil
D Piro
R Singh
-------------
Current Values:
D Caruso
J Hamil
P Perez
R Singh
-------------
Current Values:
A Jansen
B Gupta
-------------
PL/SQL procedure successfully completed.
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.N.
|
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 integer subscripts. |
5
|
LAST
Returns the last (largest) index numbers in a collection that uses 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.
|
You can use any of the above Collections Types as per your requirement. Thanks.
RELATED TOPICS:
How to decide which Collection Types should be used?
Examples of COLLECTIONS and COLLECTION Methods
It's RUDE to
Read and Run!
Get involved
and leave your Comments in the Box Below. The more people get involved, the
more we all benefit. So, leave your thoughts before you leave the page.
Superb
ReplyDeleteThis article is really very informative. Please share more article like this Thank you for sharing. Erptree.com
ReplyDeletevery Helpful !!
ReplyDeleteHi,
ReplyDeleteIn the 2nd example program for VARRAY, cursor is used to fetch the employees' name and varray is declared with size 10. What if the number of employee count is more than 10.
Wonderfully written! With good examples.
ReplyDeleteThanks a lot .
ReplyDeletethanks fro great post you can check Visit
ReplyDeleteCalling all fashion enthusiasts! If you haven't heard already, Khaadi is throwing a massive 70% off
ReplyDeleteWinter Collection sale
. Don't miss this chance to revamp your wardrobe with their signature designs at unbelievable prices.
This is a very informative post! This blog really caught my attention. Your blog is a valuable resource for the Oracle collections. Keep up the great work and keep sharing your knowledge!
ReplyDeleteKnow more about imatinib capsules veenat 100 here.