oracle建表语句

create table RETURN_HOME
(
    ID                 NUMBER not null
        constraint RETURN_HOME_PK
            primary key,
    USER_NUMBER        VARCHAR2(32),
    USER_NAME          VARCHAR2(32),
    APPLICATION_NUMBER VARCHAR2(256),
    APPLICATION_DATE   DATE,
    APPLICATION_STATUS VARCHAR2(32),
    LEAVE_CATEGORY     VARCHAR2(128),
    START_TIME         DATE,
    END_TIME           DATE,
    DIFF_DAYS          NUMBER,
    RETURN_HOME_DATE   DATE,
    RETURN_DATE        DATE,
    GENERAL_HOLIDAY    NUMBER,
    REPORT             NUMBER,
    LEGAL_HOLIDAY      NUMBER,
    LEAVE              NUMBER,
    COMPENSATORY_LEAVE NUMBER,
    OTHERS             NUMBER,
    TOTAL              NUMBER,
    CREATE_TIME        DATE,
    EDIT_TIME          DATE
);

创建表序列

CREATE SEQUENCE S_RETURN_HOME INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999 NOCYCLE CACHE 20 NOORDER;

创建表触发器

create trigger T_RETURN_HOME
    before insert or update
    on RETURN_HOME
    for each row
declare
    nextid   number;
    now_date date;
begin
    select systimestamp into now_date from dual;
    case
        when inserting then
            IF :new.id IS NULL or :new.id = 0 THEN
                select S_RETURN_HOME.nextval into nextid from sys.dual;
                :new.id := nextid;
            end if;
            :new.CREATE_TIME := now_date;
            :new.EDIT_TIME := now_date;
        when updating then
            :new.EDIT_TIME := now_date;
        end case;

end T_RETURN_HOME;