DECLARE VECTOR
DECLARE VECTOR in SQL Script declares a collection data type that is expandable, ordered, and typed. A vector requires a data type at initialization.
This section provides the general syntax for declaring a VECTOR, and describes the functionality of vectors in SQL Script. Examples are given at the end of the section.
Syntax
DECLARE <identifier> VECTOR (<data type>) [DEFAULT VECTOR [<value>, <value>]]
Base Data Types
• The DEFAULT clause is optional and can be used to initialize VECTOR values.
• A vector cannot be the base data type of another vector, so you cannot use the following declaration:
DECLARE vectorX VECTOR (VECTOR (CHAR)));
• ROW is an acceptable base data type of a vector, and is necessary for any implementation of collections, as in the following example:
DECLARE vectorX VECTOR(ROW (a INTEGER,
b INTEGER, c CHAR, d CHAR));
• ROWs can also contain vectors, and a field in the ROW can be accessed through the dot notation as follows:
DECLARE myRow ROW(a INTEGER, v VECTOR(INTEGER));
SET myRow = ROW(1, VECTOR[9,10,11]);
SET myRow.v[2] = 9;
DECLARE vecRow VECTOR(ROW (a INTEGER, b CHAR));
SET vecRow = VECTOR[(22, 'text')];
SET vecRow[1].a = vecRow[1].a + 15;
Declaration
• You cannot declare a vector as a field in a CURSOR or a PIPE, so the following declaration would not be permitted:
DECLARE myCursor CURSOR (a VECTOR(CHAR));
• Vectors can be declared as PUBLIC constants or nonpublic constants. The contents of such vectors should not be modified.
• The initial contents of a CONSTANT VECTOR must be defined in a DEFAULT clause and must be literals or references to other similar type of vectors.
Assigning Values to VECTOR Elements
• An empty vector with no base type can be created by the expression
VECTOR[]
• Elements in a vector can be assigned a value of NULL.
SET vectorX[1] = NULL;
• The vector is set to NULL at declaration and must be initialized before it can be used, as in the following example. Any reference to an uninitialized vector results in an error.
VECTOR['my text', 'your text']
This expression can be assigned to a compatible vector with the SET statement, as follows:
SET my_vector = VECTOR['my text', 'your text'];
SET your_vector = VECTOR[ROW(2,3), ROW(4,5)];
SET your_vector = my_vector;
In the above declaration, the contents of the source vector your_vector is copied to the target vector my_vector, and the target vector is initialized.
• Vectors can be used as parameters in procedures, and the procedures with OUT or INOUT parameters can alter the vector in the same manner as the SET statement.
CALL myProcedure(vectorX);
• After spaces are allocated in a vector by initializing the vector, elements in the vector can be accessed through square brackets, as in arrays in other programming languages. Vector indexes start at 1 and increment by 1.
SET vectorX[20] = 'my text';
SET yourvector[2 + index] = vectorX[20];
A vector index must evaluate to a numeric value. Otherwise, an error results, as in the following example:
SET yourvector[1 || 'text'] = ‘text’;
• If a vector index evaluates to NULL, the element reference results in NULL.
• If the target reference index is NULL, an error results, as in the following example:
SET vectorX[NULL] = 'text';
• Vectors are bound by the current allocation, but can be resized through reassignment or through system procedures.
• Vectors can be assigned to other vectors that have implicitly assignable data types. In the case where the data type is not the same, a vector is created, and all elements automatically have the CAST function run to convert the value to the target type.
Comparing Vectors
Vectors can be compared to one another if their base types are comparable. Only comparison operators such as = (equal to) and != (not equal) are supported.
Vectors are equal if they have the same number of values, and corresponding elements are equal. If either vector is NULL, the result of the comparison is unknown. If any of the elements is NULL, the result of the comparison is unknown.
Vectors and Functions
Several functions are available to modify the contents of a vector. The following functions are supported: CARDINALITY, CAST, CONCAT, EXTEND, and TRUNCATE. All vectors, regardless of their base data type, are accepted as arguments for these functions:
CARDINALITY
This function returns the number of elements allocated in the vector.
CAST
This function converts all the elements in a vector to the desired target data type. The result vector is of the same size as that of the source vector. If the vector has a NULL element, the result vector contains NULL. The source vector’s data type and the target vector’s data type must be compatible. For details, see the section
CAST.
CONCAT
This function adds two vectors that have the same data type together. If either of the vectors is NULL, an error occurs indicating that the resultant vector is NULL. Concatenating nonNULL vectors result in a new vector containing the elements from the concatenated vectors. The elements of the input vectors are added successively; that is, the elements of the first vector populates the result vector first, then the elements of the second vector populates the result vector, and so on.
Note: The || operator does the same thing as the CONCAT function.
EXTEND
This function appends the specified number of elements to a vector. The appended number of elements are assigned a NULL value, and the syntax is as follows:
SET vectorX = EXTEND (vectorX, 2);
— If the number of elements specified to be appended evaluates to NULL, this function returns NULL.
— If the vector is NULL, an error occurs, indicating that the vector is NULL.
— If the specified number is a negative number, an error occurs.
FIND_INDEX
The function searches a vector for the first occurrence of a specified value. It accepts two arguments. The first argument is any scalar value. The second argument is the vector that is searched. The index starts at 1.
— The base type of the vector and the supplied argument’s data type must be comparable or implicitly castable.
— If the searched value is not found in the vector, the result is zero.
— If either the vector or the supplied argument is NULL, the result of the function is NULL.
The following example returns a value of 3:
DECLARE v VECTOR(INT) DEFAULT VECTOR [5, 10, 50, 100];
SET i = FIND_INDEX(50, v);
TRUNCATE
This function removes a specified number of elements (the “chop count”) from the end of a vector. The syntax is as follows:
SET vector1 = TRUNCATE (vector1, chop_count)
— If the chop count evaluates to NULL, this function returns NULL.
— If the chop count is negative, or exceeds the initial size of the vector, an error occurs.
— If the vector is NULL, an error occurs.
— TRUNCATE is also a TDV-supported SQL function. Refer to
TRUNCATE, for a description.
Examples
This section contains several examples to illustrate the functionality of vectors in SQL Script.
PROCEDURE vectorExampleA()
BEGIN
DECLARE vectorX VECTOR(ROW(a int, b char));
DECLARE vectorY VECTOR(ROW(x int, y char));
SET vectorX = VECTOR[(11, 'one in vectorX'), (12, 'two in vectorX')];
SET vectorY = VECTOR[(21, 'one in vectorY'), (22, 'two in vectorY')];
CALL print(vectorX[1].b);
CALL print(vectorX[2].b);
IF vectorX != vectorY THEN
CALL print(vectorY[1].y);
END IF;
END
PROCEDURE vectorExampleB()
BEGIN
DECLARE vectorX VECTOR(ROW(a int, b char));
DECLARE vectorY VECTOR(ROW(x int, y char));
SET vectorX = VECTOR[(11, 'one in vectorX'), (12, 'two in vectorX')];
SET vectorX[1].a = vectorX[1].a + 11;
SET vectorY = VECTOR[(5, 'one in vectorY'), (10, 'two in vectorY')];
SET vectorX = vectorY;
CALL PRINT(TO_CHAR(vectorX[2].a));
END
PROCEDURE vectorExampleC(OUT x VECTOR(INTEGER))
BEGIN
DECLARE vectorX VECTOR(INTEGER);
SET x = VECTOR[5, 55, 60];
SET vectorX = x;
CALL PRINT(TO_CHAR(x[1]));
END
PROCEDURE vectorExampleD()
BEGIN
DECLARE vConstM CONSTANT VECTOR(INTEGER)
DEFAULT VECTOR[1, 2];
DECLARE vConstN CONSTANT VECTOR(INTEGER)
DEFAULT VECTOR[99, vConstM[2]]
DECLARE x INTEGER;
DECLARE y INTEGER;
SET x = vConstM[1];
SET y = vConstN[1];
CALL PRINT(TO_CHAR(x));
CALL PRINT(TO_CHAR(y));
END
PROCEDURE vectorExampleE()
BEGIN
DECLARE PUBLIC vConstM CONSTANT VECTOR(INTEGER)
DEFAULT VECTOR[1, 2];
DECLARE PUBLIC vConstN CONSTANT VECTOR(INTEGER)
DEFAULT VECTOR[99, vConstM[2]];
DECLARE x INTEGER;
SET x = vConstN[2];
CALL PRINT(TO_CHAR(x));
END
PROCEDURE vectorExampleF(OUT Name VECTOR(CHAR(255)))
BEGIN
DECLARE firstName VECTOR(CHAR);
DECLARE lastName VECTOR(CHAR);
SET firstName = VECTOR['john'];
SET lastName = VECTOR['doe'];
SET Name = CONCAT(firstName, lastName);
END
PROCEDURE vectorExampleG(OUT card INTEGER)
BEGIN
DECLARE vectorX VECTOR(INTEGER);
SET vectorX = VECTOR[5, 55, 19, 15, 23];
SET card = CARDINALITY (vectorX);
END
PROCEDURE vectorExampleH(OUT ext VECTOR(INTEGER))
BEGIN
DECLARE vectorX VECTOR(INTEGER);
DECLARE NEWVECTOR VECTOR(INTEGER);
SET vectorX = VECTOR[5, 55, 19, 15, 23];
SET vectorX = EXTEND(vectorX, 2);
SET ext = vectorX;
END
PROCEDURE vectorExampleJ(OUT ext VECTOR(INTEGER))
BEGIN
DECLARE vectorX VECTOR(INTEGER);
SET vectorX = VECTOR[5, 55, 19, 15, 23];
SET vectorX = VECTOR[NULL];
SET vectorX = EXTEND(vectorX, 2);
SET ext = vectorX;
END
PROCEDURE vectorExampleK(OUT trunc VECTOR(INTEGER))
BEGIN
DECLARE vectorX VECTOR(INTEGER);
DECLARE newvector VECTOR(INTEGER);
SET vectorX = VECTOR[5, 55, 19, 15, 23];
SET newvector = TRUNCATE(vectorX, 2);
SET trunc = newvector;
END
PROCEDURE vectorExampleM(OUT trunc VECTOR(INTEGER))
BEGIN
DECLARE vectorX VECTOR(INTEGER);
DECLARE newvector VECTOR(INTEGER);
SET vectorX = VECTOR[5, 25, 30];
SET newvector = TRUNCATE(vectorX, NULL);
SET trunc = newvector;
END