Oracle PL/SQL - Create a stored function

Introduction

The following code shows how to define a function to determine the number of emp for a given department.
Demo

SQL>
SQL> drop table departments;

Table dropped.-- www .jav a  2s  . c om

SQL>
SQL> create table departments(
  2  deptno    NUMBER(2)     primary key,
  3  dname     VARCHAR2(10)  not null unique check (dname = upper(dname)),
  4  location  VARCHAR2(8)   not null        check (location = upper(location)),
  5  mgr       NUMBER(4)) ;
SQL>
SQL> insert into departments values (10,'ACCOUNTING','NEW YORK',7007);
SQL> insert into departments values (20,'TRAINING',  'DALLAS',  7004);
SQL> insert into departments values (30,'SALES',     'CHICAGO', 7006);
SQL> insert into departments values (40,'HR',        'BOSTON',  7009);
SQL>
SQL> create or replace function emp_count(p_deptno in number)
  2  return number
  3  is
  4         cnt number(2) := 0;
  5  begin
  6         select count(*)
  7         into cnt
  8         from   emp e
  9         where  e.deptno = p_deptno;
 10         return (cnt);
 11  end;
 12  /

Function created.

SQL>
SQL> describe emp_count;
FUNCTION emp_count RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_DEPTNO                       NUMBER                  IN

SQL>
SQL> select deptno, dname, location
  2  ,      emp_count(deptno)
  3  from   departments;

   DEPTNO | DNAME      | LOCATION | EMP_COUNT(DEPTNO)
--------- | ---------- | -------- | -----------------
    1     | ACCOUNTING | NEW YORK |          00005.00
    2     | TRAINING   | DALLAS   |          00005.00
    30    | SALES      | CHICAGO  |          00004.00
    4     | HR         | BOSTON   |          00000.00

SQL>