Oracle 中的系統版本表

一些開發人員找到我，並詢問將歷史記錄添加到包含主數據的某個表的最簡單方法。作為熟悉 SQL 2011、SQL-Server（作為標準功能）和 PostgreSQL（作為提議）上下文中的系統版本表的人，我非常確定 Oracle 會支持該標準功能。

但是，在閱讀和檢查了一整天之後，我找不到對實際 SYSTEM VERSIONED 表的任何引用。作為參考，以下是Wikipedia關於標準和 Oracles 實施的說法：

…

使用 PERIOD FOR SYSTEM_TIME 註釋和 WITH SYSTEM VERSIONING 修飾符定義系統版本表（在其他地方稱為事務時間表）。系統時間段是自動維護的。系統版本表的約束不需要是臨時的，只在目前行上強制執行

……

Oracle Oracle 12c 支持符合 SQL:2011 的臨時功能。

$$ 9 $$版本 10g 和 11g 使用替代語法 AS OF TIMESTAMP 在他們所謂的閃回查詢中實現時間片查詢。$$ 10 $$值得注意的是，Oracle 的兩種實現都依賴於數據庫事務日誌，因此只允許對仍保留用於備份的最近更改進行臨時查詢。

但是，即使按照引用的連結，我也找不到顯示如何讓 Oracle 為我對行進行版本控制的單個參考。所有範例僅參考從列和到列有效性的手動更新。

我想我可以從 Data Warehousing 實現類似SCD2的東西，但這只是一種開發模式，沒有參考有效性是如何實際更新的。我真的很想為開發人員提供一個簡單的解決方案，因為手動更新有效性很容易出錯（重疊是一個因素），而且我認為需要做更多的工作。

對於 SQL 2011 標準所描述的內容，Oracle 中真的沒有系統版本控制嗎？

我對 SCD 類型 4 使用閃回數據存檔，對 SCD 類型 2 使用時間有效性（加上用於自動化的程式碼）。

為了自動化，我會VIEW在帶有INSTEAD OF觸發器的實際表上使用 a 來完成實際工作。授予對實際表的 CRUDVIEW和只讀權限。

create sequence my_data_seq;

create table my_data_history (
 surrogate_key int  generated always as identity,
 my_data_id  int default on null my_data_seq.nextval not null,
 some_data   varchar2(10),
 start_date  date not null,
 end_date    date,
 period for current_data (start_date, end_date),
 constraint my_data_history_pk primary key (surrogate_key),
 constraint my_data_history_unique_identifier unique (my_data_id, start_date),
 constraint my_data_history_valid_date_range check ( start_date < end_date )
);

create view my_data
as
select my_data_id, some_data, start_date, end_date
from my_data_history
 as of period for current_data( sysdate );

create or replace trigger my_data_trg
instead of insert or update or delete
on my_data
for each row
begin
 if deleting
 then
   update my_data_history set end_date = sysdate
   where my_data_id = :old.my_data_id and start_date = :old.start_date;
 end if;

 if updating or inserting
 then
   if :old.my_data_id <> :new.my_data_id
   then
     raise_application_error( -20000, 'This method does not support modifying PK' );
   end if;

   if inserting and ( :new.my_data_id is not null or :new.start_date is not null or :new.end_date is not null)
   then
     raise_application_error( -20001, q'[No thank you! We've alrady got one (DATA_ID, START_DATE, END_DATE)]' );
   end if;

   if updating and :new.end_date is not null
   then
     raise_application_error( -20002, q'[Don't set END_DATE. use DELETE instead]' );
   end if;

   if updating and :new.start_date <> :old.start_date
   then
     raise_application_error( -20002, q'[YOU aren't allowed to modify the START_DATE]' );
   end if;




   merge into my_data_history a
   using (
     select my_data_id, some_data, start_date, end_date
     from my_data_history
     where my_data_id = :old.my_data_id and start_date = :old.start_date
     union all
     select :new.my_data_id my_data_id, :new.some_data some_data
        ,sysdate start_date, null end_date
     from dual
   ) b
   on (a.my_data_id = b.my_data_id and a.start_date = b.start_date)
   when matched then update set a.some_data=b.some_data, a.end_date = sysdate
   when not matched then insert (my_data_id, some_data, start_date)
      values ( nvl(b.my_data_id, my_data_seq.nextval), b.some_data, b.start_date);
 end if;
end;

測試腳本：

select * from my_data;
insert into my_data some_data (some_data) values ( 'abcdefg' );
select * from my_data;
update my_data set some_data = 'xyz' WHERE my_data_id = 1;
select * from my_data;
delete from my_data where my_data_id = 1;
select * from my_data;

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
select * from my_data_history;

引用自：https://dba.stackexchange.com/questions/307769