WHILE & FOR LOOP

Question

I have question while processing records in two different looping method i.e WHILE & FOR LOOP please refer to following codes

 DECLARE
   TYPE rc_emp_type IS RECORD ( empno NUMBER,ename VARCHAR(30),sal   NUMBER, comm  NUMBER);   
  TYPE rc_emp_tab IS TABLE OF rc_emp_type;
  l_emp_rec rc_emp_tab := rc_emp_tab();
  TYPE rc_emp_calc_type IS RECORD ( empno NUMBER,
                                    ename VARCHAR(30),
                                    sal   NUMBER,
                                    comm  NUMBER,
                                    new_sal NUMBER);
     TYPE rc_emp_calc_tab IS TABLE OF rc_emp_calc_type;
  l_emp_calc_rec rc_emp_calc_tab := rc_emp_calc_tab();
  l_emp_fcalc_rec rc_emp_calc_tab := rc_emp_calc_tab();


  l_idx NUMBER;

  l_start_time TIMESTAMP;
  l_end_time TIMESTAMP;
  l_exe_time TIMESTAMP;
BEGIN
   SELECT empno,ename,sal,comm
   BULK COLLECT INTO l_emp_rec
   FROM emp;
   l_idx := l_emp_rec.FIRST;

   WHILE l_idx IS NOT NULL
   LOOP
     l_emp_calc_rec.EXTEND;
     l_emp_calc_rec(l_emp_calc_rec.LAST).empno := l_emp_rec(l_idx).empno;
     l_emp_calc_rec(l_emp_calc_rec.LAST).ename := l_emp_rec(l_idx).ename;
     l_emp_calc_rec(l_emp_calc_rec.LAST).sal := l_emp_rec(l_idx).sal;
     l_emp_calc_rec(l_emp_calc_rec.LAST).comm := l_emp_rec(l_idx).comm;
     l_emp_calc_rec(l_emp_calc_rec.LAST).new_sal := NVL(l_emp_rec(l_idx).sal,0) + NVL(l_emp_rec(l_idx).comm,0);

     l_idx := l_emp_rec.NEXT(l_idx);
   END LOOP;
      FOR l_idx  IN l_emp_rec.FIRST .. l_emp_rec.LAST
    LOOP
     l_emp_fcalc_rec.EXTEND;
     l_emp_fcalc_rec(l_emp_fcalc_rec.LAST).empno := l_emp_rec(l_idx).empno;
     l_emp_fcalc_rec(l_emp_fcalc_rec.LAST).ename := l_emp_rec(l_idx).ename;
     l_emp_fcalc_rec(l_emp_fcalc_rec.LAST).sal := l_emp_rec(l_idx).sal;
     l_emp_fcalc_rec(l_emp_fcalc_rec.LAST).comm := l_emp_rec(l_idx).comm;
     l_emp_fcalc_rec(l_emp_fcalc_rec.LAST).new_sal := NVL(l_emp_rec(l_idx).sal,0) + NVL(l_emp_rec(l_idx).comm,0);
   END LOOP;
  END;

Out of these two above procedure which is the efficient way of looping

Ideally there is no difference in logic and also not in performance. — Popeye, Dec 18 '19 at 02:44

XING · Answer 1 · 2019-12-18T05:12:00.443

Out of these two above procedure which is the efficient way of looping

While dealing with collection,For Loop sometimes throws error when the collection is Sparse. In that case its beneficial to use WHILE LOOP. Both looping mechanism is equal in performance.

Sparse:- A collection is sparse if there is at least one index value between the lowest and highest defined index values that is not defined. For example, a sparse collection has an element assigned to index value 1 and another to index value 10 but nothing in between. The opposite of a sparse collection is a dense one.

Use a numeric FOR loop when Your collection is densely filled (every index value between the lowest and the highest is defined) You want to scan the entire collection, not terminating your scan if some condition is met

Conversely, use a WHILE loop when Your collection may be sparse You might terminate the loop before you have iterated through all the elements in the collection

Thank you for your comments .. 1 more doubt how about the PGA area impacted with both loop... in WHILE LOOP we have to initiate the index values explicitly but for FOR LOOP it will handle automatically... — Vikas, Dec 18 '19 at 05:56
`PGA` (A Program Global Area) is a memory region that contains data and control information for a server process. It is a `non-shared` memory region created by Oracle when a server process is started. WHILE LOOP and FOR Loops take SGA sqlarea and as mentioned, this depends how you use to fetch records to collection. You must use `LIMIT` clause using `FOR LOOP` to avoid high memory utilization. — XING, Dec 18 '19 at 06:21

score 1 · Answer 2 · answered Dec 18 '19 at 06:20

As @XING points the difference is not in how efficient they are, but in what happens with sparse collections. Your example does not face this issue as both are built with bulk collect so there are no gaps in the index values. But this is NOT always the case. The following demo shows the difference between them.

declare
    cursor c_numbers is
        select level+23 num   -- 23 has no particulat significence 
          from dual
         connect by level <= 5; -- nor does 5     
    type base_set is table of c_numbers%rowtype; 

    while_set base_set; 
    for_set   base_set;  

    while_index integer; -- need to define while loop index 

begin
  -- populate both while and for arrays. 
  open  c_numbers;
  fetch c_numbers bulk collect into while_set; 
  close c_numbers; 

  open  c_numbers;
  fetch c_numbers bulk collect into for_set; 
  close c_numbers;

  -- Make sparse 
  while_set.delete(3);
  for_set.delete(3);

  -- loop through with while
  while_index := while_set.first;
  while while_index is not null 
  loop
      begin
         dbms_output.put_line('While_Set(' || 
                              while_index  || 
                              ') = '       || 
                              while_set(while_index).num
                              );
         while_index := while_set.next(while_index);
      exception 
      when others then
         dbms_output.put_line('Error in While_Set(' || 
                              while_index           || 
                              ') Message='          ||
                              sqlerrm
                              );
      end;
  end loop;      

  -- loop through with for
  for for_index in for_set.first .. for_set.last 
  loop
      begin
         dbms_output.put_line('For_Set(' || 
                              for_index  || 
                              ') = '     || 
                              for_set(for_index).num
                              );
      exception 
      when others then
         dbms_output.put_line('Error in For_Set(' || 
                              for_index           || 
                              ') Message='        || 
                              sqlerrm
                              );
      end;
  end loop; 

end;

Also try a for loop with a collection defines as:

type state_populations_t is table of number index by varchar2(20);
state_populations state_populations_t;

And yes, that line is in production code and has run for years,

score 1 · Accepted Answer · answered Dec 18 '19 at 11:58

If you know your collection will be densely-filled, as is the case with a collection filled by BULK COLLECT, I suggest you use a numeric FOR loop. That assumes densely-filled and therefore is most appropriate in that context.

Whenever you are not 100% certain that your collection is densely-filled, you should use a WHILE loop and the FIRST-NEXT or LAST-PRIOR methods to iterate through the collection.

You might argue that you might as well just use the WHILE loop all the time. Performance will be fine, memory consumption is not different....BUT: you might "hide" an error this way. If the collection is supposed to be dense, but it is not not, you will never know.

Finally, there is one way in which the WHILE loop could be a better performer than a FOR loop: if your collection is very sparse (eg, elements populated only in index values -1M, 0, 1M, 2M, 3M, etc.), the FOR loop will raise lots of NO_DATA_FOUND exceptions. Handling and continuing for all those exceptions will make loop execution very slow.

WHILE & FOR LOOP

3 Answers3