Index by Table (Associative Array in PLSQL)

Index by Table:

Its also called as "Associative Array" ,It's same as nested table but we have extra feature is "Subscript can be either String or Integer" and In nested table Subscript should be "Integer" only.

Like VARRAY and Nested table , we cant use Index by Table in SQL level.

Syntax:

Type Type_Name Is Table Of <Data_Type>
Index by <Subscript_Type>

Variable_Name Type_Name;


Ex:
 
SQL> Declare
  2 
  3  --Here Subscript ill be string (a,b) and value for subscripts would be integer data(1,10)
  4  Type Test_Type Is Table Of Integer
  5  Index by Varchar2(25);
  6 
  7  V_Test_Type Test_Type;
  8  V_Chk Varchar2(100);
  9  Begin
 10  --Add values to the subscripts of the Associative array
 11  V_Test_Type('a') := 1;
 12  V_Test_Type('b') := 10;
 13  dbms_output.put_line(' Count '||V_Test_Type.Count);
 14  --Checking first Subscript
 15  V_chk := V_Test_Type.First;
 16 
 17  While V_Chk Is Not Null
 18  Loop
 19  Dbms_Output.Put_Line('Subscript is '||V_Chk || ' And ItS Value Is          '||V_Test_Type(V_chk));
 20  --Traversing next Subscript
 21  V_Chk := V_Test_Type.Next(V_Chk);
 22  End loop;
 23  End;
 24  /

 Count 2
Subscript is a And ItS Value Is 1
Subscript is b And ItS Value Is 10


eg 2:

Taking sum of salary dept name wise and storing dept name in index key as varchar2 data and sal as value.

SQL> DECLARE
  2    TYPE TY_TB_V_EMP IS TABLE OF EMP.SAL%TYPE INDEX BY VARCHAR2(32767);
  3    V_TY_TB_V_EMP TY_TB_V_EMP;
  4    V_DNAME       DEPT.DNAME%TYPE;
  5  BEGIN
  6    FOR J IN (SELECT DEP.DNAME, SUM(E.SAL) AS SAL
  7                FROM DEPT DEP
  8                JOIN EMP E ON E.DEPTNO = DEP.DEPTNO
  9               GROUP BY DEP.DNAME) LOOP
 10      --Assigning salary to the dept name
 11      V_TY_TB_V_EMP(J.DNAME) := J.SAL;
 12    END LOOP;
 13    -- printing values
 14    V_DNAME := V_TY_TB_V_EMP.FIRST;
 15  
 16    WHILE V_DNAME IS NOT NULL LOOP
 17      DBMS_OUTPUT.PUT_LINE('SUBSCRIPT/Dept is  IS ' || V_DNAME ||
 18                           ' and its value/sum of salary  is ' || V_TY_TB_V_EMP(V_DNAME));
 19      V_DNAME := V_TY_TB_V_EMP.NEXT(V_DNAME);
 20    END LOOP;
 21  END;
 22  /

SUBSCRIPT/Dept is  IS ACCOUNTING and its value/sum of salary  is 13950
SUBSCRIPT/Dept is  IS RESEARCH and its value/sum of salary  is 10875
SUBSCRIPT/Dept is  IS SALES and its value/sum of salary  is 9400


PL/SQL procedure successfully completed