INSERT INTO SELECT vs. INSERT from Cursor in PL/SQL

Question

So I have this project I'm working on at work and I've noticed a lot of people using a the INSERT INTO SELECT method:

INSERT INTO candy_tbl (candy_name, 
                       candy_type, 
                       candy_qty) 
SELECT food_name, 
       food_type, 
       food_qty 
       FROM food_tbl WHERE food_type = 'C';

However, I use the following cursor method:

FOR rec IN ( SELECT 
             food_name, 
             food_type, 
             food_qty 
             FROM food_tbl WHERE food_type = 'C') 
LOOP INSERT INTO candy_tbl(candy_name, 
                           candy_type, 
                           candy_qty) 
                    VALUES(rec.food_name,
                           rec.food_type, 
                           rec.food_qty) 
END LOOP;

This will be going into a PL/SQL package. My question is, which is usually the 'preferred' method and when? I usually choose the cursor method because it gives me a little more flexibility with exception handling. But, I could see how it might be a performance issue when inserting a whole lot of records.

Solution

The FOR LOOP will require a fetch for each row from the CURSOR. The INSERT in the loop will happen 1 by 1. PLSQL runs in a PLSQL engine and SQL runs in a SQL engine, so the FOR LOOP: - runs in the PLSQL engine - sends the query to the SQL engine to execute the query and open a cursor then switches back to the PLSQL engine - each loop does a FETCH from the CURSOR then does an INSERT meaning back to the SQL engine then returning to the PLSQL engine

each switch between SQL and PLSQL as well as each FETCH is expensive.

The INSERT INTO SELECT will be sent to the SQL engine once and run there until done and then back to PLSQL.

Other advantages exist, but that is the main PLSQL difference between the 2 methods.

Comments

[Joe Taras]

The first is more efficient. You must use a cursor when you can write a SQL query to accomplish your task

[Tony Andrews]

@JoeTaras I think you meant to say "should not" rather than "must"?

[Joe Taras]

@TonyAndrews: Yes, important is try to create a right SQL query. As alternative solution a cursor (because is less efficient than a query)