Conditional Control Structure in PL/SQL

In this article, we will discuss if else in pl/SQL and case in Oracle PL/SQL. You will learn how to use the PL/SQL IF statement to either execute or pass over a sequence of words depending upon a specified condition. Also, use of CASE Statements in conditional control structure in pl/SQL.

Please also read the previous articles:

• Operators in PL/SQL – Oracle PL/SQL Tutorials
• PL/SQL Constants and Literals in Oracle PL/SQL

Decision-making statements are those in charge of executing an idea out of multiple given reports based on some condition. The condition will return either true or false. Based on what the condition returns and the associated statement is executed.

The decision making statements in PL/SQL are of two types:

1. If Else statements
2. Case statement

Let’s see them all one by one with examples:

If Else in PL/SQL

The IF statement allows you to either execute or pass over a sequence of statements, depending on a condition. If the condition is true, then execute the statement, if false, then goto else statement.

The IF a statement has three forms:

1. IF THEN
2. IF THEN ELSE
3. IF THEN ELSIF

The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. If the condition estimate (evaluate) to TRUE, the statements after the THEN execute. and if the condition is false or NULL, then the IF statement does nothing

IF condition THEN
    statements;
END IF;

The second form of IF statement adds the keyword ELSE, followed by an alternative sequence of statements. If the condition is not satisfied with the IF clause, then the value will go in else block.

Syntax:

IF condition THEN
    statements;
ELSE
    else_statements;
END IF;

Example of if-then-else statement

DECLARE
  sales  NUMBER(8,2) := 13100;
  quota  NUMBER(8,2) := 10000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 1200;
BEGIN
  IF sales > (quota + 200) THEN
     bonus := (sales - quota)/4;
  ELSE
     bonus := 50;
  END IF;
  UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

If the first condition is FALSE or NULL, the ELSIF clause tests another state. An IF statement can have numbers of ELSIF clauses; the final ELSE clause is optional. The Conditions evaluates one by one from top to bottom. If any condition is proper, then its associated sequence of statements is executed, and control passes to the next statement. If all conditions are false or NULL, the ELSE clause’s sequence is executed and exited to end if. We can say that It allows you to choose between several alternatives.

Syntax:

IF condition_1 THEN
  statements_1
ELSIF condition_2 THEN
  statements_2
[ ELSIF condition_3 THEN
    statements_3
]
...
[ ELSE
    else_statements
]
END IF;

Example Using the IF-THEN-ELSEIF Statemen

DECLARE
  sales  NUMBER(8,2) := 23000;
  bonus  NUMBER(6,2);
  emp_id NUMBER(6) := 130;
BEGIN
   IF sales > 50000 THEN
      bonus := 1500;
   ELSIF sales > 35000 THEN
      bonus := 500;
   ELSE
      bonus := 100;
   END IF;
   UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;
END;
/

Like the IF statement, the CASE statement selects one sequence of statements to execute. It works almost the same as if-then-elsif statements.

• CASE statement uses a “selector” rather than a Boolean expression to choose the sequence. A selector is an expression in which the value of which uses to select one of several alternatives.
• The expression could be of any type (arithmetic, variables, etc.)
• Each alternative is assigned with a specific pre-defined value (selector), and the option with selector value that matches the conditional expression value will be executed.
• Unlike IF-THEN-ELSIF, the CASE statement can also be used in SQL statements.
• ELSE block in the CASE statement holds the sequence that needs to be executed when none of the alternatives got selected.

Syntax

The syntax for the case statement in PL/SQL is −

CASE selector 
   WHEN 'value1' THEN a1; 
   WHEN 'value2' THEN S2; 
   WHEN 'value3' THEN S3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

Explanation of syntax:

• The conditions in the WHEN clauses are evaluated in order, from top to bottom.
• The sequence of statements associated with the WHEN clause whose condition evaluates to TRUE is executed. If more than one condition evaluates to TRUE, only the first one executes.
• If no condition evaluates to TRUE, the else_statements in the ELSE clause executes. And If you skip the ELSE clause and no expressions are TRUE, a CASE_NOT_FOUND an exception is raised.

Example of CASE statements :

DECLARE 
   grade char(1) := 'A'; 
BEGIN 
   CASE grade 
      when 'A' then dbms_output.put_line('Excellent'); 
      when 'B' then dbms_output.put_line('Very good'); 
      when 'C' then dbms_output.put_line('Well done'); 
      when 'D' then dbms_output.put_line('You passed'); 
      when 'F' then dbms_output.put_line('Better try again'); 
      else dbms_output.put_line('No such grade'); 
   END CASE; 
END; 
/

This is all about the conditional control structure in pl/sql. In the coming article, we will discuss the loops in pl/sql. So, please stay with us.

THANK YOU 🙂