PL/SQL: Are you a loop guy or single SQL statement guy?
Oracle generously provides a list of things developers can do to write their PL/SQL code. One item from that list is probably the most single code: going through the table records and update them.
Whenever you need to retrieve and modify more than one row of a database table Oracle is giving you a few options:
1) A single SQL statement. (INSERT/UPDATE/DELETE/MERGE)
or
2) You can use a cursor FOR loop to iterate through the results one row at a time to insert, update and/or delete records.
Let’s forget we have some other options like BULK COLLECT and FORALL – and take them as similar like step 2.
Even though it is possible to use a single SQL statement to modify data in a table, PL/SQL developers rarely take this approach. One of the primary reasons against such a single statement is that it is an all-or-nothing proposition: if anything goes wrong all modifications are rolled back. With PL/SQL loop you have more control over what happens when exceptions occur.
Different developers are taking the different approaches, Frankly, I must say that after working for almost 30 years as a developer I am still not sure which approach I prefer. I really like the beauty of writing one SQL statement for the update – I saw some really cleverly written masterpiece statements. You need just one statement on a few lines and you would need to use huge several line script in the loop to achieve the same result. Also, the performance here is mostly driven by Oracle itself – and believe me, Oracle is better in this than you are – whatever you think.
On the other hand, loops are much better to understand and to get the flow of the written program. A new developer can easily understand someone else's code. Also, I have a bigger option to guard and influence the flow of the changes and transactions.
Most probably every approach has its usage and scenarios where one is better than the other. I still can't decide.
And what about you? Do you prefer loops or single SQL?