Array

DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

Note:use collection instead of array since array is fixed data type

Collection

A Collection is an ordered group of elements of particular data types. It can be a collection of simple data type or complex data type (like user-defined or record types).

Collections are most useful things when a large data of the same type need to be processed or manipulated. Collections can be populated and manipulated as whole using ‘BULK’ option in Oracle.

Collection Methods

• EXISTS(n) - Returns TRUE if the specified element exists.
• COUNT - Returns the number of elements in the collection.
• LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
• FIRST - Returns the index of the first element in the collection.
• LAST - Returns the index of the last element in the collection.
• PRIOR(n) - Returns the index of the element prior to the specified element.
• NEXT(n) - Returns the index of the next element after the specified element.
• EXTEND - Appends a single null element to the collection.
• EXTEND(n) - Appends n null elements to the collection.
• EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
• TRIM - Removes a single element from the end of the collection.
• TRIM(n) - Removes n elements from the end of the collection.
• DELETE - Removes all elements from the collection.
• DELETE(n) - Removes element n from the collection.
• DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

Collection Exception

• COLLECTION_IS_NULL - You try to operate on an atomically null collection.
• NO_DATA_FOUND - A subscript designates an element that was deleted, or a nonexistent element of an associative array.
• SUBSCRIPT_BEYOND_COUNT - A subscript exceeds the number of elements in a collection.
• SUBSCRIPT_OUTSIDE_LIMIT - A subscript is outside the allowed range.
• VALUE_ERROR - A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

method tutorial link

PL/SQL provides three collection types −

• Index-by tables or Associative array
• Nested table
• Variable-size array or Varray

Index-by tables or Associative array

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds(Unbounded), allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

Nested Table Collections

Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

Varray Collections

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The following example shows a successful assignment between two collections(nested table) of the same type.

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);

  -- Assignment works.
  v_tab_2 := v_tab_1;
END;
/

SET SERVEROUTPUT ON
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);
  v_tab_2 := v_tab_1;

  IF v_tab_1 = v_tab_2 THEN
    DBMS_OUTPUT.put_line('1: v_tab_1 = v_tab_2');
  END IF;

  v_tab_1 := table_type(1, 2, 3);

  IF v_tab_1 != v_tab_2 THEN
    DBMS_OUTPUT.put_line('2: v_tab_1 != v_tab_2');
  END IF;
END;
/

The following example shows a successful assignment between two collections(nested table) of two type.

DECLARE
  TYPE table_type_1 IS TABLE OF NUMBER(10);
  TYPE table_type_2 IS TABLE OF NUMBER(10);
  v_tab_1  table_type_1;
  v_tab_2  table_type_2;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type_1(1, 2);

  -- Assignment causes compilation error.
  v_tab_2 := v_tab_1;
END;
/

output: problem

DECLARE
     TYPE emp_arr_typ IS TABLE OF VARCHAR2(10);
     emp_arr emp_arr_typ;
	 i  INTEGER := 0;
	 
	 CURSOR emp_cur IS SELECT name FROM ukaccount.ukcourse WHERE ROWNUM <= 10;
    
BEGIN
     emp_arr := emp_arr_typ();
     FOR r_emp IN emp_cur LOOP
        emp_arr.EXTEND;
        emp_arr(emp_arr.LAST) := r_emp.name;	
     END LOOP;
	 
	 -- Traverse sparse collection
	 
	  i := emp_arr.FIRST;
	  << display_loop >>
	  WHILE i IS NOT NULL LOOP
		DBMS_OUTPUT.PUT_LINE('The Name is ' || emp_arr(i));
		i := emp_arr.NEXT(i);
	  END LOOP display_loop;   
END;
/

Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.

SET SERVEROUTPUT ON

-- Collection of records.
DECLARE
  TYPE t_row IS RECORD (
    id  NUMBER,
    description VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
END;
/

.. or 


-- Collection of records based on ROWTYPE. ----- VVVI
CREATE TABLE t1 (
  id  NUMBER,
  description VARCHAR2(50)
);

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF ukaccount.ukcourse%ROWTYPE;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).name := 'Name for ' || i;
	DBMS_OUTPUT.put_line('Nnn       = ' || l_tab(i).name);
  END LOOP;
END;
/