The PL/SQL CASE statement is a control structure that allows you to execute a sequence of statements based on a condition or the value of an expression. It's akin to the switch statement in other programming languages and provides a more readable and efficient way to evaluate conditions compared to a series of IF-ELSE statements. In this tutorial, you will see multiple PL/SQL Case examples to get a better understanding of how ti works.
The CASE statement comes in two forms:
- Simple CASE: Compares an expression to a set of values.
- Searched CASE: Evaluates a set of Boolean expressions to determine the result.
In this tutorial, we will explore practical applications of the PL/SQL CASE statement, including handling multiple conditions, using it in the WHERE clause, and dealing with NULL values.
Preparing the Data Environment
Before diving into the examples, let's set up a sample data environment.
Data Setup: Creating and Inserting Sample Data
-- Creating a Sample Table CREATE TABLE employees ( id NUMBER, name VARCHAR2(50), salary NUMBER, department VARCHAR2(50), join_date DATE ); -- Inserting Sample Data INSERT INTO employees VALUES (1, 'Alice', 70000, 'IT', TO_DATE('2018-06-15', 'YYYY-MM-DD')); INSERT INTO employees VALUES (2, 'Bob', 85000, 'HR', TO_DATE('2020-01-10', 'YYYY-MM-DD')); INSERT INTO employees VALUES (3, 'Charlie', NULL, null, TO_DATE('2019-08-20', 'YYYY-MM-DD')); INSERT INTO employees VALUES (4, 'Diana', 90000, 'Marketing', TO_DATE('2021-02-17', 'YYYY-MM-DD')); INSERT INTO employees VALUES (5, 'Ethan', 75000, 'IT', TO_DATE('2017-11-25', 'YYYY-MM-DD')); Commit;
PL/SQL CASE Examples
Check the below PL/SQL Case examples for 3 different scenarios:
Example 1: PL/SQL CASE When Multiple Conditions
Scenario
We want to categorize employees into different salary bands based on their salaries and departments.
Implementation
DECLARE emp_id NUMBER := 2; salary_band VARCHAR2(20); BEGIN SELECT CASE WHEN salary < 80000 AND department = 'IT' THEN 'IT - Band 1' WHEN salary BETWEEN 80000 AND 100000 THEN 'Band 2' ELSE 'Other' END INTO salary_band FROM employees WHERE id = emp_id; DBMS_OUTPUT.PUT_LINE('Employee Salary Band: ' || salary_band); END;
Output:
Employee Salary Band: Band 2
Example 2: PL/SQL CASE in WHERE Clause
Scenario
We need to fetch employees who either joined before 2019 or have a salary greater than 80000.
Implementation
SELECT name, join_date, salary FROM employees WHERE CASE WHEN join_date < TO_DATE('2019-01-01', 'YYYY-MM-DD') THEN 'True' WHEN salary > 80000 THEN 'True' ELSE 'False' END = 'True';
Output:
NAME JOIN_DATE SALARY Alice 15-JUN-18 70000 Bob 10-JAN-20 85000 Diana 17-FEB-21 90000 Ethan 25-NOV-17 75000
Example 3: PL/SQL CASE When NULL
Scenario
Assign a default department to employees whose department is NULL.
Implementation
DECLARE emp_id NUMBER := 3; department_name VARCHAR2(50); BEGIN SELECT CASE WHEN department IS NULL THEN 'Unknown' ELSE department END INTO department_name FROM employees WHERE id = emp_id; DBMS_OUTPUT.PUT_LINE('Employee Department: ' || department_name); END;
Output:
Employee Department: Unknown
Summary of Key Takeaways
- The PL/SQL CASE statement is an effective way to handle conditional logic, offering better readability than multiple IF-ELSE statements.
- It can be used in both the SELECT statement and PL/SQL blocks.
- The CASE statement is versatile, allowing for multiple conditions, use in WHERE clauses, and handling NULL values.
- Proper data setup is crucial for testing and understanding the use of the CASE statement in different scenarios.
This tutorial should help you grasp the fundamentals and advanced uses of the PL/SQL CASE statement, making your PL/SQL programming more efficient and readable.
Leave a comment
You must login or register to add a new comment.