PL/SQL by Example / Anonymous Block and Branching

Anonymous Block

A PL/SQL program consists of anonymous and named blocks. Anonymous blocks are mainly used to separate logical parts of the program and to control exception handling.

DECLARE
  -- optional area for variable declaration
  -- single-line comment
  /* multi-line comment */
BEGIN
  -- mandatory execution section
EXCEPTION
  -- optional area for exception handling
END;

The following example demonstrates the simple declaration of a numeric variable, value assignment, a division operation, and catching a general exception with output to the console. Particularly noteworthy is the declaration, where the data type comes after the variable name, and the value assignment using :=.

DECLARE
  a NUMBER;
BEGIN
  a:=10;
  a:=a/0; -- the notation a/=0 cannot be used;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Division by zero!');
END;

Individual blocks can be nested. Variables with the same name are only valid within their own block.

DECLARE
  a NUMBER:=1; -- assignment of the default value directly in the declaration part
BEGIN
  DECLARE
    a NUMBER:=2;
    b NUMBER:=3;
  BEGIN
    dbms_output.put_line(a); -- 2
    dbms_output.put_line(b); -- 3
  END;
  dbms_output.put_line(a); -- 1
  -- variable b does not exist in this block
END;

Branching

Program flow control in PL/SQL is implemented using the standard IF THEN ELSE construct. The = operator is used for comparison.

DECLARE
  a BOOLEAN:=FALSE; -- boolean data type
BEGIN
  IF (a=TRUE) THEN  -- can be written as IF (a)
    dbms_output.put_line('true');
  ELSIF (a=FALSE) THEN -- can be written as ELSIF (NOT a)
    dbms_output.put_line('false');
  ELSE
    dbms_output.put_line('null'); -- note, BOOLEAN can be NULL
  END IF;
END;

It is also possible, of course, to use the CASE statement.

DECLARE
  a BOOLEAN;
BEGIN
  CASE a
    WHEN TRUE THEN
      dbms_output.put_line('true');
    WHEN FALSE THEN
      dbms_output.put_line('false');
    ELSE -- neither condition is met
      dbms_output.put_line('null');
  END CASE;
END;