PL/SQL Conditional Statements

PL/SQL Conditional Statements provide control over the flow of execution within Oracle database programs based on specified conditions. They include IF-THEN-ELSE statements for binary decisions, CASE statements for multiple conditions. These constructs enhance flexibility and logic in data processing, enabling dynamic and responsive behavior in Oracle PL/SQL applications.

IF-THEN-ELSE Statement

The IF-THEN-ELSE statement allows you to execute different blocks of code based on whether a condition evaluates to true or false.

IF condition THEN

   -- Code block executed if condition is true

ELSE

   -- Code block executed if condition is false

END IF;

 

Example:

DECLARE

   x NUMBER := 10;

BEGIN

   IF x > 0 THEN

      DBMS_OUTPUT.PUT_LINE('x is positive');

   ELSE

      DBMS_OUTPUT.PUT_LINE('x is non-positive');

   END IF;

END;

This PL/SQL code snippet demonstrates a basic structure used within Oracle database environments:

1.     DECLARE: This keyword begins the declaration section where variables and constants are defined. In this case, x is declared as a variable of type NUMBER and initialized with the value 10.

2.     BEGIN: Marks the beginning of the executable section of the PL/SQL block where actual logic and statements are written.

3.     IF-THEN-ELSE: This conditional statement checks whether the variable x is greater than 0 (x > 0). If this condition evaluates to true, it executes the statement inside the THEN block, which in this case outputs 'x is positive' using the DBMS_OUTPUT.PUT_LINE procedure. If the condition is false, it executes the statement inside the ELSE block, which outputs 'x is non-positive'.

4.     END IF: Marks the end of the conditional block, closing the IF-THEN-ELSE structure.

5.     END: Marks the end of the PL/SQL block.

 

ELSEIF Clause

You can use ELSIF clauses to test additional conditions after the initial IF condition.

IF condition THEN

   -- Code block executed if condition is true

ELSE

   -- Code block executed if condition is false

END IF;

Example:

DECLARE

   x NUMBER := 10;

BEGIN

   IF x > 0 THEN

      DBMS_OUTPUT.PUT_LINE('x is positive');

   ELSIF x < 0 THEN

      DBMS_OUTPUT.PUT_LINE('x is negative');

   ELSE

      DBMS_OUTPUT.PUT_LINE('x is zero');

   END IF;

END;

This PL/SQL code snippet demonstrates the use of the IF-THEN-ELSIF-ELSE statement structure, which allows for handling multiple conditions sequentially:

1.     DECLARE: This keyword marks the beginning of the declaration section where variables and constants can be initialized. In this example, x is declared as a variable of type NUMBER and assigned the value 10.

2.     BEGIN: This keyword signifies the start of the executable section of the PL/SQL block, where actual logic and statements are written.

3.     IF-THEN-ELSIF-ELSE: This conditional structure evaluates multiple conditions sequentially:

o   IF x > 0 THEN: Checks if the value of x is greater than 0. If true, it executes the statement inside the THEN block, which outputs 'x is positive' using the DBMS_OUTPUT.PUT_LINE procedure.

o   ELSIF x < 0 THEN: If the first condition (x > 0) is false, this condition checks if x is less than 0. If true, it executes the statement inside the ELSIF block, which outputs 'x is negative'.

o   ELSE: If none of the above conditions (x > 0 and x < 0) are true, the code inside the ELSE block is executed. In this case, it outputs 'x is zero'.

4.     END IF;: Marks the end of the conditional block, closing the IF-THEN-ELSIF-ELSE structure.

5.     END;: Marks the end of the PL/SQL block.

 

CASE Statement:

The CASE statement allows you to evaluate multiple conditions and execute different blocks of code based on the value of an expression.

CASE expression

   WHEN value1 THEN

      -- Code block executed if expression equals value1

   WHEN value2 THEN

      -- Code block executed if expression equals value2

   ...

   ELSE

      -- Code block executed if expression doesn't match any previous values

END CASE;

Example:

DECLARE

   x NUMBER := 2;

BEGIN

   CASE x

      WHEN 1 THEN

         DBMS_OUTPUT.PUT_LINE('x is 1');

      WHEN 2 THEN

         DBMS_OUTPUT.PUT_LINE('x is 2');

      ELSE

         DBMS_OUTPUT.PUT_LINE('x is neither 1 nor 2');

   END CASE;

END;

This PL/SQL code snippet demonstrates the use of the CASE statement, which allows for evaluating a specific expression (x in this case) against a list of possible values and executing the corresponding block of code based on the matching value:

1.     DECLARE: This keyword starts the declaration section where variables and constants can be initialized. In this example, x is declared as a variable of type NUMBER and assigned the value 2.

2.     BEGIN: This keyword signifies the start of the executable section of the PL/SQL block, where actual logic and statements are written.

3.     CASE x: This line introduces the CASE statement, which evaluates the value of x.

o   WHEN 1 THEN: If the value of x is 1, the statement inside the WHEN 1 THEN block is executed. In this case, it prints 'x is 1' using the DBMS_OUTPUT.PUT_LINE procedure.

o   WHEN 2 THEN: If the value of x is 2, the statement inside the WHEN 2 THEN block is executed. Here, it prints 'x is 2'.

o   ELSE: If the value of x does not match any of the specified values (1 or 2), the ELSE block is executed. In this case, it prints 'x is neither 1 nor 2'.

4.     END CASE;: Marks the end of the CASE statement, closing the structure.

5.     END;: Marks the end of the PL/SQL block.