1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Nested Tables in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 23, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    Nested Tables or PL/SQL tables are persistent form of collections, which are used to store arbitrary number of elements. Unlike the key-value pair, here each element is identified by a sequential number, which is maintained by Oracle server.
    [​IMG]

    The major difference between Index By Tables and Nested Tables is that Nested Tables are unbounded persistent form of collection, while Associative arrays were bounded and non persistent. Data in nested tables need not to be stored in consecutive rows, but it can be sparse. They can exist in both SQL and PL/SQL. In SQL, they can be defined and used as column type in relational table or as column type of memory variable in a PL/SQL block. In PL/SQL, they can be declared as a block level type.

    2. Notes

    • Nested Tables contain homogeneous data i.e. each row has the same data structure model
    • Nested Tables require initialization when they are part of object model or used within PL/SQL
    • Nested Tables do not have upper bound. An element cell can be explicitly added to the collection using EXTEND method.
    • Order of the Nested table elements are not preserved in database
    • PGA allocates the memory for collections. This is valid for all three types of collections.
    • Oracle raises NO_DATA_FOUND exception, if the program tries to read the element corresponding to a nonexistent index.

    3. Syntax

    In SQL

    Code (Text):
    CREATE [OR REPLACE] TYPE [TYPE NAME] IS    TABLE OF [DATA TYPE] [NOT NULL];
    In PL/SQL

    Code (Text):
    DECLARE
       TYPE [TYPE NAME] IS TABLE OF [DATA TYPE];
       VARIABLE [TYPE NAME];
    4. Using Nested Tables in SQL

    Example,

    A Nested Table can be created in database using CREATE TYPE command. The SQL below creates a nested table TYPE_ORDERS of string element types.

    Code (SQL):
    CREATE TYPE TYPE_ORDERS IS TABLE OF VARCHAR2(100);
    /
    Now, a table ORDERS is created which uses above nested table to declare a column ORDER_NAME. Note the NESTED TABLE..STORE AS clause. It is used to define a separate table in relational format to store the nested table data. In below SQL, ORDERS table contains ORDER_NAME of TYPE_ORDERS type, whose data would be stored in tabular format at memory segment NESTED_ORDERS.

    Code (SQL):
    CREATE TABLE ORDERS (
    ORDER_ID NUMBER,
    ORDER_NAME  TYPE_ORDERS)
       NESTED TABLE ORDER_NAME STORE AS NESTED_ORDERS;
    Inserting test data into ORDERS Table

    Code (SQL):
    INSERT INTO ORDERS
    VALUES (1, TYPE_ORDERS('ORDER 1','ORDER 2','ORDER 3'))
    /

    INSERT INTO ORDERS
     VALUES (2, TYPE_ORDERS('ORDER 21','ORDER 22','ORDER 23'))
    /
    The SQL below queries the ORDERS table in relational format.

    Code (SQL):
    SQL> SELECT * FROM ORDERS;

      ORDER_ID ORDER_NAME
    ---------- --------------------------------------------------
             1 TYPE_ORDERS('ORDER 1', 'ORDER 2', 'ORDER 3')
             2 TYPE_ORDERS('ORDER 21', 'ORDER 22', 'ORDER 23')
    The SQL below uses TABLE function to query the ORDERS table and display result in relational format.

    Code (SQL):
    SELECT T1.ORDER_ID,T2.*
    FROM ORDERS T1, TABLE(T1.ORDER_NAME) T2
    /

      ORDER_ID COLUMN_VALUE
    ---------- ---------------
             1 ORDER 1
             1 ORDER 2
             1 ORDER 3
             2 ORDER 21
             2 ORDER 22
             2 ORDER 23
    The SQL below queries ORDERS table using THE operator to display only nested table data. Note that the data from the scalar subquery must be queried based on primary key value.

    Code (SQL):
    SELECT VALUE(T)
    FROM THE (SELECT ORDER_NAME FROM ORDERS WHERE ORDER_ID=1) T
    /

    VALUE(T)
    -------------
    ORDER 1
    ORDER 2
    ORDER 3
    5. Nested Tables in PL/SQL

    The PL/SQL block below declares a Nested Table and its variable. Note the following points:

    1. Initialization using default constructor
    2. Appending an element using EXTEND method
    3. Using FIRST and LAST method to iterate through the elements of nested table

    Code (SQL):
    DECLARE
      TYPE TYPE_ODERS IS TABLE OF VARCHAR2(100);
      ORDER_NAME TYPE_ORDERS := TYPE_ORDERS();
    BEGIN
      FOR I IN 1..5
      LOOP
        ORDER_NAME.EXTEND;
        ORDER_NAME(I) := 'Order Number '||TO_CHAR(I);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('List of orders for '||TO_CHAR(SYSDATE));
      FOR I IN ORDER_NAME.FIRST..ORDER_NAME.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE(ORDER_NAME(I));
      END LOOP;
    END;
    /

    List OF orders FOR 21-DEC-10
    ORDER NUMBER 1
    ORDER NUMBER 2
    ORDER NUMBER 3
    ORDER NUMBER 4
    ORDER NUMBER 5

    PL/SQL PROCEDURE successfully completed.
    6. Nested Table collection methods

    Below Table lists the methods available to be used with Nested table collections.

    [TABLE]Method Description
    EXISTS(n) Returns TRUE if the nth element in a PL/SQL table exists. Note: If the index element does not exist, EXISTS method does not raises SUBSCRIPT_OUTSIDE_LIMIT exception.
    COUNT Returns the number of elements that a PL/SQL table currently contains
    FIRST | LAST Returns the first and last (smallest and largest) index number in a PL/SQL table. Returns NULL , if it is empty
    PRIOR (n) Returns the index number that precedes index in a PL/SQL table
    NEXT (n) Returns the index number that succeeds index in a PL/SQL table
    EXTEND EXTEND appends one null element to a collection. EXTEND (n) appends n null elements to a collection. EXTEND (n, j) appends n copies of the jth element to a collection.
    TRIM TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection.
    DELETE DELETE removes all elements from the table. DELETE (n) removes n elements from the table. DELETE (m,n) removes all elements in range from m..n from the table. Note: LAST returns the greatest subscript of a collection and COUNT returns the number of elements of a collection. If you delete some elements, LAST != COUNT.[/TABLE]

    Syntax for using a collection method
    Code (Text):
    collection_name.method_name[(parameters)]
    Restrictions
    1. Collection methods are not callable from SQL statements.
    2. No method can be used with a NULL collection except EXISTS. Oracle raises COLLECTION_IS_NULL exception if any method is used with NULL collection

    7. Examples

    The PL/SQL block below declares a Nested Table, deletes its third element, iterates and displays the remaining elements.

    Code (SQL):
    DECLARE
       TYPE CLUB_TAB IS TABLE OF PLS_INTEGER ;
       DTAB    CLUB_TAB  :=  CLUB_TAB(50510,50525,50550,50555,50578,50569);
       N NUMBER;
     BEGIN
       DTAB.DELETE(3);
       N := DTAB.FIRST;
       LOOP
         DBMS_OUTPUT.PUT_LINE( DTAB(N));
         N := DTAB.NEXT(N);
         EXIT WHEN N IS NULL;
         END LOOP;
     END;
    /

    50510
    50525
    50555
    50578
    50569

    PL/SQL PROCEDURE successfully completed.
     

    Attached Files:

    • Nest.JPG
      Nest.JPG
      File size:
      23.3 KB
      Views:
      29,454
  2. Gupta

    Gupta Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    150
    The forum is good but it would be helpful to us if you present complex codes.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
  4. Gupta

    Gupta Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    150
    Hi Jagadekara,

    Thank you very much for your valuable reply.

    Kind Regards,
    Gupta