PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays

     Index by tables are user defined data types which is used to store multiple data items. Basically these tables are unconstrained tables. Index by table having two parts, these are value field and key field. In value field, oracle server stores actual data, where as in key field oracle server stores indexes, that’s why index by table having ‘key value’ pairs and also indexes are by default integers, and these indexes are in between negative to positive numbers. This index field behaves like a primary key, it doesn’t accept duplicate values.

  Generally index by tables are used to improve the performance or application, because these tables are stored in memory area, that’s why these tables are also called a memory tables.

Generally to improve the performance of the application, these table indexes are using ‘binary-integer’ data type, so we are creating in two step process. First we are creating type, then only we are creating a variable of that type.

Syntax:-

Step1:- Type typename is table of data type (size) index by binary_integer;

Step2:- Variablename typename;

    Index by tables, nested tables, varrays also called as collections. If we want to integrate these collection methods. These are exists first, last, count, prior, next, delete, delete (index number, indexnumber). These collection methods are operating on indexes.

Example:

DECLARE

   TYPE t1 IS TABLE OF NUMBER (10)

                 INDEX BY BINARY_INTEGER;

   v_t   t1;

BEGIN

   v_t (1) := 10;

   v_t (2) := 20;

   v_t (3) := 30;

   v_t (4) := 40;

   v_t (5) := 50;

   DBMS_OUTPUT.put_line (v_t (4));

   DBMS_OUTPUT.put_line (v_t.FIRST);

   DBMS_OUTPUT.put_line (v_t.LAST);

   DBMS_OUTPUT.put_line (v_t.PRIOR (3));

   DBMS_OUTPUT.put_line (v_t.COUNT);

   DBMS_OUTPUT.put_line (v_t.NEXT (4));

   v_t.delete (1, 3);

   DBMS_OUTPUT.put_line (v_t.COUNT);

   DBMS_OUTPUT.put_line (v_t.FIRST);

   DBMS_OUTPUT.put_line (v_t.LAST);

   v_t.delete;

   DBMS_OUTPUT.put_line (v_t.COUNT);

END;

*write a pl/sql program to transfer all employee names form emp table and store it into index by table, and also display the content form index by table.

DECLARE

   TYPE t1 IS TABLE OF VARCHAR2 (10)

                 INDEX BY BINARY_INTEGER; 

   V_t   t1;

   CURSOR c1

   IS

      SELECT ename FROM emp;

   N     NUMBER (10) := 1;

BEGIN

   OPEN c1;

   LOOP

      FETCH c1 INTO v_t (n);

      EXIT WHEN c1%NOTFOUND;

      n := n + 1;

   END LOOP;

   FOR i IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

By Using Bulk Collect to improve performance: 

DECLARE

   TYPE t1 IS TABLE OF VARCHAR2 (10)

                 INDEX BY BINARY_INTEGER;

   v_t   t1;

BEGIN

   SELECT ename

     BULK COLLECT INTO v_t

     FROM emp;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

Example:-

DECLARE

   TYPE t1 IS TABLE OF DATE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

BEGIN

   FOR I IN 1 .. 10

   LOOP

      v_t (i) := SYSDATE + i;

   END LOOP;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.put_line (v_t (i));

   END LOOP;

END;

                         

Example:-                          

                          

DECLARE

   TYPE t1 IS TABLE OF VARCHAR2 (10)

                 INDEX BY VARCHAR2 (10);

   V_t   t1;

   x     VARCHAR2 (10);

BEGIN

   v_t ('A') := 'murali';

   v_t ('B') := 'XYZ';

   v_t ('C') := 'ABC';

   v_t ('D') := 'ZZZ';

   X := 'A';

   LOOP

      DBMS_OUTPUT.put_line (v_t (x));

      x := v_t.NEXT (x);

      EXIT WHEN x IS NULL;

   END LOOP;

END;

                     

                     

Example:-                     

                     

DECLARE

   TYPE t1 IS TABLE OF emp%ROWTYPE

                 INDEX BY BINARY_INTEGER;

   V_t   t1;

   X     NUMBER (10);

BEGIN

   SELECT *

     BULK COLLECT INTO v_t

     FROM emp;

   X := 1;

   LOOP

      DBMS_OUTPUT.

       put_line (

         v_t (x).ename || ' ' || v_t (x).sal || ' ' || v_t (x).hiredate);

      X := v_t.NEXT (x);

      EXIT WHEN x IS NULL;

   END LOOP;

END;

                 

Example:-

DECLARE

   TYPE t1 IS TABLE OF emp%ROWTYPE

                 INDEX BY BINARY_INTEGER;

   v_t   t1;

BEGIN

   SELECT *

     BULK COLLECT INTO v_t

     FROM emp;

   FOR I IN v_t.FIRST .. v_t.LAST

   LOOP

      DBMS_OUTPUT.

       put_line (

         v_t (i).ename || ' ' || v_t (i).sal || ' ' || v_t (i).deptno);

   END LOOP;

END;

Package specification:-

CREATE OR REPLACE PACKAGE ph1

IS

   TYPE t1 IS TABLE OF emp%ROWTYPE

                 INDEX BY BINARY_INTEGER;

   FUNCTION f1

      RETURN t1;

END;

/

Package body:-

CREATE OR REPLACE PACKAGE BODY ph1

IS

   FUNCTION f1

      RETURN t1

   IS

      V_t   t1;

   BEGIN

      SELECT *

        BULK COLLECT INTO v_t

        FROM emp;

      RETURN v_t;

   END f1;

END; 

DECLARE

   X   ph1.t1;

BEGIN

   X := ph1.f1;

   FOR I IN x.FIRST .. x.LAST

   LOOP

      DBMS_OUTPUT.

       put_line (x (i).ename || ' ' || x (i).sal || ' ' || x (i).hiredate);

   END LOOP;

END;


You May Like:

PLSQL Record

PL/SQL Ref Cursor

PL/SQL Bulk Collect And Bulk bind