Home » SQL & PL/SQL » SQL & PL/SQL » help with cursor (10g release 2)
help with cursor [message #322654] Sun, 25 May 2008 16:47 Go to next message
horax
Messages: 34
Registered: March 2008
Member
I don't understand why my cursor is getting a false value where it should be true.
Any help would be greatly appreciated.

I'm trying to set a value='yes' in column PREMIUM if the state='VA'. For some reason, in the part of my cursor-for-loop where it should get a true statement, it returns false.

Here's the code, resulting query, and rollback statements:
SQL> DECLARE
  2   CURSOR add_cur
  3    IS
  4    SELECT state
  5    FROM bb_shopper;
  6   lv_premium  VARCHAR2(10);
  7  BEGIN
  8  FOR rec_cur IN add_cur LOOP 
  9   IF rec_cur.state='VA' THEN
 10    UPDATE bb_shopper
 11     SET premium='yes';
 12   ELSE
 13    UPDATE bb_shopper
 14     SET premium='no';
 15   END IF;
 16  END LOOP;
 17  END;
 18  / 
 
PL/SQL procedure successfully completed.
 
SQL> select state, premium from bb_shopper;
 
ST PREMIUM
-- --------------------
NC no
VA no
NC no
VA no
VA no
VA no
NY no
 
7 rows selected.
 
SQL> rollback;
 
Rollback complete.
 
SQL> select state, premium from bb_shopper;
 
ST PREMIUM
-- --------------------
NC
VA
NC
VA
VA
VA
NY
 
7 rows selected.



The error MUST be in rows 8-9, but I can't find anything wrong with it.
Re: help with cursor [message #322655 is a reply to message #322654] Sun, 25 May 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>UPDATE bb_shopper SET premium='yes'

What does this statement do?
Re: help with cursor [message #322657 is a reply to message #322655] Sun, 25 May 2008 17:39 Go to previous messageGo to next message
horax
Messages: 34
Registered: March 2008
Member
I see where you're headed here.

I need to do an INSERT or something different than an UPDATE...right?

What would you recommend?
Re: help with cursor [message #322658 is a reply to message #322654] Sun, 25 May 2008 17:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What would you recommend?
To use SQL correctly to meet your requirements.
Re: help with cursor [message #322659 is a reply to message #322658] Sun, 25 May 2008 17:54 Go to previous messageGo to next message
horax
Messages: 34
Registered: March 2008
Member
Kind of figured that.

When you're at the bank and they ask you how much money you want to withdraw, do you say, "However much I want?"

I'm asking which command you would utilize to do what I need to do. Obviously I don't know, or else I would not have posted.
Re: help with cursor [message #322660 is a reply to message #322654] Sun, 25 May 2008 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Why are you using PL/SQL since only SQL is needed to do as desired?
Re: help with cursor [message #322667 is a reply to message #322654] Sun, 25 May 2008 21:29 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
If you want to solve this on your own, go back to your documentation, and study the "WHEN CURRENT OF" clause real good. Figure out what the implication of including it in your PL/SQL might be.

Then, figure out how you good do it with one SQL statement. If your need the PL/SQL for some reason you can still wrap that one statement in PL/SQL; you don't need a loop.
Re: help with cursor [message #322668 is a reply to message #322654] Sun, 25 May 2008 21:35 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Warning: spoiler included. If you want to solve this on your own, and just want a clue, read my previous post.

Here is the code:
 8  FOR rec_cur IN add_cur LOOP 
  9   IF rec_cur.state='VA' THEN
 10    UPDATE bb_shopper
 11     SET premium='yes';
 12   ELSE
 13    UPDATE bb_shopper
 14     SET premium='no';
 15   END IF;


Note there is no "CURRENT OF" clause. Nothing to restrict this to the row which was just fetched. So what is happening is that after each fetch, the entire table is being updated. When the row fetched has state='VA', every row has premium set to 'yes'. When the row fetched has state != 'VA', then every row has premium set to 'no'. The last row must not have been a Virginia row.

To fix the PL/SQL, add something to restrict the update to the current row. But the best fix is to replace everything with
UPDATE BB_SHOPPER
         SET premium = 'yes'
        WHERE state = 'VA';

[Updated on: Sun, 25 May 2008 22:43] by Moderator

Report message to a moderator

Re: help with cursor [message #322696 is a reply to message #322668] Sun, 25 May 2008 23:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This last update is incomplete. It does not update the other rows to 'no'.
Re: help with cursor [message #322760 is a reply to message #322654] Mon, 26 May 2008 03:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try

UPDATE bb_shopper
 SET premium = DECODE(NVL(state,'99'),'VA','yes','no')


HTH.
Re: help with cursor [message #322770 is a reply to message #322760] Mon, 26 May 2008 03:38 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Great to have you to post basic SQL statement.
Too bad for the OP and others that will not learn out to find it.

Regards
Michel
Previous Topic: Update trigger
Next Topic: Date data type
Goto Forum:
  


Current Time: Sat Dec 10 18:12:41 CST 2016

Total time taken to generate the page: 0.17419 seconds