欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle12c_Temporal Validity

程序员文章站 2022-05-21 11:12:54
...

创建Temporal Validity有两种方法一是自己指定列,一是让oracle自己指定 手工指定 CREATE TABLE Temporal_ValidityEMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER, NAME VARCHAR2100, USER_TIME_START DATE, USER_TIME_END DATE, PERIOD FOR USER_TIME USER_TI

创建Temporal Validity有两种方法一是自己指定列,一是让oracle自己指定

  • 手工指定
CREATE TABLE Temporal_Validity
(EMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER,
           NAME VARCHAR2(100),
           USER_TIME_START DATE,
           USER_TIME_END   DATE,
        PERIOD FOR USER_TIME
                  (USER_TIME_START, USER_TIME_END));
 
SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME=UPPER('Temporal_Validity');     
 
: TEMPORAL_VALIDITY
 
TABLE      COLUMN
Owner      Name                           COLUMN_ID
---------- ------------------------------ ---------
TRAVEL     USER_TIME_END                          6
TRAVEL     USER_TIME_START                        5
TRAVEL     NAME                                   4
TRAVEL     DEPTID                                 3
TRAVEL     SALARY                                 2
TRAVEL     EMPNO                                  1
TRAVEL     USER_TIME
 
7 ROWS selected.
 
 
SQL>  col NAME FOR a15
SQL> col DEFAULT$ FOR a10
SQL> col SPARE4 FOR a1
SQL> col SPARE5 FOR a1
SQL> col SPARE6 FOR a1
 
SQL> SELECT * FROM sys.col$ WHERE obj#=92087;
 
      OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                 TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$  DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING#     SPARE1     SPARE2     SPARE3 S S S     SPARE7     SPARE8
---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ----------
     92087          0          0           22          0 USER_TIME                2         22            0                     0          0          9 430120635           1      65576          0           0            0                                             0          0          0
     92087          1          1           22          0 EMPNO                    2         22            0                                0                                2          0          0           0            0                                             0          0          0
     92087          2          2           22          0 SALARY                   2         22            0                                0                                3          0          0           0            0                                             0          0          0
     92087          3          3           22          0 DEPTID                   2         22            0                                0                                4          0          0           0            0                                             0          0          0
     92087          4          4          100          0 NAME                     1        100            0                                0                                5          0        873           1            0                                             0          0        100
     92087          5          5            7          0 USER_TIME_START         12          7            0                                0                                6          0          0           0            0                                             0          0          0
     92087          6          6            7          0 USER_TIME_END           12          7            0                                0                                7          0          0           0            0                                             0          0          0
 
7 ROWS selected.
 
SQL> col COLUMN_NAME FOR a20
SQL> col TABLE_NAME FOR a15
SQL> col CONSTRAINT_NAME FOR a20                                                                                                                                                                                                                                                              USER_TIME_START                           ENABLED          VALIDATED
SQL> col R_CONSTRAINT_NAME FOR a20
SQL> @cons
SHOW constraints ON TABLE %TEMPORAL_VALIDITY%...
 
OWNER                          TABLE_NAME      CONSTRAINT_NAME      CO R_CONSTRAINT_NAME    COLUMN_NAME                      POSITION STATUS           VALIDATED
------------------------------ --------------- -------------------- -- -------------------- ------------------------------ ---------- ---------------- --------------------------
TRAVEL                         TEMPORAL_VALIDI USER_TIMEA31EBB      C                       USER_TIME                                 ENABLED          VALIDATED
                               TY
 
TRAVEL                                         USER_TIMEA31EBB      C                       USER_TIME_END                             ENABLED          VALIDATED
TRAVEL                                         USER_TIMEA31EBB      C                       USER_TIME_START                           ENABLED          VALIDATED
 
SQL> 
 
 
SQL> @DESC TEMPORAL_VALIDITY
           Name                            NULL?    TYPE
           ------------------------------- -------- ----------------------------
    1      EMPNO                                    NUMBER
    2      SALARY                                   NUMBER
    3      DEPTID                                   NUMBER
    4      NAME                                     VARCHAR2(100)
    5      USER_TIME_START                          DATE
    6      USER_TIME_END                            DATE
