Collections: Example of Associative Array with PLS_VARCHAR2 and PLS_INTEGER

Below is an example of creating a collection of families using associative arrays with PLS_VARCHAR2 and PLS_INTEGER and VARRAY collection types.

Adding an element to a NESTED TABLE or a VARRAY requires an additional step of allocating memory for that element or no of elements.

EXTEND without any parameters will extend the size of the array by 1 element. 

EXTEND(n) appends n null elements to the array. 

EXTEND(n,i) adds n elements to the array, and for each element added, it sets the value to that of the i'th element. An attempt to EXTEND a VARRAY beyond the limit declared will raise SUBSCRIPT_BEYOND_LIMIT exception.

EXTEND is not required for Associative arrays.

create or replace PROCEDURE PR_FAMILY_PLS_ARRAY
AS
 TYPE  rec_children IS RECORD
  (
   SURNAME	        VARCHAR2(100),
   NAME             VARCHAR2(100),
   RELATION         VARCHAR2(20),
   AGE              NUMBER
   );
 
 TYPE tab_children IS TABLE OF rec_children INDEX BY PLS_INTEGER;
 
 TYPE  rec_family IS RECORD
  (
   SURNAME	        VARCHAR2(100),
   FATHER           VARCHAR2(100),
   MOTHER           VARCHAR2(100),
   vt_children      tab_children,
   AGE              NUMBER
   );
   
   
 TYPE tab_family IS TABLE OF rec_family INDEX BY VARCHAR2(100);
 
 
  vt_family    tab_family; 
  v_surname   VARCHAR2(100);
  
  TYPE tab_surname_list IS VARRAY(20) OF VARCHAR2(100); 
  vt_surname tab_surname_list := tab_surname_list(); 
BEGIN
   v_surname :=  'JOHNSON';
   vt_surname.EXTEND; --this allocates 1 element size to the varray
   vt_surname(1) := v_surname;
   vt_family(v_surname).SURNAME := 'JOHNSON';
   vt_family(v_surname).FATHER := 'BEN';
   vt_family(v_surname).MOTHER := 'JENNY';
   vt_family(v_surname).vt_children(1).SURNAME := 'JOHNSON';
   vt_family(v_surname).vt_children(1).NAME := 'JAMES';
   vt_family(v_surname).vt_children(1).RELATION := 'SON';
   vt_family(v_surname).vt_children(1).AGE := 12;
   vt_family(v_surname).vt_children(2).SURNAME := 'JOHNSON';
   vt_family(v_surname).vt_children(2).NAME := 'JENCY';
   vt_family(v_surname).vt_children(2).RELATION := 'DAUGHTER';
   vt_family(v_surname).vt_children(2).AGE := 11;
 
   v_surname :=  'MICHAEL';
   vt_surname.EXTEND; -- allocate size for the next element.
   vt_surname(2) := v_surname;
   vt_family(v_surname).SURNAME := 'MICHAEL';
   vt_family(v_surname).FATHER := 'MIKE';
   vt_family(v_surname).MOTHER := 'MITCHELLE';
   vt_family(v_surname).vt_children(1).SURNAME := 'MICHAEL';
   vt_family(v_surname).vt_children(1).NAME := 'MARIO';
   vt_family(v_surname).vt_children(1).RELATION := 'SON';
   vt_family(v_surname).vt_children(1).AGE := 22;
   vt_family(v_surname).vt_children(2).SURNAME := 'MICHAEL';
   vt_family(v_surname).vt_children(2).NAME := 'MELLISSA';
   vt_family(v_surname).vt_children(2).RELATION := 'DAUGHTER';
   vt_family(v_surname).vt_children(2).AGE := 14;
   vt_family(v_surname).vt_children(3).SURNAME := 'MICHAEL';
   vt_family(v_surname).vt_children(3).NAME := 'MARTHA';
   vt_family(v_surname).vt_children(3).RELATION := 'DAUGHTER';
   vt_family(v_surname).vt_children(3).AGE := 12;
    
    FOR i IN 1.. vt_family.COUNT
    LOOP --{
      DBMS_OUTPUT.PUT_LINE('SURNAME ' || vt_family(vt_surname(i)).SURNAME);
      DBMS_OUTPUT.PUT_LINE('FATHER ' || vt_family(vt_surname(i)).FATHER);
      DBMS_OUTPUT.PUT_LINE('MOTHER ' || vt_family(vt_surname(i)).MOTHER);
      FOR j IN vt_family(vt_surname(i)).vt_children.FIRST .. vt_family(vt_surname(i)).vt_children.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE(vt_family(vt_surname(i)).vt_children(j).RELATION || ' :' || vt_family(vt_surname(i)).vt_children(j).NAME || ' ' || vt_family(vt_surname(i)).vt_children(j).SURNAME);
        DBMS_OUTPUT.PUT_LINE('AGE ' || vt_family(vt_surname(i)).vt_children(j).AGE);
      END LOOP;

    END LOOP; --}  
END;

Run the code as follows

BEGIN
  PR_FAMILY_PLS_ARRAY();
--rollback; 
END;

Below is the output of the code

SURNAME JOHNSON
FATHER BEN
MOTHER JENNY
SON :JAMES JOHNSON
AGE 12
DAUGHTER :JENCY JOHNSON
AGE 11
SURNAME MICHAEL
FATHER MIKE
MOTHER MITCHELLE
SON :MARIO MICHAEL
AGE 22
DAUGHTER :MELLISSA MICHAEL
AGE 14
DAUGHTER :MARTHA MICHAEL
AGE 12