Anonymous block cursor including group by and for update

Question

I have a table products with following description:

desc products;

 Name                  Null?    Type
---------------------- -------- --------------
 PID                   NOT NULL CHAR(4)
 PNAME                          VARCHAR2(15)
 PRICE                          NUMBER(6,2)
 DISCNT_RATE                    NUMBER(3,2)

My cursor statement is as follows:

declare
  cursor c5 is
    select pid, pname, price
      from products
    c5_rec c5%rowtype
      group by price for update;

begin
  for c5_rec in c5
  loop
    if(c5_rec.price > 100) then
      delete from products where current of c5;
    end if;
  end loop;
end;
/

It gives me error when I don't include group by and rowtype in line 4 and 5 :

integrity constraint (MYID.SYS_C0012393) violated - child record found ORA06512: at line 7

What I'm trying to do is write a PL/SQL anonymous block containing a cursor having GROUP BY clause and then performing update/delete on the resulting table of the cursor in the execute section. Where can I add the group by clause? Where am I going wrong?

Answer 1

Select all of columns in your cursor because datatype of "c5_rec" is the same number of columns and datatype of column in your table.

cursor c5 is
select pid, pname, price, discnt_rate from products

Or, you can change "c5_rec" datatype to be the same of price column.

cursor c5 is
select price from products group by price;
c5_rec c5%products.price%TYPE;

You can also do like this, it's flexible when you need more than 1 column but not all of columns.

declare 
--cursor c5 is
--select pid, pname, price from products
--c5_rec c5%rowtype
--group by price for update;
begin
for c5_rec in (select price from products group by price)
loop
if(c5_rec.price > 100) then
delete from products
where price = c5_rec.price;
end if;
end loop;
end;