Database Reference
In-Depth Information
EODA@ORA12CR1> begin
2 :deptno := 10;
3 select dname, loc, to_char( last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' )
4 into :dname,:loc,:last_mod
5 from dept
6 where deptno = :deptno;
7 end;
8 /
PL/SQL procedure successfully completed.
which we can see is currently
EODA@ORA12CR1> select :deptno dno, :dname dname, :loc loc, :last_mod lm
2 from dual;
DNO DNAME LOC LM
------- ------------ ---------- ----------------------------------------
10 ACCOUNTING NEW YORK 15-APR-2014 07.04.01.147094 PM -06:00
would use this next update statement to modify the information. The last line does the very important check to make
sure the timestamp has not changed and uses the built-in function
TO_TIMESTAMP_TZ
(
tz
is short for time zone ) to
convert the string we saved in from the
SELECT
statement back into the proper data type. Additionally, line 3 of the
UPDATE
statement updates the
LAST_MOD
column to be the current time if the row is found to be updated:
EODA@ORA12CR1> update dept
2 set dname = initcap(:dname),
3 last_mod = systimestamp
4 where deptno = :deptno
5 and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );
1 row updated.
As you can see, one row was updated, the row of interest. We updated the row by primary key (
DEPTNO
) and
verified that the
LAST_MOD
column had not been modified by any other session between the time we read it first and
the time we did the update. If we were to try to update that same record again, using the same logic but without
retrieving the new
LAST_MOD
value, we would observe the following:
EODA@ORA12CR1> update dept
2 set dname = upper(:dname),
3 last_mod = systimestamp
4 where deptno = :deptno
5 and last_mod = to_timestamp_tz(:last_mod, 'DD-MON-YYYY HH.MI.SSXFF AM TZR' );
0 rows updated.
Notice how 0 rows updated is reported this time because the predicate on
LAST_MOD
was not satisfied. While
DEPTNO 10
still exists, the value at the moment we wish to update no longer matches the timestamp value at the
moment we queried the row. So, the application knows that the data has been changed in the database, based on the
fact that no rows were modified—and it must now figure out what it wants to do about that.
