1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
|
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
SQL>
SQL> Create table mni_test (
2 X integer primary key,
3 A number,
4 B number,
5 C number,
6 D number
7 )
8 /
Table created
SQL>
SQL> Insert All
2 Into mni_test Values (1, 0.0, 0.0, 850.600, 0.0)
3 Into mni_test Values (2, 130.470, 0.0, 250.600, 394.330)
4 Into mni_test Values (3, 0.0, 0.0, 0.0, 0.0)
5 Select *
6 From dual
7 /
3 rows inserted
SQL> commit;
Commit complete
SQL>
SQL> Select systimestamp from dual
2 /
SYSTIMESTAMP
-------------------------------------------------
19/11/18 16:51:50,198000 +01:00
SQL>
SQL> Update mni_test
2 Set a = Least(0,a),
3 b = Least(0,b),
4 c = Least(0,c),
5 d = Least(0,d)
6 Where a > 0
7 Or b > 0
8 Or c > 0
9 Or D > 0
10 /
2 rows updated
SQL>
SQL> Commit
2 /
Commit complete
SQL>
SQL> Select n.x,
2 Case When n.a != a.a Then 'A modifié ancienne valeur:'||To_Char(a.a) End A_modifie,
3 Case When n.b != a.b Then 'B modifié ancienne valeur:'||To_Char(a.b) End B_modifie,
4 Case When n.c != a.c Then 'C modifié ancienne valeur:'||To_Char(a.c) End C_modifie,
5 Case When n.d != a.d Then 'D modifié ancienne valeur:'||To_Char(a.d) End D_modifie
6 From mni_test AS OF TIMESTAMP TO_TIMESTAMP('2018-11-19 16:51:49', 'YYYY-MM-DD HH24:MI:SS') a
7 Join
8 mni_test n
9 On n.x = a.x
10 Where n.a != a.a
11 Or n.b != a.b
12 Or n.c != a.c
13 Or n.d != a.d
14 /
X A_MODIFIE B_MODIFIE C_MODIFIE D_MODIFIE
--------------------------------------- ------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------------------------------------------------
1 C modifié ancienne valeur:850,6
2 A modifié ancienne valeur:130,47 C modifié ancienne valeur:250,6 D modifié ancienne valeur:394,33
SQL> |
Partager