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:

What’s inside:
Decision-making statements
PL/SQL IF THEN statement
IF THEN ELSE statement
IF THEN ELSIF statement
CASE Statement
FAQS

Decision-making statements

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
conditional control structure in pl/sql
conditional control structure in pl/sql

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

PL/SQL IF THEN statement

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;

PL/SQL IF THEN ELSE statement

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;
/

PL/SQL IF THEN ELSIF

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;
/

Case Statement

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

conditional control structure in pl/sql
conditional control structure in pl/sql
  • 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; 
/

FAQs:

1.What are the two types of exceptions.

Answer: Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.

2. What is the importance of SQLCODE and SQLERRM?

Answer: SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.

3. Explain TTITLE and BTITLE

Answer: TTITLE and BTITLE commands that control report headers and footers.

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 🙂

14 thoughts on “Conditional Control Structure in PL/SQL

Leave a Reply

Your email address will not be published. Required fields are marked *