SQL Reference - ELSEIF

ELSEIF

ELSEIF is used in SQL to branch the execution of script based on conditionals within a case statement. It serves as an intermediate between the initial IF condition and the final ELSE statement, effectively offering an additional layer of scrutiny before defaulting to the ELSE clause. This allows for a more demanding or specific set of conditions to be checked throughout the execution process.

Example

DECLARE
   num1 NUMBER := 100;
   num2 NUMBER := 20;
BEGIN
   IF num1 > num2 THEN
      DBMS_OUTPUT.PUT_LINE('Num1 is greater than Num2');
   ELSEIF num1 < num2 THEN
      DBMS_OUTPUT.PUT_LINE('Num1 is less than Num2');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Num1 is equal to Num2');
   END IF;
END;
/

Output

Num1 is greater than Num2

Explanation

In the given example, the number values are compared using the IF-ELSEIF-ELSE construct. First, the code checks if num1 is greater than num2. If the condition is true, it outputs ‘Num1 is greater than Num2’. If the condition is false, the ELSEIF statement checks if num1 is less than num2. If it’s false again, then the ELSE statement executes, and it outputs ‘Num1 is equal to Num2’. In this case, because num1 is greater than num2, the output is ‘Num1 is greater than Num2’.