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; /