Oracle PL/SQL Tutorial/Collections/Varray Methods

Collection Methods

   <source lang="sql">

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></source>

COUNT

   <source lang="sql">

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></source>

EXTEND

   <source lang="sql">

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></source>

Extend and count attributes of varray

   <source lang="sql">

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></source>

LIMIT

   <source lang="sql">

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></source>