Select for update : Update Data « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Update Data

Select for update

    
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>

   
    
    
  

Related examples in the same category