Oracle 创建表SQL实例

最新推荐文章于 2024-04-15 17:01:30 发布

原创最新推荐文章于 2024-04-15 17:01:30 发布 · 741 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle创建表触发器序列

Oracle 专栏收录该内容

13 篇文章

订阅专栏

本文档详细介绍了如何使用SQL语句创建用户表，并包含了字段说明、主键约束设置、序列生成及触发器的实现等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*
--用户表
--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;