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-compatible 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.