PL/SQL Collection Data Types

By | December 12, 2017

A collection is an ordered group of elements, all of the same type. Each element is identified by a unique subscript that represents its position in the collection

PL/SQL provides three collection types −

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

 

VARRAY

  • Varrays hold a fixed-sized sequential collection of elements ..

All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Please Note

  • Varrays are one-dimensional arrays.
  • The starting index for varrays is always 1.
  • Initialize the varray elements using the constructor method of the varray type, which has the same name as the varray.
  • A Varray is automatically NULL when it is declared and must be initialized before its elements can be referenced.

Example 1:  Predefine Varray.

DECLARE 
   Type V_Student IS VARRAY(6) OF VARCHAR2(15); 
   Type V_Phone IS VARRAY(6) OF INTEGER; 
   Names V_Student; 
   Mobile_no V_Phone; 
   Total integer; 
BEGIN 
   Names := V_Student('Sumant', 'Deepak', 'Sonali', 'Sanjeev', 'Ravi','Malobika'); 
   Mobile_no:= V_Phone(9898989898, 9797979797, 88888888, 7474747474, 9292929292,9999999999); 
   Total := Names.count; 
   dbms_output.put_line('Total  Students :-'|| Total ); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || Names(i) || ' 
       Mobile No: ' || Mobile_no(i)); 
   END LOOP; 
END; 

Example 2:  Creating Varray elements in runtime.

DECLARE 
Type XYZ IS VARRAY(5) OF INTEGER;

v1 XYZ;
BEGIN
    v1:=XYZ();
    v1.Extend();
    v1(1):=120;
    DBMS_OUTPUT.PUT_LINE(V1(1));
END;
Coding Explanation :
  • Line No 1 :Define VARRAY as a user defined data type and having maximum size of Varray(5) which hold the numeric type of value.
  • Line No 3: We declare variable v1 of data type XYZ.
  • Line No 5: xyz() is a constructor and initialized the varray
  • Line No 6: allocate the place for the first element.
  • Line No 7: Assign value for the allocated place.

 

Example 3: Varray binding in runtime  and data retrieve from table.

Declare
Type V_Name IS VARRAY(5) OF VARCHAR2(20); 
Type XYZ IS VARRAY(5) OF INTEGER;
v2 v_Name;
v1 XYZ;
Counter  NUMBER :=1;
CURSOR c1 IS SELECT first_name,salary FROM EMPLOYEES WHERE ROWNUM<=5;
BEGIN
    v1:=XYZ();
    v2:=v_Name();
    FOR rec in c1
        LOOP
            v1.Extend();
            v2.Extend();
            v1(Counter):=rec.salary;
            v2(Counter):=rec.first_name;
            DBMS_OUTPUT.PUT_LINE('Employee Name is : '||V2(Counter)||
            ' has salary  :'|| V1(Counter));
            Counter:=Counter+1;
             
        END LOOP;
       
END;

Nested Table

A nested table is like a one-dimensional array with an arbitrary number of elements and it’s quite similar to a VARRAY with the exception, that the order of the elements is not static.

  • The size of a nested table can increase dynamically.There is no upper limit to the number of rows that can be stored in a nested table type i.e., nested tables are unbounded.
  • Elements can be deleted or added anywhere in the nested table whereas a VARRAY can only add or delete elements from the end of the array.
  • Nested table types are stored in the database.
 Pl/SQL Nested Table
Example 1 
Type V_Name IS TABLE OF VARCHAR2(20); 
v2 v_Name;
Counter number:=1;
CURSOR c1 IS SELECT first_name FROM EMPLOYEES ;
BEGIN
    v2:=v_Name();
    FOR rec in c1
        LOOP
            v2.Extend();
            v2(Counter):=rec.first_name;
            DBMS_OUTPUT.PUT_LINE('Employee Name is : '||V2(Counter));
            Counter:=Counter+1;           
        END LOOP;
END;

Example 2 

Type V_Name IS TABLE OF VARCHAR2(20); 
v2 v_Name;
Counter number:=1;
CURSOR c1 IS SELECT first_name FROM EMPLOYEES ;
BEGIN
    v2:=v_Name();
    FOR rec in c1
        LOOP
            v2.Extend();
            v2(Counter):=rec.first_name;
            DBMS_OUTPUT.PUT_LINE('Employee Name is : '||V2(Counter));
            Counter:=Counter+1;           
        END LOOP;     
    FOR i in v2.FIRST..v2.LAST LOOP
       DBMS_OUTPUT.PUT_LINE('The Name at index : '||i ||' is :'||v2(i));
    END LOOP;
