I would rather avoid using an explicit cursor and use a cursor for loop.
FOR i IN
(SELECT column_list FROM table
Remember, a loop would do it row-by-row a.k.a. slow-by-slow.
Also, there is a reason why the cursor for loop is better over the explicit cursor. In the recent Oracle versions, it is optimized in a better way by doing a bulk collect limit 100 internally.
But, it's not just about bulk collecting, we are dealing with the operations we would subsequently do on the array that we have fetched incrementally. We could further improve the performance by using FORALL statement along with BULK COLLECT.
IMO, if it is possible, then do it in pure SQL. Because, SQL and PL/SQL are two different engines. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL statement executor, or SQL engine. There is an overhead associated with each context switch between the two engines. So, if possible avoid context switching by doing it in pure SQL.
Asked in February 2016Viewed 1,450 timesVoted 10Answered 1 times