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
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
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.
Well explained!
ReplyDelete