Oracle 11g函数应用小例题_创建一个以部门号为参数的函数,给次部门员工中工资小于次部门平均值的员工加上100-CSDN博客

本文链接：https://blog.csdn.net/qq_43392291/article/details/124824076

创建一个以部门号为参数的函数，给此部门员工中工资小于此部门平均值的员工加上100元工资（并显示这些员工的工号、姓名及原工资），最后返回该部门新的平均工资。调用函数，部门号用10号部门

创建函数

SQL>  CREATE OR REPLACE FUNCTION return_newavgsal
  2    (p_deptno scott.emp.deptno%TYPE)
  3     RETURN scott.emp.sal%TYPE
  4      AS
  5      v_avgsal scott.emp.sal%TYPE;
  6      BEGIN
  7       SELECT avg(sal) INTO v_avgsal FROM scott.emp WHERE deptno=p_deptno;
  8       FOR v_emp IN (SELECT empno, ename, sal FROM scott.emp where deptno=p_deptno and sal<v_avgsal)
  9     LOOP
 10     update scott.emp set sal=sal+100 where empno=v_emp.empno;
 11     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename||' '||v_emp.sal);
 12     END LOOP;
 13     SELECT avg(sal) INTO v_avgsal FROM scott.emp WHERE deptno=p_deptno;
 14     RETURN v_avgsal;
 15   EXCEPTION
 16     WHEN NO_DATA_FOUND THEN
 17   DBMS_OUTPUT.PUT_LINE(' The deptno is invalid!');
 18    END return_newavgsal;
 19
 20
 21
 22  /

调用函数

SQL> select empno,ename,sal from scott.emp where deptno='10';

     EMPNO ENAME             SAL
---------- ---------- ----------
      7782 CLARK            2550
      7839 KING             5000
      7934 MILLER           1600

SQL> variable v_avgsal number;
SQL> exec :v_avgsal:=return_newavgsal('10')

PL/SQL 过程已成功完成。

SQL> print v_avgsal;

  V_AVGSAL
----------
   3116.67