Querying Oracle PLSQL

This is the error message I get when I run the below scripts

    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;
    /

I am trying to get The Staff.bno and address of the branch where x > sum(deal.price)

    Can Someone tell me more about GROUP BY EXPRESSION! ?

---

You need to move x > sum(deal.price) into a HAVING clause after the group by. What you have written is invalid (ignoring the double WHERE) because where clause is evaluated before the SUM/GROUP BY. e.g.

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);

Edit: forgot to group by the other expression you are selecting.

---

One obvious problem with your code is that CURSOR. Cursors are variables and so need to be defined in the DECLARATION section. Then you need to open the curosr in the package body. Also you need to check whether the FETCH actually retrieves a row.

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;

---

Cursors like this are not usually required. It is way more efficient to use an implicit cursor.

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;