时态数据库(temporal database) 在 oracle 中的实现

时态数据库是种以时间为基础的数据库，它所实现的不仅仅是对当前数据库的处理，也可以对过去和未来进行处理。

A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language.

 

1. 在oracle 中创建 Type

CREATE TYPE PeriodType AS OBJECT
(
  -------------------------------------------
  -- Section 1: Member variables
  -------------------------------------------  
-- Note1: This is a close-open period.
  -- Note2: This code is for Oracle
  m_start  DATE,	-- start  of the period
  m_end  DATE,	-- end of the period
. . . 
)

 2.上面的省略号中要插入member function.

  ---------------------------------------------------------------------------
  -- Section 2: Member functions to get the attribute of this object
  ---------------------------------------------------------------------------
  -- 2.1 public function: Get the length of this period. 
    --return -2 if this period is end-with-forever
    --return -1 if this period is invalid
  MEMBER FUNCTION PeriodLength RETURN NUMBER, 
  -- 2.2 public function: Check if the date is end with forever. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsEndWithForever RETURN INTEGER, 
  -- 2.3 private function: Check if the period is a valid period, that is m_end > m_begin. 
    --return 1 for valid, o for invalid.
  MEMBER FUNCTION IsValid RETURN INTEGER, 
  
  ---------------------------------------------------------------------------
  -- Section 3: Member functions to get the relationship with an instant point.
  ---------------------------------------------------------------------------
  -- 3.1 public function: Check if the date is in this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
    --Note: if the date is the end of the period, it is not in this period, 
    --because this is a close-open period.
  MEMBER FUNCTION IsDateIn(d DATE) RETURN INTEGER, 
  -- 3.2 public function: Check if the date is out of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
    --Note: if the date is the end of the period, it is outside of the period, 
    --because this is a close-open period.
  MEMBER FUNCTION IsDateOut(d DATE) RETURN INTEGER, 
  -- 3.3 public function: Check if the date is same to the start point of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateStart(d DATE) RETURN INTEGER, 
  -- 3.4 public function: Check if the date is same to the end point of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateEnd(d DATE) RETURN INTEGER, 
  -- 3.5 public function: Check if the date is eailier than this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateBeforeStart(d DATE) RETURN INTEGER, 
  -- 3.6 public function: Check if the date is later than this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION IsDateAfterEnd(d DATE) RETURN INTEGER, 
  
  -- 3.7 public function: Get the interval between the date and the start of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
  MEMBER FUNCTION DistanceToBegin(d DATE) RETURN NUMBER, 
  -- 3.8 public function: Get the interval between the date and the end of this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if this period is invalid
	--Note: if either the date or the end of this period is MAX_TIME, this function returns -2
  MEMBER FUNCTION DistanceToEnd(d DATE) RETURN NUMBER, 
  -- 3.9 public function: Get the minimal interval between the date and the points in this period. 
  MEMBER FUNCTION DistanceToWholePeriod(d DATE) RETURN NUMBER, 

  ---------------------------------------------------------------------------
  -- Section 4: Member functions to get the relationship with an other period.
  ---------------------------------------------------------------------------
  -- 4.1 public function: check if the period is disjoint with this period, with a gap bigger than zero. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjointWithGap(p PeriodType) RETURN INTEGER, 
  -- 4.2 public function: check if the period is disjoint with this period, without a gap. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjointWithoutGap(p PeriodType) RETURN INTEGER, 
  -- 4.3 public function: check if the period is disjoint with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsDisjoint(p PeriodType) RETURN INTEGER, 
  -- 4.4 public function: Get the gap getween the period and this period
  MEMBER FUNCTION GapLength(p PeriodType) RETURN NUMBER, 
  
  -- 4.5 public function: check if the period is same with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsEqual(p PeriodType) RETURN INTEGER, 
  -- 4.6 public function: check if this period covers the period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsCover(p PeriodType) RETURN INTEGER, 
  -- 4.7 public function: check if this period is covered by the period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsCovered(p PeriodType) RETURN INTEGER, 
  -- 4.8 public function: check if the period is overlap with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsOverlap(p PeriodType) RETURN INTEGER, 
  -- 4.9 public function: check if the period intersects with this period. 
    --return 1 for TRUE, 0 for FALSE, -1 if either this period or input period is invalid
  MEMBER FUNCTION IsIntersect(p PeriodType) RETURN INTEGER,
  
  -- 4.10 public function: Get the intersect length of the period and this period
    --NOTE: if both periods are end-with-forever, the function returns -1;
  MEMBER FUNCTION IntersectLength(p PeriodType) RETURN NUMBER, 

  -- 4.11 public constructor: Get the intersect period of the period and this period
  MEMBER FUNCTION IntercectPeriod(p PeriodType) RETURN PeriodType

