Case - (PL/SQL Control Statements)

In PL/SQL, the CASE statement is a control statement that allows for conditional execution of code. It is similar to the if-then-else statement in other programming languages. The CASE statement evaluates a condition and executes one or more statements based on the result of that condition.

Syntax

The syntax for using the CASE statement in PL/SQL is as follows:

CASE expression
    WHEN condition1 THEN
        statements1;
    WHEN condition2 THEN
        statements2;
    ...
    WHEN conditionN THEN
        statementsN;
    ELSE
        elseStatements;
END CASE;

Here, expression is the value being evaluated, condition1 through conditionN are the conditions being tested, statements1 through statementsN are the statements executed if their corresponding condition is true. elseStatements are the statements executed if none of the conditions evaluate to true.

Example

Here's an example of using the CASE statement in PL/SQL:

DECLARE
    grade CHAR(1);
BEGIN
    grade := 'B';

    CASE grade
        WHEN 'A' THEN
            DBMS_OUTPUT.PUT_LINE('Excellent!');
        WHEN 'B' THEN
            DBMS_OUTPUT.PUT_LINE('Good job!');
        WHEN 'C' THEN
            DBMS_OUTPUT.PUT_LINE('Keep trying!');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Sorry, you failed.');
    END CASE;
END;
/

Output

Good job!

PL/SQL procedure successfully completed.

Explanation

In the above example, we have used the CASE statement to evaluate a grade variable. If the grade is 'A', the program outputs "Excellent!", if it is 'B', the program outputs "Good job!", if it is 'C', the program outputs "Keep trying!", and if it is any other value, the program outputs "Sorry, you failed." In this case, the value of grade is 'B' and the output is "Good job!".

Use

The CASE statement is a powerful tool in PL/SQL and can be used in a variety of applications. It is often used to evaluate user input or to make decisions within a stored procedure or function.

Important Points

• The CASE statement is a control statement used for conditional execution of code.
• It evaluates an expression and executes one or more statements based on the result of that expression.
• ELSE statement is optional but is executed if none of the conditions evaluate to true.

Summary

In summary, the CASE statement in PL/SQL allows for conditional execution of code based on the evaluation of an expression. It is a powerful tool that can be used to evaluate user input or make decisions within a stored procedure or function.