I say:
I have a pretty simple mantra when it comes to developing database software:
o You should do it in a single SQL statement if at all possible.
o If you cannot do it in a single SQL Statement, then do it in PL/SQL.
o If you cannot do it in PL/SQL, try a Java Stored Procedure.
o If you cannot do it in Java, do it in a C external procedure.
o If you cannot do it in a C external routine, you might want to seriously
think about why it is you need to do itÂ…
which means
DO NOT:
for x in ( select * from t )
loop
insert into t2 values X;
end loop;
DO INSTEAD:
insert into t2 select * from t;
which means
DO NOT:
for x in (select * from t)
loop
for y in (select * from t2 where t2.c = x.C)
loop
.....
DO INSTEAD:
for x in ( select * from t, t2 where t.C = t2.c )
loop
You have to use code sometime - to process the result of a query for example, you have transactions that take MULTIPLE sql statements. For example, to transfer $50 from checking to savings probably takes AT LEAST two sql statements (an update to the account table, an insert to a transaction table - and probably most people would use two updates to account to debit and then credit - although they could do it in one)
So, take the multiple sql statements OUT OF YOUR CODE in the client, put them in a stored procedure and use that.