Db2: Loop over an array

We saw in another article how to build an array from an SQL query (from each row, or from a column). This article shows how to loop over an array items.

Looping over an array is very simple:

DECLARE v_index BIGINT;
DECLARE v_item INT;

SET v_size = CARDINALITY(v_array);
SET v_index = 0;
WHILE v_index <= v_size DO
    -- get item
    SET v_item = v_array[v_index]
    -- set item
    SET v_array[v_index] = v_array[v_index] + 1
    SET v_index = v_index + 1
END WHILE;

Notes:

• Arrays are zero-based.
• CARDINALITY() returns a BIGINT value, but if the array is not that big we can assign it to a smaller variable.
• You will write loops like this in stored procedures. If you are not familiar with stored procedures, see Db2: Stored procedures examples in SQL.