CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
nlgatewood
nlgatewood

Reputation: 53

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

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.

Upvotes: 2

Views: 16477

Answers (4)

Niko
Niko

Reputation: 1

U Can try add Begin for first query before for rec and add END also after end loop, like below

BEGIN
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;
END;

Upvotes: 0

Belayer
Belayer

Reputation: 14934

If you really need the flexibility of cursor processing but better performance there is a third intermediate option available - BULK COLLECT and FORALL with save exceptions option. However, the trade off is much more code complexity. The following is the basic structure.

declare
    exception error_in_forall ; 
    pragma exception_init (error_in_forall, -24381);

    cursor c_select is ( select ... ) ;
    type   c_array_type table of c_select%rowtype;     
    v_select_data  c_array_type ; 

begin 
    open c_select; 
    loop
        fetch c_select
         bulk collect 
         into v_select_data; 
        forall rdata in v_select_data.first .. v_select_data.last save exceptions
            insert into ( ... ) values (v_select_data(rdata).column ... ) ;    
exceptions 
    when error_in_forall then
        <Process Oracle generated bulk error collection > 
end ;

When complete if any errors occurred during execution of the Insert then the exceptions fires once. Oracle having built a SQL%BULK_EXCEPTIONS collection containing the index value and the error code of each. See the PL/SQL Language Reference for your version for details.

Upvotes: 0

Paul
Paul

Reputation: 47

The first one is faster since it's basically a single transaction aka set-based-processing.

The latter operates by row, for a very large table there will be a large difference in performance.

Upvotes: 0

Ray DeBruyn
Ray DeBruyn

Reputation: 41

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.

Upvotes: 3

Related Questions