In this blog, we’ll discuss the Collections in PL/SQL. a set is an ordered group of elements having an equivalent data type. Each element is identified by a singular subscript that represents its position within 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 subsequent characteristics for every sort 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 within the chapter ‘PL/SQL arrays’. during this chapter, we’ll discuss the PL/SQL tables.
Both sorts of PL/SQL tables, i.e., the index-by tables and therefore the nested tables have an equivalent structure and their rows are accessed using the subscript notation. However, these two sorts of tables differ in one aspect; the nested tables are often stored during a database column and therefore the index-by tables cannot.

Index-By Table

An index-by table (also called an associative array) may be a set of key-value pairs. Each key’s unique and is employed to locate the corresponding value. The key are often either an integer or a string.
An index-by table is made using the subsequent syntax. Here, we are creating an index-by table named table_name, the keys of which can be of the subscript_type and associated values are going to 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 the way to create a table to store integer values along side names and later it prints an equivalent 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 isn't 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 subsequent 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 even be a %ROWTYPE of any database table or sort of any database table field. the subsequent example illustrates the concept. we’ll use the purchasers 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 consumers .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 subsequent 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 sort of a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array within the following aspects −
• An array features a declared number of elements, but a nested table doesn’t . the dimensions of a nested table can increase dynamically.
• An array is usually 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 made using the subsequent syntax −
TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;
This declaration is analogous to the declaration of an index-by table, but there’s no INDEX BY clause.
A nested table are often stored during a database column. It can further be used for simplifying SQL operations where you join a single-column table with a bigger table. An associative array can’t be stored within the database.
Example
The following examples illustrate the utilization 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 subsequent 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 also can be a %ROWTYPE of any database table or sort of any database table field. the subsequent example illustrates the concept. we’ll use the purchasers table stored in our database as −
Select * from customers;

 
DECLARE 
CURSOR c_customers is 
SELECT name FROM customers; 
TYPE c_list IS TABLE of consumers .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; 
/

PL/SQL procedure successfully completed.

Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. the subsequent 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 utmost size of a set.
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 set.
9	EXTEND(n) Appends n null elements to a set.
10	EXTEND(n,i) Appends n copies of the i^th element to a set.
11	TRIM Removes one element from the end of a set.
12	TRIM(n) Removes n elements from the end of a set.
13	DELETE Removes all elements from a set, setting COUNT to 0.
14	DELETE(n) Removes the n^th 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 attempt to operate an atomically null collection.
NO_DATA_FOUND	A subscript designates a component that was deleted, or a non-existent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements during 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’s defined as a PLS_INTEGER range, and therefore subscript is outside this range.

So, this brings us to the end of blog. This Tecklearn ‘Collections in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

439