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