SQL & PL/SQL

Just want to know the Pros and Cons of CURSOR FOR loops. Generally, what do you guys prefer: Associative array to CURSOR FOR loops?


If you use CURSOR FOR loop, isn't it difficult to debug?

Using PL/SQL developer i can't see what are the values are stored in CURSOR FOR loop (in master_proc SP below) as
there are no variables to look at. Not sure if TOAD can show these values

If i had used associative array(master_proc2 shown below) i can see the values stored in the array by clicking on 'View Collection variable' in PL/SQL developer

create table emp2 as select * from emp;


create or replace procedure my_proc(p_empno in number)
as
begin
update emp2 set sal=sal+100 where empno=p_empno;
end;
/

--Using CURSOR FOR loop

create or replace procedure master_proc
as
begin
	for rec in (select empno from emp2 where empno>7700)
	loop
		my_proc (rec.empno);
	end loop;
end;
/

-- Using Associative arrays
create or replace procedure master_proc2
as
type v_empno_type is table of number index by pls_integer;
v_empno v_empno_type;

begin
select empno bulk collect into v_empno from emp2 where empno>7700;

for i in 1..v_empno.count
 loop
    my_proc (v_empno(i));
 end loop;
end;
/