I have a project which creates a procedure and takes Date parameter in the argument. Upon execution of the procedure, it displays the Order Id and Order details in the Order Date column using nested cursor loop. I am also trying to display an error message for the date which is not available in my order tables. The code to create procedure is as
create or replace procedure a05_order_details_by_date(p_order_date in date)
as
v_msg varchar2(400);
v_order_id ppl_order_headers.order_id%type;
v_order_date ppl_order_headers.order_date%type := p_order_date;
cursor cur_orders is
select order_id, order_date
from ppl_order_headers
where extract(month from order_date) = extract(month from v_order_date) and
extract(year from order_date) = extract(year from v_order_date) ;
cursor cur_order_details is
select ppl_order_details.plant_id, ppl_order_details.quantity, ppl_order_details.price, sum(ppl_order_details.quantity*ppl_order_details.price) as Extcost
from ppl_order_details
join ppl_order_headers on ppl_order_details.order_id = ppl_order_headers.order_id
where ppl_order_headers.order_id = v_order_id
group by ppl_order_details.plant_id, ppl_order_details.quantity, ppl_order_details.price;
begin
<< order_loop >>
for rec_orders in cur_orders
loop
case
when (extract(month from v_order_date)) != (extract(month from rec_orders.order_date))
and
(extract(year from v_order_date))!= (extract(year from rec_orders.order_date)) then
pr.pr('There are no orders for the requested month: ' || to_char(v_order_date, 'Month YYYY'));
else
pr.pr('Order ID' || ' ' || 'Order Date');
v_order_id := rec_orders.order_id;
v_msg := rpad(v_order_id, 7) || ' ' || rec_orders.order_date;
pr.pr(v_msg);
<< order_details >>
for rec_order_details in cur_order_details
loop
pr.pr(' ' || ' Plant ID' || ' ' || 'Quantity' || ' ' || 'Price' || ' ' || 'ExtCost' );
v_msg := ' ' || rec_order_details.plant_id || ' ' || rec_order_details.quantity ||
' ' || rec_order_details.price || ' ' || rec_order_details.Extcost;
pr.pr(v_msg);
end loop;
end case;
end loop;
end;
/
The result is fine when the date is given within the dataset. but when I try to run the procedure with Future date or date which is not in dataset, it is supposed to show an error message. But instead, it just shows "Anonymous Block completed".
You're looping over orders where the order date year/month matches your argument year/month; so your loop only contains matching data. But then inside that loop you say:
case
when (extract(month from v_order_date)) != (extract(month from rec_orders.order_date))
and
(extract(year from v_order_date))!= (extract(year from rec_orders.order_date)) then
Firstly you probably meant that to be 'or' not 'and', otherwise the same month in a different year would still be valid. But much more importantly that case condition can never be true. You're inside a loop which already dictates that the month and year for this record must match your date argument, because of the cursor's where clause.
So this case statement is redundant. If any data is found by the cursor then you will always go into the 'else' clause. If no data is found then you won't come into the cursor loop at all (as Tony Andrews pointed out), so the case isn't even evaluated.
You could generate your message by counting the number of records found as you go around the loop, or by setting a boolean variable; and then checking that state after the loop:
...
-- initial state is that we haven't seen any matching records
v_record_found boolean := false;
begin
<< order_loop >>
for rec_orders in cur_orders
loop
-- we have seen records matching the argument
v_record_found := true;
pr.pr('Order ID' || ' ' || 'Order Date');
v_order_id := rec_orders.order_id;
...
<< order_details >>
for rec_order_details in cur_order_details
loop
pr.pr(...)
...
pr.pr(v_msg);
end loop;
end loop;
-- was the flag changed inside the loop?
if not v_record_found then
pr.pr('There are no orders for the requested month: '
|| to_char(v_order_date, 'Month YYYY'));
end if;
end;
If there are no matching records you don't go into the loop, and the flag is never changed to true. But if there are matching records it is set to true, and the 'no orders' messages isn't shown.