SQL> set serveroutput on SQL> --DML stataments are used ınsıde pl/sql blocks wıth no change 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> autorecovery OFF pagesize 14 pagesize 14 linesize 80 / 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> set pagesize 120 SQL> insert into countries 2 values('CY','Cyprus',1); insert into countries * ERROR at line 1: ORA-01031: insufficient privileges SQL> --countries table belongs to the user HR SQL> --HR gave all of us (public) only select prievelege SQL> --lets copy this table into our account SQL> create table nazcountries as 2 select * from countries; Table created. SQL> ed Wrote file afiedt.buf 1 create table nazcountries as 2* select * from countries SQL> select * from nazcountries 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 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> desc nazcoutries ERROR: ORA-04043: object nazcoutries does not exist SQL> desc nazcountries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> alter table nazcountries 2 add constraint nazcountries_pk primary key( COUNTRY_ID); Table altered. SQL> imsert into countries SP2-0734: unknown command beginning "imsert int..." - rest of line ignored. SQL> insert into countries 2 value 3 . SQL> insert into nazcountries 2 values('CY','Cyprus',1); 1 row created. SQL> --we can use the insert statment as it is inside our pl/sql programs SQL> ed Wrote file afiedt.buf 1 begin 2 insert into nazcountries 3 values('YY','Yyprus',1); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from nazcountries; 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 CY Cyprus 1 YY Yyprus 1 27 rows selected. SQL> --lets write an anonymous block that promts the user to enter a regionid SQL> --and changes names of all countries in that region to nazland SQL> rollback; Rollback complete. SQL> select * from nazcountries; 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> --lets write an anonymous block that promts the user to enter a regionid SQL> --and changes names of all countries in that region to nazland SQL> update nazcountries 2 set COUNTRY_NAME='Nazland' 3 where REGION_ID=®ion2conquer 4 ; Enter value for region2conquer: 1 old 3: where REGION_ID=®ion2conquer new 3: where REGION_ID=1 8 rows updated. SQL> select * from nazcountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Nazland 1 BR Brazil 2 CA Canada 2 CH Nazland 1 CN China 3 DE Nazland 1 DK Nazland 1 EG Egypt 4 FR Nazland 1 IL Israel 4 IN India 3 IT Nazland 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Nazland 1 SG Singapore 3 UK Nazland 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> rollback; Rollback complete. SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5* end; 6 / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; PL/SQL procedure successfully completed. SQL> select * from nazcountries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Nazland 1 BR Brazil 2 CA Canada 2 CH Nazland 1 CN China 3 DE Nazland 1 DK Nazland 1 EG Egypt 4 FR Nazland 1 IL Israel 4 IN India 3 IT Nazland 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Nazland 1 SG Singapore 3 UK Nazland 1 US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. SQL> rollback 2 ; Rollback complete. SQL> select * from nazcountries; 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> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT); 6* end; 7 / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; 8 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6* end; SQL> / Enter value for region2conquer: 2 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=2; 5 countries now belong to Nazife PL/SQL procedure successfully completed. SQL> --how can we use sql%found SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 dbsm_output.put_line('+++++'||SQL%FOUND||'*****'); 7* end; SQL> / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; dbsm_output.put_line('+++++'||SQL%FOUND||'*****'); * ERROR at line 6: ORA-06550: line 6, column 23: PLS-00306: wrong number or types of arguments in call to '||' ORA-06550: line 6, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 dbsm_output.put_line('+++++'||111||'*****'); 7* end; SQL> / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; dbsm_output.put_line('+++++'||111||'*****'); * ERROR at line 6: ORA-06550: line 6, column 2: PLS-00201: identifier 'DBSM_OUTPUT.PUT_LINE' must be declared ORA-06550: line 6, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 dbms_output.put_line('+++++'||111||'*****'); 7* end; SQL> / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; 8 countries now belong to Nazife +++++111***** PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 dbms_output.put_line('+++++'||SQL%FOUND||'*****'); 7* end; SQL> / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; dbms_output.put_line('+++++'||SQL%FOUND||'*****'); * ERROR at line 6: ORA-06550: line 6, column 23: PLS-00306: wrong number or types of arguments in call to '||' ORA-06550: line 6, column 2: PL/SQL: Statement ignored SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 if SQL%FOUND then 7 dbms_output.put_line('+++++found*****'); 8 else 9 dbms_output.put_line('+++++not found*****'); 10 end if; 11* end; 12 / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; 8 countries now belong to Nazife +++++found***** PL/SQL procedure successfully completed. SQL> --the boolean values sql%found sql%notfound and sql%isopen can only SQL> --be used as condıtıons SQL> --!!!!!!!!!!!!!This is NOT C!!!!!!!!!!!!!!!!!!!!!! SQL> / Enter value for region2conquer: 5 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=5; 0 countries now belong to Nazife +++++not found***** PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 begin 2 update nazcountries 3 set COUNTRY_NAME='Nazland' 4 where REGION_ID=®ion2conquer; 5 dbms_output.put_line(SQL%ROWCOUNT||' countries now belong to Nazife'); 6 if SQL%ISOPEN then 7 dbms_output.put_line('+++++open*****'); 8 else 9 dbms_output.put_line('+++++not open*****'); 10 end if; 11* end; 12 / Enter value for region2conquer: 1 old 4: where REGION_ID=®ion2conquer; new 4: where REGION_ID=1; 8 countries now belong to Nazife +++++not open***** PL/SQL procedure successfully completed. SQL> rollback; Rollback complete. 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> select regio_name 2 from regions 3 where REGION_ID =1; select regio_name * ERROR at line 1: ORA-00904: "REGIO_NAME": invalid identifier SQL> ed Wrote file afiedt.buf 1 select region_name 2 from regions 3* where REGION_ID =1 SQL> / REGION_NAME ------------------------- Europe 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> ed Wrote file afiedt.buf 1 declare 2 name varchar2(20) 3 begin 4 select region_name into name 5 from regions 6 where REGION_ID =1; 7 dbms_output.put_line('the region name is '||name); 8* end; 9 / begin * ERROR at line 3: ORA-06550: line 3, column 1: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: := ; not null default character The symbol ";" was substituted for "BEGIN" to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 name varchar2(20); 3 begin 4 select region_name into name 5 from regions 6 where REGION_ID =1; 7 dbms_output.put_line('the region name is '||name); 8* end; SQL> / the region name is Europe PL/SQL procedure successfully completed. SQL> --print name and region is of a country SQL> --read the country id from keyboard SQL> desc countries Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER SQL> ed Wrote file afiedt.buf 1 --print name and region id of a country 2 --read the country id from keyboard 3 declare 4 name varchar2(15); 5 id number; 6 begin 7 select region_name, region_id into name,id 8 from countries 9 where country_id = '&country_id'; 10 dbms_output.put_line('he answer is '||name||' '||id); 11* end; 12 / Enter value for country_id: BE old 9: where country_id = '&country_id'; new 9: where country_id = 'BE'; select region_name, region_id into name,id * ERROR at line 7: ORA-06550: line 7, column 10: PL/SQL: ORA-00904: "REGION_NAME": invalid identifier ORA-06550: line 7, column 3: PL/SQL: SQL Statement ignored SQL> ed Wrote file afiedt.buf 1 --print name and region id of a country 2 --read the country id from keyboard 3 declare 4 name varchar2(15); 5 id number; 6 begin 7 select country_name, region_id into name,id 8 from countries 9 where country_id = '&country_id'; 10 dbms_output.put_line('he answer is '||name||' '||id); 11* end; SQL> / Enter value for country_id: BE old 9: where country_id = '&country_id'; new 9: where country_id = 'BE'; he answer is Belgium 1 PL/SQL procedure successfully completed. SQL> / Enter value for country_id: CY old 9: where country_id = '&country_id'; new 9: where country_id = 'CY'; --print name and region id of a country * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 7 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 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> --when select into cannot find any rpows it crashes SQL> --we say it raisesd an exception SQL> --this exception has a special name: NO_DATA_FOUND SQL> ed Wrote file afiedt.buf 1* select * from countries SQL> ed Wrote file afiedt.buf 1 --print name and region id of a country 2 --read the country id from keyboard 3 declare 4 name varchar2(15); 5 id number; 6 begin 7 select country_name, region_id into name,id 8 from countries 9 where country_id = '&country_id'; 10 dbms_output.put_line('he answer is '||name||' '||id); 11 exception 12 when no_data_found then 13 dbms_output.put_line('no such countries'); 14* end; 15 / Enter value for country_id: BE old 9: where country_id = '&country_id'; new 9: where country_id = 'BE'; he answer is Belgium 1 PL/SQL procedure successfully completed. SQL> / Enter value for country_id: CY old 9: where country_id = '&country_id'; new 9: where country_id = 'CY'; no such countries PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 name varchar2(15); 3 id number; 4 begin 5 select country_name, region_id into name,id 6 from countries; 7 dbms_output.put_line('he answer is '||name||' '||id); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such countries'); 11* end; 12 / declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 SQL> --select into raises an exception called TOO_MANY_ROWS if more than one row is returned SQL> ed Wrote file afiedt.buf 1 declare 2 name varchar2(15); 3 id number; 4 begin 5 select country_name, region_id into name,id 6 from countries; 7 dbms_output.put_line('he answer is '||name||' '||id); 8 exception 9 when no_data_found then 10 dbms_output.put_line('no such countries'); 11 when too_many_rows then 12 dbms_output.put_line('more than one rowwwwww'); 13* end; 14 / more than one rowwwwww PL/SQL procedure successfully completed. SQL> spol off SP2-0042: unknown command "spol off" - rest of line ignored. SQL> spool off