Collections in PL/SQL and it's uses in PL/SQL Program #DOTC_Mdb

Collection In PL/SQL.

 

Collection In PL/SQL  is an ordered group of elements of a particular data types. It can be a collection of simple data type or complex data type (like user-defined or record types).  In Collection each element is identified by a unique subscript that represents its position in the collection.

 

PL/SQL Collection is of three Types

1.     Index-By Table also known as Associative Arrays

2.     Varrays

3.     Nested Tables

 In Previous Blog we already discussed about Varrays so today we will discuss about Collection type Index - by Table and Nested table and take the same example which we take for Varrays.

Index-by Table (Associative Arrays)

Index-by-table is a collection in which the array size is not fixed. In Index –by Table each key is unique and is used to locate the corresponding value. Key can be  an integer or a string. Unlike the other collection types like Varray which we discussed in previous blog where key accept subscript type is integer.

In Index – by Table the subscript can of integer or strings. At the time of creating the collection, the subscript needs be mentioned. Also index by table not store data sequentially.

Example of creating Index-by Table (Associative Arrays)  by  below Syntax :

 

 

 

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

 

table_name  type_name;

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.       

 

We can understand the use of Index –by table with below program as in Varray we used the same table employee. Employee table Stored in database with Record is :

 

Select * from employee;

In Above table we will use Cursor and data type for Index-By Table to get the elements of employee name and value stored in Employee table for student_name column

 

DECLARE

   CURSOR c1 is

      select name from employee;

   TYPE student_list IS TABLE of employee.employee_name%type INDEX BY binary_integer;

   name_list student_list;

   counter integer :=0;

BEGIN

   FOR i IN c1 LOOP

      counter := counter +1;

      name_list(counter) := i.employee_name;

      dbms_output.put_line('Employee('||counter||'):'||name_list(counter));

   END LOOP;

END;

/

 

The output of above code is :

Employee(1): Ashish

Employee(2): Prashant

Employee(3): Deepak   

Employee(4):  Krishna

Employee(5): Gyan

 

Note : Same output we get by using Varray but here you see some syntax different . Difference between Varray and Index by table is that In Index by table subscript type can be integer or string whereas in Varray subscript Type is integer only also Index – by table created only in PL/SQL block whereas Varray in both PL/SQL block and at Schema Level.

Nested Table : Nested table is a collection in which the size of the array is not fixed. Nested table has the integer subscript type but in Index by Table subscribt type can be both integer and String . Nested table is like a one-dimensional array with an arbitrary number of elements.

Difference with Nested table and Arrays in Collection.

An array has a declared number of elements, but in nested table it does not.Size of a nested table can be increase dynamically.An array is always dense, i.e., it always has consecutive subscripts. Nested array is dense initially, but it can become sparse when elements are deleted.

 

Example of creating Nested Table by  below Syntax :

TYPE type_name IS TABLE OF element_type [NOT NULL];

 table_name type_name;

Syntax declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause in Nested Table. A nested table can be stored in a database column. It can be used for simplifying SQL operations where you join a single-column table with a larger table. Index –by Table value cannot be stored in database tables.

 We can understand the Nested Table with below program by using the same table employee. Employee table Stored in database with Record is :

Select * from employee;

DECLARE

   CURSOR C1 is 

      SELECT  name FROM employee; 

   TYPE student_list IS TABLE of employee.employee_name%type;

   name_list  student_list := student_list();

   counter integer :=0;

BEGIN

   FOR i IN C1 LOOP

      counter := counter +1;

      name_list.extend;

      name_list(counter)  := i.employee_name;

      dbms_output.put_line('Employee('||counter||'):'||name_list(counter));

   END LOOP;

END;

/  

The output of above code is :

Employee(1): Ashish

Employee(2): Prashant

Employee(3): Deepak   

Employee(4):  Krishna

Employee(5): Gyan

Same Output we get from index-by table but here you can see some different Syntax . Also Nested table column value we can use in database tables. Hope you have understand all the collections also i used same table for all the collection for better understanding. For Varray you can see my previous blog as link is below .

https://dheeraj60.blogspot.com/2020/06/varray-in-plsql-to-manipulate-elements.html

 

Difference between Varray , Index-by Table and Nested table is as below :

Index –by table can be dense or sparse but Varray always Dense while Nested table starts with dense and can become sparse.

Index by table subscript Type can be integer or string while Subscript type of Varray and Nested table is integer only.

Index By Table can only be created in PL/SQL block only while Nested table and Varray can be created at Pl/SQL block as well as at Schema Level.

Important Note : Nested table column can be stored in Database Table .

 

Please go through this Blog and let me know if you have any doubts.

Thanks.