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

Table Function In Oracle

Table functions are used to return collection and its queried like a table using "TABLE " keyword.

Simple words we can say as "Functions that can be called in the FROM clause of query.



Syntax:
Select Column_Value From Table(Collection_Name);

We may have functions which has return type as collection those kind of functions also can be called with this.

Syntax:

Select Column_Value From Table(Function_Name());

CREATE OR REPLACE TYPE INTEGER_TYPE IS TABLE OF INTEGER;

SQL> SELECT COLUMN_VALUE FROM TABLE(INTEGER_TYPE(1,2,3,4,5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5


Let's see one eg. with function which returns multiple values.

In 1st Step create nested table at SQL level so that it can be used as data type wherever we required.



SQL> CREATE OR REPLACE TYPE INTEGER_TYPE IS TABLE OF INTEGER;
  2  /


Create Function to return employee number list which are belongs to particular deptno.



SQL> CREATE OR REPLACE FUNCTION TABLE_FUNCTION(V_DEPNO INTEGER)
  2  RETURN INTEGER_TYPE
  3  IS
  4  V_int Integer_Type;
  5  BEGIN
  6    SELECT Empno
  7      BULK COLLECT INTO V_Int
  8      FROM EMP
  9     WHERE DEPTNO = V_DEPNO;
 10   RETURN V_INT;
 11  EXCEPTION
 12   WHEN OTHERS THEN
 13     DBMS_OUTPUT.PUT_LINE('ERR MSG '||SQLERRM );
 14  END;
 15  /
SQL> ALTER FUNCTION TABLE_FUNCTION COMPILE;



Here we are calling above function in table function..
 
SQL> SELECT * FROM TABLE(TABLE_FUNCTION(V_DEPNO => 10));

COLUMN_VALUE
------------
        7782
        7839
        7934
 


If see above ,I Used "Select * From "  and closely observe the column name what it displays in while printing Output,It's Column_Value.


Here "COLUMN_VALUE " Is pseudo-column which we use to deal with nested table as below.




SQL> SELECT COLUMN_VALUE AS emp_no FROM TABLE(TABLE_FUNCTION(V_DEPNO => 10));

    EMP_NO
----------
      7782
      7839
      7934

SQL> 


Pls post your queries if you have any doubts on this topic.Thank you.

Nested Table in PLSQL

Nested Table :

In earlier post we had learnt about VARRAYS, now lets see about Nested table concept.

1) Like VARRAY ,Nested Table size doesn't fix and its Maximum size is unlimited
2) Like VARRAY,Nested table also can be created at SQL and PLSQl level.

as we already know if we create at SQL level then we can use it as DATA TYPE.

Ref : How to use VARRAY/Nested table as data Type in table creation or package level.Check below post .

https://myplsqltutorial.blogspot.in/2017/02/how-to-use-varraynested-table-as-data.html




3) Only Difference between VARRAY and Nested Table is Size ,
In Varray while declaration time itself we wll come to know Maximum size of the collection but in Nested table there is no fixed size .

So based on this point ,you would be able to understand when to use VARRAY and Nested Table.

If you know maximum size before in hand ,then go for VARRAY otherwise NESTED Table. Most of the people use nested table for not to take risk on this size.


Syntax:

CREATE OR REPLACE TYPE TYPE_NAME IS TABLE Of DATATYPE;

Eg:

SQL> CREATE OR REPLACE Type nested_table_Varchar_type is Table Of Varchar2(32767);
  2  /

Type created

SQL> CREATE OR REPLACE Type nested_table_Integer_type is Table Of Integer;
  2  /

Type created


One thing to remember is ,we cant create Nested table OR VARRAY with %TYPE In SQL level ,only we can use %TYPE in PLSQL level with Collections.
Because we know that ,if its created in SQL level then it ill work as DATA type so it will accept only data types to work as a data type anywhere in your schema.

Program 1: 



SQL> DECLARE
  2    TYPE INTEGER_TYPE IS TABLE OF EMP.EMPNO%TYPE;
  3  --Initializing nested table at declaration time itself
  4    V_INTEGER_TYPE INTEGER_TYPE := INTEGER_TYPE();
  5    V_INDX         INTEGER := 0;
  6    CURSOR C1 IS
  7      SELECT EMPNO FROM EMP;
  8  BEGIN
  9  -- If you want to initialize the nested table in body  then you can use as
 10  --below command and as of now in this program i commented because
 11  --in declare section itself it got initialized.
 12  -- V_INTEGER_TYPE := INTEGER_TYPE();
 13    FOR J IN C1 LOOP
 14      V_INTEGER_TYPE.EXTEND;
 15      V_INDX := V_INDX + 1;
 16      V_INTEGER_TYPE(V_INDX) := J.EMPNO;
 17      DBMS_OUTPUT.PUT_LINE(V_INTEGER_TYPE(V_INDX));
 18 
 19    END LOOP;
 20    --Checking count in nested table
 21    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 22                         V_INTEGER_TYPE.count);
 23 
 24    --Deleting 1 element/subscript from nested table
 25    V_INTEGER_TYPE.DELETE(1);
 26    --Checking count in nested table after deletion
 27    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 28                         V_INTEGER_TYPE.count);
 29 
 30  END;
 31  /

7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
No of Record in collection 14
No of Record in collection 13

PL/SQL procedure successfully completed


