PL/SQL - Arrays

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));