查询 Oracle PLSQL

Question

这是当我运行以下脚本时收到的错误消息，

    ERROR at line 1:
    ORA-00979: not a GROUP BY expression
    ORA-06321: at "s3398293.P2", line 7
    ORA-06321: at "s3398293.P2", line 18
    ORA-06321: at line 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;
    /

我试图获取 The Staff.bno 和 x > 的分支地址。总和(交易.价格)

    Can Someone tell me more about GROUP BY EXPRESSION! ?

Answer 1

您需要将 x > 移动到sum(deal.price) 放入 group by 之后的 HAVING 子句中。您编写的内容无效（忽略双 WHERE），因为 where 子句是在 SUM/GROUP BY 之前计算的。例如

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

编辑：忘记按您选择的其他表达式进行分组。

Answer 2

您的代码的一个明显问题是 CURSOR。游标是变量，因此需要在 DECLARATION 部分中定义。然后你需要打开包体中的curosr。您还需要检查 FETCH 是否实际检索到一行。

create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
       CURSOR c1 for
            SELECT staff.bno ,
                branch.street||' '||branch.suburb||' '||branch.postcode
                FROM deal , staff, contact , property 
                where staff.peid = contact.peid
                and contact.pno = property.pno
                and property.pno = deal.pno 
                group by staff.bno
                HAVING x > sum(deal.price);
      staff_info  staff.bno%TYPE;
      address_info address%TYPE;
    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;

---

通常不需要这样的游标。使用隐式游标效率更高。

create or replace 
    PROCEDURE p2(x NUMBER ) 
    as
    BEGIN
         for r in ( SELECT staff.bno ,
                           branch.street||' '||branch.suburb||' '||branch.postcode as address_info
                    FROM deal , staff, contact , property 
                    where staff.peid = contact.peid
                    and contact.pno = property.pno
                    and property.pno = deal.pno 
                    group by staff.bno
                    HAVING x > sum(deal.price))
         loop
            dbms_output.put_line('BRANCH# '||' '||'ADDRESS');
            dbms_output.put_line(r.bno ||' '|| r.address_info);  
         end loop;
    END;