時間的な有効性による表の作成

例5-10に、時間的な有効性の使用を示します。

Live SQL

「Oracle Live SQL: 時間的な有効性の使用の例」で、Oracle Live SQLの関連する例を表示して実行します。

例5-10 時間的な有効性による表の作成

/* 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',
          '[e-mail]', 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',
             '[e-mail]', 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