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 » 

Functions:
  1. Create a function
  2. Call a function with named and mixed notation
  3. Return the average salary
  4. Information on Functions
  5. Dropping a Function
Related: