A collection method is a PL subprogram, which can be a function that returns information about a collection or a stored procedure that performs operations on a collection.
Collection methods facilitate use and maintenance of collections.
Note
For an empty collection,
EXISTSis the only collection method that will not cause the predefinedCOLLECTION_IS_NULLexception.
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. |
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.