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

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more