SQL> --using DML commands in pl/sql blocks SQL> --insert delete update are used as they are without modification SQL> --we must check for error condıtıons usıng exceptıon handlıng (next topic) SQL> desc regions Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25) SQL> begin 2 . SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> begin 2 insert into regions(REGION_ID,REGION_NAME) 3 values(77,'AAAA'); 4 end; 5 / insert into regions(REGION_ID,REGION_NAME) * ERROR at line 2: ORA-06550: line 2, column 15: PL/SQL: ORA-01031: insufficient privileges ORA-06550: line 2, column 3: PL/SQL: SQL Statement ignored SQL> create table myfriends( 2 id number primary key, 3 name varchar2(10) check(name !='Batu')); create table myfriends( * ERROR at line 1: ORA-00955: name is already used by an existing object SQL> create table buddy( 2 id number primary key, 3 name varchar2(10) check(name !='Batu')); Table created. SQL> ed Wrote file afiedt.buf 1 begin 2 insert into buddy(id,name) 3 values(1,'Ayse'); 4* end; SQL> / PL/SQL procedure successfully completed. SQL> select ( from buddy; select ( from buddy * ERROR at line 1: ORA-00936: missing expression SQL> select * from buddy; ID NAME ---------- ---------- 1 Ayse SQL> ed Wrote file afiedt.buf 1 declare 2 i number :=2; 3 begin 4 insert into buddy(id,name) 5 values(i,'Fatma'); 6 i = i+1; 7 insert into buddy(id,name) 8 values(i,'Fatma'); 9 i = i+1; 10 insert into buddy(id,name) 11 values(i,'Fatmagul'); 12 i = i+1; 13 insert into buddy(id,name) 14 values(i,'adssd'); 15 delete from buddy 16 where id=1; 17* end; 18 / i = i+1; * ERROR at line 6: ORA-06550: line 6, column 5: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue. ORA-06550: line 9, column 5: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue. ORA-06550: line 12, column 5: PLS-00103: Encountered the symbol "=" when expecting one of the following: := . ( @ % ; The symbol ":= was inserted before "=" to continue. SQL> ed Wrote file afiedt.buf 1 declare 2 i number :=2; 3 begin 4 insert into buddy(id,name) 5 values(i,'Fatma'); 6 i := i+1; 7 insert into buddy(id,name) 8 values(i,'Fatma'); 9 i := i+1; 10 insert into buddy(id,name) 11 values(i,'Fatmagul'); 12 i := i+1; 13 insert into buddy(id,name) 14 values(i,'adssd'); 15 delete from buddy 16 where id=1; 17* end; SQL> / PL/SQL procedure successfully completed. SQL> select * from buddy; ID NAME ---------- ---------- 2 Fatma 3 Fatma 4 Fatmagul 5 adssd SQL> begin 2 insert into buddy 3 values(2,'aa'); 4 end; 5 / begin * ERROR at line 1: ORA-00001: unique constraint (USER15.SYS_C0017245) violated ORA-06512: at line 2 SQL> --the above message is an exception that is raised when uniqueness of a column is violated SQL> begin 2 update buddy 3 set name='Batu' 4 where id=4; 5 end; 6 / begin * ERROR at line 1: ORA-02290: check constraint (USER15.SYS_C0017244) violated ORA-06512: at line 2 SQL> --the above is the check constraint violation SQL> --we must learn how to handle these exceptions SQL> declare 2 v_id number := &anid; 3 v_name varchar2(10) := &aname; 4 begin 5 ç 6 . SQL> ed Wrote file afiedt.buf 1 declare 2 v_id number := &anid; 3 v_name varchar2(10) := &aname; 4 begin 5 insert into buddy 6 values(v_id,v_name); 7* end; SQL> / Enter value for anid: 9 old 2: v_id number := &anid; new 2: v_id number := 9; Enter value for aname: 'ggg' old 3: v_name varchar2(10) := &aname; new 3: v_name varchar2(10) := 'ggg'; PL/SQL procedure successfully completed. SQL> select * from buddy; ID NAME ---------- ---------- 2 Fatma 3 Fatma 4 Fatmagul 5 adssd 9 ggg SQL> select * from regions; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 3 Asia 4 Middle East and Africa SQL> select region_name 2 from regions 3 where region_id=1; REGION_NAME ------------------------- Europe SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(20); 3 begin 4 select region_name INTO v_region_name 5 from regions 6 where region_id=1; 7 dbms_output.put_line(v_region_name); 8* end; 9 / Europe PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(20); 3 v_region_id number; 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_name); 9* end; SQL> / Europe PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(20); 3 v_region_id number; 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_name); 9 dbms_output.put_line(v_region_id); 10* end; SQL> / Europe 1 PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(20); 3 v_region_id number; 4 begin 5 select region_id, region_name INTO v_region_id, v_region_name 6 from regions; 7 dbms_output.put_line(v_region_name); 8 dbms_output.put_line(v_region_id); 9* end; 10 / declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 SQL> --select into works only when erxactly one row ıs retrıeved! SQL> --ın such cases we must use explicit cursors (next week) SQL> ed Wrote file afiedt.buf 1 declare 2 v_region_name varchar2(20); 3 v_region_id number; 4 begin 5 select region_id, region_name INTO v_region_id, v_region_name 6 from regions 7 where region_id=99; 8 dbms_output.put_line(v_region_name); 9 dbms_output.put_line(v_region_id); 10* end; 11 12 / declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 5 SQL> ü--is select into returns no rows, it also crashes!Q!!!! we must habdle this exceptıon SP2-0734: unknown command beginning "ü--is sele..." - rest of line ignored. SQL> spool off