p19

/*
Now PROJECTION
*/

begin
   for carol in 
        (select num, cube from x)
   loop
         dbms_output.put_line(carol.num || 
	   ' CUBE IS:  ' || carol.cube);
   end loop;
end;
/    

/* 

Note:  In the loop body we can only use columns
that were mentioned in the cursor-select statement.
So, if we don't have a star.... then we cannot use all
the columns!!

On the other hand, a star never hurts!

See p20.sql for the same result, with a star.

*/

p20


begin

   for counter in 
        (select * from x)
   loop
         dbms_output.put_line(counter.num || 
			   ' ' || counter.cube);
   end loop;

end;
/    


/* 

If we have a * we can use all the columns in
the loop body.

*/


p21



/*
Now selecting a few rows:
A SELECT in the sense of Relational Algebra
*/

begin

   for counter in 
        (select * 
	from x
	where sqr > 4)
   loop
         dbms_output.put_line(counter.num || ' ' || counter.sqr ||
			   ' ' || counter.cube);
   end loop;

end;
/    

p21b

/*
Now selecting with a PL/SQL variable in the select statement.
*/

declare

   anumber number;

begin

   anumber := 1+1;
   for counter in 
        (select * 
	from x
	where num > anumber)
   loop
         dbms_output.put_line(counter.num || ' ' || counter.sqr ||
			   ' ' || counter.cube);
   end loop;

end;
/    


p21c



/*
Nesting cursor loop in normal loop is okay (of course!).
*/


declare

   anumber number;

begin

   for i in 1.. 12
   loop   
      anumber := i + 1;
      dbms_output.put_line(i);
      for counter in 
           (select * 
   	from x
   	where num > anumber)
      loop
            dbms_output.put_line(counter.num || ' ' || counter.sqr ||
   			   ' ' || counter.cube);
      end loop;
   end loop;
   
end;
/    
   


p21d



/*
What if I use a local variable with the 
same name as a column?
In this case, there is no syntax error.
However, the second num in the WHERE appears
to be the column name ALSO.  
We can show this, by changing the > sign to >=.
num > num is never true.
num >= num is always true.
The column name "shadows" the variable name.
*/


declare
   num number;
begin
   for i in 1.. 12
   loop   
      num := i + 1;
      dbms_output.put_line(i);
      for counter in 
           (select * from x where num > num)    -- This is never true
      loop
            dbms_output.put_line(counter.num || ' ' || counter.sqr ||
   			   ' ' || counter.cube);
      end loop;
   end loop;
end;
/