Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/articles/oracle/)
-   -   Oracle Nested Tables and Varrays (http://www.go4expert.com/articles/oracle-nested-tables-varrays-t20494/)

kidas 24Dec2009 13:09

Oracle Nested Tables and Varrays
 

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

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

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

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

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

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

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: sql

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: sql

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

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

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

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

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

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

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.

shabbir 3Jan2010 23:30

Re: Oracle Nested Tables and Varrays
 
Nominate this article for Article of the month - Dec 2009

shabbir 18Jan2010 18:41

Re: Oracle Nested Tables and Varrays
 
Vote for this article for Article of the Month - December 2009

Full Zip Hoody 30Sep2010 08:51

Re: Oracle Nested Tables and Varrays
 
"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


All times are GMT +5.5. The time now is 22:31.