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_LAS**T

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_CORDINALIT**Y

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 |

Also Read: How to call Array Functions in Oracle

Related Posts