Collection methods are PL subprograms that can be functions returning information about collections or procedures that operate on collections.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Collection methods make collections easier to use and maintain.
Note
For an empty collection, EXISTS is the only collection method that does not raise the predefined exception COLLECTION_IS_NULL.
The following table lists the collection methods supported by OceanBase Database.
| Method | Type | Description |
|---|---|---|
| DELETE | Procedure | Removes elements from a collection. |
| TRIM | Procedure | Removes elements from the end of a VARRAY or nested table. |
| EXTEND | Procedure | Adds elements to the end of a VARRAY or nested table. |
| EXISTS | Function | Returns TRUE if and only if the specified element exists in a VARRAY or nested table. |
| FIRST | Function | Returns the first index of a collection. |
| LAST | Function | Returns the last index of a collection. |
| COUNT | Function | Returns the number of elements in a collection. |
| LIMIT | Function | Returns the maximum number of elements that a collection can contain. |
| PRIOR | Function | Returns the index before the specified index. |
| NEXT | Function | Returns the index after the specified index. |
The basic syntax for calling a collection method is as follows:
collection_name.method
You can call a collection method in a PL subprogram of its type (function or procedure), but you cannot call it in an SQL statement.
In a subprogram, the attributes of a collection parameter are inherited from the bound parameter. You can apply a collection method to such a parameter. For a VARRAY parameter, the value of LIMIT is always determined by the parameter type definition, regardless of the parameter mode.