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)