pl-sql
  1. pl-sql-conditional-statements
PreviousNext

Conditional Statements in PL/SQL

Conditional statements in PL/SQL are used to execute different code based on different conditions. There are three types of conditional statements in PL/SQL: IF-THEN, IF-THEN-ELSE, and CASE statements.

Syntax

The syntax for the IF-THEN statement is:

IF condition THEN
   statements;
END IF;

The syntax for the IF-THEN-ELSE statement is:

IF condition THEN
   statements;
ELSE
   statements;
END IF;

The syntax for the CASE statement is:

CASE expression
   WHEN value1 THEN
      statements;
   WHEN value2 THEN
      statements;
   WHEN value3 THEN
      statements;
   ...
   ELSE
      statements;
END CASE;

Example

Here is an example of each of the three conditional statements in PL/SQL:

-- IF-THEN statement
DECLARE
   x NUMBER := 20;
BEGIN
   IF x < 10 THEN
      DBMS_OUTPUT.PUT_LINE('x is less than 10');
   END IF;
   DBMS_OUTPUT.PUT_LINE('End of IF statement');
END;

-- IF-THEN-ELSE statement
DECLARE
   x NUMBER := 20;
BEGIN
   IF x < 10 THEN
      DBMS_OUTPUT.PUT_LINE('x is less than 10');
   ELSE
      DBMS_OUTPUT.PUT_LINE('x is greater than or equal to 10');
   END IF;
   DBMS_OUTPUT.PUT_LINE('End of IF-THEN-ELSE statement');
END;

-- CASE statement
DECLARE
   grade CHAR(1) := 'B';
BEGIN
   CASE grade
      WHEN 'A' THEN
         DBMS_OUTPUT.PUT_LINE('Excellent');
      WHEN 'B' THEN
         DBMS_OUTPUT.PUT_LINE('Good');
      WHEN 'C' THEN
         DBMS_OUTPUT.PUT_LINE('Fair');
      WHEN 'D' THEN
         DBMS_OUTPUT.PUT_LINE('Needs Improvement');
      ELSE
         DBMS_OUTPUT.PUT_LINE('Invalid Grade');
   END CASE;
   DBMS_OUTPUT.PUT_LINE('End of CASE statement');
END;

Output

The output of the above code would be:

End of IF statement
x is greater than or equal to 10
End of IF-THEN-ELSE statement
Good
End of CASE statement

Explanation

In the above example, we have three different conditional statements. The first statement is an IF-THEN statement that checks whether the value of x is less than 10, and since it is not, it goes to the end of the statement and outputs "End of IF statement".

The second statement is an IF-THEN-ELSE statement that checks whether the value of x is less than 10, and since it is not, it executes the ELSE block and outputs "x is greater than or equal to 10" before going to the end of the statement and outputting "End of IF-THEN-ELSE statement".

The third statement is a CASE statement that checks the value of the variable grade and outputs a different message based on its value. In this case, since the value of grade is 'B', it outputs "Good" before going to the end of the statement and outputting "End of CASE statement".

Use

Conditional statements in PL/SQL are used to execute different pieces of code based on different conditions. They are common in many different types of programs and can be used to control program flow.

Important Points

  • Conditional statements in PL/SQL allow for the execution of different code based on different conditions.
  • There are three different types of conditional statements in PL/SQL: IF-THEN, IF-THEN-ELSE, and CASE statements.
  • The syntax for each type of conditional statement is different, but they all involve checking some condition before executing different code.

Summary

In summary, conditional statements in PL/SQL are used to execute different pieces of code based on different conditions. There are three different types of conditional statements in PL/SQL: IF-THEN, IF-THEN-ELSE, and CASE statements, each with its own syntax and use case. Conditional statements are a powerful tool for controlling program flow and are used in many different types of programs.

TriggerPatterns
Published on: