Return the average salary
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
SQL> CREATE FUNCTION average_salary (dept IN INTEGER)
2 RETURN NUMBER AS
3 v_average_salary NUMBER;
4 BEGIN
5 SELECT AVG(sal) INTO v_average_salary FROM emp
6 WHERE deptno = dept;
7 RETURN v_average_salary;
8 END average_salary;
9 /
Function created.
SQL>
SQL>
SQL>
SQL> SELECT average_salary(20) FROM dual;
AVERAGE_SALARY(20)
------------------
1887.5
SQL>
Home »
Oracle »
PL/SQL »
Oracle »
PL/SQL »
Functions:
- Create a function
- Call a function with named and mixed notation
- Return the average salary
- Information on Functions
- Dropping a Function
Related: