VARRAY
Also known as varying arrays
It is an aggregation of values stored in a single column
A single column in the database would point to a group of elements
Can associate a single identifier with an entire collection
Can reference the entire collection as a whole or access the elements individually
To access individual elements, use standard subscripting syntax i.e. array_name(subscript)
Maximum size must be specified at time of definition
Index has a fixed lower bound of 1 and an extensible upper bound
A varying array allows to store repeating attributes of a record in a single row.
Steps for Creating VARRAYs
Create a type which will hold information for a single line item
Create an array of type known as Varray type, holding multiple values of a particular type
Create a table having one of the columns, known as varray column, based on the Varray type
The SYNTAX for creating a VARRAY
CREATE TYPE
AS VARRAY(limit) OF ;
Example – In a factory table there are workers using different tools. Each worker is using say 3 numbers of tools. Thus if a single worker is taking 3 tools then that worker’s name will come thrice in the table. So there will be data redundancy. Varrays will repeat values that change for a particular column which will save the storage space.
A set of rows is repeated in one column in varray.
Step 1 – Create an varray holding 3 elements of varchar2(20).
create or replace type TOOLS_VA as varray(3) of varchar2(20);
Step 2 - Create a table having one of the column as the varray column based on the varray type.
create table factory
(name varchar2(20) primary key,
tools TOOLS_VA);
Step 3 – Inserting record in the factory table
Insert into factory values ('John', TOOLS_VA ('Hammer', 'Sledge', 'Drill'));
Insert into factory values ('Smith', TOOLS_VA ('Screw Gauge', 'Hammer', 'AX'));
Insert into factory values ('Martin', TOOLS_VA (null, 'Sledge', null));
To see names of tools for each worker.
select F.Name, V.*
from Factory F, TABLE(F.Tools) V;
V is an alias table created for the tool names. TABLE function is used to simplify the process of selecting data from varying arrays.
Conditional records
To see records of persons having Hammer tool
select F.Name, V.*
from Factory F, TABLE(F.Tools) V
where V.Column_Value = 'Hammer'
Summary records
To see count of tools for each person
select F.Name, Count(Column_Value)
from Factory F, TABLE(F.Tools) V
Group By F.Name
Updating VARRAYs
Individual elements cannot be updated
Entire VARRAY has to be updated if one of the elements has to be changed
To change the value of first index tool of Martin from null to Vernier C
update factory
set tools = tools_va('Vernier C','Sledge', NULL)
where name = 'Martin';
Creating customized and advance report using PL/SQL blocks
Displaying data from the varray column –
declare
cursor cf is
select * from factory;
vcf cf%rowtype;
begin
for vcf in cf
loop
/*This loop is for the normal column name.*/
dbms_output.put_line('Contact Name '|| vcf.name);
for i in 1..vcf.tools.count
loop
/*This loop is for the number of tools for the current row's name*/
dbms_output.put_line('-------------' || vcf.tools(i));
end loop;
dbms_output.put_line('----------------------------------');
end loop;
end;
/
------------------------------------------------------------------------------------------------------
Displaying status for each record whether Hammer is given or not—
declare
cursor cf is
select * from factory;
vcf cf%rowtype;
x number;
begin
for vcf in cf
loop
x := 0;
dbms_output.put_line('Contact Name -- '|| vcf.name);
for i in 1..vcf.tools.count
loop
if vcf.tools(i) = 'Hammer' then
x := x 1;
end if;
end loop;
if x > 0 then
dbms_output.put_line('Hammer is supplied');
else
dbms_output.put_line('Hammer is not supplied');
end if;
dbms_output.put_line('--------------------------- ');
end loop;
end;
/
To display the second tool provided for each person
declare
cursor cf is
select * from factory;
vcf cf%rowtype;
x number;
begin
for vcf in cf
loop
x := 0;
dbms_output.put_line('Contact Name -- '|| vcf.name);
for i in 1..vcf.tools.count
loop
if i = 2 then
dbms_output.put_line(vcf.tools(i));
end if;
end loop;
x := x 1;
end loop;
end;
/
Displaying the names of workers who have been given shafts.
declare
cursor cf is
select * from factory;
vcf cf%rowtype;
countOfShaftHolders number;
begin
dbms_output.put_line('The Shaft holders are --> ');
countOfShaftHolders := 0;
for vcf in cf
loop
for i in 1..vcf.tools.count
loop
if vcf.tools(i)='Shaft' then
dbms_output.put_line(vcf.name);
countOfShaftHolders := countOfShaftHolders 1;
end if;
end loop;
end loop;
if countOfShaftHolders = 0 then
dbms_output.put_line('Sorry, there are no shaft holders');
end if;
end;
Creating a varray from a type
Multiple attributes for a single record and multiple sets of attributes for a single record.
create or replace type Monthly_Sales as Object
(Month Varchar2(3),
Sales Number(6));
/
create or replace type Monthly_Sales_VA as varray(4) of Monthly_Sales;
/
create table city_details
(name varchar2(15),
performance Monthly_Sales_VA)
/
Insert into city_details
values('Pune',
Monthly_Sales_VA( Monthly_Sales('Jan',9000),
Monthly_Sales('Feb',7500),
Monthly_Sales('Mar',8000),
Monthly_Sales('Apr',10000))
)
/
select A.name, V.*
from city_details A, TABLE(A.Performance) V;
To see details of varray query the view user_coll_types
select type_name,COLL_TYPE, UPPER_BOUND
from user_coll_types
Discussion about this article
| Author | Body |
Rahul
8/29/2009 8:25 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|