The Geek Diary

IF statements in PL/SQL

by

You have learned to write PL/SQL blocks containing declarative and executable sections. You have also learned to include expressions and SQL statements in the executable block. In this post, you will learn how to use control structures such as IF statements in a PL/SQL block.

IF Statement

The structure of the PL/SQL IF statement is similar to the structure of IF statements in other procedural languages. It allows PL/SQL to perform actions selectively based on conditions.

Syntax:

IF condition THEN
  statements;
[ELSIF condition THEN
  statements;]
[ELSE
  statements;]
END IF;

In the syntax:

  • condition: Is a Boolean variable or expression that returns TRUE, FALSE, or NULL.
  • THEN: Introduces a clause that associates the Boolean expression with the sequence of statements that follows it.
  • statements: Can be one or more PL/SQL or SQL statements. (They may include additional IF statements containing several nested IF, ELSE, and ELSIF statements.) The statements in the THEN clause are executed only if the condition in the associated IF clause evaluates to TRUE.
  • ELSIF: Is a keyword that introduces a Boolean expression (If the first condition yields FALSE or NULL, the ELSIF keyword introduces additional conditions.)
  • ELSE: Introduces the default clause that is executed if and only if none of the earlier predicates (introduced by IF and ELSIF) are TRUE. The tests are executed in sequence so that a later predicate that might be true is preempted by an earlier predicate that is true.
  • END: IF Marks the end of an IF statement.
Note: ELSIF and ELSE are optional in an IF statement. You can have any number of ELSIF keywords but only one ELSE keyword in your IF statement. END IF marks the end of an IF statement and must be terminated by a semicolon.

Simple IF Statement

Simple IF Example:

DECLARE
  v_myage number:=31;
BEGIN
  IF v_myage < 11
  THEN
    DBMS_OUTPUT.PUT_LINE(' I am a child ');
  END IF;
END;
/

The example above shows a simple IF statement with the THEN clause.

  • The v_myage variable is initialized to 31.
  • The condition for the IF statement returns FALSE because v_myage is not less than 11.
  • Therefore, the control never reaches the THEN clause.

Adding Conditional Expressions

An IF statement can have multiple conditional expressions related with logical operators such as AND, OR, and NOT.

For example:

IF (myfirstname='Christopher' AND v_myage <11)
...

The condition uses the AND operator and, therefore, evaluates to TRUE only if both conditions are evaluated as TRUE. There is no limitation on the number of conditional expressions. However, these statements must be related with appropriate logical operators.

IF THEN ELSE Statement

An ELSE clause is added to the code in the previous section. The condition has not changed and, therefore, still evaluates to FALSE. Recall that the statements in the THEN clause are executed only if the condition returns TRUE. In this case, the condition returns FALSE and the control moves to the ELSE statement.

DECLARE
  v_myage number:=31;
BEGIN
  IF
    v_myage < 11 THEN
    DBMS_OUTPUT.PUT_LINE(' I am a child ');
  ELSE
    DBMS_OUTPUT.PUT_LINE(' I am not a child ');
  END IF;
END;
/

The output of the block is shown below the code.

anonymous block completed
I am not a child

IF ELSIF ELSE Clause

Consider the example shown below:

DECLARE
  v_myage number:=31;
BEGIN
  IF v_myage < 11 THEN
    DBMS_OUTPUT.PUT_LINE(' I am a child ');
  ELSIF v_myage < 20 THEN
    DBMS_OUTPUT.PUT_LINE(' I am young ');
  ELSIF v_myage < 30 THEN
    DBMS_OUTPUT.PUT_LINE(' I am in my twenties');
  ELSIF v_myage < 40 THEN
    DBMS_OUTPUT.PUT_LINE(' I am in my thirties');
  ELSE
    DBMS_OUTPUT.PUT_LINE(' I am always young ');
  END IF;
END;
/

Output:

anonymous block completed
I am in my thirties

The IF clause may contain multiple ELSIF clauses and an ELSE clause. The example illustrates the following characteristics of these clauses:

  • The ELSIF clauses can have conditions, unlike the ELSE clause.
  • The condition for ELSIF should be followed by the THEN clause, which is executed if the condition for ELSIF returns TRUE.
  • When you have multiple ELSIF clauses, if the first condition is FALSE or NULL, the control shifts to the next ELSIF clause.
  • Conditions are evaluated one by one from the top.
  • If all conditions are FALSE or NULL, the statements in the ELSE clause are executed.
  • The final ELSE clause is optional.

NULL Value in IF Statement

Consider the example shown below:

DECLARE
  v_myage number;
BEGIN
  IF v_myage < 11 THEN
    DBMS_OUTPUT.PUT_LINE(' I am a child ');
  ELSE
    DBMS_OUTPUT.PUT_LINE(' I am not a child ');
  END IF;
END;
/

In the example shown above, the variable v_myage is declared but not initialized. The condition in the IF statement returns NULL rather than TRUE or FALSE. In such a case, the control goes to the ELSE statement.

Guidelines:

  • You can perform actions selectively based on conditions that are being met.
  • When you write code, remember the spelling of the keywords:
    – ELSIF is one word.
    – END IF is two words.
  • If the controlling Boolean condition is TRUE, the associated sequence of statements is executed; if the controlling Boolean condition is FALSE or NULL, the associated sequence of statements is passed over. Any number of ELSIF clauses is permitted.
  • Indent the conditionally executed statements for clarity.

You May Also Like