存储过程编译错误

Question

有人可以帮我解决这个问题，这是我运行此脚本时收到的错误消息

 第 1 行出现错误：
    ORA-00979: 不是 GROUP BY 表达式
    ORA-06321: 在“s3398293.P2”，第 7 行
    ORA-06321: 在“s3398293.P2”，第 18 行
    ORA-06321: 在第 1 行

代码：

    create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
        staff_info  staff.bno%TYPE;
        address_info varchar2(20);

            CURSOR c1 IS
                SELECT staff.bno ,
                branch.street || ' ' || branch.suburb || ' ' || branch.postcode 
                FROM deal , staff, contact , property , branch
                where staff.peid = contact.peid
                and contact.pno = property.pno
                and property.pno = deal.pno 
                and staff.peid = branch.peid
                group by staff.bno
                HAVING x > sum(deal.price);

   BEGIN
         OPEN c1;
         LOOP
            FETCH c1 INTO staff_info,address_info ;
            EXIT WHEN c1%notfound;
            dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
            dbms_output.put_line(staff_info ||' '|| address_info);  
         END LOOP;
         close c1;
   END;
   /

有人可以告诉我更多关于 GROUP BY EXPRESSION 的信息吗？ ？

Answer 1

按如下方式更改游标语句（编辑 2！）：

            SELECT staff.bno staff_info,
            branch.street || ' ' || branch.suburb || ' ' || branch.postcode address_info
            FROM deal , staff, contact , property , branch
            where staff.peid = contact.peid
            and contact.pno = property.pno
            and property.pno = deal.pno 
            and staff.peid = branch.peid
            group by staff.bno ,
            branch.street || ' ' || branch.suburb || ' ' || branch.postcode 
            HAVING sum(deal.price) < x;

编辑 - 根据注释：

您的 LOOP 应如下所示：

     FOR R IN C1
     LOOP
        staff_info := R.staff_info;
        address_info := R.address_info;
        dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
        dbms_output.put_line(staff_info ||' '|| address_info);  
     END LOOP;