IF statements in PL/SQL

In PL/SQL (Procedural Language/Structured Query Language), the IF statement is used to execute a block of code conditionally, based on whether a specified condition is true or false. Here are the basic forms of the IF statement in PL/SQL:

Simple IF Statement

This form executes a block of code if a condition is true.

IF condition THEN
    -- code to be executed if condition is true
END IF;

Example:
DECLARE
    salary NUMBER := 4000;
    bonus  NUMBER := 0;
BEGIN
    IF salary > 3000 THEN
        bonus := 500;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);
END;

Output
Bonus: 500

IF-THEN-ELSE Statement

This form executes one block of code if the condition is true, and another block if the condition is false.
 
 IF condition THEN
    -- code to be executed if condition is true
ELSE
    -- code to be executed if condition is false
END IF;
 
Example:
DECLARE
    salary NUMBER := 2500;
    bonus  NUMBER := 0;
BEGIN
    IF salary > 3000 THEN
        bonus := 500;
    ELSE
        bonus := 200;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);
END;

In this example, since the salary is not greater than 3000, the bonus is set to 200. The output will be:
Bonus: 200

IF-THEN-ELSIF Statement

This form allows for multiple conditions to be checked sequentially. It executes the block of code corresponding to the first true condition.
 
IF condition1 THEN
    -- code to be executed if condition1 is true
ELSIF condition2 THEN
    -- code to be executed if condition2 is true
ELSIF condition3 THEN
    -- code to be executed if condition3 is true
ELSE
    -- code to be executed if all conditions are false
END IF;
 
Example:
 DECLARE
    salary NUMBER := 2800;
    bonus  NUMBER := 0;
BEGIN
    IF salary > 3000 THEN
        bonus := 500;
    ELSIF salary > 2000 THEN
        bonus := 300;
    ELSE
        bonus := 100;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);
END;

In this example, since the salary is greater than 2000 but not greater than 3000, the bonus is set to 300. The output will be:
Bonus: 300

Real time 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);

DECLARE
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
    v_department_id employees.department_id%TYPE;
    v_raise_amount NUMBER;
BEGIN
    FOR emp_rec IN (SELECT employee_id, first_name, last_name, salary, department_id FROM employees)
    LOOP
        v_employee_id := emp_rec.employee_id;
        v_first_name := emp_rec.first_name;
        v_last_name := emp_rec.last_name;
        v_salary := emp_rec.salary;
        v_department_id := emp_rec.department_id;

        IF v_department_id = 10 THEN
            v_raise_amount := v_salary * 0.10;  -- 10% raise for department 10
        ELSIF v_department_id = 20 THEN
            v_raise_amount := v_salary * 0.05;  -- 5% raise for department 20
        ELSE
            v_raise_amount := v_salary * 0.03;  -- 3% raise for other departments
        END IF;

        UPDATE employees
        SET salary = salary + v_raise_amount
        WHERE employee_id = v_employee_id;

        DBMS_OUTPUT.PUT_LINE('Employee ' || v_first_name || ' ' || v_last_name || ' in department ' || v_department_id || ' received a raise of ' || v_raise_amount);
    END LOOP;
END;
/


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