CODEWITHSUNDEEP
oracle-databaseplsql
Max
Max

Reputation: 19

Sales report pl/sql

I am trying to create a procedure that generates sales report for any given month. To invoke the procedure I can pass only one parameter that is the month to get the number of sales as well as the total sale.I joined three tables that are: orders, odetails and parts as in the select query below. My problem is the procedure I created does not generate any record when I pass the date to it! Could you please show me how to alter?

create or replace PROCEDURE SALE_REPORT (P_DATE IN DATE ) 
   AS
V_PNO ODETAILS.PNO%TYPE;
NUMBER_SALES ODETAILS.QTY%TYPE;
TOTAL_SALE  PARTS.PRICE%TYPE;
V_SHIPPED ORDERS.SHIPPED%TYPE;
BEGIN
 DBMS_OUTPUT.PUT_LINE('           REPORT          ');
 DBMS_OUTPUT.PUT_LINE('*****************************');
  FOR REC IN 
  (SELECT OD.PNO, SUM(OD.QTY) "NUMBER OF SALES", SUM(NVL(OD.QTY,0) * NVL  
                                   (P.PRICE,0)) AS TOTAL_SALE, O.SHIPPED 
  INTO V_PNO, NUMBER_SALES, TOTAL_SALE, V_SHIPPED 
  FROM ORDERS O, ODETAILS OD, PARTS P
  WHERE O.ONO = OD.ONO
  AND P.PNO = OD.PNO
  AND O.SHIPPED = TO_CHAR(P_DATE, 'DD-MON-yyyy')
  GROUP BY  O.SHIPPED, OD.PNO
  ORDER BY SUM(OD.QTY) DESC) LOOP

  DBMS_OUTPUT.PUT_LINE('SHIP DATE '||  V_PNO);
  DBMS_OUTPUT.PUT_LINE('SHIP DATE '|| V_SHIPPED);
  DBMS_OUTPUT.PUT_LINE('NUMBER OF SALES '|| NUMBER_SALES);
  DBMS_OUTPUT.PUT_LINE('TOTAL SALES '|| TOTAL_SALE );
  EXIT WHEN SQL%NOTFOUND;
   END LOOP;
END SALE_REPORT;

Upvotes: 0

Views: 1245

Answers (1)

Timekiller
Timekiller

Reputation: 3126

First of all, your syntax.

If you're using a FOR loop, you don't need SELECT ... INTO construct, use REC variable you declared - actually, I'm rather surprised that this even compiled. EXIT WHEN SQL%NOTFOUND; is also unnecessary as FOR loop automatically ends one there are no more records. Also, give columns better alias, you're going to use them as members of REC.

It should look like this:

FOR REC IN 
  (SELECT OD.PNO, SUM(OD.QTY) NUMBER_SALES, SUM(NVL(OD.QTY,0) * NVL  
                                   (P.PRICE,0)) AS TOTAL_SALE, O.SHIPPED 
  FROM ORDERS O, ODETAILS OD, PARTS P
  WHERE O.ONO = OD.ONO
  AND P.PNO = OD.PNO
  AND O.SHIPPED = TO_CHAR(P_DATE, 'DD-MON-yyyy')
  GROUP BY  O.SHIPPED, OD.PNO
  ORDER BY SUM(OD.QTY) DESC) LOOP

  DBMS_OUTPUT.PUT_LINE('SHIP DATE '||  REC.PNO);
  DBMS_OUTPUT.PUT_LINE('SHIP DATE '|| REC.SHIPPED);
  DBMS_OUTPUT.PUT_LINE('NUMBER OF SALES '|| REC.NUMBER_SALES);
  DBMS_OUTPUT.PUT_LINE('TOTAL SALES '|| REC.TOTAL_SALE );
END LOOP;

I would also make this query into a cursor and move to the declaration, but that's matter of preference I guess.

Now, if this still doesn't print anything, then your query doesn't return any rows. Take it to SQL worksheet, substitute P_DATE manually and debug.

EDIT:

If o.shipped is any date within month, and you're passing a date as 01-12-2015 for example, then your query will only return for rows where o.shipped exactly matches your P_DATE, that is, first day of the month. You should either convert dates to strings containing month and year:

...
AND TO_CHAR(O.SHIPPED, 'mm.yyyy') = TO_CHAR(P_DATE, 'mm.yyyy')
...

or use TRUNC function with a second parameter MONTH, which will transform your dates to the first day of month.

...
AND TRUNC(O.SHIPPED, 'MONTH') = TRUNC(P_DATE, 'MONTH')
...

Upvotes: 1

Related Questions