Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SPL statementsThese topics describe Stored Procedure Language (SPL) statements, which you use to write SPL routines. You can store these routines in the database as user-defined routines (UDRs).
FOREACH Use the FOREACH statement to declare a direct cursor that can select and manipulate more than one row from a the result set of a query, or more than one element from a collection.
Using Collection Variables
Modifying Elements in a Collection Variable

Modifying Elements in a Collection Variable

To update an element of a collection within an SPL routine, you must first declare a cursor with the FOREACH statement.

Then, within the FOREACH loop, select elements one at a time from the collection variable, using the collection variable as a collection-derived table in a SELECT query.

When the cursor is positioned on the element to be updated, you can use the WHERE CURRENT OF clause, as follows:

The UPDATE statement with the WHERE CURRENT OF clause updates the value in the current element of the collection variable.
The DELETE statement with the WHERE CURRENT OF clause deletes the current element from the collection variable.