Oracle 12C -- temporal validity

2015-11-03 14:49 abce 阅读(1509) 评论(0) 编辑收藏举报

temporal validity需要在创建表的时候使用一个vaild-time维度(包含时间起始和结束)

创建有valid-time维度的表

(1)显式指定两个date-time列

SQL> create table emp(empno number,salary number,deptid number,name varchar2(20),user_time_start date,user_time_end date,period for user_time (user_time_start,user_time_end));

(2)隐式的vaild-time列

SQL> create table emp2(empno number,salary number,deptid number,name varchar2(20), period for user_time);

vaild-time维度使用关键字"period for"指定。
如果使用隐式的valid-time列，不需要指两个日期列，oracle会创建两个隐藏列，并自动加上valid-time维度的前缀名。例如上一个例子中的前缀名是：user_time
(3)创建表之后修改表的属性

SQL> create table emp3(empno number,salary number,deptid number,name varchar2(20));
SQL> alter table emp3 add period for user_time;

valid-time列是隐藏的，默认不会显示

SQL> desc emp
 Name                 Null?    Type
 -------------------- -------- --------------
 EMPNO                         NUMBER
 SALARY                        NUMBER
 DEPTID                        NUMBER
 NAME                          VARCHAR2(20)
 USER_TIME_START               DATE
 USER_TIME_END                 DATE

SQL> desc emp2
 Name                Null?    Type
 ------------------- -------- ----------------
 EMPNO                        NUMBER
 SALARY                       NUMBER
 DEPTID                       NUMBER
 NAME                         VARCHAR2(20)

SQL> desc emp3
 Name               Null?    Type
 ------------------ -------- -----------------
 EMPNO              NUMBER
 SALARY             NUMBER
 DEPTID             NUMBER
 NAME               VARCHAR2(20)

SQL> insert into emp2(empno,salary,deptid,name,user_time_start,user_time_end) values(2,1000,20,'john2',sysdate,null);

1 row created.

SQL> insert into emp3(empno,salary,deptid,name) values(3,3000,30,'john3');

1 row created.

SQL> select * from emp2;

     EMPNO     SALARY     DEPTID NAME
---------- ---------- ---------- --------------------
         2       1000         20 john2

1 row selected.

SQL> select * from emp3;

     EMPNO     SALARY     DEPTID NAME
---------- ---------- ---------- --------------------
         3       3000         30 john3

1 row selected.

SQL> select empno,salary,deptid,name,user_time_start,user_time_end from emp2;

     EMPNO     SALARY     DEPTID NAME                 USER_TIME_START                          USER_TIME_END
---------- ---------- ---------- -------------------- ---------------------------------------- ----------------------------------------
         2       1000         20 john2                03-NOV-15 06.15.12.000000 AM +08:00

1 row selected.

SQL> select empno,salary,deptid,name,user_time_start,user_time_end from emp3;

     EMPNO     SALARY     DEPTID NAME                 USER_TIME_START                          USER_TIME_END
---------- ---------- ---------- -------------------- ---------------------------------------- ----------------------------------------
         3       3000         30 john3

1 row selected.

SQL>