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

Pages

Tuesday, 7 March 2017

PL/SQL Nested tables

       
         Nested tables are user defined data types, which are used to store multiple data items into single unit. Basically nested table is an unconstrained table. Generally, we are not allowed to store Index by table permanently into Database. To overcome them problem oracle 8.0 introduced nested tables, arrays to store permanently in Database using SQL. Nested table is an extension of index by table. Generally in index table, we are not allowed to add or remove indexes, but in nested table we are adding or removing indexes using extend from collection methods.

   By default in nested table indexes start with 1 and also these indexes are conceptions. This is a user defined type.so, we are creating two-step process. Before we are storing data into nested table, array we must initialize through constructor. Here constructor name is same as type name.


Syntax:-  Type typename  is table of data type (size);
               Variable name typename:=typename();

Example:-

DECLARE
   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1 := t1 ();
BEGIN
   V_t.EXTEND (300);
   V_t (300) := 60;
   DBMS_OUTPUT.put_line (v_t (300));
END;
          
          
          
Note: - we can also store actual through the constructor itself, in this case oracle server start indexes 1 onwards.

Example:-

DECLARE
   TYPE t1 IS TABLE OF NUMBER (10);

   V_t   t1
            := t1 (10,
                   20,
                   30,
                   40);
BEGIN
   DBMS_OUTPUT.put_line (v_t.FIRST);
   DBMS_OUTPUT.put_line (v_t.LAST);
   DBMS_OUTPUT.put_line (v_t.COUNT);
   DBMS_OUTPUT.put_line (v_t.COUNT);
END;

Using bulk collect:-


Example:-

DECLARE
   TYPE t1 IS TABLE OF VARCHAR2 (20);

   V_t   t1 := 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;
        

Share this article :

0 comments:

Post a Comment