Cursor vs FOR Loop in Oracle

Question

So I have a block of PLSQL code that will drop all indexes as shown in the following block of code:

DECLARE
DRP_STMNT   VARCHAR2(100) := ''; 
BEGIN
    FOR I IN (SELECT INDEX_NAME FROM USER_INDEXES) LOOP
        DRP_STMNT := 'DROP INDEX ' || I.INDEX_NAME;
        EXECUTE IMMEDIATE DRP_STMNT;
    END LOOP;
END;
/

That will do the job but should I use a cursor instead of embedding my SELECT statement within the FOR LOOP?

Does the FOR LOOP look at the entire table or does it process one row at a time?

What do you guys suggest using and why?

@APC would that be the same case if I want to create it as a stored procedure? — samg
– samg, Commented Aug 22, 2018 at 20:39

Littlefoot · Accepted Answer · 2018-08-22 20:31:00Z

3

The result will be just the same.

Though, you'll consume some more energy as you'd have to type more characters as you have to declare a cursor and cursor variable, open a cursor, start the loop, fetch from it, take care about exiting the loop, close a cursor.

When you use a cursor FOR loop (as you did), there's much less typing as Oracle does lots of that for you. For free.

answered Aug 22, 2018 at 20:31

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

5 Comments

samg Over a year ago

But will what I have right now perform the same way as if I had a cursor (which is basically pointing at each row at a time)?

Littlefoot Over a year ago

You'd be doing it one-by-one either way, no difference.

Boneist Over a year ago

@Littlefoot that's not strictly true; Oracle can and will turn cursor for loops into fetch ... bulk collect limit 100 style loops behind the scenes, assuming the plsql_optimize_level is >=2. For the OP: it doesn't matter whether the cursor is explicitly (declare cursor some_curosr is ...; begin for rec in some_cursor loop ...) or implicitly for rec in (select ....) loop, the behaviour of the loop is the same.

Littlefoot Over a year ago

Ah! That's for advanced users, @Boneist. Didn't know that (obviously, I'm not that advanced person). Thank you!

Boneist Over a year ago

@Littlefoot every day is a learning day *{:-) And now you are an advanced user, under your definition!

Collectives™ on Stack Overflow

Cursor vs FOR Loop in Oracle

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related