VENDOR REPORT FOR A FIRM

create table vendor(day date,bought number(5),sold number(5),ret
number(5),profit_loss number(5),remark varchar2(10));
insert into vendor values(to_date('01_jan_09'),100,80,null,null,null);
insert into vendor values(to_date('02_jan_09'),100,99,null,null,null);
insert into vendor values(to_date('03_jan_09'),100,98,null,null,null);
insert into vendor values(to_date('04_jan_09'),100,85,null,null,null);
insert into vendor values(to_date('05_jan_09'),100,90,null,null,null);
insert into vendor values(to_date('06_jan_09'),100,99,null,null,null);
insert into vendor values(to_date('07_jan_09'),100,78,null,null,null);
insert into vendor values(to_date('08_jan_09'),100,88,null,null,null);
insert into vendor values(to_date('09_jan_09'),100,87,null,null,null);
insert into vendor values(to_date('10_jan_09'),100,80,null,null,null);
insert into vendor values(to_date('11_jan_09'),100,85,null,null,null);
insert into vendor values(to_date('12_jan_09'),100,82,null,null,null);
insert into vendor values(to_date('13_jan_09'),100,86,null,null,null);
insert into vendor values(to_date('14_jan_09'),100,90,null,null,null);
insert into vendor values(to_date('15_jan_09'),100,95,null,null,null);
declare
cursor c1 is
select * from vendor order by day;
vven c1%rowtype;
begin
dbms_output.put_line('Date No.bought No.sold No.returned profit/loss remark');
dbms_output.put_line('--------------------------------------------------------');
open c1;
loop
fetch c1 into vven;
exit when c1%notfound;
vven.ret:=vven.bought-vven.sold;
vven.profit_loss:=vven.sold*.75+vven.ret*.30-vven.bought*.70;
if(vven.profit_loss<0)>0) then
vven.remark:='Profit';
else
vven.remark:='Nill';
end if;
end if;
update vendor set ret=vven.ret,profit_loss=vven.profit_loss where
day=vven.day;
dbms_output.put_line('vven.day,vven.bought,vven.sold,vven.ret,vven.profit_loss,re
mark');
end loop;
close c1;
end;
/

select * from vendor;
--------- --------- --------- --------- ----------- ---------- --------- -------
DAY BOUGHT SOLD RET PROFIT_LOSS
--------- --------- --------- --------- ----------- ---------- --------- ------
01-JAN-09 100 80 20 4
02-JAN-09 100 99 1 5
03-JAN-09 100 98 2 4
04-JAN-09 100 85 15 -2
05-JAN-09 100 90 10 1
06-JAN-09 100 99 1 5
07-JAN-09 100 78 22 -5
08-JAN-09 100 88 12 0
09-JAN-09 100 87 13 -1
10-JAN-09 100 80 20 -4
11-JAN-09 100 85 15 -2
12-JAN-09 100 82 18 -3
13-JAN-09 100 86 14 -1
14-JAN-09 100 90 10 1
15-JAN-09 100 95 5 3
--------- --------- --------- --------- ----------- ---------- --------- ------

Set server output on;
declare
cursor c1 is
select*from vendor order by day;
vven c1%rowtype;
begin
dbms_output.put_line('DATE NO.BOUGHT NO.SOLD NO.RETURNED
PROFIT_LOSS REMARK');
dbms_output.put_line('--------------------------------------------------------');
open c1;
loop
fetch c1 into vven;
exit when c1%notfound;
vven.ret:=vven.bought-vven.sold;
vven.profit_loss:=vven.sold*.75+vven.ret*.30-vven.bought*.70;
if(vven.profit_loss<0) then
vven.remark:='LOSS';
else if(vven.profit_loss>0) then
vven.remark:='PROFIT';
else
vven.remark:='NULL';
end if;
end if;
update vendor set ret=vven.ret,profit_loss=vven.profit_loss,remark=vven.remark
where day=vven.day;
dbms_output.put_line('vven.day,vven.bought,vven.sold,vven.ret,vven.profit_loss,vv
en.remark');
end loop;
close c1;
end
/

OUTPUT

--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------
DAY BOUGHT SOLD RET PROFIT_LOSS REMARK
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- --------- --
01-JAN-09 100 80 20 4 LOSS
02-JAN-09 100 99 1 5 PROFIT
03-JAN-09 100 98 2 4 PROFIT
04-JAN-09 100 85 15 -2 LOSS
05-JAN-09 100 90 10 1 PROFIT
06-JAN-09 100 99 1 5 PROFIT
07-JAN-09 100 78 22 -5 LOSS
08-JAN-09 100 88 12 0 NULL
09-JAN-09 100 87 13 -1 LOSS
10-JAN-09 100 80 20 -4 LOSS
11-JAN-09 100 85 15 -2 LOSS
12-JAN-09 100 82 18 -3 LOSS
13-JAN-09 100 86 14 -1 LOSS
14-JAN-09 100 90 10 1 PROFIT
15-JAN-09 100 95 5 3 PROFIT
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------