Convert pure for loop into for loop using cursor

Question

Please, help me to convert this loop statement:

BEGIN
   FOR employee_rec in (SELECT * FROM BONUS)
   LOOP
      IF employee_rec.BONVALUE > 500 THEN
        UPDATE BONUS
        SET BONUS.TAX = BONUS.BONVALUE * 0.12 WHERE employee_rec.BONVALUE = BONUS.BONVALUE;
      ELSIF 
      -- ...
      END IF;

   END LOOP;
END;

into statement where CURSOR being used.

Generally we would like to see what sort of attempt you have made at solving the problem yourself - as well as where you are hitting problems. Requests for code generally don't go very well. — Sh4d0wsPlyr
– Sh4d0wsPlyr, Commented Dec 21, 2015 at 15:48
I didn't make attempt bc I don't fully understand syntax and usage of cursor. Hope example of conversion will clarify for me both aspects. — Rudziankoŭ
– Rudziankoŭ, Commented Dec 21, 2015 at 15:51
You're already using a cursor. That's what the SELECT * FROM BONUS is. If you want to name that cursor, well, it's not that difficult... simply define a cursor in the declaration section and give it a name. However, it looks like in this instance, you don't need a cursor-for-loop at all; I'm guessing that the other IF clauses are also updates to the bonus table? If so, you ought to be able to achieve this in a single update statement — Boneist
– Boneist, Commented Dec 21, 2015 at 15:52
As an aside, I've met a lot of Oracle developers in my time who (wrongly) don't refer to an implicit cursor as a cursor. Just an observation. — Drumbeg
– Drumbeg, Commented Dec 21, 2015 at 16:05

Drumbeg · Accepted Answer · 2015-12-21 16:00:03Z

3

What you have is a loop based on an implicit cursor definition. It sounds like you want a loop based on an explicit cursor definition.

As mentioned in the comments. Just define your cursor explicitly in the declaration section.

DECLARE
   CURSOR cur_bonus IS
   SELECT *
   FROM bonus;
BEGIN
   FOR employee_rec IN cur_bonus
   LOOP
      IF employee_rec.BONVALUE > 500 THEN
        UPDATE BONUS
        SET BONUS.TAX = BONUS.BONVALUE * 0.12 WHERE employee_rec.BONVALUE = BONUS.BONVALUE;
      ELSIF 
      -- ...
      END IF;

   END LOOP;
END;

answered Dec 21, 2015 at 16:00

Drumbeg

1,9441 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert pure for loop into for loop using cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related