Oracle temporal database source code

Code transferred from http://brianyuzhao.iteye.com/blog/379823

 

Temporal Database is a time-based database. It not only processes the current database, but also processes the past and the future.

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.

This code is successfully tested in oracle11g execution tool PL/SQL developer.

 

You are advised to create a new user during the test as follows:

(1) log on to the console as an administrator.

(2) create a user, such as test.

Create user Test2 identified by Test2

(3) grant the database connection permission to the user: connect

Grant connect to Test2

(4) grant users the permission to create tables in any tablespace: Resource

Grant resource to Test2

Next we can create a new type in the tablespace where Test2 is located.

 

---------- Create type ------------ create or replace type periodtype as object (------------------------------------------- -- Section 1: member variables values -- 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, role -- 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)/---- create 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 '2017-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;/show errors ---- member function for implementing periodtype ---- 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; /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 values (d_start date, n_len number) return periodtype isbegin return makeperiodfromstartend (d_start, d_start + n_len); end;/show errors ------- implement the 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; 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 '2017-12-31 ', date '2017-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; 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 ;/