Program 2:

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2    TYPE INTEGER_TYPE IS TABLE OF EMP.EMPNO%TYPE;
  3  -- Initializing and assigning values to collection at declaration time itself
  4    V_INTEGER_TYPE INTEGER_TYPE := INTEGER_TYPE(10, 20, 30, 41, 51);
  5    V_INDX         INTEGER := 0;
  6 
  7  BEGIN
  8  --Check Count
  9    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 10                         V_INTEGER_TYPE.count);
 11--  Delete 3rd element from nested table,
     -- DELETE(3) Means it wont delete 3 elements from end of nested table 
     --  It ill just delete 3rd element/Subscript from collection
 12    V_INTEGER_TYPE.DELETE(3);
 13  --  Getting 1st subscript to move back or front
 14    V_INDX := V_INTEGER_TYPE.FIRST;
   
 15  DBMS_OUTPUT.PUT_LINE('Element is ' || V_INDX);
 16    WHILE V_INDX IS NOT NULL LOOP
 17      DBMS_OUTPUT.PUT_LINE('Element Value ' || V_INTEGER_TYPE(V_INDX));
-- Get next subscript and it ill be success for 4 times, after that it ill return NULL
-- So condition will be failed and  exit the loop
 18      V_INDX := V_INTEGER_TYPE.NEXT(V_INDX);
 19    END LOOP;
 20  END;
 21  /

No of Record in collection 5
Element Value 10
Element Value 20
Element Value 41
Element Value 51

PL/SQL procedure successfully completed

How to use VARRAY/Nested table as Data Type in Table Creation or Package level

Let's see following examples to understand VARRAY and NESTED TABLE concepts in table creation .


SQL> CREATE OR REPLACE TYPE Mobile_VARRAY IS VARRAY(2) OF INTEGER;
  2  /

Type created

SQL> CREATE TABLE CUST_new( C_ID INTEGER,Phone Mobile_VARRAY);

Table created

SQL> insert into CUST_new values('101',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> insert into CUST_new values('102',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> insert into CUST_new values('103',Mobile_VARRAY(9164841212,9164841213));

1 row inserted

SQL> select * from cust_new;

                                   C_ID PHONE
--------------------------------------- -----
                                    101 <Obje
                                    102 <Obje
                                    103 <Obje





You may wanted to query this table to see all information ,then you need to know about Table Function Concept in Oracle.

Ref : For Table Function concept https://myplsqltutorial.blogspot.in/2017/02/table-function-in-oracle.html

 SQL> select c_id, column_value as Phone_Number from CUST_new c1,table(c1.Phone) c2;

                                   C_ID PHONE_NUMBER
--------------------------------------- ------------
                                    101   9164841212
                                    101   9164841213
                                    102   9164841212
                                    102   9164841213
                                    103   9164841212
                                    103   9164841213

6 rows selected 



There may be scenario's like to have store multiple address for single customer or multiple information for any particular column then we can do as below.

So we ill take scenario  to have multiple account details for single customer.

Now Design column for account details .
It can be
1) Type of account 2) account number 3) Customer_id 4) Branch 5) Location 

Based on above design ,lets create object type to hold all information in single column.

CREATE OR REPLACE TYPE ACC_DETAILS IS OBJECT
(Type_Acc Varchar2(100), acc_number integer, C_id Varchar2(100),
Branch Varchar2(100),Location Varchar2(100));

 

We had already know how to create object type .
Ref for Creating object type: https://myplsqltutorial.blogspot.in/2017/02/how-to-create-object-type-in-sql.html

Now our main task is to hold multiple account details per single Customer.

so lets create Nested table with Object type to achive this.

CREATE OR REPLACE TYPE   ACC_DETAILS_TABLE IS TABLE OF ACC_DETAILS;

Now create table which holds customer information.

CREATE TABLE CUSTOMER (Id Integer,Name Varchar2(100),Acc_Det  ACC_DETAILS_TABLE)
NESTED TABLE  Acc_Det STORE AS ACC_DETAILS_TABLE_FIELD;

Eg:

SQL> CREATE OR REPLACE TYPE ACC_DETAILS IS OBJECT
  2  (
  3    Type_Acc   Varchar2(100),
  4    acc_number integer,
  5    C_id       Varchar2(100),
  6    Branch   Varchar2(100),
  7    Location Varchar2(100));
  8  /

Type created

SQL> CREATE OR REPLACE TYPE   ACC_DETAILS_TABLE IS TABLE OF ACC_DETAILS;
  2  /

Type created

SQL>
SQL> CREATE TABLE CUSTOMER (Id Integer,Name Varchar2(100),Acc_Det  ACC_DETAILS_TABLE)
  2  NESTED TABLE  Acc_Det STORE AS ACC_DETAILS_TABLE_FIELD
  3  /

Table created

SQL>
SQL> INSERT INTO CUSTOMER VALUES(101,'VENKY',ACC_DETAILS_TABLE(ACC_DETAILS('Current',12121212,'A101','Marathahalli','Bangalore'),
  2                                                            ACC_DETAILS('Savings',13131313,'A101','Marathahalli','Bangalore'),
  3                                                            ACC_DETAILS('PPF_Acc',14141414,'A101','Marathahalli','Bangalore'),
  4                                                            ACC_DETAILS('Loan_Acc',15151515,'A101','Marathahalli','Bangalore'),
  5                                                            ACC_DETAILS('Deposit_Acc',16161616,'A101','Marathahalli','Bangalore')));

1 row inserted

SQL> SELECT * FROM CUSTOMER;

                                     ID NAME                                                                             ACC_DET
--------------------------------------- -------------------------------------------------------------------------------- -------
                                    101 VENKY                                                                            <Object




let's below command to get full information.