Your company wants to automate the process of giving a salary raise to employees. You need to create a stored procedure that increases salary by a given percentage for a specific department.
Task
Write a PL/SQL stored procedure named give_raise that accepts a department name and a percentage, then updates all employees in that department with the raise.
Sample Schema: employees
emp_id
name
department
salary
1
John Doe
IT
70000
2
Jane Smith
IT
65000
3
Mike Johnson
HR
55000
Solution:
-- Create the stored procedure
CREATE OR REPLACE PROCEDURE give_raise(
p_department IN VARCHAR2,
p_percentage IN NUMBER
) AS
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percentage / 100)
WHERE department = p_department;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Raise applied to ' || SQL%ROWCOUNT || ' employees.');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END give_raise;
/
-- Execute the procedure (give 10% raise to IT department)
BEGIN
give_raise('IT', 10);
END;
/
This stored procedure encapsulates the business logic for giving raises. It accepts parameters, updates the database, and handles errors. The procedure can be reused and called from applications or other SQL scripts.
Benefits: Code reusability, security (users can execute without direct table access), and performance (compiled and stored in the database).