Composite data types

Composite data types are values that are comprised of zero or more elements, where each element has a value of a particular data type.

SQL Anywhere supports the ROWS and ARRAYS composite data types. These data types are a more efficient way to store lists because they provide the ability to define the structure and data type of their values. They also make access to list elements easier to achieve, either directly, by using double square brackets; or as result set, by using the UNNEST operator. Consider using the ARRAY data type if you are storing lists as delimited strings in VARCHAR columns, and parsing them using sa_split_list. ARRAYS are very helpful when storing different objects that are all related in some ways. ROWS are helpful when storing multiple values related to one object.

Declaring an ARRAY type

An ARRAY type is a homogeneous, ordered collection that can be passed in whole or in part as an argument to SQL stored procedures or functions.

An ARRAY type can consist of up to 6.4 million elements. An ARRAY is initialized to a zero-length ARRAY of the declared type, where each element is NULL.

An ARRAY constructor builds an ARRAY value so the array can be processed in a query or passed as an argument to a SQL stored procedure or function.

The database server supports the following syntaxes for declaring variables of the ARRAY type:

DECLARE variable-name element-type-name ARRAY [ ( maximum-size ) ]
DECLARE variable-name ARRAY [ ( maximum-size ) ] OF element-type-name

The array can contain up to 6.4 million elements if maximum-size is omitted. Variables of the ARRAY type are not initialized to NULL the way all other non-composite variables are. Instead, they are initialized to a zero-length array.

ARRAY types cannot be stored as columns in a base or temporary table and are not supported in:

the outermost SELECT list of a view definition
a top-level SELECT block or query expression that is returned to the client
a base table
a temporary table
an embedded SQL FETCH statement

The following example illustrates how to declare an array of 5 integers:

DECLARE NewArray INTEGER ARRAY(5);

The following example illustrates an alternative way of declaring an array of 5 integers that is compatible with the Oracle syntax:

DECLARE NewArray ARRAY(5) OF INTEGER;

The following example illustrates how to declare a two-dimensional array, where New2DArray contains 10 elements, each of which is a five-element array of integers:

DECLARE New2DArray INTEGER ARRAY(5) ARRAY(10);

The following example illustrates an alternative way of declaring a two-dimensional array that is compatible with the Oracle syntax, where New2DArray contains 10 elements, each of which is a five-element array of integers:

DECLARE New2DArray ARRAY(10) OF ARRAY(5) OF INTEGER;

Declaring a ROW type

A ROW type is described by a row type descriptor, which consists of the field descriptor of each field in the ROW type. ROW types are restricted to 45000 fields, which is the same limit as the number of columns in a table.

Variables in the ROW type are initialized to a ROW of the declared type where each field is initialized as NULL.

A ROW supports the construction of structured types, consisting of a group of fields of potentially different types. ROW types can be part of higher-order row types, which permits complex structures involving other row types and arrays.

The following example illustrates how to declare a variable, student, that is defined as a structured type of four different fields:

DECLARE student ROW(studentID INTEGER,
    student_first_name VARCHAR(40),
    student_last_name VARCHAR(50),
    student address LONG VARCHAR);

The following example illustrates how to assign a ROW as a complete structure:

DECLARE employee ROW(empID INTEGER,
    address ROW(street_address LONG VARCHAR,
        city VARCHAR(50),
        province VARCHAR(30),
        country VARCHAR(40)
    )
); 
DECLARE temp_address ROW(street_address LONG VARCHAR,
    city VARCHAR(50),
    province VARCHAR(30),
    country VARCHAR(40)
); 
SET temp_address = employee.address;