Infolinks

Friday 29 January 2016

Nested Tables and Varrays

Oracle now supports three types of collections:
  • PL/SQL tables are singly dimensioned, unbounded, sparse collections of homogeneous elements and are available only in PL/SQL (see Chapter 10). These are now called index-by tables.
  • Nested tables are also singly dimensioned, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are available in both PL/SQL and the database (for example, as a column in a table).
  • VARRAYs, like the other two collection types, are also singly dimensioned collections of homogeneous elements. However, they are always bounded and never sparse. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved.
Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column. For example, the employee table used by the HR department could store the date of birth for each employee's dependents in a single column, as shown in Table 19.1.

Id (NUMBER)
Name (VARCHAR2)
Dependents_ages (Dependent_birthdate_t)
10010
Zaphod Beeblebrox
12-JAN-1763
4-JUL-1977
22-MAR-2021
10020
Molly Squiggly
15-NOV-1968
15-NOV-1968
10030
Joseph Josephs
10040
Cepheus Usrbin
27-JUN-1995
9-AUG-1996
19-JUN-1997
10050
Deirdre Quattlebaum
21-SEP-1997
It's not terribly difficult to create such a table. First we define the collection type:
CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;
Now we can use it in the table definition:
CREATE TABLE employees (
   id NUMBER,
   name VARCHAR2(50),
   ...other columns...,
   Dependents_ages Dependent_birthdate_t
);
We can populate this table using the following INSERT syntax, which relies on the type's default constructor to transform a list of dates into values of the proper datatype:
INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ...,
   Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977', '22-MAR-2021'));

Differences
One chief difference between nested tables and VARRAYs surfaces when we use them as column datatypes. Although using a VARRAY as a column's datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table's data.
Nested tables, by contrast, are stored in special auxiliary tables called store tables, and there is no pre-set limit on how large they can grow. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.


No comments:

Post a Comment