oracle - PL/SQL：ORA-06550：此处不允许使用组函数

Question

我有以下错误

使用此代码

DECLARE v_manager_id NUMERIC;
BEGIN
SELECT MANAGER_ID INTO v_manager_id, COUNT(MANAGER_ID)
FROM DEPARTMENTS
GROUP BY MANAGER_ID
HAVING COUNT(MANAGER_ID) > 1;
END;

标签： oracleplsqlgroup-byhaving-clause

Answer 1

应该

DECLARE 
  v_manager_id NUMBER;
BEGIN
  SELECT MANAGER_ID 
  INTO v_manager_id
  FROM DEPARTMENTS
  GROUP BY MANAGER_ID
  HAVING COUNT(MANAGER_ID) > 1;
END;

或者，如果您需要 的值，则count(manager_id)必须声明一个变量，例如

DECLARE 
  v_manager_id NUMBER;
  v_count      NUMBER
BEGIN
  SELECT MANAGER_ID, COUNT(MANAGER_ID)
  INTO v_manager_id, v_count
  FROM DEPARTMENTS
  GROUP BY MANAGER_ID
  HAVING COUNT(MANAGER_ID) > 1;
END;