Oracle PL/SQL Tutorial/Collections/Varray Methods

Collection Methods

SQL> -- EXISTS
SQL> DECLARE
  2    TYPE numberVarray IS VARRAY(5)OF NUMBER;
  3
  4    v_numarray numberVarray :=numberVarray(10,20,30,40,50);
  5
  6  BEGIN
  7
  8    IF v_numarray.EXISTS(4) THEN
  9
 10      DBMS_OUTPUT.PUT_LINE("The element 4 exists in the variable array.");
 11
 12    END IF;
 13
 14  END;
 15  /
The element 4 exists in the variable array.
PL/SQL procedure successfully completed.
SQL>
SQL>

COUNT

SQL> DECLARE
  2    TYPE numberVarray IS VARRAY(10)OF NUMBER;
  3
  4    v_numarray numberVarray :=numberVarray(10,20,30,40,50);
  5
  6    TYPE numberTableType IS TABLE OF NUMBER;
  7
  8    v_numlist numberTableType :=numberTableType(101,201,301,401);
  9
 10  BEGIN
 11
 12    DBMS_OUTPUT.PUT_LINE("Varray Count = "||TO_CHAR(v_numarray.COUNT));
 13
 14    DBMS_OUTPUT.PUT_LINE("Nested Table Count = "||TO_CHAR(v_numlist.COUNT));
 15  END;
 16  /
Varray Count = 5
Nested Table Count = 4
PL/SQL procedure successfully completed.
SQL>
SQL>

EXTEND

SQL> DECLARE
  2    TYPE numberVarray IS VARRAY(10)OF NUMBER;
  3
  4    v_numarray numberVarray :=numberVarray(NULL,NULL);
  5
  6    TYPE numberTableType IS TABLE OF NUMBER;
  7
  8    v_numlist numberTableType :=numberTableType(NULL);
  9  BEGIN
 10    v_numarray(1):=1001;
 11
 12    v_numarray(2):=1002;
 13
 14    v_numarray.EXTEND;
 15
 16    v_numarray(3):=1003;
 17
 18
 19    v_numlist(1):=101;
 20
 21    v_numlist.EXTEND(5);
 22
 23    v_numlist(5):=105;
 24  END;
 25  /
PL/SQL procedure successfully completed.
SQL>

Extend and count attributes of varray

SQL>
SQL> DECLARE
  2     TYPE lv_name_array IS VARRAY(5) OF VARCHAR2(10);
  3     lv_name_array_rec lv_name_array := lv_name_array("dave","pat","bob");
  4  BEGIN
  5     FOR lv_loop_num in 1..lv_name_array_rec.COUNT LOOP
  6        DBMS_OUTPUT.PUT_LINE(lv_name_array_rec(lv_loop_num));
  7     END LOOP;
  8     lv_name_array_rec.EXTEND;
  9     lv_name_array_rec(lv_name_array_rec.COUNT) := "tony";
 10     DBMS_OUTPUT.PUT_LINE(lv_name_array_rec(4));
 11  END;
 12  /
dave
pat
bob
tony
PL/SQL procedure successfully completed.
SQL>

LIMIT

SQL> DECLARE
  2    TYPE numberVarray IS VARRAY(10)OF NUMBER;
  3
  4    v_numarray numberVarray :=numberVarray(10,20,30);
  5
  6  BEGIN
  7
  8    DBMS_OUTPUT.PUT_LINE("Varray Count = "||TO_CHAR(v_numarray.COUNT));
  9
 10    DBMS_OUTPUT.PUT_LINE("Varray Limit = "||TO_CHAR(v_numarray.LIMIT));
 11  END;
 12  /
Varray Count = 3
Varray Limit = 10
PL/SQL procedure successfully completed.
SQL>
SQL>