Well, I disagree with his examples - but not the gist.
His examples are the form of:
get some rows
process them one by one
Now, if it were:
get some rows
process them
do a bulk update back into database
Then I would say "bulk bind".
The implicit cursor for loop implicitly array fetches 100 at a time - as you can see from his numbers - 100 was the 'sweet spot' (it is in general, the good number to use for this sort of stuff). So, if you are just fetching and processing the rows - a row at a time, just use the cursor for loop.
Here is an example of when I would not explicitly bulk bind:
ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 10;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure process( p_str in out varchar2 )
2 is
3 begin
4 p_str := lower(p_str);
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2><b>
ops$tkyte%ORA10GR2> begin
2 for x in (select username from t)
3 loop
4 process( x.username );
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed. </b>
why complicate that - there would be no benefit. Now, on the other hand, if I called PROCESS
and then put the data back (process in this case would have to be something a LOT more complex than what I did above - in this example, we should not even be using plsql - but assume process is really complex procedural code and is necessary ) then I would, for example:
<b>
ops$tkyte%ORA10GR2> declare
2 l_rowids sys.odciridlist;
3 l_usernames sys.odcivarchar2list;
4 cursor c is select rowid, username from t;
5 l_limit number := 100;
6 begin
7 open c;
8 loop
9 fetch c bulk collect into l_rowids, l_usernames limit l_limit;
10 exit when ( l_rowids.count = 0 );
11
12 for i in 1 .. l_rowids.count
13 loop
14 process( l_usernames(i) );
15 end loop;
16
17 forall i in 1 .. l_rowids.count
18 update t set username = l_usernames(i) where rowid = l_rowids(i);
19 end loop;
20 close c;
21 end;
22 /
PL/SQL procedure successfully completed.
</b>
Now it makes sense to do it explictly - because we get N rows, process them all, and then FORALL update them (bulk update)
If you take the forall away - remove the need to update - I would revert back to my prior example (lots easier to code, read, understand, maintain, debug whatever)
So, I stick with the for x in (select) construct unless I have an opportunity to forall i bulk update/insert/delete - then I would implicitly array fetch - so as to have the data in an array, process it, and bulk it back in.