In this section, we will talk about clusters in PL/SQL. The PL/SQL programming language gives an information structure called the VARRAY, which can store a fixed-size successive assortment of components of a similar kind. A varray is utilized to store an arranged assortment of information, anyway it is regularly better to consider a cluster an assortment of factors of a similar sort.
All varrays comprise of adjacent memory areas. The most reduced location relates to the primary component and the most elevated location to the last component.
An exhibit is a piece of assortment type information and it represents variable-size clusters. We will examine other assortment types in a later section 'PL/SQL Collections'.
Every component in a varray has a file related with it. It additionally has a most extreme size that can be changed progressively.
Creating a Varray Type
A varray type is made with the CREATE TYPE articulation. You should indicate the greatest size and the kind of components put away in the varray.
The essential sentence structure for making a VARRAY type at the pattern level is −
CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>
- varray_type_name is a legitimate trait name,
- n is the quantity of components (most extreme) in the varray,
- element_type is the information sort of the components of the cluster.
Most extreme size of a varray can be changed utilizing the ALTER TYPE articulation.
CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); / Type created.
The essential grammar for making a VARRAY type inside a PL/SQL block is −
TYPE varray_type_name IS VARRAY(n) of <element_type>
For instance −
TYPE namearray IS VARRAY(5) OF VARCHAR2(10); Type grades IS VARRAY(5) OF INTEGER;
Allow us presently to work out on a couple of guides to comprehend the idea −
The accompanying project shows the utilization of varrays −
DECLARE type namesarray IS VARRAY(5) OF VARCHAR2(10); type grades IS VARRAY(5) OF INTEGER; names namesarray; marks grades; total integer; BEGIN names := namesarray('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 creates 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.
Please note −
- In Oracle climate, the beginning list for varrays is consistently 1.
- You can introduce the varray components utilizing the constructor strategy for the varray type, which has a similar name as the varray.
- Varrays are one-dimensional clusters.
- A varray is naturally NULL when it is proclaimed and should be introduced before its components can be referred to.
Components of a varray could likewise be a %ROWTYPE of any information base table or %TYPE of any data set table field. The accompanying model delineates the idea.
We will utilize the CUSTOMERS table put away in our data set 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 | +----+----------+-----+-----------+----------+
Following model utilizes cursor, which you will concentrate in detail in a different section.
DECLARE CURSOR c_customers is SELECT name FROM customers; type c_list is varray (6) of customers.name%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.