SQL> --to see the output of put_line on screen we must type the followıng sql*plus command SQL> set serveroutput on SQL> --write an anonymous block that will print numbers 10 to 5 SQL> --usıng whıle loop SQL> declare 2 i number :=10; 3 begin 4 while i>=5 5 loop 6 dbms_output.put_line(i); 7 i--; 8 end loop; 9 end; 10 / end loop; * ERROR at line 8: ORA-06550: line 8, column 3: PLS-00103: Encountered the symbol "END" when expecting one of the following: := . ( @ % ; The symbol ";" was substituted for "END" to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 i number :=10; 3 begin 4 while i>=5 5 loop 6 dbms_output.put_line(i); 7 i:=i-1; 8 end loop; 9* end; SQL> / 10 9 8 7 6 5 PL/SQL procedure successfully completed. SQL> --write an anonymous block that will print numbers 10 to 5 SQL> --use basıc loop SQL> ed Wrote file afiedt.buf 1 declare 2 i number :=10; 3 begin 4 loop 5 dbms_output.put_line(i); 6 i:=i-1; 7 exit when i=4; 8 end loop; 9* end; 10 / 10 9 8 7 6 5 PL/SQL procedure successfully completed. SQL> -- change step size to 2 SQL> ed Wrote file afiedt.buf 1 declare 2 i number :=10; 3 begin 4 loop 5 dbms_output.put_line(i); 6 i:=i-2; 7 exit when i=4; 8 end loop; 9* end; SQL> / 10 8 6 PL/SQL procedure successfully completed. SQL> --write an anonymous block that will print numbers 10 to 5 SQL> --for loop SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 5 .. 10 4 loop 5 dbms_output.put_line(i); 6 end loop; 7* end; 8 / 5 6 7 8 9 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 10 .. 5 4 loop 5 dbms_output.put_line(i); 6 end loop; 7* end; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in reverse 5 .. 10 4 loop 5 dbms_output.put_line(i); 6 end loop; 7* end; SQL> / 10 9 8 7 6 5 PL/SQL procedure successfully completed. SQL> --print even numbers between 2 and 10 using for loop SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 2 .. 10 4 loop 5 dbms_output.put_line(i); 6 end loop; 7* end; 8 / 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 2 .. 10 4 loop 5 dbms_output.put_line(i); 6 i:=i+1; 7 end loop; 8* end; SQL> / i:=i+1; * ERROR at line 6: ORA-06550: line 6, column 8: PLS-00363: expression 'I' cannot be used as an assignment target ORA-06550: line 6, column 8: PL/SQL: Statement ignored SQL> --for loop counter cannot be modified by the programmer SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 2 .. 10 4 loop 5 if mod(i,2)=0 then 6 dbms_output.put_line(i); 7 end if; 8 end loop; 9* end; SQL> / 2 4 6 8 10 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 --we have no direct control over the loop counter of the for loop 3 for i in 1 .. 5 4 loop 5 dbms_output.put_line(i*2); 6 end loop; 7* end; 8 / 2 4 6 8 10 PL/SQL procedure successfully completed. SQL> --we can use dml commands as they are inside pol/sql blocks SQL> create table friend( 2 id number(2) constraint friend_id_pk primary key, 3 name varchar2(10) 4 ); Table created. SQL> insert into friend(id,name) 2 values(1,'ali'); 1 row created. SQL> select * from friend; ID NAME ---------- ---------- 1 ali SQL> ed Wrote file afiedt.buf 1 begin 2 insert into friend(id,name) 3 values(2,'veli'); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from friend; ID NAME ---------- ---------- 1 ali 2 veli SQL> --write a pl/sql block to increase the id of all employees by 5 SQL> ed Wrote file afiedt.buf 1 begin 2 update friend 3 set id=id+5; 4* end; 5 / PL/SQL procedure successfully completed. SQL> select * from friend; ID NAME ---------- ---------- 6 ali 7 veli SQL> ed Wrote file afiedt.buf 1 begin 2 delete from friend 3 where id=1; 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from friend; ID NAME ---------- ---------- 6 ali 7 veli SQL> --oracle uses implicit cursors to execute sql commands used inside pl/sql blocks SQL> --implicit cursor is not CONTROLLED or MODIFIED by the programmer SQL> -- the programmer can view the cursor attributes: ROWCOUNT, ISOPEN, FOUND,NOTFOUND SQL> --name of the implicit cursor is sql% SQL> ed Wrote file afiedt.buf 1 begin 2 delete from friend 3 where id=1; 4 dbms_output.put_line(sql%rowcount||' rows deleted'); 5* end; SQL> / 0 rows deleted PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 delete from friend 3 where id=1; 4 if sql%found then 5 dbms_output.put_line(sql%rowcount||' rows deleted'); 6 else 7 dbms_output.put_line('no rows deleted'); 8 end if; 9* end; SQL> / no rows deleted PL/SQL procedure successfully completed. SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> --list all info on all regions SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> --list name of region 1 SQL> select REGION_NAME 2 from regions 3 where REGION_ID=1; REGION_NAME ------------------------- Europe SQL> --select statement fetches data SQL> --we must be able to "USE" this data SQL> ed Wrote file afiedt.buf 1 begin 2 select REGION_NAME 3 from regions 4 where REGION_ID=1; 5* end; 6 / select REGION_NAME * ERROR at line 2: ORA-06550: line 2, column 1: PLS-00428: an INTO clause is expected in this SELECT statement SQL> --when we use select the value fetched must be assigned to a varıable SQL> --therefore the select statement is used with a small modification SQL> ed Wrote file afiedt.buf 1 declare 2 v1 varchar2(25); 3 begin 4 select REGION_NAME into v1 5 from regions 6 where REGION_ID=1; 7 dbms_output.put_line(v1); 8* end; SQL> / Europe PL/SQL procedure successfully completed. SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> select * from countries; 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> closecursor OFF pagesize 14 linesize 80 arraysize 15 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 country code, aname and region id of the country that start with C SQL> --print country code, aname and region id of the country that start with K SQL> ed Wrote file afiedt.buf 1 declare 2 v_country_id number(2); 3 v_country_name varchar2(25); 4 v_region_id number(2); 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name); 10* end; 11 / declare * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 6 SQL> ed Wrote file afiedt.buf 1 declare 2 v_country_id char(2); 3 v_country_name varchar2(25); 4 v_region_id number(2); 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name); 10* end; SQL> / Kuwait PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_country_id countries.country_id%type; 3 v_country_name varchar2(25); 4 v_region_id number(2); 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name); 10* end; SQL> / Kuwait 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; 4 v_region_id countries.region_id%type; 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name); 10* end; SQL> / Kuwait 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; 4 v_region_id countries.region_id%type; 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name||' '||v_country_id||' '||v_region_id); 10* end; SQL> / Kuwait KW 4 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; 4 v_region_id countries.region_id%type; 5 begin 6 select country_id, country_name, region_id into V_country_id, v_country_name, v_region_id 7 from countries 8 where upper(country_name) like 'K%'; 9 dbms_output.put_line(v_country_name); 10 dbms_output.put_line(v_country_id); 11 dbms_output.put_line(v_region_id); 12* end; SQL> / Kuwait KW 4 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; 4 v_region_id countries.region_id%type; 5 v_region_name regions.region_name%type; 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_name); 14 dbms_output.put_line(v_country_id); 15 dbms_output.put_line(v_region_id); 16 dbms_output.put_line(v_region_name); 17* end; 18 / Kuwait KW 4 Middle East and Africa PL/SQL procedure successfully completed. SQL> spool off