【12c】新特性：Oracle 12c Temporal Validity 时间有效性
在应用程序开发中，有时会碰到这样一个场景：设置一条记录的生效时间范围，比如某个产品信息，在某个时间段内是有效的，一般设计表时会在表中增加valid_start和valid_end这两个字段来限制，并通过设置这两个字段来实现具体产品的生效范围，如果查看当前生效的记录，则需要通过时间进行过滤。
Oracle 12.1.0.1引入了Temporal Validity（时间有效性），通过这一特性，在不增加列的情况下可以实现时间有效性的的判断，下面将对这一特性进行演示。
1 数据库版本
ALEN@PROD2> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 02 语法结构
要实现时间有效性，在创建表或修改表时指定如下语法即可：
说明：
- valid_time_column：用于指定有效时间维度的名称，Oracle将会使用该名称创建数据类型为number的不可见虚拟列；
- start_time_column：指定起始时间列的名称，数据类型为date或timestamp；
- end_time_column：指定结束时间列的名称，数据类型为date或timestamp；
- 如果不指定start_time_column和end_time_column，则会自动创建valid_time_column_START和valid_time_column_END列。
3 创建表
3.1 不指定start_time_column和end_time_column
ALEN@PROD2> create table prod(id number,name varchar2(100),period for valid);
Table created.
ALEN@PROD2> desc prod
Name Null? Type
----------------------- -------- ----------------
ID NUMBER
NAME VARCHAR2(100)
ALEN@PROD2> select column_name,data_type,data_length,column_id,segment_column_id, hidden_column from user_tab_cols where table_name='PROD';
COLUMN_NAME DATA_TYPE DATA_LENGTH COLUMN_ID SEGMENT_COLUMN_ID HIDDEN_COL
------------------------------ ------------------------------ ----------- ---------- ----------------- ----------
NAME VARCHAR2 100 2 4 NO
ID NUMBER 22 1 3 NO
VALID NUMBER 22 YES
VALID_END TIMESTAMP(6) WITH TIME ZONE 13 2 YES
VALID_START TIMESTAMP(6) WITH TIME ZONE 13 1 YES3.2 指定start_time_column和end_time_column
ALEN@PROD2> create table prod_new(id number,name varchar2(100),valid_start_dt date, valid_end_dt date, period for valid (valid_start_dt,valid_end_dt));
Table created.
ALEN@PROD2> desc prod_new;
Name Null? Type
----------------------- -------- ----------------
ID NUMBER
NAME VARCHAR2(100)
VALID_START_DT DATE
VALID_END_DT DATE
ALEN@PROD2> select column_name,data_type,data_length,column_id,segment_column_id, hidden_column from user_tab_cols where table_name='PROD_NEW';
COLUMN_NAME DATA_TYPE DATA_LENGTH COLUMN_ID SEGMENT_COLUMN_ID HIDDEN_COL
------------------------------ ------------------------------ ----------- ---------- ----------------- ----------
VALID NUMBER 22 YES
ID NUMBER 22 1 1 NO
NAME VARCHAR2 100 2 2 NO
VALID_START_DT DATE 7 3 3 NO
VALID_END_DT DATE 7 4 4 NO4 创建测试数据
ALEN@PROD2> insert into prod(id,name,valid_start,valid_end) values(1,'Apple_01',date'2019-01-01',date'2019-10-31');
1 row created.
ALEN@PROD2> insert into prod(id,name,valid_start,valid_end) values(2,'Car',date'2020-01-01',date'2020-05-01');
1 row created.
ALEN@PROD2> insert into prod(id,name,valid_start,valid_end) values(3,'现金贷',date'2020-03-01',date'2020-11-01');
1 row created.
ALEN@PROD2> insert into prod(id,name,valid_start,valid_end) values(4,'消费贷',date'2019-03-01',null);
1 row created.
ALEN@PROD2> commit;
Commit complete.5 查询数据
ALEN@PROD2> select id,name,trunc(valid_start),trunc(valid_end) from prod;
ID NAME TRUNC(VALID_START) TRUNC(VALID_END)
---------- -------------------- ------------------- -------------------
1 Apple_01 2019-01-01 00:00:00 2019-10-31 00:00:00
2 Car 2020-01-01 00:00:00 2020-05-01 00:00:00
3 现金贷 2020-03-01 00:00:00 2020-11-01 00:00:00
4 消费贷 2019-03-01 00:00:006 检索数据
6.1 普通查询方式
SQL> select * from prod where date '2019-05-01' between valid_start and nvl(valid_end,sysdate);
ID NAME
---------- --------------------
1 Apple_01
4 消费贷6.2 SELECT... AS OF方式
ALEN@PROD2> select * from prod as of period for valid date'2020-03-01';
ID NAME
---------- --------------------
2 Car
3 现金贷
4 消费贷
ALEN@PROD2> select * from prod as of period for valid date '2019-10-31';
ID NAME
---------- --------------------
4 消费贷6.2 SELECT... VERSIONS BETWEEN方式
ALEN@PROD2> select * from prod versions period for valid between date '2020-03-01' and date '2020-05-01';
ID NAME
---------- --------------------
2 Car
3 现金贷
4 消费贷7 会话级别的可见性控制
除了使用闪回查询、闪回版本查询数据之外，还可以通过dbms_flashback_archive的存储过程ENABLE_AT_VALID_TIME在会话级别指定表数据的可见性：显示所有数据（默认），显示指定时间的有效数据或者显示当前的有效数据。
1）默认显示所有数据
ALEN@PROD2> select * from prod;
ID NAME
---------- --------------------
1 Apple_01
2 Car
3 现金贷
4 消费贷2）显示指定时间的数据
ALEN@PROD2> exec dbms_flashback_archive.enable_at_valid_time('ASOF',date '2019-09-01');
PL/SQL procedure successfully completed.
ALEN@PROD2> select id,name from prod;
ID NAME
---------- --------------------
1 Apple_01
4 消费贷3）显示当前时间可见的数据
ALEN@PROD2> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');
PL/SQL procedure successfully completed.
ALEN@PROD2> select id,name from prod;
ID NAME
---------- --------------------
3 现金贷
4 消费贷4）显示所有数据
ALEN@PROD2> exec dbms_flashback_archive.enable_at_valid_time('ALL');
PL/SQL procedure successfully completed.
ALEN@PROD2> select id,name from prod;
ID NAME
---------- --------------------
1 Apple_01
2 Car
3 现金贷
4 消费贷以上就是Oracle 12c关于时间有效性的介绍，在使用时需要注意隐藏字段以及SQL中条件的正确判断。
