p11

-- We are going to need a table now to experiment with ...

drop table x
/
create table x (   -- create or replace does not work
   num  number,
   sqr  number,
   cube number
)
/
insert into x values (1, 1, 1)
/
insert into x values (2, 4, 8)
/
insert into x values (3, 9, 27)
/
insert into x values (4, 16, 64)
/


p12

We need to start with a philosophical question:

A = A + B;  (long form)
A = A + 1;  (long form)

means the same thing like

A += B;     (short form)
A++         (short form; note the "1" is invisible here)
++A         THIS IS JAVA.  This is NOT PL/SQL !!!!!!!!

So... what should we teach?
Only the long form?   But why make people work harder?
Only the short form?  But will people understand?
---> Teach both.
Which one first?
Obviously the long form is easier to understand, so we 
start with that.

We face the same problem with table access from PL/SQL.
There is a long form and a short form.  
There are even intermediate forms. :(
In the short form something really important is invisible.

-----------------------------------------------
-- I advance to processing select statements.
-- With a CURSOR.
-- Note that this example only processes 
-- a single line.

declare

   anumber number; -- Three variables.
   asquare number;
   acube number := 0;

   cursor    thomas    is select * from x;
--  ^^^        ^^^     ^^^^^^^^^^^^^^^
-- a "type"  a "name"  an "initalizer"

-- A cursor is like a pointer to the
-- first row in a table.  Later it moves
-- down.

begin

   open thomas;   -- like opening a file
   fetch thomas into   -- load one row
      anumber, asquare, acube;   -- into these variables;
                                 -- Then move down one row;
   close thomas;  -- like "unloading" the table

   dbms_output.put_line(anumber || ' ZZZ ' || asquare || ' ZZZ ' ||  acube);
end;
/    

p13

--  Now let's process two rows.

declare

   anumber number;
   asquare number;
   acube number;

   cursor jane           
      is select * from x;

begin

   open jane;

   fetch jane into
      anumber, asquare, acube;
   dbms_output.put_line(anumber || ' ' || asquare || ' ' ||  acube);

   fetch jane into   -- Now get the second line
      anumber, asquare, acube;
   dbms_output.put_line(anumber || ' XX ' || asquare || ' YY ' ||  acube);

   close jane;

end;
/    

-- This program will send TWO rows of the table
-- to the screen.

p14


--  This sends a whole table to the screen.
--  A few boolean cursor attributes are automatically maintained by
--  the system. 

declare
   delilah number;
   martin number;
   acube number;
   cursor eric  is select * from x;
begin
   open eric;
   loop   -- This is an endless loop
      fetch eric into
         delilah, martin, acube;
      if eric%found -- boolean attribute of the cursor 
                    -- set automatically by the system.
		    -- If there was a line it is
		    -- set to true (1)
      then
         dbms_output.put_line(' ' || delilah || ' --- ' || 
                              martin || ' --- ' ||  acube);
      else 
	 exit;  -- This jumps out of the loop
      end if;
   end loop;
   close eric;

end;
/    

p15


/*

Other cursor attributes
automatically set by the
system.

cursorname%NOTFOUND  
cursorname%ROWCOUNT   number of rows fetched so far
cursorname%ISOPEN

*/

p16

/*
It's hard to believe how much shorter this can be done.
But some people don't "understand" it.
*/

declare
   cursor cathy is select * from x; -- Declare cursor
begin

   for counter in cathy  -- counter is automatically declared.
                        -- As variable that loops over rows.
			-- This FOR loops over all rows.
			-- Inside this loop the column names
			-- are known without declaration!
			-- And... I don't need any % syntax
   loop
      dbms_output.put_line(counter.num || 
			   ' ' || counter.sqr || ' ' || counter.cube);
   end loop;

end;
/    


(No p17)

p18


/*

You can do it even shorter.
You don't even need to declare a cursor.
It is declared implicitly when you use 
a FOR loop.

This is the shortest form.

*/

begin

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

end;
/