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

God's Word for the day

The need of reflection and self control

One who is wise is cautious in everything;

  When sin is all around one guards against wrongdoing.

Every intelligent person knows wisdom,

  And praises the one who finds her.

Those who are skilled in words become wise themselves,

  And pour forth apt proverbs.

Sirach 18:27-29

Hearers and Doers 

Everyone who hears these words of mine and acts on them

  Will be like a wise man who built his house on rock.

The rain fell, the floods came and the winds blew

  and beat on that house, but it did not fall,

Because it had been founded on rock.

And everyone who hears these words of mine

  and does not act on them will be like a foolish man

who built his house on sand.

  The rain fell and the floods came, and the winds blew,

and beat against that house, and it fell.

   -- And great was its fall!

Mathew 7:24-27