← Back to Overview

PL/SQL Block for Conditional Salary Deduction

Concept Explanation

What is it?

This PL/SQL block demonstrates the use of Conditional Logic within a database transaction. It retrieves data, modifies it based on a rule, and then checks if the result meets a specific condition before finalizing the process.

Why is it important?

Business rules often require checking specific conditions after an operation. For example, a bank might allow a withdrawal but must alert the user if their balance falls below a minimum threshold. This block simulates that logic for an employee payroll scenario.

Where is it used?

Commonly used in financial systems, inventory management (checking for low stock after a sale), and automated HR systems for rule-based adjustments.

Real-world example

Imagine a subscription service that deducts a monthly fee. If the deduction leaves the customer's wallet balance below a certain amount, the system sends an automated "Low Balance" alert.

---

Algorithm

Step-by-step logic:

1. Start
2. Declare variables to store the Employee Number (v_emp_no), Name (v_name), and Salary (v_salary).
3. Accept input from the user for the v_emp_no.
4. Fetch the current record from the employee table for the given v_emp_no.
5. Apply core logic:
   • Deduct Rs. 200 from the current salary: v_salary = v_salary - 200.
6. Execute UPDATE statement to update the new salary in the employee table.
7. Check the condition:
   • If v_salary < 1000, then display the message: "[Name]'s Salary is less than 1000".
8. Display success message for the update.
9. End

Edge Cases: * Employee Not Found: Handled by a NO_DATA_FOUND exception. * Insufficient Original Salary: If the salary is already very low, the deduction might make it zero or negative.

---

Implementations

Oracle (19c)

-- Table Structure:
-- CREATE TABLE employee (
--     emp_no NUMBER(5) PRIMARY KEY,
--     name VARCHAR2(50),
--     salary NUMBER(10, 2)
-- );

DECLARE
    v_emp_no  employee.emp_no%TYPE;
    v_name    employee.name%TYPE;
    v_salary  employee.salary%TYPE;
BEGIN
    -- 1. Accept Employee Number from user
    v_emp_no := &Enter_Employee_Number;

    -- 2. Fetch current details
    SELECT name, salary INTO v_name, v_salary
    FROM employee
    WHERE emp_no = v_emp_no;

    -- 3. Perform Deduction
    v_salary := v_salary - 200;

    -- 4. Update the record
    UPDATE employee
    SET salary = v_salary
    WHERE emp_no = v_emp_no;

    -- 5. Conditional check and message display
    IF v_salary < 1000 THEN
        DBMS_OUTPUT.PUT_LINE(v_name || '''s Salary is less than 1000');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary updated successfully for ' || v_name);
    END IF;

    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Employee Number ' || v_emp_no || ' not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
        ROLLBACK;
END;
/

---

Explanation

• Oracle
  • SELECT INTO: This clause is used to retrieve data from a table and assign it directly to PL/SQL variables.
  • IF-THEN-ELSE: The standard control structure used to implement the conditional check (v_salary < 1000).
  • Variable Substitution: The '|| v_name || '''s ...' syntax handles the concatenation and the apostrophe for the possessive "Name's".
  • Exception Handling: The NO_DATA_FOUND exception is critical here in case the user enters an invalid employee ID.
  • Transaction Control: COMMIT ensures the deduction is saved, and ROLLBACK ensures no partial changes are made if an error occurs.

---

Complexity Analysis

• Time Complexity: O(1) - Single row lookup and single row update based on a Primary Key index.
• Space Complexity: O(1) - Minimal memory usage for local variables.

---

Flowchart

graph TD
    A[Start] --> B[Accept Employee No]
    B --> C[Fetch Name and Salary]
    C --> D[Deduct 200 from Salary]
    D --> E[Update Employee Table]
    E --> F{Salary < 1000?}
    F -- Yes --> G[Display 'Salary is less than 1000']
    F -- No --> H[Display 'Salary Updated Successfully']
    G --> I[Commit & End]
    H --> I

---

Sample Dry Run

Step	Variable	Value	Description
1	v_emp_no	101	Input
2	v_name	'John'	Fetched from DB
3	v_salary	1150	Fetched from DB
4	New v_salary	950	1150 - 200
5	Update DB	Done	Salary set to 950
6	Condition Check	950 < 1000 (TRUE)	Condition met
7	Output	"John's Salary is less than 1000"	Final Display

---

Practice Problems

Easy

• Modify the block to accept the deduction amount from the user instead of hardcoding 200.

Medium

• Prevent the update if the resulting salary would be negative.

Hard

• Modify the block to handle multiple employees at once using a cursor.

---

Interview Tips

• Explain why SELECT INTO is risky without exception handling (it throws an error if 0 or >1 rows are returned).
• Discuss the difference between a standalone PL/SQL block and a database trigger for this type of logic.

---

Related Concepts

• PL/SQL Control Structures (Loops, IF)
• SQL DML (UPDATE)
• Exception Handling (Standard & User-defined)

---

"Logic is the beginning of wisdom, not the end." - Leonard Nimoy