Hello,
I am a new PL/SQL user, and as such am having an issue with a loop.
I have craeted a functino that will return the day of the week from a given date.
I ahve also created a procedure to call that function, but here's the trick: every date corresponds with a basketid #, and I need to output every basketid# with corresponding day of the week each item was ordered (what the function brings up).
I seem to have gotten the loop to function correctly, but I am having issues with it as it will only output the FIRST basketid and day of the week listed in the cursor. There are 14 iterations that it needs to go through, and it does all 14, but it never moves to the 'next' value in the basketid column.
Can you guys help?
Here's the code:
--function to extract day from date entered
CREATE OR REPLACE FUNCTION day_ord_sf
(p_orderdate IN bb_basket.dtordered%type)
RETURN varchar2
IS
lv_day_txt VARCHAR2(14);
BEGIN
SELECT TO_CHAR(p_orderdate, 'DAY')
INTO lv_day_txt
FROM bb_basket
WHERE p_orderdate=dtordered;
RETURN lv_day_txt;
END;
/
--procedure to call up the function
CREATE OR REPLACE PROCEDURE day_order_sp
(p_idbasket IN bb_basket.idbasket%type)
IS
lv_orderdate date:='23-JAN-07';
lv_day_txt varchar2(14);
lv_basket_num bb_basket.idbasket%type;
BEGIN
DECLARE
CURSOR cur_dayorder IS
SELECT
idbasket, dtordered
FROM bb_basket;
BEGIN
SELECT idbasket
INTO lv_basket_num
FROM bb_basket
WHERE lv_orderdate=dtordered;
BEGIN
FOR rec_dayorder IN cur_dayorder LOOP
IF lv_basket_num=p_idbasket THEN
lv_day_txt:=day_ord_sf(lv_orderdate);
END IF;
DBMS_OUTPUT.PUT_LINE('Basket # '||lv_basket_num||' was ordered on '||lv_day_txt);
END LOOP;
END;
END;
/
My output looks like this:
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
Basket #3 was ordered on TUESDAY.
It won't even change if I input the number 5 for the p_idbasket...what gives?