主页 / dba / 问题 / 307769
Accepted
0xCAFEBABE
Asked: 2022-02-22 03:09:37 +0800 CST

Oracle 中的系统版本表

  • 772

一些开发人员找到我，并询问将历史记录添加到包含主数据的某个表的最简单方法。作为熟悉 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 中真的没有系统版本控制吗？

oracle

1 个回答

  1. Best Answer

    我对 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;
    • 0

相关问题