存储过程COMPILE错误-Java 学习之路

有人可以帮助我，这是我运行此脚本时收到的错误消息

第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的信息！？

1 回答

更改游标语句如下（ EDIT 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;

EDIT - as per comments:

您的 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;

回复于 2024-05-03T11:02:42+08:00

存储过程COMPILE错误

1 回答

相关问题