posted 13 years ago
Hi,
you don't need to use PL/SQL, it can be done in a single merge operation.
If something can be done in pure SQL - do it in SQL, do not use PL/SQL, it will be almost always more efficient and less error-prone.
Database is much better and faster in selecting/joining/crunching rows than fetching/looping and updating them manually in the procedural code.
This merge statement is executed by DB as a single atomic operation, even if it applies to hundreds million of rows.
Of course you can wrap it into a PL/SQL procedure:
The base question is - what is a reason to do it in this way ? Why to always set all rows in this table to null ?
And how many rows both tables have, how frequently this code could be executed and how many users could simultaneously try to execute it?