CREATE TABLE EMP1
(EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);这是创建的表,要求创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。
(EMPNO NUMBER(4) NOT NULL PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);这是创建的表,要求创建一个函数,以员工号为参数,返回该员工所在部门的平均工资。
v_avgsal NUMBER;
BEGIN
SELECT AVG(sal)
INTO v_avgsal
FROM emp a
WHERE a.deptno = (SELECT deptno FROM emp b WHERE b.empno = i_empno);
RETURN v_avgsal;
END;
/