END;
 SQL Nested Table

Create Type (TYPE_STATE)  and table (Country_mast) in SQL prompt.

CREATE TYPE TYPE_STATE IS TABLE OF VARCHAR2(100);


CREATE TABLE Country_mast (
Country_Code VARCHAR2(20) ,
STATE_NAME  TYPE_STATE)
   	NESTED TABLE STATE_NAME STORE AS NESTED_ORDER;

Insert Data into Country_mast and associate nested table.

   Insert into  Country_mast values('US',TYPE_STATE('California','Florida','Georgia','New Jersey','Texas'));
   
   Insert into Country_mast values('AUS',TYPE_STATE('New South Wales','South Australia','Victoria'));
   
   Insert into Country_mast values('IND',TYPE_STATE('New Delhi','UP','HP','Rajsthan','MP','Uttrakhand','GOA','Karnatka'));
   

Query With Nested Table

   select * from Country_mast;
      
   select  TAB1.Country_Code,TAB2.* from Country_mast TAB1, TABLE(TAB1.STATE_NAME) TAB2;

Index-by tables or Associative array :

Index by Table (Associative arrays) is a collection type which consists set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.
From Oracle 10g onwards Index by Table are called Associative arrays.
An Index by Table is sparse arrays, meaning that only the elements you load into them are stored.

Example 1 :

This is a simple example of integer and string index key value.

    TYPE A is TABLE of NUMBER INDEX BY PLS_INTEGER;
    TYPE B is TABLE of NUMBER INDEX BY VARCHAR2(10);
    TYPE K is TABLE of VARCHAR2(30) INDEX BY PLS_INTEGER;
    v1 A;
    v2 B;
    V3 K;
    BEGIN
        v1(1):=10;
        v1(5):=30;

        v2('A'):=12;
        v2('B'):=25;

        v3(1):='UK';
        v3(20):='US';
        v3(21):='Japan';

        DBMS_OUTPUT.PUT_LINE(v1(5));
    END;
Example 2 :

Using PL /SQL Collection Method.

 TYPE A is TABLE of DATE INDEX BY PLS_INTEGER;
    v1 A;
    BEGIN
      FOR x in 1..20 LOOP
      v1(x):=sysdate+x;
      END LOOP;
      IF v1.EXISTs(21) THEN
          DBMS_OUTPUT.PUT_LINE('Element Exists');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Element not Exists');
      END IF;
        DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS:'||v1.count());
        v1.DELETE(7);
         DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS After Delete:'||v1.count());
      v1.DELETE(3,5);
         DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS After Delete1:'||v1.count());

    END;
Example 3 :

Using PL /SQL Collection Method with Exception handling.

 TYPE A is TABLE of DATE INDEX BY PLS_INTEGER;
    v1 A;
    BEGIN
          FOR x in 1..20 LOOP
            v1(x):=sysdate+x;
          END LOOP;
         
          IF v1.EXISTs(21) THEN
              DBMS_OUTPUT.PUT_LINE('Element Exists');
          ELSE
             DBMS_OUTPUT.PUT_LINE('Element not Exists');
          END IF;
          DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS:'||v1.count());
          v1.DELETE(7);
          DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS After Delete:'||v1.count());
          v1.DELETE(3,5);
          DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS After Delete1:'||v1.count());
          For i in v1.first..v1.last LOOP
          BEGIN
                DBMS_OUTPUT.PUT_LINE('Total No of ELEMENTS After Deleteed:'||v1(i));
                EXCEPTION
                WHEN NO_DATA_FOUND then
                 DBMS_OUTPUT.PUT_LINE('no data on this node');
                END;
          End LOOP;

    END;

Collection Method :

Please Note :

  • Collection methods cannot be called from SQL statements.
  • EXTEND and TRIM cannot be used with associative arrays.
  • EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures.
  • EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.
Topic related Interview Question:

 

Leave a Reply

Your email address will not be published. Required fields are marked *