Decision Making in PL/SQL

Decision making in PL/SQL allows you to execute a sequence of statements based on a condition. The primary constructs for decision-making in PL/SQL are IF, ELSIF, and CASE.

IF Statement:

Simple IF Statement:

IF condition THEN
   -- code to be executed if condition is TRUE
END IF;

IF-ELSE Statement:

IF condition THEN
   -- code to be executed if condition is TRUE
ELSE
   -- code to be executed if condition is FALSE
END IF;

IF-ELSIF Statement:

IF condition1 THEN
   -- code to be executed if condition1 is TRUE
ELSIF condition2 THEN
   -- code to be executed if condition2 is TRUE
ELSE
   -- code to be executed if neither condition is TRUE
END IF;

Example:

DECLARE
    a NUMBER := 30;
    b NUMBER := 20;
BEGIN
    IF a > b THEN
        DBMS_OUTPUT.PUT_LINE('a is greater than b');
    ELSE
        DBMS_OUTPUT.PUT_LINE('b is greater than or equal to a');
    END IF;
END;

CASE Statement:

The CASE statement provides a way to perform multiple actions depending on the value of an expression.

Simple CASE Statement:

CASE expression
    WHEN value1 THEN
        -- sequence of statements
    WHEN value2 THEN
        -- sequence of statements
    ...
    ELSE
        -- default sequence of statements
END CASE;

Searched CASE Statement: This does not evaluate based on a single expression's value but instead checks for multiple conditions.

CASE 
    WHEN condition1 THEN
        -- sequence of statements
    WHEN condition2 THEN
        -- sequence of statements
    ...
    ELSE
        -- default sequence of statements
END CASE;

Example:

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('Good');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Average');
    END CASE;
END;

It's crucial to use these constructs appropriately, depending on the specific needs of your PL/SQL logic. They allow you to implement a wide range of logic flows within your PL/SQL programs, making your code more dynamic and responsive to different conditions and inputs.

Basic IF Statement:

DECLARE
    v_salary NUMBER := 60000;
BEGIN
    IF v_salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('High Salary');
    END IF;
END;

Using CASE Statement in PL/SQL:

DECLARE
    v_day VARCHAR2(10) := 'Monday';
BEGIN
    CASE v_day
        WHEN 'Monday' THEN
            DBMS_OUTPUT.PUT_LINE('Start of the week');
        WHEN 'Friday' THEN
            DBMS_OUTPUT.PUT_LINE('End of the week');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Midweek');
    END CASE;
END;

Nested IF-ELSE Statements in PL/SQL:

DECLARE
    v_age NUMBER := 25;
BEGIN
    IF v_age < 18 THEN
        DBMS_OUTPUT.PUT_LINE('Minor');
    ELSE
        IF v_age < 65 THEN
            DBMS_OUTPUT.PUT_LINE('Adult');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Senior');
        END IF;
    END IF;
END;

Handling NULL Values in PL/SQL Conditions:

DECLARE
    v_value NUMBER := NULL;
BEGIN
    IF v_value IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Value is NULL');
    END IF;
END;

PL/SQL Decision-Making with BOOLEAN Variables:

DECLARE
    v_flag BOOLEAN := TRUE;
BEGIN
    IF v_flag THEN
        DBMS_OUTPUT.PUT_LINE('Flag is TRUE');
    END IF;
END;

Using ELSIF in PL/SQL for Multiple Conditions:

DECLARE
    v_score NUMBER := 75;
BEGIN
    IF v_score >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('Excellent');
    ELSIF v_score >= 70 THEN
        DBMS_OUTPUT.PUT_LINE('Good');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Average');
    END IF;
END;

Short-Circuit Evaluation in PL/SQL:

DECLARE
    v_value NUMBER := 10;
    v_flag BOOLEAN := TRUE;
BEGIN
    IF v_flag AND v_value > 5 THEN
        DBMS_OUTPUT.PUT_LINE('Flag is TRUE and Value is greater than 5');
    END IF;
END;

PL/SQL DECODE Function for Decision-Making:

DECLARE
    v_day VARCHAR2(10) := 'Monday';
BEGIN
    CASE v_day
        WHEN 'Monday' THEN
            DBMS_OUTPUT.PUT_LINE('Start of the week');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Other day');
    END CASE;
END;

Logical Operators in PL/SQL Conditions:

DECLARE
    v_age NUMBER := 25;
    v_salary NUMBER := 60000;
BEGIN
    IF v_age > 21 AND v_salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('Qualified');
    END IF;
END;

PL/SQL GOTO Statement for Branching:

DECLARE
    v_value NUMBER := 5;
BEGIN
    IF v_value < 10 THEN
        GOTO low_value;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('High Value');
    GOTO end_block;
    
    <<low_value>>
    DBMS_OUTPUT.PUT_LINE('Low Value');
    
    <<end_block>>
    NULL;
END;

Error Handling in PL/SQL with EXCEPTION:

DECLARE
    v_value NUMBER := 'abc'; -- Invalid value
BEGIN
    BEGIN
        -- Code that might cause an exception
        v_value := TO_NUMBER('abc');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Error: Invalid value');
    END;
END;

PL/SQL EXIT Statement for Loop Termination:

DECLARE
    v_counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration ' || v_counter);
        v_counter := v_counter + 1;
        IF v_counter > 5 THEN
            EXIT;
        END IF;
    END LOOP;
END;