Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Please help with Cursor Loop

631898Apr 17 2008 — edited Apr 17 2008
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 15 2008
Added on Apr 17 2008
13 comments
885 views