Use data referenced by cursor to update table

    

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.

Related examples in the same category

1.	Cursor with a single value
2.	Update statement with cursor variable
3.	Server-side cursor variables.
4.	A cursor FOR loop.
5.	Cursor Variable Example 2
6.	Read full table data from a cursor
7.	Single column cursor
8.	Use Complex cursor to simplify the pl/sql logic
9.	Use cursor to do full table scan
10.	Use number functions when creating a cursor value
11.	Form sentences from database data
12.	Two identical queries to demonstrate the impact of changes

Use data referenced by cursor to update table : Cursor Value « Cursor « Oracle PL / SQL

Related examples in the same category