What are Array functions Read today

Arrays also called collections. The array functions help you use the values present in arrays are collections. I have listed Array functions from both DB2 and Oracle.

DB2 Array Functions

ARRAY_AGG

The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array. ARRAY_AGG can be invoked in the following situations:

Select list of a SELECT INTO statement

Select list of a fullselect in the definition of a cursor that is not scrollable

Select list of a scalar fullselect as a source data item for a SET assignment-statement (or SQL PL assignment-statement)

A RETURN statement in an SQL scalar function

ARRAY_DELETE

The ARRAY_DELETE function deletes elements from an array. This function can be specified only in the following specific contexts:

As a source value for a SET assignment-statement (or SQL PL assignment-statement) or VALUES INTO statement.

As the value to be returned in a RETURN statement in an SQL scalar function.

ARRAY_FIRST

The ARRAY_FIRST function returns the minimum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If array expression is not null and the array is not empty, the value of the result is the minimum array index value, which is 1 for an ordinary array

ARRAY_LAST

The ARRAY_LAST function returns the maximum array index value of an array. The data type of the result is the data type of the array index, which is INTEGER for an ordinary array. If the array expression is not null and the array is not empty, the value of the result is the maximum array index value, which is the cardinality of the array for an ordinary array.

ARRAY_NEXT

The ARRAY_NEXT function returns the next larger array index value, relative to a specified array index value

ARRAY_PRIOR
The ARRAY_PRIOR function returns the next smaller array index value, relative to a specified array index value.

CARDINALITY
The CARDINALITY function returns the number of elements in an array. The data type of the result is BIGINT.

The result of the CARDINALITY function is as follows:

For an ordinary array, the result is the highest array index for which the array has an assigned element. Elements that have been assigned the null value are considered to be assigned elements.

For an associative array, the result is the actual number of unique array index values that are defined in array-expression.

For an empty array, the result is 0.

MAX_CORDINALITY

The MAX_CARDINALITY function returns the maximum number of elements that an array can contain. This value is the cardinality that was specified in the CREATE TYPE statement for an ordinary array type.

The result of the MAX_CARDINALITY function is as follows:

For an ordinary array, the result is the maximum number of elements that an array can contain.

For an associative array, the result is the null value

TRIM_ARRAY

The TRIM_ARRAY function deletes elements from the end of an ordinary array. It can be invoked only in the following contexts:

A source value for SET assignment-statement or SQL PL assignment-statement, or a VALUES INTOstatement

The value that is returned in a RETURN statement in an SQL scalar function

Array Functions You can use only in PL/SQL

Oracle Array Functions

COUNT	Returns number of elements in the array
EXISTS	Returns Boolean true if the element at the specified index exists; otherwise returns false
EXTEND	Increases size of array by 1 or by the number specified, ie. EXTEND(n) Cannot be used with associative arrays
FIRST	Navigates to the first element in the array
NEXT	Navigates to the next element in the array
LAST	Navigates to the last element in the array
PRIOR	Navigates to the previous element in the array
TRIM	Removes the last element of the array, or the last n elements if a number is specified, ie. TRIM(n) Cannot be used with associative arrays
DELETE	Removes all elements of an array, or the nth element, if a parameter is specified