SQL> begin 2 dbms_output.put_line('hello'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> begin 2 dbms_output.put_line('hello'); 3 end; 4 / hello PL/SQL procedure successfully completed. SQL> --print numbers between 1 and 5 on screen SQL> -- use a while loop SQL> declare 2 x number := 1; 3 begin 4 while x<=5 5 loop 6 dbms_output.put_line('x'); 7 x := x+1; 8 end loop; 9 end; 10 / x x x x x PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 while x<=5 5 loop 6 dbms_output.put_line(x); 7 x := x+1; 8 end loop; 9* end; SQL> / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> --print numbers between 1 and 5 on screen SQL> -- use basıc loop SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x := x+1; 7 exit when x=6; 8 end loop; 9* end; 10 / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x := x+1; 7 exit when x>=6; 8 end loop; 9* end; SQL> / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x := x+1; 7 exit when x>5; 8 end loop; 9* end; SQL> / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x := x+1; 7 exit when x=6; 8 end loop; 9* end; SQL> / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> --print numbers in steps of 2 SQL> ed Wrote file afiedt.buf 1 declare 2 x number := 1; 3 begin 4 loop 5 dbms_output.put_line(x); 6 x := x+2; 7 exit when x>=6; 8 end loop; 9* end; SQL> /8 1 3 5 PL/SQL procedure successfully completed. SQL> --print numbers between 1 and 5 on screen SQL> --use for loop SQL> ed Wrote file afiedt.buf 1 begin 2 for x in 1 .. 5 3 loop 4 dbms_output.put_line(x); 5 end loop; 6* end; 7 / 1 2 3 4 5 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for x in 1 .. 5 3 loop 4 dbms_output.put_line(x); 5 x :=x+1; 6 end loop; 7* end; SQL> / x :=x+1; * ERROR at line 5: ORA-06550: line 5, column 5: PLS-00363: expression 'X' cannot be used as an assignment target ORA-06550: line 5, column 5: PL/SQL: Statement ignored SQL> --for loop counter is declared and incremented/decremented implicitely SQL> --the programmer may not moıdify the loop counter SQL> ed Wrote file afiedt.buf 1 begin 2 for x in 1 .. 10 3 loop 4 if mod(x,2)=0 then 5 dbms_output.put_line(x); 6 end if; 7 end loop; 8* end; SQL> / 2 4 6 8 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for x in 1 .. 5 3 loop 4 dbms_output.put_line(x*2); 5 end loop; 6* end; 7 / 2 4 6 8 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 x number :=5; 3 begin 4 while x>= 1 5 loop 6 dbms_output.put_line(x); 7 x :=x-1; 8 end loop; 9* end; 10 / 5 4 3 2 1 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for x in 5 .. 1 3 loop 4 dbms_output.put_line(x*2); 5 end loop; 6* end; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 for x in reverse 1 .. 5 3 loop 4 dbms_output.put_line(x); 5 end loop; 6* end; SQL> / 5 4 3 2 1 PL/SQL procedure successfully completed. SQL> --we can use the dml commands with no modifications inside pl/sql blocks SQL> create table movies 2 ( id number(2) constraint movies_id_pk primary key, 3 title varchar2(20)); Table created. SQL> insert into movies 2 SQL> insert into movies(id ,title) 2 values(1,'X-Men'); 1 row created. SQL> select * from movies; ID TITLE ---------- -------------------- 1 X-Men SQL> ed Wrote file afiedt.buf 1 begin 2 insert into movies(id ,title) 3 values(2,'Wolverine'); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from movies; ID TITLE ---------- -------------------- 1 X-Men 2 Wolverine SQL> --write an anonymous block to incresea all movie ids by 3 SQL> ed Wrote file afiedt.buf 1 begin 2 update movies 3 set id=id+3; 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from movies; ID TITLE ---------- -------------------- 4 X-Men 5 Wolverine SQL> --when sql commands are used inside pl/sql blocks an implicit cursor called sql% is created SQL> --the programmer has no control over this implicit cursor. the execution is performed by the dbms_engine SQL> --but the programmer may see the cursor attributes SQL> ed Wrote file afiedt.buf 1 begin 2 update movies 3 set id=id+3; 4 dbms_output.put_line(sql%rowcount||' rows updated'); 5* end; 6 / 2 rows updated PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 delete from movies 3 where id=&movieid; 4 dbms_output.put_line(sql%rowcount||' rows deleted'); 5* end; 6 / Enter value for movieid: 1 old 3: where id=&movieid; new 3: where id=1; 0 rows deleted PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 delete from movies 3 where id=&movieid; 4 if sql%notfound then 5 dbms_output.put_line('no rows deleted'); 6 else 7 dbms_output.put_line(sql%rowcount||' rows deleted'); 8 end if; 9* end; 10 / Enter value for movieid: 1 old 3: where id=&movieid; new 3: where id=1; no rows deleted PL/SQL procedure successfully completed. SQL> / Enter value for movieid: 3 old 3: where id=&movieid; new 3: where id=3; no rows deleted PL/SQL procedure successfully completed. SQL> / Enter value for movieid: 4 old 3: where id=&movieid; new 3: where id=4; no rows deleted PL/SQL procedure successfully completed. SQL> select * from movies; ID TITLE ---------- -------------------- 7 X-Men 8 Wolverine SQL> begin 2 delete from movies 3 where id=&movieid; 4 if sql%notfound then 5 dbms_output.put_line('no rows deleted'); 6 else 7 dbms_output.put_line(sql%rowcount||' rows deleted'); 8 end if; 9 end; 10 / Enter value for movieid: 7 old 3: where id=&movieid; new 3: where id=7; 1 rows deleted PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into movies(id,title) 3 values(1,'AAAAA'); 4 insert into movies 5 values(2,'EEEEE'); 6 if sql%notfound then 7 dbms_output.put_line('no rows modified'); 8 else 9 dbms_output.put_line(sql%rowcount||' rows modified'); 10 end if; 11* end; 12 / 1 rows modified PL/SQL procedure successfully completed. SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> ed Wrote file afiedt.buf 1 select * from regions 2* where id=1 SQL> / where id=1 * ERROR at line 2: ORA-00904: "ID": invalid identifier SQL> select * from regions 2 where REGION_ID =1; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe SQL> ed Wrote file afiedt.buf 1 begin 2 select * from regions 3 where REGION_ID =1; 4* end; SQL> / select * from regions * ERROR at line 2: ORA-06550: line 2, column 2: PLS-00428: an INTO clause is expected in this SELECT statement SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_id number(2); 3 v_region_name varchar2(25); 4 begin 5 select region_id,region_name INTO v_region_id, v_region_name 6 from regions 7 where REGION_ID =1; 8* end; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_id number(2); 3 v_region_name varchar2(25); 4 begin 5 select region_id,region_name INTO v_region_id, v_region_name 6 from regions 7 where REGION_ID =1; 8 dbms_output.put_line(v_region_id||' '||v_region_name); 9* end; 10 / 1 Europe PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_id number(2); 3 v_region_name varchar2(25); 4 begin 5 select region_id,region_name INTO v_region_id, v_region_name 6 from regions 7 where REGION_ID =1; 8 dbms_output.put_line(v_region_id||' '||v_region_name); 9* end; SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select * from countries 2 ; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> blockterminator "." (hex 2e) pagesize 14 pagesize 14 linesize 80 set pagesize 100 SQL> / CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> --print the name , country id and region name of the country whose name starts with K SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select COUNTRY_ID,COUNTRY_NAME,REGION_NAME 2 from regions,countries 3 where regions.REGION_ID=countries.REGION_ID and upper(COUNTRY_NAME)='K%'; no rows selected SQL> SQL> ed Wrote file afiedt.buf 1 select COUNTRY_ID,COUNTRY_NAME,REGION_NAME 2 from regions,countries 3* where regions.REGION_ID=countries.REGION_ID and upper(COUNTRY_NAME) like 'K%' SQL> / CO COUNTRY_NAME REGION_NAME -- ---------------------------------------- ------------------------- KW Kuwait Middle East and Africa SQL> ed Wrote file afiedt.buf 1 declare 2 v_country_id countries.country_id%type;--anchored declaration 3 v_country_name countries.country_name%type;--anchored declaration 4 v_region_name regions.region_name%type;--anchored declaration 5 begin 6 select COUNTRY_ID,COUNTRY_NAME,REGION_NAME into v_country_id,v_country_name,v_region_name 7 from regions,countries 8 where regions.REGION_ID=countries.REGION_ID and upper(COUNTRY_NAME) like 'K%'; 9 dbms_output.put_line(v_country_id||v_country_name||v_region_name); 10* end; 11 / KWKuwaitMiddle East and Africa PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_country_id countries.country_id%type;--anchored declaration 3 v_country_name countries.country_name%type;--anchored declaration 4 v_region_name regions.region_name%type;--anchored declaration 5 v_region_id regions.region_id%type;--anchored declaration 6 begin 7 select COUNTRY_ID,COUNTRY_NAME,REGION_id into v_country_id,v_country_name,v_region_id 8 from countries 9 where upper(COUNTRY_NAME) like 'K%'; 10 select region_name into v_region_name 11 from regions 12 where region_id=v_region_id; 13 dbms_output.put_line(v_country_id||v_country_name||v_region_name); 14* end; 15 / KWKuwaitMiddle East and Africa PL/SQL procedure successfully completed. SQL> spool off