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:
PLSQL Collections
- Nested table
- Variable size array
- Associate array
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.
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.
Examples
The below example describes the Nested table program.
[c]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.[/c]
The above Nested table program displays the student name and marks.
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 |
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.
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.
Examples
The below example explains the associate array program.
[c]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.[/c]
The above associative array program displays the salaries_list of all employees.
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.