I want to Create a function that returns the number of employees in a department-CodePudding

The query I tried is this,

CREATE OR REPLACE FUNCTION get_dno (d_no IN Employee.DepartmentNo%TYPE)
RETURN NUMBER
IS
  d_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO d_cnt 
  FROM employee GROUP BY DeptNo=d_no;

  RETURN d_cnt;
END get_dno;

and use this query below to get value from user.

DECLARE
  get_dptno NUMBER;
BEGIN
  get_dptno := get_dno(:d_no);
  DBMS_OUTPUT.PUT_LINE('No of employees in searched DeptNo : ' ||get_dptno);
END;

But I couldn't even get the Function part to run.

The error I got was where I used GROUP BY, I think

This is the error message I got

Is there any workaround for the same logic ???

CodePudding user response：

When you group by column there is no condition accept please remove code after column name DeptNo.

CREATE OR REPLACE FUNCTION get_dno (d_no IN Employee.DepartmentNo%TYPE)
RETURN NUMBER
IS
  d_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO d_cnt 
  FROM employee GROUP BY DeptNo;

  RETURN d_cnt;
END get_dno;

CodePudding user response：

You are almost there, you want WHERE rather than GROUP BY and you need to work out if the column name is DepartmentNo or DeptNo and use the correct column name in the signature and in the SELECT query:

CREATE FUNCTION get_dno (
  d_no IN Employee.DepartmentNo%TYPE
) RETURN NUMBER
IS
  d_cnt NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   d_cnt 
  FROM   employee
  WHERE  DepartmentNo=d_no;

  RETURN d_cnt;
END get_dno;
/

Then:

DECLARE
  get_dptno NUMBER;
BEGIN
  get_dptno := get_dno(:d_no);
  DBMS_OUTPUT.PUT_LINE('No of employees in searched DeptNo : ' ||get_dptno);
END;
/

Works and, for the sample data:

CREATE TABLE employee (id, departmentno) AS
SELECT  1, 1 FROM DUAL UNION ALL
SELECT  2, 1 FROM DUAL UNION ALL
SELECT  3, 1 FROM DUAL UNION ALL
SELECT  4, 1 FROM DUAL UNION ALL
SELECT  5, 2 FROM DUAL UNION ALL
SELECT  6, 2 FROM DUAL UNION ALL
SELECT  7, 3 FROM DUAL UNION ALL
SELECT  8, 3 FROM DUAL UNION ALL
SELECT  9, 3 FROM DUAL UNION ALL
SELECT 10, 4 FROM DUAL;

If :dno is 1 then outputs:

No of employees in searched DeptNo : 4

db<>fiddle here