手工指定是正常列,可以通过SELECT * 来查看
  • 系统自动
  • SQL> 
    SQL> CREATE TABLE TEMPORAL_VALIDITY2
      2  ( empno NUMBER, salary NUMBER, deptid NUMBER, name VARCHAR2(100),
      3   PERIOD FOR user_time);
     
    TABLE created.
     
     
    SQL> @DESC TEMPORAL_VALIDITY2
               Name                            NULL?    TYPE
               ------------------------------- -------- ----------------------------
        1      EMPNO                                    NUMBER
        2      SALARY                                   NUMBER
        3      DEPTID                                   NUMBER
        4      NAME                                     VARCHAR2(100)
     
    SQL> 
     
    SQL> col TABLE_NAME FOR a25
    SQL> col OWNER FOR a10
    SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME=UPPER('Temporal_Validity2');     
     
    OWNER      TABLE_NAME                COLUMN_NAME           COLUMN_ID
    ---------- ------------------------- -------------------- ----------
    TRAVEL     TEMPORAL_VALIDITY2        NAME                          4
    TRAVEL                               DEPTID                        3
    TRAVEL                               SALARY                        2
    TRAVEL                               EMPNO                         1
    TRAVEL                               USER_TIME
    TRAVEL                               USER_TIME_END
    TRAVEL                               USER_TIME_START
     
    7 ROWS selected.
     
    SQL> 
     
    SQL> @o TEMPORAL_VALIDITY2
     
    owner                     object_name                    object_type        STATUS           OID      D_OID CREATED           LAST_DDL_TIME
    ------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- -----------------
    TRAVEL                    TEMPORAL_VALIDITY2             TABLE              VALID          92089      92089 20140526 22:41:37 20140526 22:41:37
     
    SQL> SELECT * FROM sys.col$ WHERE obj#=92089;
     
          OBJ#       COL#    SEGCOL# SEGCOLLENGTH     OFFSET NAME                 TYPE#     LENGTH FIXEDSTORAGE PRECISION#      SCALE      NULL$  DEFLENGTH DEFAULT$      INTCOL#   PROPERTY  CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING#     SPARE1     SPARE2     SPARE3 S S S     SPARE7     SPARE8
    ---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ----------
         92089          0          1           13          0 USER_TIME_START        181         13            0                     6          0                                1         32          0           0            0                                             6          0          0
         92089          0          2           13          0 USER_TIME_END          181         13            0                     6          0                                2         32          0           0            0                                             6          0          0
         92089          0          0           22          0 USER_TIME                2         22            0                     0          0          9 430191669           3      65576          0           0            0                                             0          0          0
         92089          1          3           22          0 EMPNO                    2         22            0                                0                                4          0          0           0            0                                             0          0          0
         92089          2          4           22          0 SALARY                   2         22            0                                0                                5          0          0           0            0                                             0          0          0
         92089          3          5           22          0 DEPTID                   2         22            0                                0                                6          0          0           0            0                                             0          0          0
         92089          4          6          100          0 NAME                     1        100            0                                0                                7          0        873           1            0                                             0          0        100
     
    7 ROWS selected.
     
    SQL> @cons
    SHOW constraints ON TABLE %TEMPORAL_VALIDITY2%...
     
    OWNER                          TABLE_NAME                CONSTRAINT_NAME      CO R_CONSTRAINT_NAME    COLUMN_NAME                      POSITION STATUS           VALIDATED
    ------------------------------ ------------------------- -------------------- -- -------------------- ------------------------------ ---------- ---------------- --------------------------
    TRAVEL                         TEMPORAL_VALIDITY2        USER_TIMEA43435      C                       USER_TIME_END                             ENABLED          VALIDATED
    TRAVEL                                                   USER_TIMEA43435      C                       USER_TIME_START                           ENABLED          VALIDATED
     
    SQL>

    系统自动创建了2个隐藏列USER_TIME_START,USER_TIME_END

    插入数据

    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (101,1900,90,'ADAM',to_date('2000-01-01', 'yyyy-mm-dd') ,to_date('2004-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (102,2900,90,'ADAM',to_date('2001-01-01', 'yyyy-mm-dd') ,to_date('2005-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (103,3900,90,'ADAM',to_date('2002-01-01', 'yyyy-mm-dd') ,to_date('2006-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (104,4900,90,'ADAM',to_date('2003-01-01', 'yyyy-mm-dd') ,to_date('2007-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (105,5900,90,'ADAM',to_date('2004-01-01', 'yyyy-mm-dd') ,to_date('2008-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (106,6900,90,'ADAM',to_date('2005-01-01', 'yyyy-mm-dd') ,to_date('2009-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (107,7900,90,'ADAM',to_date('2006-01-01', 'yyyy-mm-dd') ,to_date('2010-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (108,8900,90,'ADAM',to_date('2007-01-01', 'yyyy-mm-dd') ,to_date('2011-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (109,9900,90,'ADAM',to_date('2008-01-01', 'yyyy-mm-dd') ,to_date('2012-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (110,1900,90,'ADAM',to_date('2009-01-01', 'yyyy-mm-dd') ,to_date('2014-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (111,2900,90,'ADAM',to_date('2010-01-01', 'yyyy-mm-dd') ,to_date('2011-05-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (112,3900,90,'ADAM',to_date('2011-01-01', 'yyyy-mm-dd') ,to_date('2011-08-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (113,4900,90,'ADAM',to_date('2012-01-01', 'yyyy-mm-dd') ,to_date('2013-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (114,5900,90,'ADAM',to_date('2013-01-01', 'yyyy-mm-dd') ,to_date('2014-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (115,6900,90,'ADAM',to_date('2014-01-01', 'yyyy-mm-dd') ,to_date('2015-01-01', 'yyyy-mm-dd'));
     
    1 ROW created.
     
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (116,7900,90,'ADAM',to_date('2002-04-01', 'yyyy-mm-dd') ,to_date('2002-06-01', 'yyyy-mm-dd'));om
      2  
    SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (116,7900,90,'ADAM',to_date('2002-04-01', 'yyyy-mm-dd') ,to_date('2002-06-01', 'yyyy-mm-dd'))
      2  ;
     
    1 ROW created.
     
    SQL> commit;
     
    Commit complete.
     
    SQL> SELECT * FROM TEMPORAL_VALIDITY2;
     
         EMPNO     SALARY     DEPTID NAME
    ---------- ---------- ---------- ---------------
           101       1900         90 ADAM
           101       1900         90 ADAM
           102       2900         90 ADAM
           103       3900         90 ADAM
           104       4900         90 ADAM
           105       5900         90 ADAM
           106       6900         90 ADAM
           107       7900         90 ADAM
           108       8900         90 ADAM
           109       9900         90 ADAM
           110       1900         90 ADAM
           111       2900         90 ADAM
           112       3900         90 ADAM
           113       4900         90 ADAM
           114       5900         90 ADAM
           115       6900         90 ADAM
           116       7900         90 ADAM
     
     
     
    SQL> SELECT NAME,
      2  to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd')
      3  FROM TEMPORAL_VALIDITY2 ORDER BY 2;
     
    NAME            TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN
    --------------- -------------------- --------------------
    ADAM            2000-01-01           2011-12-31
    ADAM            2000-01-01           2004-01-01
    ADAM            2001-01-01           2005-01-01
    ADAM            2002-01-01           2006-01-01
    ADAM            2002-04-01           2002-06-01
    ADAM            2003-01-01           2007-01-01
    ADAM            2004-01-01           2008-01-01
    ADAM            2005-01-01           2009-01-01
    ADAM            2006-01-01           2010-01-01
    ADAM            2007-01-01           2011-01-01
    ADAM            2008-01-01           2012-01-01
    ADAM            2009-01-01           2014-01-01
    ADAM            2010-01-01           2011-05-01
    ADAM            2011-01-01           2011-08-01
    ADAM            2012-01-01           2013-01-01
    ADAM            2013-01-01           2014-01-01
    ADAM            2014-01-01           2015-01-01
    • 示例1
    • oracle12c_Temporal Validity

    SQL> SELECT NAME,
      2  to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd')
      3  FROM TEMPORAL_VALIDITY2  AS OF PERIOD FOR USER_TIME
      4          to_date('2004-01-01', 'yyyy-mm-dd') ORDER BY 2;
     
    NAME            TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN
    --------------- -------------------- --------------------
    ADAM            2000-01-01           2011-12-31
    ADAM            2001-01-01           2005-01-01
    ADAM            2002-01-01           2006-01-01
    ADAM            2003-01-01           2007-01-01
    ADAM            2004-01-01           2008-01-01

    The query displays all whose

  • 示例2
  • oracle12c_Temporal Validity

    SQL> SELECT NAME,
      2  to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd')
      3  FROM TEMPORAL_VALIDITY2  versions PERIOD FOR USER_TIME
      4          BETWEEN to_date('2004-01-01', 'yyyy-mm-dd')
      5            AND     to_date('2008-01-01', 'yyyy-mm-dd')
      6  ORDER BY 2;
     
    NAME            TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN
    --------------- -------------------- --------------------
    ADAM            2000-01-01           2011-12-31
    ADAM            2001-01-01           2005-01-01
    ADAM            2002-01-01           2006-01-01
    ADAM            2003-01-01           2007-01-01
    ADAM            2004-01-01           2008-01-01
    ADAM            2005-01-01           2009-01-01
    ADAM            2006-01-01           2010-01-01
    ADAM            2007-01-01           2011-01-01
    ADAM            2008-01-01           2012-01-01

    The query displays all whose USER_TIME_START is less than or equal to ‘2004-01-01′ and USER_TIME_END greater than or equal to ‘2008-01-01′‘.

  • 示例3
  • oracle12c_Temporal Validity

    SQL> EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> SELECT NAME,
      2  to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd')
      3  FROM TEMPORAL_VALIDITY2 ORDER BY 2;
     
    NAME            TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN
    --------------- -------------------- --------------------
    ADAM            2014-01-01           2015-01-01
  • 示例4
  • SQL> EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL')
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL> SELECT NAME,
      2  to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd')
      3  FROM TEMPORAL_VALIDITY2 ORDER BY 2;
     
    NAME            TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN
    --------------- -------------------- --------------------
    ADAM            2000-01-01           2011-12-31
    ADAM            2000-01-01           2004-01-01
    ADAM            2001-01-01           2005-01-01
    ADAM            2002-01-01           2006-01-01
    ADAM            2002-04-01           2002-06-01
    ADAM            2003-01-01           2007-01-01
    ADAM            2004-01-01           2008-01-01
    ADAM            2005-01-01           2009-01-01
    ADAM            2006-01-01           2010-01-01
    ADAM            2007-01-01           2011-01-01
    ADAM            2008-01-01           2012-01-01
    ADAM            2009-01-01           2014-01-01
    ADAM            2010-01-01           2011-05-01
    ADAM            2011-01-01           2011-08-01
    ADAM            2012-01-01           2013-01-01
    ADAM            2013-01-01           2014-01-01
    ADAM            2014-01-01           2015-01-01
     
    17 ROWS selected.