PL-SQL Collections

Collections in SQL are one dimensional and are similar to arrays
VARRAYs have an upper bound on the size

Nested Table

Similar to array. Index starts from 1
COUNT can be used to find the size of the nested table

How To Create Nested Table As Database Object In Oracle | RebellionRider

SET SERVEROUTPUT ON;
 
DECLARE
    TYPE nested_table IS TABLE OF NUMBER;
    v_array nested_table := nested_table(0,1,2,3,4);
BEGIN
    FOR i in 1 .. v_array.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(i || ' => ' || v_array(i));
    END LOOP;
END;
/

VARRAYs

The LIMIT keyword is used to access the upper limit of the VARRAY
The EXTEND keyword is used to initialize the VARRAY. When used outside the loop we use EXTEND(n) when using inside the loop for dynamic allocation we just use EXTEND
The values can be directly specified in the initialization step as well

Introduction To PL/SQL VARRAYs In Oracle Database | RebellionRider

How To Create VARRAYs As Database Object In Oracle Database | RebellionRider

SET SERVEROUTPUT ON;
DECLARE
    TYPE varray_type IS VARRAY(10) OF NUMBER;
    new_varray varray_type := varray_type();
BEGIN
    new_varray.EXTEND(10);    
    FOR i in 1 .. new_varray.LIMIT
    LOOP
	 	--new_varray.EXTEND;
        new_varray(i) := i * 10;
        DBMS_OUTPUT.PUT_LINE(new_varray(i));
    END LOOP;
END;
/

Associative Array

Similar to dictionary (key, value) pair from other programming languages

SET SERVEROUTPUT ON;
DECLARE
    TYPE ass_array IS TABLE OF NUMBER
    INDEX BY VARCHAR2(20);
    array ass_array;
    counter VARCHAR2(20);
BEGIN
    array('Book 1') := 1234;
    array('Book 2') := 5678;
    array('Book 3') := 91011;
    counter := array.FIRST;
    WHILE counter IS NOT NULL
    LOOP
        DBMS_OUTPUT.PUT_LINE('Key : ' || counter || ' Value : ' || array(counter));
        counter := array.NEXT(counter);
    END LOOP;
END;
/

Functions

COUNT: Can be used in For loop as upper bound. Returns the number of elements that are present in the collection (Used with Nested Table)
EXISTS(n) : Used to check if an value is present at an index
FIRST, LAST: Returns the first and last element of an collection. Returns NULL if collection does not have value. Error if Index does not exist
LIMIT: Can be used in for loop as upper bound. Returns the size of the VARRAY
PRIOR(n), NEXT(n): Returns the previous and next index

Procedures

DELETE, DELETE(n), DELETE(start, end): Deletes an value at an index from the collection EXTEND, EXTEND(n), EXTEND(n, v): It is used to assign memory. Attach a single NULL value. Append NULL n times. Append n times the value at index v. Cannot be used with associative arrays
TRIM, TRIM(n): Remove 1 element from end. Remove n elements from end