Scribd
Upload
4 views1 page

Employee Bonus Calculation Functions

Uploaded by

solive
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
4 views1 page

Employee Bonus Calculation Functions

Uploaded by

solive
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE OR REPLACE FUNCTION get_bonus_by_emp(emp_id IN NUMBER) RETURN NUMBER IS

emp_sal NUMBER;
BEGIN
SELECT salary INTO emp_sal FROM employee WHERE id = emp_id;
IF emp_sal <= 20000 THEN
RETURN emp_sal * 0.10; -- 10% bonus
ELSE
RETURN emp_sal * 0.05; -- 5% bonus
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0; -- If employee ID doesn't exist
END;
/
DECLARE
bo NUMBER;
BEGIN
FOR d IN (SELECT id, fname FROM employee) LOOP
bo := get_bonus_by_emp([Link]);
dbms_output.put_line('Name: ' || [Link] || ', Bonus: ' || bo);
END LOOP;
END;
/

CREATE OR REPLACE FUNCTION get_bonus(sal IN NUMBER) RETURN NUMBER IS


BEGIN
IF sal <= 20000 THEN
RETURN sal * 0.10;
ELSE
RETURN sal * 0.05;
END IF;
END;
/
CREATE OR REPLACE PROCEDURE update_bonus(emp_id IN NUMBER) IS
emp_sal NUMBER;
bonus_amt NUMBER;
BEGIN
SELECT salary INTO emp_sal FROM employee WHERE id = emp_id;
bonus_amt := get_bonus(emp_sal);
UPDATE employee SET salary = salary + bonus_amt WHERE id = emp_id;
dbms_output.put_line('Bonus of ' || bonus_amt || ' added to employee ID '|| emp_id
|| '. New salary: ' || (emp_sal + bonus_amt));
END;
/
BEGIN
FOR d IN (SELECT id, fname FROM employee) LOOP
update_bonus([Link]);
END LOOP;
END;
/

You might also like