如何在Oracle 12c中使用时间有效性? [英] How to use temporal validity in Oracle 12c?

问题描述

如何在Oracle 11g中设计一个表，使其在以后与Oracle 12c中的新的时间有效性" 功能兼容?

How to design a table in Oracle 11g so that it is compatible later on with the new "Temporal validity" feature in Oracle 12c?

Oracle 12c的在线文档在SQL语言指南(

The online documentation of Oracle 12c specifies how to define temporal validity inthe SQL Language guide (http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_7002.htm#CJADHJHB)

ALTER TABLE my_table ADD (PERIOD FOR my_valid_time (my_valid_start, my_valid_end) );

因此，人们可以使用11g中已经存在的旧的valid_from和valid_till列，并在12c中使用它们直到适当的时期，对吗?

So one could use the good old valid_from and valid_till columns already in 11g and beef them up to proper periods in 12c, right?

我继承了使用固定的魔术日期作为自始至终"和永远"的数据库，例如DATE '1900-01-01'和DATE '3999-12-31'.显然，12c改为使用NULL.

I've inherited databases that use fixed magic dates for "since always" and "for ever", for instance DATE '1900-01-01' and DATE '3999-12-31'. Apparently, 12c uses NULL instead.

那么，我们是否必须放弃使用固定的魔术日期并切换到NULL日期?

So, do we have to abandon using fixed magic dates and switch to NULL dates?

推荐答案

是的，您将能够更改12c中的表以启用时间有效性(请参见文档的ALTER TABLE部分:

Yes, you will be able to ALTER the table in 12c to enable Temporal Validity (see the ALTER TABLE section of the docs: http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_3001.htm#CJAEGCFI)

它通过将where子句转换为< =和">或为null"子句而起作用，因此，如果不需要，您无需更改固定日期.

It works by converting your where clause to a "<=" and "> or is null" clause, so you don't need to change the fixed dates if you don't want to.

汤姆·凯特(Tom Kyte)刚刚在他的博客上发布了有关此内容的文章，并列举了一些出色的例子: http://tkyte.blogspot.com /2013/07/12c-flashforward-flashback-or-see-it-as.html

Tom Kyte just posted on his blog about this today, with some execellent examples: http://tkyte.blogspot.com/2013/07/12c-flashforward-flashback-or-see-it-as.html

这篇关于如何在Oracle 12c中使用时间有效性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！