In this part, we will examine the Collections in PL/SQL. An assortment is an arranged gathering of components having a similar information type. Every component is recognized by a remarkable addendum that addresses its situation in the assortment.
PL/SQL gives three assortment types −
- Record by tables or Associative cluster
- Settled table
- Variable-size cluster or Varray
Prophet documentation gives the accompanying attributes to each sort of assortments −
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 |
The two kinds of PL/SQL tables, i.e., the record by tables and the settled tables have similar design and their lines are gotten to utilizing the addendum documentation. Be that as it may, these two kinds of tables contrast in one viewpoint; the settled tables can be put away in a data set section and the file by tables can't.
Index-By Table
A list by table (additionally called an acquainted cluster) is a bunch of key-esteem sets. Each key is remarkable and is utilized to find the relating esteem. The key can be either a whole number or a string.
A list by table is made utilizing the accompanying language structure. Here, we are making a file by table named table_name, the keys of which will be of the subscript_type and related qualities will 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 model tells the best way to make a table to store number qualities alongside names and later it prints similar rundown 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 IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
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
Components of a file by table could likewise be a %ROWTYPE of any data set table or %TYPE of any information base table field. The accompanying model delineates the idea. We will utilize the CUSTOMERS table put away in our information base 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 customers.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;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
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 settled table resembles a one-dimensional cluster with a subjective number of components. Notwithstanding, a settled table contrasts from a cluster in the accompanying angles −
- A cluster has a proclaimed number of components, however a settled table doesn't. The size of a settled table can increment progressively.
- An exhibit is consistently thick, i.e., it generally has sequential addendums. A settled exhibit is thick at first, yet it can become inadequate when components are erased from it.
A settled table is made utilizing the accompanying sentence structure −
TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;
This assertion is like the announcement of a record by table, however there is no INDEX BY proviso.
A settled table can be put away in a data set section. It can additionally be utilized for disentangling SQL activities where you join a solitary segment table with a bigger table. An acquainted exhibit can't be put away in the data set.
Example
The accompanying models outline the utilization of settled 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;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
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
Components of a settled table can likewise be a %ROWTYPE of any data set table or %TYPE of any data set table field. The accompanying model represents the idea. We will utilize the CUSTOMERS table put away in our information base 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 customerS.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;
/
At the point when the above code is executed at the SQL brief, it delivers the accompanying outcome −
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
PL/SQL procedure successfully completed.
Collection Methods
PL/SQL gives the underlying assortment strategies that make assortments simpler to utilize. The accompanying table records the strategies and their motivation −
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 maximum size of a collection. |
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 collection. |
9 |
EXTEND(n) Appends n null elements to a collection. |
10 |
EXTEND(n,i) Appends n copies of the ith element to a collection. |
11 |
TRIM Removes one element from the end of a collection. |
12 |
TRIM(n) Removes n elements from the end of a collection. |
13 |
DELETE Removes all elements from a collection, setting COUNT to 0. |
14 |
DELETE(n) Removes the nth 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 accompanying table gives the assortment exemptions and when they are raised −
Collection Exception | Raised in Situations |
---|---|
COLLECTION_IS_NULL | You try to operate on an atomically null collection. |
NO_DATA_FOUND | A subscript designates an element that was deleted, or a nonexistent element of an associative array. |
SUBSCRIPT_BEYOND_COUNT | A subscript exceeds the number of elements in 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 is defined as a PLS_INTEGER range, and the subscript is outside this range. |