--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;