oracle12c 神奇的时间有效性功能temporal validity

郑全

你正在创建一个产品报价的应用。它在如下的表保存这价格：

create table product_prices (
  product_id int not null,
  unit_cost  number(10,2) not null);

你想要扩展它并保存价格修改的完整历史。你计划着使用12c的时间有效性功能(temporal validity)来完成。

具体如下:

--1.显示系统当前的日期：

  SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  TO_CHAR(SYSDATE,'YY
  -------------------
  2019-08-24 17:36:12

--显示用户

SQL> show user
USER is "HR"

--2.添加period for 显示字句来完成：

create table product_prices (
  product_id int not null,
  unit_cost  number(10,2) not null,
  price_start date,
  price_end  date,
  period for price(price_start,price_end));

--验证：
SQL> desc product_prices
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
PRODUCT_ID                                NOT NULL NUMBER(38)
UNIT_COST                                 NOT NULL NUMBER(10,2)
PRICE_START                                        DATE
PRICE_END                                          DATE

--3.插入记录

insert into product_prices (product_id,unit_cost, price_start, price_end)

                              values (1,9.99, date'2000-01-01', null);

insert into product_prices (product_id,unit_cost, price_start, price_end)

                             values (2,5.50, date'2000-01-01', date'2016-06-01');

insert into product_prices (product_id,unit_cost, price_start, price_end)

                            values (2,5.95, date'2016-06-01', null);

insert into product_prices (product_id,unit_cost, price_start, price_end)

                            values (3,7.00, date'2016-06-01', null);

commit;

--查看一下总的数据：

SQL> select * from product_prices;

PRODUCT_ID  UNIT_COST PRICE_STA PRICE_END
---------- ---------- --------- ---------
         1       9.99 01-JAN-00
         2        5.5 01-JAN-00 01-JUN-16
         2       5.95 01-JUN-16
         3          7 01-JUN-16

--4.只查看2016-01-01月的数据

SQL> select * from product_prices  as of period for price date'2016-01-01';
PRODUCT_ID  UNIT_COST PRICE_STA PRICE_END
---------- ---------- --------- ---------
         1       9.99 01-JAN-00
         2        5.5 01-JAN-00 01-JUN-16

--5.使用DBMS_FLASHBACK_ARCHIVE 显示指定时间的数据

SQL>  execute DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ASOF',date'2016-01-01');

PL/SQL procedure successfully completed.

SQL> select * from product_prices;

PRODUCT_ID  UNIT_COST PRICE_STA PRICE_END
---------- ---------- --------- ---------
         1       9.99 01-JAN-00
         2        5.5 01-JAN-00 01-JUN-16

--上面只看到 price_start< '2016-01-01'  < price_end 范围的数据，超过这个范围的就不出来了。

--6.显示当前会话时间内的价格

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2019-08-24 18:17:32

execute DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time(‘CURRENT');

SQL> select * from product_prices
  2  ;

PRODUCT_ID  UNIT_COST PRICE_STA PRICE_END
---------- ---------- --------- ---------
         1       9.99 01-JAN-00
         2       5.95 01-JUN-16
         3          7 01-JUN-16
-我们当前时间为2019-08-24，所以看到有效时间为price_start< '2019-08-24'  < price_end 范围的数据就出来了。而   2        5.5 01-JAN-00 01-JUN-16 这一行就不用显示了，因为不满足条件。

--7.显示全表的价格execute DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');
SQL> execute DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL>  select * from product_prices
  2  ;

PRODUCT_ID  UNIT_COST PRICE_STA PRICE_END
---------- ---------- --------- ---------
         1       9.99 01-JAN-00
         2        5.5 01-JAN-00 01-JUN-16
         2       5.95 01-JUN-16
         3          7 01-JUN-16
--看到是否全部价格都出来了。