DB2 SQL PL control flow

IF, ELSEIF, and ELSE

The IF statement runs different SQL procedure statements depending on search conditions. IBM’s shape is:

1
2
3
4
5
6
IF rating = 1
THEN SET new_salary = new_salary + (new_salary * 0.10);
ELSEIF rating = 2
THEN SET new_salary = new_salary + (new_salary * 0.05);
ELSE SET new_salary = new_salary + (new_salary * 0.02);
END IF;

• IF search-condition — evaluated first. If it is true, the THEN statement(s) run and the rest of the IF is skipped.
• ELSEIF search-condition — optional, repeatable. Tried only when every previous IF/ELSEIF was not true. Order matters: put the most specific tests first.
• ELSE — optional catch-all when nothing was true (including when earlier conditions were unknown).
• END IF — required terminator. Forgetting it is a common CREATE PROCEDURE syntax error.

A search condition that is unknown (null compared with a value, for example) is not true. Processing continues to the next ELSEIF or ELSE — the same three-valued logic you already use in WHERE. If rating is NULL, none of the equality tests fire and ELSE runs. Guard with rating IS NULL if null means something else.

THEN/ELSEIF/ELSE each take an SQL procedure statement. That can be a single SET or UPDATE, or a nested IF, WHILE, or BEGIN … END if you need a block. A label may sit in front of IF for GOTO/LEAVE in unusual designs; everyday code rarely labels IF.

COMPARE: ELSEIF is not a second independent IF. After a true branch runs, later ELSEIF clauses are not evaluated. Two separate IF statements would both run if both conditions were true.

CASE statement

The CASE statement is a multi-way branch. It is not the CASE expression you write in a select list. Two forms:

Simple CASE

Compare one expression to a list of values:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CASE rating
  WHEN 1 THEN
    UPDATE DSN8C10.EMP
       SET SALARY = SALARY * 1.10,
           BONUS  = 1000
     WHERE EMPNO = EMPNUMBR;
  WHEN 2 THEN
    UPDATE DSN8C10.EMP
       SET SALARY = SALARY * 1.05,
           BONUS  = 500
     WHERE EMPNO = EMPNUMBR;
  ELSE
    UPDATE DSN8C10.EMP
       SET SALARY = SALARY * 1.02
     WHERE EMPNO = EMPNUMBR;
END CASE;

Searched CASE

Each WHEN has its own search condition (like ELSEIF):

1
2
3
4
5
6
7
8
CASE
  WHEN V_BONUS > 0 AND V_SAL > 100000 THEN
    SET P_CLASS = 'HIGH';
  WHEN V_BONUS > 0 THEN
    SET P_CLASS = 'BONUS';
  ELSE
    SET P_CLASS = 'BASE';
END CASE;

First true WHEN wins. If none is true and there is no ELSE, Db2 raises a CASE-not-found condition (an exception you should either handle or avoid by coding ELSE). Always include ELSE for status flags so a new rating code does not blow up the CALL.

End the statement with END CASE. A CASE expression ends with END only: SET V = CASE WHEN … END. Mixing the two is a syntax error that looks “almost right” in a code review.

Loop statements

WHILE

WHILE tests at the top. Use it when zero trips are legal — for example a cursor already at end-of-data, or an array whose CARDINALITY is 0.

1
2
3
4
5
6
7
8
9
SET DATEINDEX = 1;
SET DATESCOUNT = CARDINALITY(MYDATES);
WHILE DATEINDEX <= DATESCOUNT DO
  IF DAYOFWEEK(MYDATES[DATEINDEX]) IN (1, 7) THEN
    SET WEEKENDS[WEEKENDINDEX] = MYDATES[DATEINDEX];
    SET WEEKENDINDEX = WEEKENDINDEX + 1;
  END IF;
  SET DATEINDEX = DATEINDEX + 1;
END WHILE;

The search condition is re-evaluated before every iteration. If it is unknown, it is not true, so the loop ends. Increment your index inside the body or you will loop forever when the condition stays true.

Classic cursor pattern: FETCH once, WHILE flag = 0 DO process, FETCH again, END WHILE, with a CONTINUE handler for NOT FOUND that sets the flag. The extra FETCH before the loop is what makes WHILE safe when the result is empty.

REPEAT

REPEAT always runs the body once, then leaves when UNTIL is true. That matches “try something, then see if we are done” — including FETCH-until-not-found if you test SQLSTATE after FETCH inside the body.

1
2
3
4
5
6
SET V_TRIES = 0;
REPEAT
  SET V_TRIES = V_TRIES + 1;
  -- attempt work
UNTIL V_TRIES >= 3 OR V_OK = 1
END REPEAT;

UNTIL true means stop. That is the opposite mnemonic of WHILE true means continue. REPEAT UNTIL V_DONE = 1 is easy to invert by accident if you grew up on WHILE.

LOOP

LOOP has no condition. Something in the body must exit: LEAVE, RETURN, or a handler that EXITs the compound. LOOP is the natural shape when the exit test sits in the middle (FETCH, then if not found LEAVE, else process the row).

