PL/SQL code to prepare a report for a news paper whole seller

PL/SQL code to prepare a report for a news paper wholeseller with the following details
WEEK,PURCHASE,SALES,PURCHASE,RETURN,PROFIT,LOSS

  • Cost of paper 60ps
  • Selling prise 758ps
  • Loss on purchase return 30ps



CREATE TABLE NEWS(WEEK INT NOT NULL,PURCHASE INT,SALES INT,RETURN INT,PROFIT NUMBER(10,2),LOSS NUMBER(10,2));
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(1,100,80);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(2,80,75);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(3,100,94);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(4,100,60);
INSERT INTO NEWS(WEEK,PURCHASE,SALES) VALUES(5,100,77);
SELECT *FROM NEWS;
DECLARE
I INT:=1;
BOT INT;
SID INT;
RTN INT;
PRF NUMBER(10,2);
BEGIN
WHILE I<=5
LOOP
SELECT SALES INTO SID FROM NEWS WHERE WEEK=I;
SELECT PURCHASE INTO BOT FROM NEWS WHERE WEEK=I;
RTN:=BOT-SID;
PRF:=SID*0.75+RTN*0.30-BOT*0.60;
UPDATE NEWS SET PROFIT=PRF,LOSS=0,RETURN=RTN WHERE WEEK =I AND PRF>=0;
UPDATE NEWS SET PROFIT=0,LOSS=-1*PRF,RETURN=RTN WHERE WEEK =I AND PRF<=0;
I:=I+1;
END LOOP;
END;


SELECT *FROM NEWS;

 

 

 

Tables:

before updating

 

1

after executing the calculations

2

Advertisements