Arrays

Use of Array Types

Before the use of arrays, an array type needs to be defined:

TYPE array_type IS VARRAY(size) OF data_type [NOT NULL];

Its parameters are as follows:

• array_type: indicates the name of the array type to be defined.
• VARRAY: indicates the array type to be defined.
• size: indicates the maximum number of members in the array type to be defined. The value is a positive integer.
• data_type: indicates the types of members in the array type to be created.
• NOT NULL: an optional constraint. It can be used to ensure that none of the elements in the array is NULL.

• In GaussDB(DWS), an array automatically increases. If an access violation occurs, a null value will be returned, and no error message will be reported. If out-of-bounds write occurs in an array, the message Subscript outside of limit is displayed.
• The scope of an array type defined in a stored procedure takes effect only in this storage process.
• It is recommended that you use one of the preceding methods to define an array type. If both methods are used to define the same array type, GaussDB(DWS) prefers the array type defined in a stored procedure to declare array variables.

In GaussDB(DWS) 8.1.0 and earlier versions, the system does not verify the length of array elements and out-of-bounds write because the array can automatically increase. This version adds related constraints to be compatible with Oracle databases. If out-of-bounds write exists, you can configure varray_verification in the parameter behavior_compat_options to be compatible with previously unverified operations.

Example:

Declaration and Use of Rowtype Arrays

In addition to the declaration and use of common arrays and non-null arrays in the preceding example, the array also supports the declaration and use of rowtype arrays.

Example:

Array Related Functions

GaussDB(DWS) supports Oracle-related array functions. You can use the following functions to obtain array attributes or perform operations on the array content.

COUNT

Returns the number of elements in the current array. Only the initialized elements or the elements extended by the EXTEND function are counted.

Use:

varray.COUNT or varray.COUNT()

Example:

FIRST and LAST

The FIRST function can return the subscript of the first element. The LAST function can return the subscript of the last element.

Use:

varray.FIRST or varray.FIRST()

varray.LAST or varray.LAST()

Example:

EXTEND

The EXTEND function is used to be compatible with two Oracle database operations. In GaussDB(DWS), an array automatically grows, and the EXTEND function is not necessary. For a newly written stored procedure, you do not need to use the EXTEND function.

The EXTEND function can extend arrays. The EXTEND function can be invoked in either of the following ways:

• Method 1:

  EXTEND contains an integer input parameter, indicating that the array size is extended by the specified length. After executing the EXTEND function, the values of the COUNT and LAST functions change accordingly.

  Use:

  varray.EXTEND(size)

  By default, one bit is added to the end of varray.EXTEND, which is equivalent to varray.EXTEND(1).

• Method 2:

  EXTEND contains two integer input parameters. The first parameter indicates the length of the extended size. The second parameter indicates that the value of the extended array element is the same as that of the element with the index subscript.

  Use:

  varray.EXTEND(size, index)

Example:

NEXT and PRIOR

The NEXT and PRIOR functions are used for cyclic array traversal. The NEXT function returns the subscript of the next array element based on the input parameter index. If the subscript reaches the maximum value, NULL is returned. The PRIOR function returns the subscript of the previous array element based on the input parameter index. If the minimum value of the array subscript is reached, NULL is returned.

Use:

varray.NEXT(index)

varray.PRIOR(index)

Example:

The execution output is as follows:

EXISTS

Determines whether an array subscript exists.

Use:

varray.EXISTS(index)

Example:

The execution output is as follows:

TRIM

Deletes a specified number of elements from the end of an array.

Use:

varray.TRIM(size)

varray.TRIM is equivalent to varray.TRIM(1), because the default input parameter is 1.

Example:

The execution output is as follows:

DELETE

Deletes all elements from an array.

Use:

varray.DELETE or varray.DELETE()

Example:

The execution output is as follows:

LIMIT

Returns the allowed maximum length of an array.

Use:

varray.LIMIT or varray.LIMIT()

Example:

The execution output is as follows: