Varrays :
Varrays are
ordered groups of items of type VARRAY. Varrays can be used to associate a
single identifier with an entire collection. This allows manipulation of the
collection as a whole and easy reference of individual elements. The maximum size
of a varray needs to be specified in its type definition. The range of values
for the index of a varray is from 1 to the maximum specified in its type
definition. If no elements are in the array, then the array is atomically null.
The main use of a varray is to group small or uniform-sized collections of
objects.
Elements of a
varray cannot be accessed individually through SQL, although they can be
accessed in PL/SQL, OCI, or Pro*C using the array style subscript. The type of
the element of a VARRAY can be any PL/SQL type except the following:
BOOLEAN
TABLE
VARRAY
object types WITH TABLE OR VARRAY attributes
REF CURSOR
NCHAR
NCLOB
NVARCHAR2
Varrays can be
used to retrieve an entire collection as a value. Varray data is stored
in-line, in the same tablespace as the other data in its row. When a varray is
declared, a constructor with the same name as the varray is implicitly defined.
The constructor creates a varray from the elements passed to it. You can use a
constructor wherever you can use a function call, including the SELECT, VALUES,
and SET clauses.
A varray can be
assigned to another varray, provided the datatypes are the exact same type. For
example, suppose you declared two PL/SQL types:
TYPE My_Varray1
IS VARRAY(10) OF My_Type;
TYPE My_Varray2 IS VARRAY(10) OF My_Type;
An object of
type My_Varray1 can be assigned to another object of type My_Varray1 because
they are the exact same type. However, an object of type My_Varray2 cannot be
assigned to an object of type My_Varray1 because they are not the exact same
type, even though they have the same element type. Varrays can be
atomically null, so the IS NULL comparison operator can be used to see if a
varray is null. Varrays cannot be compared for equality or inequality.
Examples for Varrays
Example 1:
---------
The following
shows how to create a simple VARRAY:
a) First, define
a object type ELEMENTS as follows:
CREATE TYPE MEDICINES AS OBJECT (
MED_ID NUMBER(6),
MED_NAME VARCHAR2(14),
MANF_DATE DATE);
b) Next, define
a VARRAY type MEDICINE_ARR which stores MEDICINES objects:
CREATE
TYPE MEDICINE_ARR AS VARRAY(40) OF MEDICINES;
c) Finally,
create a relational table MED_STORE which has MEDICINE_ARR as a column type:
CREATE
TABLE MED_STORE (
LOCATION VARCHAR2(15),
STORE_SIZE NUMBER(7),
EMPLOYEES NUMBER(6),
MED_ITEMS MEDICINE_ARR);
Example 2:
----------
The following
example shows how to insert two rows into the MED_STORE table:
INSERT
INTO MED_STORE
VALUES ('BELMONT',1000,10,
MEDICINE_ARR(MEDICINES(11111,'STOPACHE',SYSDATE)));
INSERT
INTO MED_STORE
VALUES ('REDWOOD CITY',700,5,
MEDICINE_ARR(MEDICINES(12345,'STRESS_BUST',SYSDATE)));
Example 3:
----------
The following
example shows how to delete the second row we have inserted in example 6 above:
DELETE
FROM MED_STORE WHERE LOCATION = 'REDWOOD CITY';
Example 4:
----------
The following
example shows how to update the MED_STORE table and add more medicines to the
Belmont store:
UPDATE
MED_STORE
SET MED_ITEMS = MEDICINE_ARR (
MEDICINES(12346,'BUGKILL',SYSDATE),
MEDICINES(12347,'INHALER',SYSDATE),
MEDICINES(12348,'PAINKILL',SYSDATE));
