PL/SQL Varray

     Varray is an user defined data type, which is used to store same datatype in a single unit, it is also same as nested table i.e., before we are storing values. We must initialize the values through constructor, and also using varrays we are storing up to 2 GB data. Varrays also supports all collection methods as same a nested table and also supports limit collection method. This collection method returns size of the array. This is a user defined type, so we are creating in two step process.

    

         Syntax:-  type typename is varray(maxsize) of data type(size);

                           Variablename typename:=typename();

DECLARE

   TYPE t1 IS VARRAY (10) OF VARCHAR2 (10);

   V_t   t1

            := t1 ('a',

                   'b',

                   'c',

                   'd');

   Z     BOOLEAN;

BEGIN

   DBMS_OUTPUT.put_line (v_t.LIMIT);

   DBMS_OUTPUT.put_line (v_t.COUNT);

   DBMS_OUTPUT.put_line (v_t.FIRST);

   DBMS_OUTPUT.put_line (v_t.LAST);

   DBMS_OUTPUT.put_line (v_t.PRIOR (3));

   DBMS_OUTPUT.put_line (v_t.NEXT (3));

   DBMS_OUTPUT.put_line (v_t.LAST);

   FOR i IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

   z := v_t.EXISTS (3);

   IF z = TRUE

   THEN

      DBMS_OUTPUT.

       put_line ('inde z exists with an element' || ' ' || v_t (3));

   ELSE

      DBMS_OUTPUT.put_line ('index z does not exists');

   END IF;

   v_t.EXTEND;

   DBMS_OUTPUT.put_line (v_t.COUNT);

   v_t (5) := 'e';

   v_t.EXTEND (2);

   v_t (6) := 'f';

   v_t (7) := 'g';

   FOR i IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

   v_t.EXTEND (3, 4);

   FOR i IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

   v_t.TRIM (4);

   DBMS_OUTPUT.put_line (v_t.COUNT);

   v_t.delete;

   DBMS_OUTPUT.put_line (v_t.COUNT);

END;

Note:- in varrays we are not allowed to delete range of elements, but we can delete all the elements using delete collection method

You May Like:

PL/SQL Bulk Collect And Bulk bind

PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

PL/SQL Nested tables 

PL/SQL Ref Cursor

PLSQL Record