-- emp_count.sql

CREATE OR REPLACE FUNCTION emp_count(state_code varchar2 DEFAULT '%')
RETURN NUMBER
AS
  total NUMBER;
BEGIN
  SELECT COUNT(ID)
    INTO total
    FROM employee INNER JOIN person USING (ID)
   WHERE state LIKE upper(state_code);
  RETURN total;
END;
/
