-- Write a function that accepts
-- department_id as a parameter
-- and returns the count of employees
-- in that department

-- e.g. count_employees(1001)

CREATE OR REPLACE FUNCTION hr.count_employees(deptid employees.department_id%TYPE) -- This is the parameter it takes
RETURN NUMBER -- This is what it returns to the user
IS
emp_count NUMBER(6); -- I'll use this to store the answer
BEGIN
    SELECT COUNT(employee_id) INTO emp_count
    FROM hr.employees
    WHERE department_id = deptid; -- Run the query for whatever deptid the user has requested

    RETURN emp_count; -- Return the answer to the user
END;
-- Function code ends here

-- This is how to call a function for a simple test
SET SERVEROUTPUT ON
BEGIN
    DBMS_OUTPUT.PUT_LINE(hr.count_employees(50)); -- Count employees in deptid 50
END;