Oracle Nested Tables and Varrays

Discussion in 'Oracle' started by kidas, Dec 24, 2009.

  1. kidas

    kidas Super Moderator

    Joined:
    Nov 25, 2008
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.club-oracle.com

    Introduction



    Oracle provides two collection types: nested tables and varying arrays or VARRAYS. A collection is an ordered group of elements of the same type. Each element from the group can be accessed using a unique subscript. The element types of a collection can be either built-in datatypes, user-defined types or references (REFs) to object types.

    Nested Tables



    An ordered group of items of type TABLE are called nested tables. Nested tables can contain multiple columns and can be used as variables, parameters, results, attributes, and columns. They can be thought of as one column database tables. Rows of a nested table are not stored in any particular order.

    The size of a nested table can increase dynamically, i.e., nested tables are unbounded. Elements in a nested table initially have consecutive subscripts, but as elements are deleted, they can have non-consecutive subscripts.

    Nested tables can be fully manipulated using SQL, Pro*C, OCI, and PL/SQL. The range of values for nested table subscripts is 1..2147483647. To extend a nested table, the built-in procedure EXTEND must be used. To delete elements, the built-in procedure DELETE must be used.

    An uninitialized nested table is atomically null, so the IS NULL comparison operator can be used to see if a nested table is null. Oracle8 provides new operators such as CAST, THE, and MULTISET for manipulating nested tables.

    Examples of Nested Tables



    Example 1:
    -------------

    The following example illustrates how a simple nested table is created.

    a) First, define a Object type as follows:
    Code:
       SQL> CREATE TYPE ELEMENTS AS OBJECT (
         2> ELEM_ID   NUMBER(6),
         3> PRICE     NUMBER(7,2));
         4> /
    
    b) Next, create a table type ELEMENTS_TAB which stores ELEMENTS objects:

    Code:
      SQL> CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
         2> /
    
    c) Finally, create a database table STORAGE having type ELEMENTS_TAB as one of its columns:
    Code:
       SQL> CREATE TABLE STORAGE (
         2> SALESMAN  NUMBER(4),
         3) ELEM_ID   NUMBER(6),
         4) ORDERED   DATE,
         5) ITEMS     ELEMENTS_TAB)
         6) NESTED TABLE ITEMS STORE AS ITEMS_TAB;

    Example 2:
    ----------

    This example demonstrates how to populate the STORAGE table with a single row:
    Code:
    SQL> INSERT INTO STORAGE
      2> VALUES (100,123456,SYSDATE,
      3> ELEMENTS_TAB(ELEMENTS(175692,120.12),
      4>              ELEMENTS(167295,130.45),
      5>              ELEMENTS(127569,99.99)));

    Example 3:
    ----------

    The following example demonstrates how to use the operator THE which is used in a SELECT statement to identify a nested table:
    Code:
    SQL> INSERT INTO
      2> THE
      3> (SELECT ITEMS FROM STORAGE WHERE ELEM_ID = 123456)
      4> VALUES (125762, 101.99);

    Example 4:
    ----------

    The following example shows how to update the STORAGE table row where salesman column has value 100:
    Code:
    SQL> UPDATE STORAGE
      2> SET ITEMS = ELEMENTS_TAB(ELEMENTS(192512, 199.99))
      3> WHERE SALESMAN = 100;
    

    Varrays



    Varrays are ordered groups of items of type VARRAY. Varrays can be used to associate a single identifier with an entire collection. This allows manipulation of the collection as a whole and easy reference of individual elements.

    The maximum size of a varray needs to be specified in its type definition. The range of values for the index of a varray is from 1 to the maximum specified in its type definition. If no elements are in the array, then the array is atomically null. The main use of a varray is to group small or uniform-sized collections of objects.

    Elements of a varray cannot be accessed individually through SQL, although they can be accessed in PL/SQL, OCI, or Pro*C using the array style subscript. The type of the element of a VARRAY can be any PL/SQL type except the following:
    Code:
      BOOLEAN
      TABLE
      VARRAY
      object types with TABLE or VARRAY attributes
      REF CURSOR
      NCHAR
      NCLOB
      NVARCHAR2
    Varrays can be used to retrieve an entire collection as a value. Varray data is stored in-line, in the same tablespace as the other data in its row.

    When a varray is declared, a constructor with the same name as the varray is implicitly defined. The constructor creates a varray from the elements passed to it. You can use a constructor wherever you can use a function call, including the SELECT, VALUES, and SET clauses.

    A varray can be assigned to another varray, provided the datatypes are the exact same type. For example, suppose you declared two PL/SQL types:
    Code:
      TYPE My_Varray1 IS VARRAY(10) OF My_Type;
      TYPE My_Varray2 IS VARRAY(10) OF My_Type;
    An object of type My_Varray1 can be assigned to another object of type My_Varray1 because they are the exact same type. However, an object of type My_Varray2 cannot be assigned to an object of type My_Varray1 because they are not the exact same type, even though they have the same element type.

    Varrays can be atomically null, so the IS NULL comparison operator can be used to see if a varray is null. Varrays cannot be compared for equality or inequality.

    Examples for Varrays



    Example 5:
    ---------

    The following shows how to create a simple VARRAY:

    a) First, define a object type ELEMENTS as follows:
    Code:
       SQL> CREATE TYPE MEDICINES AS OBJECT (
         2> MED_ID    NUMBER(6),
         3> MED_NAME  VARCHAR2(14),
         4> MANF_DATE DATE);
         5> /
    b) Next, define a VARRAY type MEDICINE_ARR which stores MEDICINES objects:

    Code:
       SQL> CREATE TYPE MEDICINE_ARR AS VARRAY(40) OF MEDICINES;
         2> /
    
    c) Finally, create a relational table MED_STORE which has MEDICINE_ARR as a column type:
    Code:
       SQL> CREATE TABLE MED_STORE (
         2> LOCATION    VARCHAR2(15),
         3> STORE_SIZE  NUMBER(7),
         4> EMPLOYEES   NUMBER(6),
         5> MED_ITEMS   MEDICINE_ARR);

    Example 6:
    ----------

    The following example shows how to insert two rows into the MED_STORE table:

    Code:
    SQL> INSERT INTO MED_STORE
      2> VALUES ('BELMONT',1000,10,
      3>     MEDICINE_ARR(MEDICINES(11111,'STOPACHE',SYSDATE)));
    
    SQL> INSERT INTO MED_STORE
      2> VALUES ('REDWOOD CITY',700,5,
      3>     MEDICINE_ARR(MEDICINES(12345,'STRESS_BUST',SYSDATE)));

    Example 7:
    ----------

    The following example shows how to delete the second row we have inserted in example 6 above:
    Code:
    SQL> DELETE FROM MED_STORE
      2> WHERE LOCATION = 'REDWOOD CITY';
    
    Example 8:
    ----------

    The following example shows how to update the MED_STORE table and add more medicines to the Belmont store:

    Code:
    SQL> UPDATE MED_STORE
      2> SET MED_ITEMS = MEDICINE_ARR (
      3>        MEDICINES(12346,'BUGKILL',SYSDATE),
      4>        MEDICINES(12347,'INHALER',SYSDATE),
      5>        MEDICINES(12348,'PAINKILL',SYSDATE));
    Differences Between Nested Tables and Varrays
    ---------------------------------------------

    Nested tables are unbounded, whereas varrays have a maximum size.

    Individual elements can be deleted from a nested table, but not from a varray. Therefore, nested tables can be sparse, whereas varrays are always dense.

    Varrays are stored by Oracle in-line (in the same tablespace), whereas nested table data is stored out-of-line in a store table, which is a system-generated database table associated with the nested table.

    When stored in the database, nested tables do not retain their ordering and subscripts, whereas varrays do.

    Nested tables support indexes while varrays do not.​
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  4. Full Zip Hoody

    Full Zip Hoody New Member

    Joined:
    Sep 29, 2010
    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Programer
    Location:
    US of A
    "Nested tables can contain multiple columns and can be used as variables, parameters, results, attributes, and columns." - yes indeedi agree, everything here is so useful.. thanks a lot
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice