Example 5-10 shows the use of temporal validity.
Example 5-10 Creating a table with temporal validity
/* Create a time with an employee tracking timestamp */
/* using the specified columns*/
CREATE TABLE employees_temp (
employee_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) NOT NULL, phone_number VARCHAR2(20), hire_date DATE NOT NULL,
job_id VARCHAR2(10) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2),
manager_id NUMBER(6), department_id NUMBER(4),
PERIOD FOR emp_track_time);
DESCRIBE employees_temp
Name Null? Type
------------------------------------------------------- -------- ---------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,28) DATA_TYPE, COLUMN_ID AS COL_ID,
SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN
FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_TEMP';
NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HID
---------------------- ---------------------------- ------ ---------- ---------- ---
EMP_TRACK_TIME_START TIMESTAMP(6) WITH TIME ZONE 1 1 YES
EMP_TRACK_TIME_END TIMESTAMP(6) WITH TIME ZONE 2 2 YES
EMP_TRACK_TIME NUMBER 3 YES
EMPLOYEE_ID NUMBER 1 3 4 NO
FIRST_NAME VARCHAR2 2 4 5 NO
LAST_NAME VARCHAR2 3 5 6 NO
EMAIL VARCHAR2 4 6 7 NO
PHONE_NUMBER VARCHAR2 5 7 8 NO
HIRE_DATE DATE 6 8 9 NO
JOB_ID VARCHAR2 7 9 10 NO
SALARY NUMBER 8 10 11 NO
COMMISSION_PCT NUMBER 9 11 12 NO
MANAGER_ID NUMBER 10 12 13 NO
DEPARTMENT_ID NUMBER 11 13 14 NO
/* Insert/update/delete with specified values for time columns */
INSERT INTO employees_temp(emp_track_time_start, emp_track_time_end, employee_id, first_name,
last_name, email, hire_date, job_id, salary, manager_id, department_id)
VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris',
TIMESTAMP '2012-11-30 12:00:01 Europe/Paris', 251, 'Scott', 'Tiger',
'scott.tiger@example.com', DATE '2009-05-21', 'IT_PROG', 50000, 103, 60);
INSERT INTO employees_temp(emp_track_time_start, emp_track_time_end, employee_id, first_name,
last_name, email, hire_date, job_id, salary, manager_id, department_id)
VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris',
TIMESTAMP '2012-12-31 12:00:01 Europe/Paris', 252, 'Jane', 'Lion',
'jane.lion@example.com', DATE '2009-06-11', 'IT_PROG', 50000, 103, 60);
UPDATE employees_temp set salary = salary + salary * .05
WHERE emp_track_time_start <= TIMESTAMP '2009-06-01 12:00:01 Europe/Paris';
SELECT employee_id, SALARY FROM employees_temp;
EMPLOYEE_ID SALARY
----------- ----------
251 52500
252 52500
/* No rows are deleted for the following statement because no records */
/* are in the specified track time. */
DELETE employees_temp WHERE emp_track_time_end < TIMESTAMP '2001-12-31 12:00:01 Europe/Paris';
0 rows deleted.
/* Show rows that are in a specified time period */
SELECT employee_id FROM employees_temp
WHERE emp_track_time_start > TIMESTAMP '2009-05-31 12:00:01 Europe/Paris' AND
emp_track_time_end < TIMESTAMP '2012-12-01 12:00:01 Europe/Paris';
EMPLOYEE_ID
-----------
251
/* Show rows that are in a specified time period */
SELECT employee_id FROM employees_temp AS OF PERIOD FOR
emp_track_time TIMESTAMP '2012-12-01 12:00:01 Europe/Paris';
EMPLOYEE_ID
-----------
252