a while loop in oracle

Question

Hi I wrote a while loop in Oracle SQL. I think the syntax is good, but it cannot be excute for some reason. Can someone help me to check where the problems is? Many thanks!

It only uses one table "test_fruit", with colume "price".

BEGIN
  WHILE (select sum(price) from test_fruit) <50 LOOP
    update test_fruit set price = price + 5; 
    EXIT WHEN (select max(price) from test_fruit) >20; 
  END LOOP;
END;

By the way, What I am trying to do is to update table, and increase the price of each row, until the sum is 50. Also max price in each row cannot exceed 20. thanks!

What's the error? What are you expecting the loop to do? What is it actually doing? — Mike Dinescu, Sep 25 '13 at 15:41
It would also help to show a short table of test values that illustrate what you're trying to do. — Adam Miller, Sep 25 '13 at 15:45

Yann39 · Accepted Answer · 2013-09-25T16:12:12.700

7

There are several ways to do it but you can try something like this :

DECLARE
  l_sumprice INTEGER := 0;
  l_maxprice INTEGER := 0;
BEGIN
  WHILE l_sumprice < 50 AND l_maxprice < 20 LOOP
    UPDATE test_fruit SET price = price + 5;
    SELECT sum(price), max(price) INTO l_sumprice, l_maxprice FROM test_fruit;
  END LOOP;
END;

Try the fiddle

edited Sep 25 '13 at 16:12

answered Sep 25 '13 at 15:59

Yann39

14,285
11
56
84

a while loop in oracle

1 Answers1