Branching and Conditional Control Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

The IF-THEN-ELSE and CASE statements allow code to decide on the correct course of action for the current circumstances.  In the following example, the IF-THEN-ELSE statement is used to decide whether today is a weekend day.

SET SERVEROUTPUT ON
DECLARE
  l_day  VARCHAR2(10);
BEGIN
  l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));

  IF l_day IN ('SATURDAY', 'SUNDAY') THEN
    DBMS_OUTPUT.put_line('It''s the weekend!');
  ELSE
    DBMS_OUTPUT.put_line('It''s not the weekend yet!');
  END IF;
END;
/

First, the expression between the IF and the THEN is evaluated.  If the expression equates to TRUE, the code between the THEN and the ELSE is performed.  If the expression equates to FALSE, the code between the ELSE and the END IF is performed.  The IF-THEN-ELSE statement can be extended to cope with multiple decisions by using the ELSIF keyword.  The example below uses this extended form to produce a different message for Saturday and Sunday.

Also see my notes on elsif.

SET SERVEROUTPUT ON
DECLARE
  l_day  VARCHAR2(10);
BEGIN
  l_day := TRIM(TO_CHAR(SYSDATE, 'DAY')); 

  IF l_day = 'SATURDAY' THEN
    DBMS_OUTPUT.put_line('The weekend has just started!');
  ELSIF l_day = 'SUNDAY' THEN
    DBMS_OUTPUT.put_line('The weekend is nearly over!');
  ELSE
    DBMS_OUTPUT.put_line('It''s not the weekend yet!');
  END IF;
END;
/

SQL CASE expressions were introduced in the later releases of Oracle 8i, but Oracle 9i included support for CASE statements in PL/SQL for the first time.  The CASE statement is the natural replacement for large IF-THEN-ELSIF-ELSE statements.  The following code gives an example of a matched CASE statement.

SET SERVEROUTPUT ON
DECLARE
  l_day  VARCHAR2(10);
BEGIN
  l_day := TRIM(TO_CHAR(SYSDATE, 'DAY')); 

  CASE l_day
    WHEN 'SATURDAY' THEN
      DBMS_OUTPUT.put_line('The weekend has just started!');
    WHEN 'SUNDAY' THEN
      DBMS_OUTPUT.put_line('The weekend is nearly over!');
    ELSE
      DBMS_OUTPUT.put_line('It''s not the weekend yet!');
  END CASE;
END;
/

The WHEN clauses of a matched CASE statement simply state the value to be compared.  If the value of the variable specified after the CASE keyword matches this comparison value, the code after the THEN keyword is performed.

A searched CASE statement has a slightly different format, with each WHEN clause containing a full expression, as shown below.

SET SERVEROUTPUT ON
DECLARE
  l_day  VARCHAR2(10);
BEGIN
  l_day := TRIM(TO_CHAR(SYSDATE, 'DAY'));  

  CASE
    WHEN l_day = 'SATURDAY' THEN
      DBMS_OUTPUT.put_line('The weekend has just started!');
    WHEN l_day = 'SUNDAY' THEN
      DBMS_OUTPUT.put_line('The weekend is nearly over!');
    ELSE
      DBMS_OUTPUT.put_line('It''s not the weekend yet!');
  END CASE;
END;
/

Looping Statements in PL/SQL

Loops allow sections of code to be processed multiple times.  In its most basic form, a loop consists of the LOOP and END LOOP statement, but this form is of little use as the loop will run forever.

BEGIN
  LOOP
    NULL;
  END LOOP;
END;
/

Typically it would be expected to define an end condition for the loop using the EXIT WHEN statement along with a Boolean expression.  When the expression equates to TRUE, the loop stops.  The example below uses this syntax to print out numbers from 1 to 5.

SET SERVEROUTPUT ON
DECLARE
  i  NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN i > 5;
    DBMS_OUTPUT.put_line(i);
    i := i + 1;
  END LOOP;
END;
/

The placement of the EXIT WHEN statement can affect the processing inside the loop.  For example, placing it at the start of the loop means the code within the loop may be executed “0 to many” times, like a while-do loop in other languages.  Placing the EXIT WHEN at the end of the loop means the code within the loop may be executed “1 to many” times, like a do-while loop in other languages.

The FOR-LOOP statement allows code within the loop to be repeated a specified number of times based on the lower and upper bounds specified in the statement.  The example below shows how the previous example could be recoded to use a FOR-LOOP statement.

SET SERVEROUTPUT ON
BEGIN
  FOR i IN 1 .. 5 LOOP
    DBMS_OUTPUT.put_line(i);
  END LOOP;
END;
/

The WHILE-LOOP statement allows code within the loop to be repeated until a specified expression equates to TRUE.  The following example shows how the previous examples can be recoded to use a WHILE-LOOP statement.

SET SERVEROUTPUT ON
DECLARE
  i  NUMBER := 1;
BEGIN
  WHILE i <= 5 LOOP
    DBMS_OUTPUT.put_line(i);
    i := i + 1;
  END LOOP;
END;
/

In addition to these loops, a special cursor FOR-LOOP is available as shown earlier.

GOTO in PL/SQL

The GOTO statement allows a program to branch unconditionally to a predefined label.  The following example uses the GOTO statement to repeat the functionality of the examples in the previous section.

SET SERVEROUTPUT ON
DECLARE
  i  NUMBER := 1;
BEGIN
  LOOP
    IF i > 5 THEN
      GOTO exit_from_loop;
    END IF;
    DBMS_OUTPUT.put_line(i);
    i := i + 1;
  END LOOP; 

  << exit_from_loop >>
  NULL;
END;
/

In this example, the GOTO has been made conditional by surrounding it with an IF statement.  When the GOTO is called, the program execution immediately jumps to the appropriate label defined using double-angled brackets.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts: