PL/SQL CASE Statement Overview
In PL/SQL, the CASE statement allows you to execute a series of statements
based on a selector, which can be a variable, function, or expression.
Similar to an IF statement, the CASE statement employs the keyword WHEN to
evaluate conditions and execute corresponding statements. The selector is
assessed for a boolean value, and the CASE statement proceeds from top to
bottom. Upon finding the first TRUE condition, the associated THEN clause is
executed, and the flow continues until the END CASE.
Syntax for the CASE Statement:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END;
Example: Using Employee table.
-- Creating a sample table
CREATE TABLE employees (
employee_id NUMBER,
salary NUMBER
);
-- Inserting sample data
INSERT INTO employees VALUES (1, 50000);
INSERT INTO employees VALUES (2, 70000);
INSERT INTO employees VALUES (3, 90000);
INSERT INTO employees VALUES (4, 120000);
INSERT INTO employees VALUES (5, 150000);
PL/SQL Case Statement Program:
-- PL/SQL Block with CASE Statement
DECLARE
v_employee_id NUMBER;
v_salary NUMBER;
BEGIN
-- Set the employee_id for which you want to check the salary
v_employee_id := 3;
-- Retrieve the salary for the given employee_id
SELECT salary INTO v_salary FROM employees WHERE employee_id =
v_employee_id;
-- Using CASE statement to categorize employees based on salary
CASE
WHEN v_salary < 60000 THEN
DBMS_OUTPUT.PUT_LINE('Low Salary Range');
WHEN v_salary >= 60000 AND v_salary < 100000 THEN
DBMS_OUTPUT.PUT_LINE('Medium Salary Range');
WHEN v_salary >= 100000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary Range');
ELSE
DBMS_OUTPUT.PUT_LINE('Salary Not Available');
END CASE;
END;
/
Output:
Medium Salary Range
More topic in PL/SQL
