Using Collection Data Types

The following sections of the chapter rely on several different examples to show how you can manipulate collections in SPL programs.

The basics of handling collections in SPL programs are illustrated with the numbers table, as Figure 420 shows.

CREATE TABLE numbers
(
   id INTEGER PRIMARY KEY,
   primes       SET( INTEGER NOT NULL ),
   evens        LIST( INTEGER NOT NULL ),
   twin_primes  LIST( SET( INTEGER NOT NULL )
                   NOT NULL )

The primes and evens columns hold simple collections. The twin_primes column holds a nested collection, a LIST of SETs. (Twin prime numbers are pairs of consecutive prime numbers whose difference is 2, such as 5 and 7, or 11 and 13. The twin_primes column is designed to allow you to enter such pairs.

Some examples in this chapter use the polygons table in Figure 421 to illustrate how to manipulate collections. The polygons table contains a collection to represent two-dimensional graphical data. For example, suppose that you define an opaque data type named point that has two double-precision values that represent the x and y coordinates of a two-dimensional point whose coordinates might be represented as '1.0, 3.0'. Using the point data type, you can create a table that contains a set of points that define a polygon.

CREATE OPAQUE TYPE point ( INTERNALLENGTH = 8);

CREATE TABLE polygons
(
   id          INTEGER PRIMARY KEY,
   definition  SET( point NOT NULL )
);

The definition column in the polygons table contains a simple collection, a SET of point values.