Write a PLSQL function that checks whether the salary of an employee is less than the average salary of all the employees. If the salary is lees, the program should update the salary of that employee by 5% of their current salary and print the old employee salary, the average salary of all the employees, and the new employee salary after update. Your program should handle all the possible exceptions. Your output should be formatted as below:
O/P: Old salary is: $510
Avg salary is: $957.05
New salary is: $765
1-Huguette Sandrine: $765
CREATE OR REPLACE FUNCTION checkEMPsal()
return number
IS
avg_sal number;
BEGIN
select AVG(emp_sal) into avg_sal from employee;
CodePudding user response：
Function? I'd rather use a procedure here. Function returns some value, but - in this task - you're doing a lot more.
SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE p_raise (par_empno IN emp.empno%TYPE)
2 IS
3 l_avg NUMBER;
4 l_sal_old emp.sal%TYPE;
5 l_sal_new emp.sal%TYPE;
6 BEGIN
7 -- average salary
8 SELECT ROUND (AVG (sal)) INTO l_avg FROM emp;
9
10 -- employee's salary
11 SELECT sal
12 INTO l_sal_old
13 FROM emp
14 WHERE empno = par_empno;
15
16 IF l_sal_old < l_avg
17 THEN
18 l_sal_new := l_sal_old * 1.05;
19
20 UPDATE emp
21 SET sal = l_sal_new
22 WHERE empno = par_empno;
23
24 DBMS_OUTPUT.put_line (
25 'old salary: '
26 || l_sal_old
27 || ', new salary: '
28 || l_sal_new
29 || ', average salary: '
30 || l_avg);
31 ELSE
32 DBMS_OUTPUT.put_line (
33 'Employee''s salary is not lower than average salary - no raise');
34 END IF;
35 EXCEPTION
36 WHEN NO_DATA_FOUND
37 THEN
38 DBMS_OUTPUT.put_line ('Employee not found');
39 END;
40 /
Procedure created.
For sample data in Scott's schema:
SQL> SELECT empno, ename, sal
2 FROM emp
3 ORDER BY sal;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 840
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7788 SCOTT 3000
7902 FORD 3000
7839 KING 5000
14 rows selected.
Testing:
SQL> EXEC p_raise(7369);
old salary: 840, new salary: 882, average salary: 2076
PL/SQL procedure successfully completed.
SQL> EXEC p_raise(7788);
Employee's salary is not lower than average salary - no raise
PL/SQL procedure successfully completed.
SQL> EXEC p_raise(-1);
Employee not found
PL/SQL procedure successfully completed.
SQL>
CodePudding user response：
Use a PROCEDURE (rather than a FUNCTION) and use SELECT ... FOR UPDATE ... and UPDATE ... RETURNING ... INTO ... to get the values to output:
CREATE PROCEDURE checkEMPsal(
v_id IN EMPLOYEES.ID%TYPE
)
IS
v_rowid ROWID;
v_name employees.name%TYPE;
v_old_salary employees.emp_sal%TYPE;
v_new_salary employees.emp_sal%TYPE;
v_average employees.emp_sal%TYPE;
BEGIN
SELECT AVG(emp_sal)
INTO v_average
FROM employees;
SELECT ROWID,
emp_sal,
name
INTO v_rowid,
v_old_salary,
v_name
FROM employees
WHERE id = v_id
FOR UPDATE OF emp_sal;
IF v_old_salary < v_average THEN
UPDATE employees
SET emp_sal = emp_sal * 1.05
WHERE ROWID = v_rowid
RETURNING emp_sal INTO v_new_salary;
ELSE
v_new_salary := v_old_salary;
END IF;
DBMS_OUTPUT.PUT_LINE('Old salary is: ' || TO_CHAR(v_old_salary, 'FM$999G990D00'));
DBMS_OUTPUT.PUT_LINE('Avg salary is: ' || TO_CHAR(v_average, 'FM$999G990D00'));
DBMS_OUTPUT.PUT_LINE('New salary is: ' || TO_CHAR(v_new_salary, 'FM$999G990D00'));
DBMS_OUTPUT.PUT_LINE(v_name || ': ' || TO_CHAR(v_new_salary, 'FM$999G990D00'));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many employees found.');
END;
/
Which, for the sample data:
CREATE TABLE employees (id, name, emp_sal) AS
SELECT LEVEL, CHR(64 LEVEL), LEVEL * 100 FROM DUAL CONNECT BY LEVEL <= 10;
Then:
BEGIN
DBMS_OUTPUT.ENABLE;
checkEmpSal(2);
checkEmpSal(7);
checkEmpSal(11);
END;
/
Outputs:
Old salary is: $200.00 Avg salary is: $550.00 New salary is: $210.00 B: $210.00 Old salary is: $700.00 Avg salary is: $551.00 New salary is: $700.00 G: $700.00 Employee not found.