给定一个随时间变化的source表，这里我将定义source_before和source_after以显示表中发生的差异，但实际上它是同一个表

create table source_before (
binary_path varchar2(40),
hostname varchar2(40),
change_column varchar2(40),
flag varchar2(20) default 'open'
);

insert all 
into source_before (binary_path,hostname,change_column) values ('python','a','drn') 
into source_before (binary_path,hostname,change_column) values ('java','b','drn') 
into source_before (binary_path,hostname,change_column) values ('apache','c','drn')
into source_before (binary_path,hostname,change_column) values ('spark','d','drn')
select * from dual;

create table source_after (
binary_path varchar2(40),
hostname varchar2(40),
change_column varchar2(40),
flag varchar2(20) default 'open'
);

insert all 
into source_after (binary_path,hostname,change_column) values ('java','b','DMZ') 
into source_after (binary_path,hostname,change_column) values ('apache','c','drn')
into source_after (binary_path,hostname,change_column) values ('NEW','NEW','NEW')
select * from dual;

--- source_before
python  a   open    drn
java    b   open    drn
apache  c   open    drn
spark   d   open    drn

--- source_after
java    b   open    DMZ
apache  c   open    drn
NEW    NEW  open    NEW

主键是组合（binary_path，hostname），现在给定了一个目标表，我想在其中获取源表中发生的更改。

create table destination (
binary_path varchar2(40),
hostname varchar2(40),
change_column varchar2(40),
flag varchar2(20),
creation_time timestamp,
open_close_timestamp timestamp
);

表最初为空，flag列应遵循以下逻辑：
1.第一次检测到主键组合时，flag应为open，这是source_*表中的默认值。
1.每当主键组合从source_*表中消失时，flag应该为close。
1.每当主键组合再次从source_*表中出现时，flag（以前为close）应变为re-open。
我用两个语句模拟了这种行为：

merge into destination d
using (select * from source_before) s on (d.hostname = s.hostname and d.binary_path = s.binary_path)
when matched then update
set 
d.change_column = s.change_column,
d.flag = 
(
case
when d.flag = 'close' then 're-open' 
else s.flag
end
)
when not matched then insert 
(d.binary_path,d.hostname,d.change_column,d.flag,d.creation_time,d.open_close_timestamp)
values
(s.binary_path,s.hostname,s.change_column,s.flag,current_timestamp,current_timestamp)
;

UPDATE destination d
SET 
flag   = 'close',
open_close_timestamp = current_timestamp
WHERE NOT EXISTS   (SELECT *
                   FROM   source_after s
                   WHERE  s.hostname = d.hostname and
                   d.binary_path = s.binary_path) and flag <> 'close'                   
                   ;

当flag更新到close状态时，我还设法更新了列open_close_timestamp以获取最新的时间戳，但是，当flag更改为open或re-open时，我没有设法更新open_close_timestamp。re-open和close，而且还可以基于主键组合的出现、重新出现和消失来确定修改发生时的时间戳。注意，为了重新出现，标志只能是处于其先前状态的close。
EDIT：
按照要求，我将更好地解释时间戳的逻辑：
我可能不太清楚的一点是，只有当flag行中的值从一种状态切换到另一种状态时，时间戳才应该更新。也就是说，如果该值在close之前，并且我们正在更新一个新值，而该值仍然是close，则时间戳应该相同，并且不更新为最近的当前时间。但是如果新值是open并且在它关闭之前，则我们应当标记新时间，因为状态改变了。
如果我以destination作为一个空表开始，并运行我上面写的代码，它只会工作一次，它只会更新状态切换的时间戳，从之前是open的行到之后变成close的行。

apache  c   drn open    07-01-2023 08.44.26.479389000 AM    07-01-2023 08.44.26.479389000 AM
spark   d   drn close   07-01-2023 08.44.26.479389000 AM    07-01-2023 08.44.48.451292000 AM
python  a   drn close   07-01-2023 08.44.26.479389000 AM    07-01-2023 08.44.48.451292000 AM
java    b   drn open    07-01-2023 08.44.26.479389000 AM    07-01-2023 08.44.26.479389000 AM

问题是它只能工作一次，因为我继续在flag列中处理状态更改，时间戳将不反映状态更改的更改，如close-〉open，open-〉re-open。
当before关闭时，允许我不更改close时间戳的是上面代码的这一部分。

UPDATE destination d
.......
 and flag <> 'close'   <------------- this restriction                  
                       ;

我在想一个方法，让所有的状态变化都能实现。