Conditional Processing

Contact Us or call 1-877-932-8228
Conditional Processing

Conditional Processing

The IF Statement

There are a number of variations of the IF statement available. The simplest involves code that only executes if a condition is true (IF). More complex variations allow you to indicate alternative conditions to test (ELSIF) and a default behavior to take if none of the conditions tested evaluates to true (ELSE). The IF statement provides similar functionality to the SQL DECODE function and the SQL CASE expression.

Code Sample:

Within-the-Block/Demos/if_demo.sql
BEGIN
  dbms_output.put_line('Starting if_demo.sql...');
    
  IF 1=1 THEN
    dbms_output.put_line('In first IF block');
  END IF;
  
  IF TRUE THEN
    dbms_output.put_line('In second IF block');
  END IF;

  IF 1=0 THEN
    dbms_output.put_line('In third IF block');
  END IF;

  IF NOT 1=0 THEN
    dbms_output.put_line('In fourth IF block');
  END IF;

  IF 'a'='a' THEN
    dbms_output.put_line('In fifth IF block');
  END IF;

  IF 'A'='a' THEN
    dbms_output.put_line('In sixth IF block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

The most common Boolean test is for equality. The = sign is used to test for equality, while the := combination of characters is used for variable assignment. There is a distinct PL/SQL Boolean type, so TRUE and FALSE represent true and false (not 0 and 1 as in some other languages). If an expression evaluates to false, the block is skipped. The NOT key word changes a false expression to true and vice versa. Besides numbers and Boolean values, character comparisons are also possible (as well as other types). Case sensitivity does matter in the context of a string or character data in a table when testing for equality.

Code Sample:

Within-the-Block/Demos/if_else_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_else_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSE
    dbms_output.put_line('In ELSE block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

In an IF/ELSE construct, any expression that does not evaluate to TRUE (resulting in the execution of the code in the IF block) will result in the execution of code in the ELSE block.

Code Sample:

Within-the-Block/Demos/if_elsif_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_elsif_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSIF v_number=2 THEN
    dbms_output.put_line('In ELSIF block');
  END IF;
 
  dbms_output.put_line('Ending...');
END;

Code Explanation

Any number of ELSIF blocks can be included. These must include an expression to be evaluated (as in a simple IF clause).

Code Sample:

Within-the-Block/Demos/if_elsif_else_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting if_elsif_else_demo.sql...');
  
  IF v_number=1 THEN
    dbms_output.put_line('In IF block');
  ELSIF v_number=2 THEN
    dbms_output.put_line('In ELSIF block');
  ELSE
    dbms_output.put_line('In ELSE block');
  END IF;

  dbms_output.put_line('Ending...');
END;

Code Explanation

IF, ELSIF and ELSE can be used together to guarantee that one of the available blocks will be executed.

Boolean expressions can also be joined together using AND and OR keywords. In some cases, the use of these keywords can reduce the need for ELSIF conditions. Although all conditional scenarios can be addressed using an IF statement in combination with ELSIF and ELSE, the ANSI/ISO CASE statement provides an efficient and readable alternative.

The CASE Statement

With the release of Oracle 9i, Oracle added support for the CASE statement and CASE expression in PL/SQL. Because both the CASE statement and CASE expression come in the same two flavors only the CASE statement is covered here. A simple CASE statement, also known as a selected CASE, begins with the keyword CASE followed by an arbitrarily complex selector expression that can evaluate to any scalar PL/SQL data type (and a few other types as well). A search CASE statement also begins with the keyword CASE but is not followed by a selector expression. Instead, each WHEN clause contain conditions that evaluate to Boolean values.

Code Sample:

Within-the-Block/Demos/case_demo.sql
DECLARE
  -- Change the value of v_number to see the execution path change
  v_number NUMBER := 1;
BEGIN
  dbms_output.put_line('Starting case_demo.sql...');
  -- DBMS_OUTPUT.PUT - no new line  
  -- Selected/simple case statement:
  CASE v_number
    WHEN 1 THEN dbms_output.put('ONE');
    WHEN 2 THEN dbms_output.put('TWO');
    ELSE dbms_output.put('ANOTHER NUMBER');
  END CASE;

  -- Searched case statement:
  CASE 
    WHEN v_number < 2 
      THEN dbms_output.put_line(' is less than 2.');
    WHEN v_number >= 2
      THEN dbms_output.put_line(' is greater than or equal to 2.');
  END CASE;

  dbms_output.put_line('Ending...');
END;

Code Explanation

A CASE statement can be used in a similar manner as the IF/ELSIF/ELSE statement. In the first example a single selector expression is evaluated (simple case), in the second example, different expressions are evaluated (search case).

The following table summarizes the differences between a simple CASE and search CASE statement. Image of PL/SQL Subprogram Characteristics

Next