Database Reference
In-Depth Information
Table 16.4
Oracle Object Collection Datatypes.
Datatype
Parameters
Example
VARRAY
(subscript)
Object(subscript)
Fixed-length array or reserved chunk of memory for a fixed
number of array elements. VARRAY collections can be
resized and used in temporary tables.
Nested Table
TABLE(…)
TABLE (SELECT …)
Dynamic array or pointer to a variable number of array ele-
ments. Nested table columns can be divided into sepa-
rate tablespaces.
Associative Array
Only available in PL/SQL. PL/SQL is covered in Chapter 24.
Indexed dynamic array. Faster access than a nested table using
an index.
16.2.4.1
Using VARRAY Collections
The ARTIST table contains a VARRAY collection object called INSTRU-
MENTSCOLLECTION. We have already been introduced to the
INSTRUMENTSCOLLECTION datatype in this chapter. The following
script snippets are a small section of the MUSIC schema creation script (see
Appendix A). The first thing we do is create a type for the collection of
instruments. The INSTRUMENTSCOLLECTION type has a fixed num-
ber of 10 elements for each ARTIST table entry.
CREATE OR REPLACE TYPE INSTRUMENTSCOLLECTION
AS VARRAY(10) OF VARCHAR2(32);
/
Next we create the ARTIST table including the INSTRUMENTSCOL-
LECTION type. Because the INSTRUMENTSCOLLECTION is effec-
tively a new datatype (user-defined type), it simply becomes the datatype
definition for the INSTRUMENTS column.
CREATE TABLE ARTIST(
ARTIST_ID NUMBER NOT NULL
, NAME VARCHAR2(32) NOT NULL, STREET VARCHAR2(32)
, POBOX CHAR(20), CITY VARCHAR2(32)
, STATE_PROVINCE VARCHAR2(32), COUNTRY VARCHAR2(32)
 
Search WWH ::




Custom Search