PL/SQL - SPLessons

PL/SQL Collections

Chapter 29

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Collections

PL/SQL Collections

shape Description

A group of components containing comparable data types are called PLSQL Collections. Every component is distinguished by a unique kind of appendix and represents the status in the accumulation.

PL/SQL gives 3 sorts of collections, they are:

Nested table

shape Description

An arbitrary number of components that exist in one dimensional array is said to be a nested table. Moreover, the nested table will implement some changes with these arrays.

  • A Nested table doesn’t contain a proclaimed number of elements, whereas, an array contains pronounced number of elements. And, the nested table size will grow progressively.
  • An array is dependably back to back subscripts and the nested array is likewise successive subscripts. When the components are deleted, it becomes spare.

shape Syntax

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

Type_name => Name of the table.

Element_type => Size of the table.

NOT NULL =>Contains a value.

shape Examples

The below example describes the Nested table program.

SQL> DECLARE
  2  TYPE name_table IS TABLE OF VARCHAR2(10);
  3  TYPE grades IS TABLE OF INTEGER;
  4  names names_tables;
  5  marks grades;
  6  total integer;
  7  BEGIN
  8  names:=names_table('Shah','Mike','Maddi','Alex','Peter');
  9  marks:=grades(92,87,98,97,78);
 10  total:=name.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:Shah,Marks:92
Student:Mike,Marks:87
Student:Maddi,Marks:98
Student:Alex,Marks:97
Student:Peter,Marks:78
PL/SQL procedure successfully completed.

The above Nested table program displays the student name and marks.

variable size array

shape Description

PL/SQL contains several pre-defined set of attributes that make it easy to access the functions.

S.NO Method Name Purpose
1 Count Returns the number of components that a set contains
2 Exists Returns the output as true if the set exists, else, returns false
3 First Returns the smallest index number first
4 Limit Checks the maximum size of the set
5 Last Returns the largest index first
6 Next Returns the index number that succeeds the index ‘n’
7 Prior Returns the index number followed by the nth index
8 Extend(n) Affix ‘n’ null elements to a set
9 Extend Affix one null element to a set
10 Trim(n) Deletes ‘n’ elements from the end of a collection
11 Trim Deletes ‘n’ components from the end of a set
12 Delete(n) Deletes the nth component from an associate array
13 Delete Deletes all components from the set, setting count to 0

Associate array

shape Description

An associative array table is also called as index-by table and is used for accumulation of key-quality pairs. Every key is utilized to trace the related values containing the unique pair of keys. The key can be either a string or a whole number.

shape Syntax

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

Type_name => Name of the table.

Element_type => Size of the table.

NOT NULL =>Contains a value.

shape Examples

The below example explains the associate array program.

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('Shah'):=72000;
  8  salary_list('Mike'):=72500;
  9  salary_list('Maddi'):=80000;
 10  salary_list('Martin'):=85000;
 11  salary_list('James'):=82000;
 12  --printing the table
 13  name:=salary_list.FIRST;
 14  WHILE name IS NOT null LOOP
 15  dbms_output.put_line('Salary of'||name||'is'||TO_CHAR(salary_list(name)));
 16  name:=salary_list.NEXT(name);
 17  END LOOP;
 18  END;
 19  /
Salary of Shah is 72000
Salary of Mike is 72500
Salary of Maddi is 80000
Salary of Martin is 85000
Salary of James is 82000
PL/SQL procedure successfully completed.

The above associative array program displays the salaries_list of all employees. 

Summary

shape Key Points

  • PLSQL Collections – Set of elements containing comparable data types.
  • Nested table – An arbitrary number of components that exist in one dimensional array.
  • Variable size array – Contains a number of pre-defined attributes by default.
  • Associate array – Also called as index-by table, used for accumulation of key value pairs.