Follow us: Connect on YouTube 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 :

3 comments:

  1. Selamat Petang,

    The challenge however, is we don’t yet know how it will be used making to hard to assess their value proposition and consequently value.

    how to apply refcursor in package with multiple insted of multple cursors in oracle?

    Give one example
    .

    Thank you very much and will look for more postings from you.

    Regards,

    ReplyDelete
  2. This is an awesome post. Really very informative and creative contents. This concept is a good way to enhance the knowledge. I like it and help me to development very well. Thank you for this brief explanation and very nice information. Well, got a good knowledge.
    Php course in chennai

    ReplyDelete
  3. Ohayo,


    Brilliant article, glad I slogged through the PL/SQL Index by tables (or) PL/SQL tables (or) associative arrays it seems that a whole lot of the details really come back to from my past project.


    Please, can you advise me on this query if it is correct? I have been asked to produce a report of vacation by staff for the year 2017 and group by line manager. The question is looking at this query, I have ordered by line manager which I think is showing staff under each manager but I am not sure If I need to break on the line manager and how to accomplish this. Please note that I inherited these table definitions.


    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).


    Many Thanks,
    Radhey

    ReplyDelete