Find the total purchase of each customer & total sale of each product.

Q. Write a PL/SQL code to find

     a) the total purchase in Rupees of each customer.
     b) total sale of each product.

Finally insert these values in two other tables using this table.

There are ten customers with codes 0 to 9 and five products with codes 0 to 4. The rates of products are Rs. 15, 35, 42, 51 and 60 respectively. Write a program to find the total purchase in Rs. of each customer and total sale of each product using this table and insert these values in two other tables.

Answer:

Step 1: Create Table 'corder'

CREATE TABLE corder ( "Invoice Number " Varchar2(4), "Invoice Date " Date, "Customer Code " Number(1), "Product Code " Number(1), "Quantity Sold " Number(3) )


Create table 'totalpurchase'

CREATE TABLE totalpurchase ( "Customer Code " Number(1), Total Purchase Number(10) )

CREATE TABLE totalsale ( "Product Code " Number(1), Total Sale Number )

Note: The created tables are displayed in the output screen.

Step 2: Create a procedure 'find_sale_purchase'

CREATE OR REPLACE PROCEDURE find_sale_purchase AS
CURSOR c1 IS
SELECT distinct("Customer Code ")
FROM corder;

CURSOR c2(v_ccode number) IS
SELECT *
FROM corder
WHERE "Customer Code "=v_ccode;

CURSOR c3 IS
SELECT distinct("Product Code ")
FROM corder;

CURSOR c4(v_pcode number) IS
SELECT *
FROM corder
WHERE "Product Code "=v_pcode;

rs number;

TOTAL_PURCHASE number:=0;
TOTAL_SALE number:=0;
BEGIN

DELETE
FROM totalpurchase;

DELETE
FROM totalsale;

FOR i IN c1 LOOP
FOR j IN c2(i."Customer Code ") LOOP
SELECT decode(j."Product Code ",0,15,1,35,2,42,3,51,4,60,0) INTO rs
FROM dual;

rs:=rs*j."Quantity Sold ";
TOTAL_PURCHASE:=TOTAL_PURCHASE+rs;
END LOOP;

INSERT INTO totalpurchase
VALUES(i."Customer Code ",
       TOTAL_PURCHASE);

END LOOP;
rs:=0;

FOR i IN c3 LOOP
FOR j IN c4(i."Product Code ") LOOP
SELECT decode(j."Product Code ",0,15,1,35,2,42,3,51,4,60,0) INTO rs
FROM dual;

rs:=rs*j."Quantity Sold ";
TOTAL_SALE:=TOTAL_SALE+rs;
END LOOP;  

INSERT INTO totalsale
VALUES(i."Product Code ",
       TOTAL_SALE);

END LOOP;
END;
/


The above code will create the stored procedure successfully.

Output:

purchase sale
▲