建表主键自增_oracel建表，并创建主键自增序列

--DROP TABLE MGMT_CHL_STORE_BUSI_SCALE CASCADE CONSTRAINTS;

/*==============================================================*/

/* Table: MGMT_CHL_STORE_BUSI_SCALE */

/*==============================================================*/

CREATE TABLE MGMT_CHL_STORE_BUSI_SCALE

(

"SEQ_ID" NUMBER(8) PRIMARY KEY ,

"CHANNEL_CODE" VARCHAR2(32),

"CHANNEL_NAME" VARCHAR2(64),

"BUSINESS_DAY" VARCHAR2(32),

"BUSINESS_CREATE_TIME1" VARCHAR2(64),

"BUSINESS_CREATE_TIME2" VARCHAR2(64),

"BUSINESS_CREATE_TIME3" VARCHAR2(64),

"BUSINESS_CREATE_TIME4" VARCHAR2(64),

"BUSINESS_CREATE_TIME5" VARCHAR2(64),

"BUSINESS_CREATE_TIME6" VARCHAR2(64),

"BUSINESS_CREATE_TIME7" VARCHAR2(64),

"BUSINESS_CREATE_TIME8" VARCHAR2(64),

"BUSINESS_CREATE_TIME9" VARCHAR2(64),

"BUSINESS_CREATE_TIME10" VARCHAR2(64),

"BUSINESS_CREATE_TIME11" VARCHAR2(64),

"BUSINESS_CREATE_TIME12" VARCHAR2(64),

"DAY_BUSINESS_TOTAL" VARCHAR2(64),

"STATE" VARCHAR2(8),

CREATE_OPER_ID VARCHAR2(32),

CREATE_TIME DATE,

REMARK VARCHAR2(64),

"BUSINESS_MONTH" VARCHAR2(64)

);

COMMENT ON TABLE MGMT_CHL_STORE_BUSI_SCALE IS

'营业厅业务办理量表';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."CHANNEL_CODE" IS

'营业厅编码';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."CHANNEL_NAME" IS

'营业厅名称';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_DAY" IS

'业务办理日期20191113';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME1" IS

'业务具体办理时间点1';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME2" IS

'业务具体办理时间点2';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME3" IS

'业务具体办理时间点3';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME4" IS

'业务具体办理时间点4';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME5" IS

'业务具体办理时间点5';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME6" IS

'业务具体办理时间点6';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME7" IS

'业务具体办理时间点7';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME8" IS

'业务具体办理时间点8';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME9" IS

'业务具体办理时间点9';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME10" IS

'业务具体办理时间点10';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME11" IS

'业务具体办理时间点11';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_CREATE_TIME12" IS

'业务具体办理时间点12';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."DAY_BUSINESS_TOTAL" IS

'日合计办理量';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."STATE" IS

'状态(0,终止,1:正常)';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE.CREATE_OPER_ID IS

'创建者ID(登录名)';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE.CREATE_TIME IS

'创建时间';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE.REMARK IS

'备注';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."BUSINESS_MONTH" IS

'业务办理月201911';

COMMENT ON COLUMN MGMT_CHL_STORE_BUSI_SCALE."SEQ_ID" IS

'流水主键';

--创建序列/自增值

create sequence S_MGMT_CHL_STORE_BUSI_SCALE

minvalue 1

maxvalue 99999999

start with 1

increment by 1

NOCYCLE

nocache;

--建完表和自增值后键一个触发器

create or replace trigger mem_trig_scale before

insert on MGMT_CHL_STORE_BUSI_SCALE for each row when (new.SEQ_ID is null)

begin

select S_MGMT_CHL_STORE_BUSI_SCALE.nextval into:new.SEQ_ID from dual;

end;

--以下亲测可用，注意测试insert into()时，不要插入SEQ_ID

--SELECT * from MGMT_CHL_STORE_BUSI_SCALE;

--INSERT INTO MGMT_CHL_STORE_BUSI_SCALE (CHANNEL_CODE,CHANNEL_NAME,BUSINESS_DAY,BUSINESS_CREATE_TIME1,BUSINESS_CREATE_TIME2,BUSINESS_CREATE_TIME3,BUSINESS_CREATE_TIME4,BUSINESS_CREATE_TIME5,BUSINESS_CREATE_TIME6,BUSINESS_CREATE_TIME7,BUSINESS_CREATE_TIME8,BUSINESS_CREATE_TIME9,BUSINESS_CREATE_TIME10,BUSINESS_CREATE_TIME11,BUSINESS_CREATE_TIME12,DAY_BUSINESS_TOTAL,STATE,CREATE_OPER_ID,CREATE_TIME,REMARK,BUSINESS_MONTH)

--VALUES ('2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19' ,NULL ,null,'22');

--DROP SEQUENCE S_MGMT_CHL_STORE_BUSI_SCALE;

--drop TRIGGER mem_trig_scale;

--drop table MGMT_CHL_STORE_BUSI_SCALE;

--TRUNCATE TABLE MGMT_CHL_STORE_BUSI_SCALE;

标签：自增,SCALE,建表,BUSINESS,CREATE,CHL,BUSI,MGMT,主键

来源： https://www.cnblogs.com/zhangliwei/p/12785146.html