Oracle 表创建 ID自增长

最新推荐文章于 2023-03-03 11:59:25 发布

nzm918

最新推荐文章于 2023-03-03 11:59:25 发布

阅读量91

点赞数

分类专栏：数据库文章标签： Oracle Cache

本文链接：https://blog.csdn.net/nzm918/article/details/83810599

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

*************************************************************************

1. 创建表空间

*************************************************************************

create tablespace nsw datafile 'E:\nsw.dbf' size 20M autoextend on next 5M maxsize unlimited;

*************************************************************************

2. 创建用户

*************************************************************************

create user nsw identified by nsw default tablespace nsw;

*************************************************************************

3. 给用户授权

*************************************************************************

grant resource to nsw;
grant create session to nsw;

*************************************************************************
4 创建表icm_cert

*************************************************************************

CREATE TABLE icm_cert (
ID number(11) NOT NULL ,
CERT_USERCODE varchar2(255) NOT NULL,
CERT_SERIALNUMBER varchar2(255) NOT NULL unique ,
CERT_SUBJECT varchar2(255) default NULL,
CERT_BUF clob,
CERT_BEGIN varchar2(255) default NULL,
CERT_END varchar2(255) default NULL,
CERT_TYPE varchar2(255) default NULL,
CERT_STATUS varchar2(255) default NULL,
PRIMARY KEY (ID)
)

*************************************************************************
5 创建序列sequence

*************************************************************************

create sequence icm_cert_seq minvalue 1 maxvalue 999999999999999999 start with 1 increment by 1 cache 20;

*************************************************************************
6创建触发器

*************************************************************************

CREATE OR REPLACE TRIGGER "icm_cert_trig"
BEFORE INSERT ON icm_cert
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
BEGIN
SELECT icm_cert_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END icm_cert_trig;

*************************************************************************
7插入查询删除

*************************************************************************
insert into icm_cert(CERT_USERCODE, CERT_SERIALNUMBER,CERT_SUBJECT,CERT_BUF,CERT_BEGIN,CERT_END,CERT_TYPE,CERT_STATUS) values ('nsw','123','1','2','3','4','5','6');

select * from icm_cert;

delete from icm_cert