1
2
3
4
5
6
7
8
9
10
OPEN C1;
FETCH_LOOP:
LOOP
  FETCH C1 INTO V_EMPNO, V_SAL;
  IF V_AT_END = 1 THEN
    LEAVE FETCH_LOOP;
  END IF;
  SET V_TOTAL = V_TOTAL + V_SAL;
END LOOP FETCH_LOOP;
CLOSE C1;

The label FETCH_LOOP appears before LOOP and optionally again after END LOOP. If you code the ending label, it must match. LEAVE FETCH_LOOP continues at CLOSE C1, not at END of the whole procedure — unless the loop is the last statement.

FOR

FOR walks every row of a select-statement. Db2 declares, opens, fetches, and closes an implicit cursor. You write the SELECT once and the body once per row.

1
2
3
4
5
6
7
8
FOR EMPROW AS
    SELECT EMPNO, LASTNAME, SALARY
      FROM DSN8C10.EMP
     WHERE WORKDEPT = P_DEPT
DO
  SET V_FULL = EMPROW.LASTNAME;
  SET V_TOTAL = V_TOTAL + EMPROW.SALARY;
END FOR;

• for-loop-name (EMPROW) qualifies the column names inside the body (EMPROW.SALARY). All select-list items must have unique names — use AS if needed.
• Optional cursor-name CURSOR FOR lets you name the cursor; if you omit it, Db2 generates a name. Either way you must not OPEN, FETCH, or CLOSE it yourself, and you must not reference it outside the FOR.
• Zero rows: the body never runs. No NOT FOUND to handle for the FOR itself.
• Positioned UPDATE/DELETE of “the current FOR row” is not the job of FOR. Use an explicit cursor with FOR UPDATE OF when you need WHERE CURRENT OF.

ITERATE and LEAVE

Both need a label that names a loop or compound statement currently executing.

• LEAVE label — stop that labelled construct. Continue with the statement after its END LOOP / END WHILE / END REPEAT / END FOR / END. Leaving the outermost BEGIN ends the procedure body.
• ITERATE label — skip the rest of this iteration of the labelled WHILE, REPEAT, LOOP, or FOR and begin the next iteration (re-test WHILE, next FOR row, and so on). ITERATE on a compound that is not a loop is the wrong tool; use LEAVE or restructure.

1
2
3
4
5
6
7
8
9
10
11
DEPT_LOOP:
WHILE V_MORE = 1 DO
  FETCH C1 INTO V_DEPT, V_SAL;
  IF V_AT_END = 1 THEN
    LEAVE DEPT_LOOP;
  END IF;
  IF V_SAL IS NULL THEN
    ITERATE DEPT_LOOP;  -- skip remaining body for this row
  END IF;
  SET V_TOTAL = V_TOTAL + V_SAL;
END WHILE DEPT_LOOP;

Nested loops: LEAVE inner_label leaves only the inner loop. LEAVE outer_label leaves both. Without labels, you cannot say which loop you mean — so nested FETCH loops always get names.

GOTO label exists in SQL PL. It jumps to a labelled statement in the same scope with restrictions IBM documents (you cannot jump into a handler or into the middle of a FOR in arbitrary ways). Prefer structured LEAVE/ITERATE/IF so the next person can still see the exits.

Choosing a construct

• Two or three outcomes on one flag — IF / ELSEIF / ELSE
• Many discrete codes, each with different SQL — CASE statement
• Walk a query read-only — FOR
• Walk a cursor with FETCH and a not-found flag — WHILE after a priming FETCH, or LOOP with LEAVE in the middle
• Must run once then maybe again — REPEAT
• Skip junk rows — ITERATE
• Bail out of nested work — LEAVE on the outer label (or an EXIT handler, next page)

Search conditions inside WHILE/REPEAT/IF still see SQLSTATE from the previous statement unless you SET flags in handlers. A CONTINUE handler for an error that occurred while evaluating the search condition of IF/CASE/WHILE/REPEAT resumes after END IF / END CASE / END WHILE / END REPEAT, not inside the THEN branch. That surprise is why many shops set V_AT_END in a handler and test only the flag in WHILE.

Explain It Like I'm Five

IF is a fork in the path: if the sign says “rating 1,” take the left trail; else if “rating 2,” take the middle; else take the default trail. CASE is a row of labelled doors — open the first door whose label matches. WHILE is “keep walking while the light is green” (you might never start). REPEAT is “take one step, then see if we are done.” LOOP is a roundabout with no exit sign — you must choose an exit (LEAVE). FOR is a teacher handing you each homework paper in a stack; when the stack is empty, class is over. ITERATE means “skip the rest of this paper, give me the next one.” LEAVE means “I am done with this whole stack.”

Exercises

1. Rewrite the rating salary IF example as a searched CASE statement. Include ELSE.
2. Explain what happens if rating is NULL in that IF without an IS NULL test.
3. Write a WHILE that sums SALARY for a department using a cursor, a NOT FOUND flag, and a priming FETCH. Then sketch the same work as a LOOP with LEAVE.
4. When would you pick REPEAT instead of WHILE for a retry counter?
5. Write a FOR loop that inserts LASTNAME into a declared global temporary table. List two reasons you would not use FOR if you needed WHERE CURRENT OF.