How to do update loop in Oracle

Question

I'm trying to learn more about using Oracle, and I'm trying to understand loops and how to use them. I've got this :

loop
update table
set amount=(amount+3)
exit when amount>=20
end loop;

Currently amount is 12, so I'd expect it to add 3 to amount until it reaches 21, because then it will be greater than 20. But the error I get is "ORA-00933: SQL command not properly ended" I've also got loop and exit underlined in red.

Am I missing something simple, or am I misunderstanding how to use loops?

You are missing a semicolon on the update statement. However, there is no need at all to use pl/sql to do this — OldProgrammer
– OldProgrammer, Commented Mar 5, 2018 at 16:17
'SQL command not properly ended' is referring to lines 3 and 4 line where you are missing semi-colons. — BriteSponge
– BriteSponge, Commented Mar 5, 2018 at 16:19

Kaushik Nayak · Accepted Answer · 2018-03-05 16:50:38Z

2

You should use a BEGIN..END block for writing PL/SQL. For simulating your update operation through loops, you may use RETURNING INTO clause with a variable.

Although this solution is for your understanding, in real time it is not an efficient way to run Update statement in loop like this. It would be better to run a single update statement setting the final value.

Note: This works for a single row update.

SET SERVEROUTPUT ON
DECLARE 
v_amount NUMBER;

BEGIN
 LOOP

    UPDATE yourtable 
     SET amount = amount + 3 -- WHERE clause
       RETURNING amount 
    INTO v_amount;

    DBMS_OUTPUT.PUT_LINE(v_amount);

    EXIT when v_amount >= 20;
 END LOOP;
END;
/

15
18
21

edited Mar 5, 2018 at 16:50

answered Mar 5, 2018 at 16:27

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

user3634056 Over a year ago

Thanks, this works. You're right that a single update statement would be best, this is a good way for me to see how I would do a loop if I needed to.

ArtBajji · Accepted Answer · 2018-03-06 03:25:50Z

1

I understand that the objective here is to learn LOOPs in Oracle, yet the example chosen is not ideal. You may want to check about ContextSwitching. Avoid issuing SQLs/DMLs inside loops as far as possible. Also never use PL/SQL to perform that which can be done using SQL unless it costs too much CPU.

update yourtable
set amount = amount + trunc((20 - amount)/3) * 3
where amount <= 20;

commit;

Instead, I would advise that a good place to start learning about LOOPs in Oracle in a structured way is using OracleDocumentation.

answered Mar 6, 2018 at 3:25

ArtBajji

9607 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to do update loop in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related