3. 创建 global function

-- 5.1 private function: Check if the date is the max date time. 
CREATE OR REPLACE FUNCTION IsMaxDateTime(d Date) RETURN INTEGER IS 
BEGIN 
    IF TRUNC(d) = Date '9999-12-31' THEN
        RETURN 1;
    ELSE 
        RETURN 0;
    END IF;
END;
/
show errors

-- 5.2 private function: compare 2 dates. returns 1 if d1 > d2; returns 0 if d1 = d2; returns -1 if d1 < d2;
CREATE OR REPLACE FUNCTION CompareDates(d1 Date, d2 Date) RETURN INTEGER IS
BEGIN 
    IF IsMaxDateTime(d1) = 1 OR IsMaxDateTime(d2) = 1  THEN
        IF TRUNC(d1) > TRUNC(d2) THEN
            RETURN 1;
        ELSIF TRUNC(d1) = TRUNC(d2) THEN
            RETURN 0;
        ELSE
            RETURN -1;
        END IF;
    ELSE 
        IF d1 > d2 THEN
            RETURN 1;
        ELSIF d1 = d2 THEN
            RETURN 0;
        ELSE
            RETURN -1;
        END IF;
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION LengthBetweenDates(d1 Date, d2 Date) RETURN INTEGER IS
BEGIN 
    IF IsMaxDateTime(d1) = 1 THEN
        RETURN -2;
    ELSIF IsMaxDateTime(d2) = 1 THEN
        RETURN -2;
    ELSE 
        IF d1 > d2 THEN
            RETURN d1-d2;
        ELSE
            RETURN d2-d1;
        END IF;
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION MakePeriodFromStartEnd(d1 Date, d2 Date) RETURN PeriodType IS
BEGIN 
    IF CompareDates(d1, d2) = -1 THEN
        RETURN PeriodType(d1, d2);
    ELSE
        RETURN PeriodType(d2, d1);
    END IF;
END;
/
show errors

CREATE OR REPLACE FUNCTION MakePeriodFromStartLength(d_start Date, n_len NUMBER ) RETURN PeriodType IS
BEGIN 
    RETURN MakePeriodFromStartEnd(d_start, d_start+n_len);
END;
/
show errors

 

 4. 要实现这些member function

