SQL>
SQL> create table my_data(
2 id number,
3 data varchar2(100) );
Table created.
SQL>
SQL> insert into my_data
2 select rownum, to_char( to_date( rownum, 'J' ), 'JSP' ) from all_objects
3 where rownum < 6;
5 rows created.
SQL>
SQL> select * from my_data;
ID DATA
---------- ----------------------------------------------------------------------------------------------------
1 ONE
2 TWO
3 THREE
4 FOUR
5 FIVE
SQL> insert into my_data values ( 6, 'SIC' );
1 row created.
SQL>
SQL> declare
2 l_row my_data%rowtype;
3 begin
4 select * into l_row from my_data where id = 6 for update;
5 update my_data set data = 'SIX' where id = 6;
6 commit;
7 exception
8 when NO_DATA_FOUND then
9 insert into my_data values ( 6, 'SIX' );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from my_data;
ID DATA
---------- ----------------------------------------------------------------------------------------------------
1 ONE
2 TWO
3 THREE
4 FOUR
5 FIVE
6 SIX
6 rows selected.
SQL>
SQL> drop table my_data;
Table dropped.
SQL>