Hi guys im planning to generate my report using stored procedure (oracle), but what i want to display is if the procedure is executed, i want the end of the report message to be ( End of the report for "specified year" and if there isnt any data at all i want to display ( " NO Record for "specified year" )
CREATE OR REPLACE PROCEDURE booking_pending_report(in_month IN NUMBER,in_year IN NUMBER) AS
cursor pending_booking_basedondate_cursor is
select bo.journeystatus As "Journey Status",bo.bookingdate As "Booking Date",bo.fromdestination As "Location From",bo.todestination As "Desired Location", co.customerid As "Customer ID", co.customername As "Customer Name", co.customertel AS "Customer Phone No", concat(TRUNC((SYSDATE - TO_DATE(co.customerdob, 'DD-MON-YYYY'))/ 365.25),' years old') as "Customer Age", co.customergender as "Gender"
from booking bo, customer co
where bo.journeystatus = 'Pending' and bo.bookingdate between to_date('01-'||in_month||'-'||in_year,'dd-mm-yyyy') AND to_date('30-'||in_month||'-'||in_year,'dd-mm-yyyy') and bo.customerid = co.customerid
group by bo.journeystatus, bo.fromdestination,bo.todestination, bo.bookingdate, co.customerid, co.customername, co.customertel, co.customerdob, co.customergender
order by bookingdate;
pending_booking_basedondate_record pending_booking_basedondate_cursor%ROWTYPE;
v_count number(2);
v_countforchecking number(2);
BEGIN
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(rpad('.',30)||rpad('ON DEMAND REPORT',34));
DBMS_OUTPUT.PUT_LINE(rpad('.',6)||'Booking that are still on "Pending" in year '||in_year);
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
OPEN pending_booking_basedondate_cursor;
v_count:=1;
v_countforchecking :=0;
Loop
fetch pending_booking_basedondate_cursor into pending_booking_basedondate_record;
if pending_booking_basedondate_cursor%NOTFOUND and v_countforchecking = 1 then
DBMS_OUTPUT.PUT_LINE(rpad('.',23)||'End of RECORD for the year '||in_year||'!!!');
CLOSE pending_booking_basedondate_cursor;
EXIT;
elsif pending_booking_basedondate_cursor%NOTFOUND then
DBMS_OUTPUT.PUT_LINE(rpad('.',23)||'NO RECORD for the year '||in_year||'!!!');
CLOSE pending_booking_basedondate_cursor;
EXIT;
else
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('('||v_count||')'||' Customer ('||pending_booking_basedondate_record."Customer Name"||')');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('Booking Status :'||pending_booking_basedondate_record."Journey Status");
DBMS_OUTPUT.PUT_LINE('Booking Date :'||pending_booking_basedondate_record."Booking Date");
DBMS_OUTPUT.PUT_LINE('Location From :'||pending_booking_basedondate_record."Location From");
DBMS_OUTPUT.PUT_LINE('Location From :'||pending_booking_basedondate_record."Desired Location");
DBMS_OUTPUT.PUT_LINE('Customer Age :'||pending_booking_basedondate_record."Customer Age");
DBMS_OUTPUT.PUT_LINE('Customer Phone number :'||pending_booking_basedondate_record."Customer Phone No");
DBMS_OUTPUT.PUT_LINE('Customer Gender :'||pending_booking_basedondate_record."Gender");
v_count:=v_count+1;
v_countforchecking := v_countforchecking+1;
end if;
END Loop;
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(rpad('.',30)||'END OF REPORT');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------------------');
END;
/
I want my report to be something like this (IF theres record)
SQL> exec booking_pending_report(3,2021)
----------------------------------------------------------------------------
. ON DEMAND REPORT
. Booking that are still on "Pending" in year 2021
-----------------------------------------------------------------------------
--------------------------------------------------
(1) Customer (Hollee)
--------------------------------------------------
Booking Status :Pending
Booking Date :22/MAR/2021
Location From :Kedah
Location From :Pahang
Customer Age :31 years old
Customer Phone number :013-5941276
Customer Gender :Female
--------------------------------------------------
(2) Customer (Casey)
--------------------------------------------------
Booking Status :Pending
Booking Date :24/MAR/2021
Location From :Perak
Location From :Johor
Customer Age :21 years old
Customer Phone number :016-3818244
Customer Gender :Female
. **End OF RECORD** for the year 2021!!!
-----------------------------------------------------------------------------
. END OF REPORT
-----------------------------------------------------------------------------
but the output kept giving me this result!
SQL> exec booking_pending_report(3,2021)
-----------------------------------------------------------------------------
. ON DEMAND REPORT
. Booking that are still on "Pending" in year 2021
-----------------------------------------------------------------------------
--------------------------------------------------
(1) Customer (Hollee)
--------------------------------------------------
Booking Status :Pending
Booking Date :22/MAR/2021
Location From :Kedah
Location From :Pahang
Customer Age :31 years old
Customer Phone number :013-5941276
Customer Gender :Female
--------------------------------------------------
(2) Customer (Casey)
--------------------------------------------------
Booking Status :Pending
Booking Date :24/MAR/2021
Location From :Perak
Location From :Johor
Customer Age :21 years old
Customer Phone number :016-3818244
Customer Gender :Female
. **NO RECORD** for the year 2021!!! >> it doesnt make sense as there is record
-----------------------------------------------------------------------------
. END OF REPORT
-----------------------------------------------------------------------------
I only wanna display the no record when:
SQL> exec booking_pending_report(3,2028)
-----------------------------------------------------------------------------
. ON DEMAND REPORT
. Booking that are still on "Pending" in year 2028
-----------------------------------------------------------------------------
. **NO RECORD** for the year 2028!!!
-----------------------------------------------------------------------------
. END OF REPORT
-----------------------------------------------------------------------------
I suspect there is something wrong with my if else statement within the loop! Please help!
Aucun commentaire:
Enregistrer un commentaire