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;
- Arrays are zero-based.
BIGINTvalue, 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.