A collection method is a PL subprogram, which can be a function that returns information about a collection or a procedure that performs operations on a collection.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Collection methods facilitate use and maintenance of collections.
Note
For an empty collection, EXISTS is the only collection method that will not cause the predefined COLLECTION_IS_NULL exception.
The following table describes the collection methods supported in OceanBase Database.
| Method | Type | Description |
|---|---|---|
| DELETE | Stored procedure | Deletes an element from a collection. |
| TRIM | Stored procedure | Trims an element from the end of a variable-size array (or varray) or nested table. |
| EXTEND | Stored procedure | Appends elements to the end of a varray or nested table. |
| EXISTS | Function | Returns TRUE only if the specified element in a varray or nested table exists. |
| FIRST | Function | Returns the first index in a collection. |
| LAST | Function | Returns the last index in a collection. |
| COUNT | Function | Returns the number of elements in a collection. |
| LIMIT | Function | Returns the maximum number of elements supported in a collection. |
| PRIOR | Function | Returns the index before a specified index. |
| NEXT | Function | Returns the index after a specified index. |
The syntax for calling a collection method is as follows:
collection_name.method
A collection method can be called in a PL subprogram of its type (function or procedure), and cannot be called in an SQL statement.
In a subprogram, collection parameters use the attributes of bound parameters. You can apply collection methods to these parameters. For parameters of a varray, the value of LIMIT is always determined by the parameter type definition, regardless of the parameter mode.