CREATE OR REPLACE TYPE BODY PeriodType AS 

    MEMBER FUNCTION IsValid RETURN INTEGER IS
    BEGIN
        IF IsMaxDateTime(m_start) = 1 THEN
            RETURN 0;
        END IF;

        IF m_end <= m_start THEN 
            RETURN 0;
        END IF;

        RETURN 1;
    END;
  
    MEMBER FUNCTION PeriodLength RETURN NUMBER IS 
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_end, m_start);
    END;
      
    MEMBER FUNCTION IsEndWithForever RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        RETURN IsMaxDateTime(m_end);
    END;
      
    MEMBER FUNCTION IsDateIn(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        --if m_start > d, return false;
        IF m_start > d THEN 
            RETURN 0;               
        END IF;
        --if m_end < d, return false;
        IF CompareDates(m_end, d) = -1 THEN
            RETURN 0;               
        END IF;
        --if m_end = d, return false;
        IF CompareDates(m_end, d) = 0 THEN
            RETURN 0;               
        END IF;
        
        RETURN 1;
    END;

    MEMBER FUNCTION IsDateOut(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF IsDateIn(d) = 1 THEN
            RETURN 0;
        ELSE
            RETURN 1;
        END IF;
    END;

    MEMBER FUNCTION IsDateStart(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF m_start = d THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateEnd(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF CompareDates(m_end, d) = 0 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateBeforeStart(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
          RETURN -1;               
        END IF;

        IF m_start > d THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDateAfterEnd(d DATE) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF CompareDates(m_end, d) = -1 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION DistanceToBegin(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_start, d);
    END;

    MEMBER FUNCTION DistanceToEnd(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN LengthBetweenDates(m_end, d);
    END;

    MEMBER FUNCTION DistanceToWholePeriod(d DATE) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDateIn(d) = 1 THEN 
            RETURN 0;
        END IF;
        
        IF IsDateEnd(d) = 1 THEN 
            RETURN 0;
        END IF;
        
        IF IsDateBeforeStart(d) = 1 THEN 
            RETURN LengthBetweenDates(m_start, d);
        END IF;
        
        RETURN LengthBetweenDates(m_end, d);
    END;

    MEMBER FUNCTION IsDisjointWithGap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF m_end < p.m_start THEN 
            RETURN 1;               
        ELSIF p.m_end < m_start THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;

    END;

    MEMBER FUNCTION IsDisjointWithoutGap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF m_end = p.m_start THEN 
            RETURN 1;               
        ELSIF p.m_end = m_start THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsDisjoint(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF IsDisjointWithGap(p) = 1 THEN 
            RETURN 1;
        ELSIF IsDisjointWithoutGap(p) = 1 THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION GapLength(p PeriodType) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDisjointWithGap(p) = 0 THEN
            RETURN 0;
        ELSE
            IF m_start > p.m_end THEN
                RETURN m_start - p.m_end;
            ELSE
                RETURN p.m_start - m_end;
            END IF;
        END IF;
        
    END;

    MEMBER FUNCTION IsCover(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF m_start <= p.m_start THEN 
            IF CompareDates(m_end, p.m_end)=0 THEN
                RETURN 1;
            ELSIF CompareDates(m_end, p.m_end)=1 THEN
                RETURN 1;
            ELSE
                RETURN 0;
            END IF;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsCovered(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF p.IsCover(MakePeriodFromStartEnd(m_start, m_end)) = 1 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsEqual(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        IF IsCover(p) = 1 AND IsCovered(p) = 1 THEN 
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsOverlap(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF m_start < p.m_end AND p.m_start < m_end THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IsIntersect(p PeriodType) RETURN INTEGER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        
        IF IsDisjoint(p) = 0 THEN
            RETURN 1;
        ELSE
            RETURN 0;
        END IF;
    END;

    MEMBER FUNCTION IntercectPeriod(p PeriodType) RETURN PeriodType IS
    BEGIN 
        IF IsIntersect(p) = 0 THEN
            RETURN PeriodType(Date '9999-12-31', Date '9999-12-31');
        ELSE
            IF IsCover(p) = 1 THEN
                RETURN p;
            ELSIF IsCovered(p) = 1 THEN
                RETURN PeriodType(m_start, m_end);
            ELSE
                IF m_start < p.m_start THEN
                    RETURN PeriodType(p.m_start, m_end);
                ELSE
                    RETURN PeriodType(m_start, p.m_end);
                END IF;
            END IF;
        END IF;
        RETURN p;
    END;

    MEMBER FUNCTION IntersectLength(p PeriodType) RETURN NUMBER IS
    BEGIN 
        IF IsValid() = 0 THEN 
            RETURN -1;               
        END IF;
        IF p.IsValid() = 0 THEN 
            RETURN -1;               
        END IF;

        RETURN IntercectPeriod(p).PeriodLength();
    END;
END;
/