PL/SQL - Arrays

Varrays:

• Can be used to associate a single identifier with an entire collection.
• Used to manipulate the collection as a whole and easy reference of individual elements.
• Maximum size of a varray needs to be specified in its type definition.
• Range of values for the index of a varray is from 1 to the maximum specified in its type definition.
• Array is NULL when no elements are in the array
• The main usage is to group small or uniform-sized collections of objects.
• We can not access varray individually using SQL but can be accessed in PL/SQL using the array style subscript.
• 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.

We can't use below types in varray:

• BOOLEAN
• TABLE
• VARRAY
• object types WITH TABLE OR VARRAY attributes
• REF CURSOR
• NCHAR
• NCLOB
• NVARCHAR2

We declared two PL/SQL types:

TYPE v_varray1 IS VARRAY(10) OF My_Type;

TYPE v_varray2 IS VARRAY(10) OF My_Type;

An object of type v_varray1 can be assigned to another object of type v_varray1 because they are the exact same type.However, an object of type v_varray2 cannot be assigned to an object of type v_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.

Sample:

• Define an object
• Define Varray
• Use Varray

Defining an Object

CREATE TYPE LAPTOPS AS OBJECT (

LAPTOP_ID    NUMBER(6),

LAPTOP_NAME  VARCHAR2(14),

MANF_DATE DATE);

Defining Varray

CREATE TYPE LAPTOP_ARRAY AS VARRAY(40) OF LAPTOPS;

Create a relational table LAPTOP_STORE which has LAPTOP_ARRAY as a column type:

CREATE TABLE LAPTOP_STORE (

LOCATION    VARCHAR2(15),

STORE_SIZE  NUMBER(7),

EMPLOYEES   NUMBER(6),

ITEMS   LAPTOP_ARRAY);

Sample:

Insert data into the LAPTOP_STORE table:

INSERT INTO LAPTOP_STORE VALUES ('Hyderabad',1000,10, LAPTOP_ARRAY(LAPTOPS(11111,'HP',SYSDATE)));

INSERT INTO LAPTOP_STORE VALUES ('Amaravati',700,5, LAPTOP_ARRAY(LAPTOPS(12345,'HCL',SYSDATE)));

Sample:

Delete the data from TAble:

DELETE FROM LAPTOP_STORE WHERE LOCATION = 'Hyderabad';

Sample:

Update the LAPTOP_STORE table and add more LAPTOPS:

UPDATE LAPTOP_STORE

SET ITEMS = LAPTOP_ARRAY (

LAPTOPS(12346,'DELL',SYSDATE),

LAPTOPS(12347,'LENOVO',SYSDATE),

LAPTOPS(12348,'APPLE',SYSDATE));

Complete Tutorial