oracle小笔记

To_Date('2003-02-24 12:30:20','yyyy-mm-dd hh:mi:ss') 

create or replace function
-- -- works in roughly the same way as sybase datsdiff
-- call would be eg. datediff('month',date1,date2)
-- p_what would be 'HOUR', 'DAY','MONTH' OR 'QUARTER','H','D','M','MI'
datediff( p_what in varchar2,
          p_d1   in date,
         p_d2   in date ) return number
as
l_result    number;
begin
    l_result:=null;
    if (upper(p_what) = 'HOUR' or upper(p_what) = 'H') then
      l_result:=((p_d2-p_d1)*24);
    end if;
    if (upper(p_what) = 'MI') then
      l_result:=((p_d2-p_d1)*1440);
    end if;
    if (upper(p_what) = 'DAY' or upper(p_what) = 'D') then
      l_result:=(p_d2-p_d1);
    end if;
    if (upper(p_what) = 'MONTH' or upper(p_what) = 'M') then
      l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
    end if;
    if (upper(p_what) = 'QUARTER') then
      l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) - (floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));
    end if;
  l_result:=floor(l_result);
  return l_result;
end;
/

create or replace function
getdate return date
as
l_result    date;
begin
  select sysdate into l_result from dual;
  return sysdate;
end;
/

CREATE SEQUENCE XZFW_Consultation_ID_seq NOCACHE;
create table XZFW_Consultation (
   ID                   integer   constraint XZFW_Consultation_ID_seq primary key not null,
   Name                 varchar2(10)          not null,
   Tel                  varchar2(50)          null,
   Email                varchar2(50)          null,
   Org_id               integer          not null,
   Title                varchar2(100)         null,
   Content              varchar2(3000)        not null,
   Writetime            date            not null,
   Type                 varchar2(2)           not null,
   Flag                 varchar2(2)           null,
   pid                  integer   null,
   User_id              integer   null,
   Same                 int     default 0 null,
   Yid                  integer   null
);