我被这个PLSQL函数问题困住了，你能帮我吗？

Question

编写一个PLSQL函数，检查员工的薪资是否低于所有员工的平均薪资。如果薪资为lees，则程序应将该员工的薪资更新为其当前薪资的5%，并打印旧员工薪资、所有员工的平均薪资以及更新后的新员工薪资。您的程序应该处理所有可能的异常。您的输出应该被格式化如下：

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;

Answer 1

功能？我宁愿在这里使用过程。函数返回一些值，但是--在这个任务中--您要做的更多。

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.

对于Scott模式中的示例数据：

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.

测试：

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>

Answer 2

使用PROCEDURE (而不是FUNCTION)，并使用SELECT ... FOR UPDATE ...和UPDATE ... RETURNING ... INTO ...获取要输出的值：

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;
/

对于样本数据：

CREATE TABLE employees (id, name, emp_sal) AS
SELECT LEVEL, CHR(64+LEVEL), LEVEL * 100 FROM DUAL CONNECT BY LEVEL <= 10;

然后：

BEGIN
  DBMS_OUTPUT.ENABLE;
  checkEmpSal(2);
  checkEmpSal(7);
  checkEmpSal(11);
END;
/

产出：

旧工资是:200.00美元阿夫格工资是:550.00美元新工资是:210.00十亿美元:210.00美元旧工资是:700.00美元阿夫格工资是:551.00美元新工资是:700.00 G: 700.00美元雇员找不到。

小提琴