|
SQL>
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,sysdate, 123.12, sysdate+1, '12 noon', 'CA',1, null, 'Happy Birthday to you');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,sysdate, 50.98, sysdate+2, '1 pm', 'CA',7, 'name1', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,sysdate, 35.99, sysdate+3, '1 pm', 'VS',2, 'Tom', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,sysdate, 19.95, sysdate+4, '5 pm', 'CA',2, 'Mary', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,sysdate, 10.95, sysdate+5, '4:30 pm', 'VS', 2, 'Jack', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (6, 9,sysdate-3, 22.95, sysdate+6, '1 pm', 'VS', 2, 'Mike', 'Happy Birthday' );
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (7, 9,sysdate-4, 35.95, sysdate+7, '12 noon', 'VS', 2, 'Jason', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (8, 12,sysdate-6, 35.95, sysdate+8, '12 noon', 'DI',3, 'Lite', 'Happy New Year');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (9, 12,sysdate-9, 75.95, sysdate+9, '12 noon', 'CA',7, 'Sara', 'Happy New Year' );
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (10, 4, sysdate, 19.95, sysdate, '2:30 pm', 'VG',2, 'Mary', 'Happy Birthday');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (11, 2, sysdate, 30.00, sysdate+2, '1:30 pm', 'VG',2, 'Mary', 'Happy New Year');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (12, 7, sysdate-3, 21.95, sysdate-2, '3:30 pm', 'CA',2, 'Mary', 'Happy New Year');
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (13, 7, sysdate, 21.95, sysdate, '3:30 pm', 'CA',2, 'Mary', 'Happy Birthday');
1 row created.
SQL>
SQL> create table sale(
2 gift_id integer
3 ,product_id integer
4 ,quantity number(4,0)
5 ,price number(7,2)
6 ,primary key (gift_id ,product_id)
7 );
Table created.
SQL>
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> insert into sale(gift_id, product_id, quantity, price) values(11, 8, 1, 30.00);
1 row created.
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 fetch c1 into giftIDValue, totalValue;
9 while c1%found loop
10 update gift
11 set total_price = totalValue
12 where gift_id = giftIDValue;
13 fetch c1 into giftIDValue, totalValue;
14 end loop;
15 close c1;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table gift;
Table dropped.
SQL> drop table sale;
Table dropped.
SQL>
|