Oracle Collection - Varrays

Varrays hold a fixed number of elements, although the number of elements can be changed at runtime. Like nested tables, varrays use sequential numbers as the index or key to the elements. You can define equivalent SQL types, allowing varrays to be stored in database tables. Varrays are a good choice when the number of elements is known in advance, and when the elements are likely to be accessed in sequence.

      A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size (but not a minimum size) when you declare the VARRAY type.

         In general, the VARRAY type should be used when the number of items to be stored is small; it is not suitable for large numbers of items or elements. Note that you cannot index or constrain VARRAY values. Varray is available in PL/SQL as well as in SQL

Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array

Use to preserve ordered list

Use when working with a fixed set, with a known number of entries

Use when you need to store in the database and operate on the Collection as a whole

  Varrays in SQL

CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type;

 Varrays in PLSQL

TYPE varray_type IS VARRAY(SIZE) OF element_type (datatype) ;

varray_name varray_type; (variable type in PL/SQL)

varray_name := varray_type();(create it using the constructor)


CREATE TABLE table_name(field1 [VARCHAR2 NUMBER DATE],field2 varray_type);

Example :
CREATE TYPE Project AS OBJECT ( project_no NUMBER(2), title  VARCHAR2(35),cost  NUMBER(7,2));

CREATE TYPE ProjectList AS VARRAY(50) OF Project;

CREATE TABLE department ( dept_id  NUMBER(2), name  VARCHAR2(15), budget NUMBER(11,2), projects ProjectList);


BEGIN
   INSERT INTO department
      VALUES(30, 'Accounting', 1205700,
         ProjectList(Project(1, 'Design New Expense Report', 3250),
                     Project(2, 'Outsource Payroll', 12350),
                     Project(3, 'Evaluate Merger Proposal', 2750),
                     Project(4, 'Audit Accounts Payable', 1425)));
END;

DECLARE
   new_projects ProjectList :=
      ProjectList(Project(1, 'Issue New Employee Badges', 13500),
                  Project(2, 'Develop New Patrol Plan', 1250),
                  Project(3, 'Inspect Emergency Exits', 1900),
                  Project(4, 'Upgrade Alarm System', 3350),
                  Project(5, 'Analyze Local Crime Stats', 825));
BEGIN
   UPDATE department
      SET projects = new_projects WHERE dept_id = 60;
END;
---