Table of Contents Previous Next


4 Stored Procedure Language : 4.11 Collection Methods

Collection methods are functions and procedures that provide useful information about a collection that can aid in the processing of data in the collection. The following sections discuss the collection methods supported by Advanced Server.
4.11.1 COUNT
COUNT is a method that returns the number of elements in a collection. The syntax for using COUNT is as follows:
collection is the name of a collection.
For a varray, COUNT always equals LAST.
4.11.2 DELETE
The DELETE method deletes entries from a collection. You can call the DELETE method in three different ways.
Use the first form of the DELETE method to remove all entries from a collection:
collection.DELETE
Use the second form of the DELETE method to remove the specified entry from a collection:
collection.DELETE(subscript)
Use the third form of the DELETE method to remove the entries that are within the range specified by first_subscript and last_subscript (including the entries for the first_subscript and the last_subscript) from a collection.
collection.DELETE(first_subscript, last_subscript)
If first_subscript and last_subscript refer to non-existent elements, elements that are in the range between the specified subscripts are deleted. If first_subscript is greater than last_subscript, or if you specify a value of NULL for one of the arguments, DELETE has no effect.
The following example demonstrates using the DELETE method to remove the element with subscript 0 from the collection:
COUNT indicates that before the DELETE method, there were 5 elements in the collection; after the DELETE method was invoked, the collection contains 4 elements.
4.11.3 EXISTS
The EXISTS method verifies that a subscript exists within a collection. EXISTS returns TRUE if the subscript exists; if the subscript does not exist, EXISTS returns FALSE. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.EXISTS(subscript)
collection is the name of the collection.
subscript is the value that you are testing for. If you specify a value of NULL, EXISTS returns false.
The following example verifies that subscript number 10 exists within the associative array:
4.11.4 EXTEND
The EXTEND method increases the size of a collection. There are three variations of the EXTEND method. The first variation appends a single NULL element to a collection; the syntax for the first variation is:
collection.EXTEND
collection is the name of a collection.
The following example demonstrates using the EXTEND method to append a single, null element to a collection:
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 6 elements.
The second variation of the EXTEND method appends a specified number of elements to the end of a collection.
collection.EXTEND(count)
collection is the name of a collection.
count is the number of null elements added to the end of the collection.
The following example demonstrates using the EXTEND method to append multiple null elements to a collection:
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
The third variation of the EXTEND method appends a specified number of copies of a particular element to the end of a collection.
collection.EXTEND(count, index_number)
collection is the name of a collection.
count is the number of elements added to the end of the collection.
index_number is the subscript of the element that is being copied to the collection.
The following example demonstrates using the EXTEND method to append multiple copies of the second element to the collection:
COUNT indicates that before the EXTEND method, there were 5 elements in the collection; after the EXTEND method was invoked, the collection contains 8 elements.
Note: The EXTEND method cannot be used on a null or empty collection.
4.11.5 FIRST
FIRST is a method that returns the subscript of the first element in a collection. The syntax for using FIRST is as follows:
collection is the name of a collection.
4.11.6 LAST
LAST is a method that returns the subscript of the last element in a collection. The syntax for using LAST is as follows:
collection is the name of a collection.
4.11.7 LIMIT
LIMIT is a method that returns the maximum number of elements permitted in a collection. LIMIT is applicable only to varrays. The syntax for using LIMIT is as follows:
collection is the name of a collection.
For an initialized varray, LIMIT returns the maximum size limit determined by the varray type definition. If the varray is uninitialized (that is, it is a null varray), an exception is thrown.
For an associative array or an initialized nested table, LIMIT returns NULL. If the nested table is uninitialized (that is, it is a null nested table), an exception is thrown.
4.11.8 NEXT
NEXT is a method that returns the subscript that follows a specified subscript. The method takes a single argument; the subscript that you are testing for.
collection.NEXT(subscript)
collection is the name of the collection.
The following example demonstrates using NEXT to return the subscript that follows subscript 10 in the associative array, sparse_arr:
4.11.9 PRIOR
The PRIOR method returns the subscript that precedes a specified subscript in a collection. The method takes a single argument; the subscript that you are testing for. The syntax is:
collection.PRIOR(subscript)
collection is the name of the collection.
If the subscript specified does not have a predecessor, PRIOR returns NULL. If the specified subscript is greater than the last subscript in the collection, the method returns the last subscript. If you specify a NULL subscript, PRIOR does not return a value.
4.11.10 TRIM
The TRIM method removes an element or elements from the end of a collection. The syntax for the TRIM method is:
collection.TRIM[(count)]
collection is the name of a collection.
count is the number of elements removed from the end of the collection. Advanced Server will return an error if count is less than 0 or greater than the number of elements in the collection.
The following example demonstrates using the TRIM method to remove an element from the end of a collection:
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 4 elements.
COUNT indicates that before the TRIM method, there were 5 elements in the collection; after the TRIM method was invoked, the collection contains 3 elements.

4 Stored Procedure Language : 4.11 Collection Methods

Table of Contents Previous Next