Overview on Sequential Control and Conditional Control Statements in PL/SQL

PL/SQL supports both sequential control statements and conditional control statements for use in programming logic. The sequential control statements are the GOTO and NULL statements. The GOTO statement is seldom used. It is used intermittently to simplify logic. The NULL statement is used to develop readability by clarifying the usage of conditional statements.

The Conditional control are the IF and CASE statements. When it is necessary to take alternative action depending on the situation the IF and CASE statements lets a sequence of statements to be executed based on the value of a condition. A condition is any variable or expression that results in either a TRUE or FALSE Boolean value and returns the same.

The GOTO statement transfers the control unconditionally to the given labeled statement or block. An executable statement or PL/SQL block has to follow the label.
The general syntax is

GOTO < label_name>;

There are some restrictions to a GOTO statement. It cannot be used to transfer control to a label within an IF statement, CASE statement or LOOP statement or sub–block. It also cannot be used to transfer control out of a sub program or from an exception handler into the current block.

The NULL statement is used to denote no action is required and passes control to the next statement. It is especially used in a conditional construct to denote that the possibility has been taken care of, but requires no action or to denote that no action is required for any unnamed exception captured by WHEN OTHERS in an exception handler. A NULL statement can be used to create stubs when designing applications using the top down approach. A stub is a dummy subprogram that is defined at a later stage after the main program has been debugged and tested. The syntax of the NULL statement is

NULL;

The IF statement executes a sequence of statements if the condition specified is satisfied. The simplest form of IF statement is as given below.

IF condition THEN
sequence_of_statements
END IF;

The sequence of statements is executed when the condition is true. If the condition is not true the control passes to the statement after END IF. The second form of IF statement is the IF-THEN-ELSE statement.
The general syntax is as given below.

IF condition THEN
sequence_of_statements1
ELSE
sequence_of_statements2
END IF;

In the above the, sequence of statements following THEN is executed when the condition is true, otherwise the sequence of statements following ELSE gets executed.
The third form of IF statement is IF-THEN-ELSIF statement. This allows the usage of additional conditions.

The syntax is

IF condition1 THEN
sequence_of_statements1
ELSIF condition2 THEN
sequence_of_statements2
ELSE
sequence_of_statements3
END IF;

If condition1 holds true, then sequence of statements following THEN, is executed.
If condition1 is not true, then the condition in the ELSIF clause is checked. If this holds true the second sequence of statements following THEN this is executed. If both conditions are false the sequence of statements following ELSE are executed.

The CASE statement uses a selector to determine the sequence of statements to execute. It is efficient and easy to interpret. It begins with the keyword CASE followed by a SELECTOR. This can be a single variable or a function call. When the value of the selector variable has the same value as of a WHEN clause expression, the corresponding statements following the WHEN clause is executed. Control is transferred to next statement.
If there is no match found for the Selector value, the statements following ELSE clause is executed. The general syntax is

[<<label_name>>]
CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
...
WHEN expressionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];

If ELSE clause is not specified, PL/SQL by default adds the implicit ELSE clause.

ELSE RAISE CASE_NOT_FOUND;

To avoid CASE_NOT_FOUND error, the conditions specified must cover all cases so that it is impossible for none of it to be satisfied.

A CASE expression selects a result from the alternatives given and returns it. Instead of the sequence of statements specified in the CASE statement, result must be specified in CASE expression. This has no likelihood of any error to be raised in the event of no WHEN conditions being met. Instead a CASE expression returns NULL.

CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;

An advanced form of CASE statement is searched CASE statement, which has the syntax:
[<<label_name>>]
CASE
WHEN search_condition1 THEN sequence_of_statements1;
WHEN search_condition2 THEN sequence_of_statements2;
...
WHEN search_conditionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];

This has no selector. Also the search condition in the WHEN clause must only yield a Boolean vale. The search conditions are executed in sequence and whichever yields true the corresponding statements are executed and control passes to next statement and not subsequent WHEN clause. The ELSE clause function is similar to that of the general CASE statement specified earlier.

PL/SQL conditional statements should be used in a way that logic can be simplified and the flow not obscured while making decisions. If possible nested IF statements should be avoided and ELSIF statements used to make the code more understandable. Also when comparing a single expression to multiple values, a single CASE statement can be used instead of an IF with several ELSIF clauses.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |