Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 26 February 2017

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;

Share this article :

0 comments:

Post a Comment