p22

/*
Now it's time to learn how to insert data.
The insert syntax is the same as in SQL.

Insert is simple and does not require something
like a cursor because the program itself does not
have to handle a table of data.

The difficulty of processing a whole table of data
inside of a program is one version of the "impedance 
mismatch problem."  

*/

begin

   insert into x values (5, 25, 125);

end;
/    

p22b


/*
More inserts.
This also shows a local function.
*/
declare
   anu number; asq number; acu number;
   function mysq(x number) 
   return number is
   begin
      return x * x;
   end;
   function mycu(x number) 
   return number is
   begin 
      return x * x * x;
   end;
begin
   anu := 7;
   asq := mysq(7);
   acu := mycu(7);
   insert into x values (anu, asq, acu);
end;
/    


p22c


/*
Insert in a loop.
*/

declare
   anu number;
   asq number;
   acu number;
   function mysq(y number) 
   return number is
   begin
      return y * y;
   end;
   function mycu(y number) 
   return number is
   begin 
      return y * y * y;
   end;

begin
   for i in 5..2000
   loop

      anu := i;
      asq := mysq(i);
      acu := mycu(i);
      insert into x values (anu, asq, acu);
  
   end loop;
end; 
/    

p23



/*
Can we delete?  Yes we can.

*/

declare
    abc number;

begin
    abc := 6;

    delete from x where num = 5;
    delete from x where num = abc;

end;
/    

p24


/*
Can we update?  Yes we can!

*/



begin

    update x 
    set num = 5
    where num = 4;

end;
/    


p24b


/*
Can we update with variable?  Yes we can!

*/


declare
   mywhat number;
   mywhere number;

begin
    mywhere := 5;
    mywhat := 25;

    update x 
    set sqr = mywhat
    where num = mywhere;

end;
/    


(no p24c)

p24d



// This program seems to work.
// But it DOES NOT CHANGE THE DATABASE

begin 
   for j in (select * from x)
   loop
       j.num := 0;
       dbms_output.put_line(j.num);
   end loop;
end;



(no p24e)


p24f

-- Do you always need a cursor?  
-- Well, this DOES NOT work.

declare
   n number;
begin 
   n := select count(*) from x;
   dbms_output.put_line(n);
end;


p24g



/* However, THESE WORK: */

declare
   n number;
begin
   select count(*) into n from x;
   dbms_output.put_line(n);
end;


declare
   a number;
   b number := 0;
begin
   select num, sqr into a, b  from x where num=2;
   dbms_output.put_line(a || ' ' || b);
end;



declare
   a number;
   b number := 0;

   i number;
begin

  for i in 1..4
  loop
      select num, sqr into a, b  from x where num=i ;
      dbms_output.put_line(a || ' ' || b);
  end loop;
end;

p25


/*
Can we alter?  NO, we cannot!!!
(Without advanced packages.)
*/

begin

   create table temp (num number);   -- Does NOT work.
   alter table x add (sum number);   -- Does NOT work.

end;
/    

This works:

begin
   execute immediate 'create table temp (num number)';
   execute immediate 'alter table x add (sum number)';   -- Does work.
end;

Note: The string may NOT contain a ;
So you can't combine two DDL commands
into one execute immediate.

Note: "truncate table" also requires 
execute immediate.

Even though it seems to be a DML operation.


p25b



/*
Can we create from inside of
a PL/SQL program?  NO, we cannot!!!
(Unless we execute immediate.)

*/

begin

   create table zztop (     -- does NOT work
      a  number,
      b  number,
      c  number
   );

end;
/    

p26


/* Update cursor addition */
/* This works */

-- We would like to add all values in a row and then
-- save the sum in a new column.  We already created
-- the new column with an "alter" before.
-- But here is how you do the adding.

alter table x add (fourth number)


declare
   cursor janet is 
      select * from x for update;

begin

   for jane in janet 
   loop
      update x 
      set fourth = jane.num + jane.sqr + jane.cube
      where current of janet;
   end loop;

end;
/




BTW: Renaming a column.

alter table y rename column a 
to aa
/

(no p27)