Wednesday, June 10, 2009

Mimicing "break" in a PL/SQL FOR Loop

It seems a major omission in the Oracle PL/SQL Programming language that the BREAK keyword cannot be used in FOR loops (or WHILE loops) to skip to the next iteration.

Here is a (trivial) example of how you can implement "break" functionality using exceptions:
DECLARE
e_skip_row EXCEPTION;
l_total PLS_INTEGER := 0;
BEGIN
--
dbms_output.enable(10000);
--
FOR l_index IN 1..100 LOOP
BEGIN
--
-- If the row is even then skip
IF MOD(l_index,2) = 0 THEN
RAISE e_skip_row;
END IF;
--
l_total := l_total + l_index;
--
EXCEPTION
WHEN e_skip_row THEN NULL;
END;
END LOOP;
--
dbms_output.put_line('Total is: '||l_total);
--
END;

No comments: