oracle時態資料庫源碼

本文代碼轉自http://brianyuzhao.iteye.com/blog/379823

 

 時態資料庫是種以時間為基礎的資料庫，它所實現的不僅僅是對當前資料庫的處理，也可以對過去和未來進行處理。

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.

該代碼在Oracle11g 執行工具：PL/SQL Developer中測試通過

 

測試時最好建立一個使用者 ，具體步驟如下：

（1）首先通過管理員登陸 

（2）建立以使用者如：test test

create user test2 identified by test2 

（3）給使用者賦予串連資料庫的許可權：connect 

grant connect to test2

（4）給使用者賦予在任意的資料表空間建表的許可權：resource

grant resource to test2

 下面就可以在test2所在的資料表空間建立新的類型了

 

----------建立 Type------------CREATE or replace 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    ---------------------------------------------------------------------------  -- 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)/----建立 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 ISBEGIN     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----實現PeriodType的成員函數member function----CREATE OR REPLACE FUNCTION LengthBetweenDates(d1 Date, d2 Date) RETURN INTEGER ISBEGIN     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 errorsCREATE OR REPLACE FUNCTION MakePeriodFromStartEnd(d1 Date, d2 Date) RETURN PeriodType ISBEGIN     IF CompareDates(d1, d2) = -1 THEN        RETURN PeriodType(d1, d2);    ELSE        RETURN PeriodType(d2, d1);    END IF;END;/show errorsCREATE OR REPLACE FUNCTION MakePeriodFromStartLength(d_start Date, n_len NUMBER ) RETURN PeriodType ISBEGIN     RETURN MakePeriodFromStartEnd(d_start, d_start+n_len);END;/show errors-------實現PeriodType中的成員函數----------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;/