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

? 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

?

?