Oracle 创建表SQL实例

/*
--用户表
--select * from sysuser
--drop table sysuser
--drop sequence seq_sysuser
--insert into sysuser (strcode,strname,blnEnable) values ('admin','admin',1);
--insert into sysuser (sysuserid,strcode,strname,blnEnable) values (999,'zhangsan','zhangsan',1);
*/

-- Create table
create table sysuser
(
  sysuserid NUMBER not null,
  strcode       VARCHAR2(50) not null,
  strname       VARCHAR2(500) not null,
  blnEnable     NUMBER(1) not null,  
  datLastSave   DATE default sysdate not null
);
 
-- Add comments to the columns
comment on column sysuser.sysuserid  is '用户ID';
comment on column sysuser.strcode  is '用户编码';
comment on column sysuser.strname  is '用户名称';
comment on column sysuser.blnEnable  is '启用';
comment on column sysuser.datLastSave  is '最近保存时间';
 
-- Create/Recreate primary, unique and foreign key constraints
alter table sysuser  add constraint PK_sysuser primary key (sysuserid);

--alter table sysuser  add constraint FK_sysuser_RefTableID foreign key (RefTableID)
 -- references RefTable (RefTableID) on delete cascade; 
 
--create sequence
create sequence seq_sysuser
minvalue 1 maxvalue 999999999999999999999999999
start with 1 increment by 1 cache 20;

--create trigger
CREATE OR REPLACE TRIGGER tri_sysuser_bi
BEFORE INSERT ON sysuser FOR EACH ROW
BEGIN
  IF (:new.sysuserid is null) or (:new.sysuserid<0) THEN
     SELECT seq_sysuser.nextval INTO :new.sysuserid FROM DUAL;
  END IF;
END;