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 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 procedures | Deletes an element from a collection. |
| TRIM | Stored procedures | Trims an element from the end of a variable-size array (or varray) or nested table. |
| EXTEND | Stored procedures | Appends elements to the end of a varray or nested table. |
| EXISTS | Functions | Returns TRUE only if the specified element in a varray or nested table exists. |
| FIRST | Functions | Returns the first index in a collection. |
| LAST | Functions | Returns the last index in a collection. |
| COUNT | Functions | Returns the number of elements in a collection. |
| LIMIT | Functions | Returns the maximum number of elements supported in a collection. |
| PRIOR | Functions | Returns the index before a specified index. |
| NEXT | Functions | Returns the index after a specified index. |
Syntax for calling a collection method:
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.