SQL> create table sale(
2 gift_id integer
3 ,product_id integer
4 ,quantity number(4,0)
5 ,price number(7,2)
6 ,total_order_price number(9,2)
7 ,primary key (gift_id ,product_id)
8 );
Table created.
SQL> -- order_item table inserts
SQL> insert into sale(gift_id, product_id, quantity, price)values(1, 2, 10, 23.00 );
1 row created.
SQL> insert into sale(gift_id, product_id, quantity, price)values(2, 1, 1, 23.11 );
1 row created.
SQL>
SQL>
SQL>
SQL> --set termout on
SQL> DECLARE
2 giftIDValue sale.gift_id%type;
3 totalValue sale.price%type;
4 cursor c1 is
5 select gift_id, sum(price * quantity) from sale group by gift_id;
6 begin
7 open c1;
8
9 fetch c1 into giftIDValue, totalValue;
10
11 while c1%found loop
12 update sale set price = totalValue where gift_id = giftIDValue;
13
14 fetch c1 into giftIDValue, totalValue;
15
16 end loop;
17
18 close c1;
19 end;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table sale;
Table dropped.