CASE Statement in PL/SQL


The CASE statement in PL/SQL is used to execute a sequence of statements based on a selector value. There are two types of CASE statements in PL/SQL:

  • Simple CASE Statement
  • Searched CASE Statement

Simple CASE Statement

The simple CASE statement compares an expression to a set of simple expressions to determine the result.

Syntax:
CASE selector
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    ELSE resultN
END;

Example:
DECLARE
    grade CHAR(1) := 'B';
    result VARCHAR2(20);
BEGIN
    result := CASE grade
                 WHEN 'A' THEN 'Excellent'
                 WHEN 'B' THEN 'Good'
                 WHEN 'C' THEN 'Fair'
                 WHEN 'D' THEN 'Poor'
                 ELSE 'Fail'
              END;
    DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;

Searched CASE Statement

The searched CASE statement evaluates a set of boolean expressions to determine the result.

Syntax
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END;

Example:
DECLARE
score NUMBER := 85;
result VARCHAR2(20);
BEGIN
result := CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
    END;
DBMS_OUTPUT.PUT_LINE('Grade: ' || result);
END;


Using CASE Statement in SQL Queries
Example:

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    salary        NUMBER,
    department_id NUMBER
);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (1, 'John', 'Doe', 5000, 10);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (2, 'Jane', 'Smith', 6000, 20);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (3, 'Emily', 'Jones', 7000, 10);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (4, 'Michael', 'Brown', 8000, 30);
INSERT INTO employees (employee_id, first_name, last_name, salary, department_id) VALUES (5, 'Sarah', 'Davis', 9000, 20);

SELECT employee_id,
       first_name,
       last_name,
       salary,
       CASE
           WHEN salary > 10000 THEN 'High'
           WHEN salary > 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more