Explain nested PL/SQL blocks and block labels

How it works

PL/SQL blocks can nest — an inner BEGIN...END is a full block with its own DECLARE section, and it can read variables from any enclosing block (an inner variable with the same name shadows the outer one). A label (<<label_name>>) placed before a block or loop lets a later EXIT, CONTINUE, GOTO, or a qualified reference (label.variable) target that specific block or loop by name, which matters once loops are nested.

<<outer_block>>
DECLARE
  v_total NUMBER := 0;
BEGIN
  <<outer_loop>>
  FOR v_i IN 1..3 LOOP
    <<inner_loop>>
    FOR v_j IN 1..3 LOOP
      IF v_j = 2 THEN
        CONTINUE outer_loop;
      END IF;
      outer_block.v_total := outer_block.v_total + 1;
    END LOOP inner_loop;
  END LOOP outer_loop;
  DBMS_OUTPUT.PUT_LINE('Total: ' || v_total);
END outer_block;
/

Related Questions