server/ses-manage/sql/2019-06/2019-06-27.sql

-- Create table
create table STD_REG_INFO_LOG
(
  id                NUMBER(10) not null,
  ticket_no         VARCHAR2(20) not null,
  enrol_no          VARCHAR2(9),
  std_name          VARCHAR2(40),
  cert_no           VARCHAR2(50),
  std_pass          VARCHAR2(32),
  std_eng_name      VARCHAR2(40),
  std_family_reg    VARCHAR2(32),
  std_birth_place   VARCHAR2(100),
  std_sex           VARCHAR2(2),
  political_status  VARCHAR2(6),
  std_nation        VARCHAR2(3),
  std_edu           VARCHAR2(2),
  std_occupation    VARCHAR2(10),
  std_healthy       VARCHAR2(2),
  major_id          NUMBER(10),
  major_code        VARCHAR2(30),
  std_birth         VARCHAR2(40),
  std_mobile        VARCHAR2(11),
  std_postcode      VARCHAR2(50),
  std_address       VARCHAR2(200),
  std_email         VARCHAR2(60),
  remark            VARCHAR2(500),
  std_source        NUMBER(4),
  enrol_county_city VARCHAR2(6),
  belong_city_id    VARCHAR2(6),
  enrol_place       VARCHAR2(6),
  assist_id         NUMBER(10),
  school_id         NUMBER(10),
  learn_form        NUMBER(2),
  old_ticket_no1    VARCHAR2(20),
  old_ticket_no2    VARCHAR2(20),
  exam_area_id      NUMBER(10),
  photo_url         VARCHAR2(150),
  school_code       VARCHAR2(20),
  junior_no         VARCHAR2(50),
  undergradu_no     VARCHAR2(50),
  status            NUMBER(10),
  reg_time          VARCHAR2(50),
  exam_time_id      VARCHAR2(10),
  is_new_stu        NUMBER(1),
  operator          VARCHAR2(30),
  oper_time         DATE,
  oper_type         NUMBER(2),
  oper_time_temp    VARCHAR2(50)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table STD_REG_INFO_LOG
  is '考生操作日志表';
-- Add comments to the columns 
comment on column STD_REG_INFO_LOG.ticket_no
  is '准考证号';
comment on column STD_REG_INFO_LOG.enrol_no
  is '报名号';
comment on column STD_REG_INFO_LOG.std_name
  is '姓名';
comment on column STD_REG_INFO_LOG.cert_no
  is '身份证号';
comment on column STD_REG_INFO_LOG.std_pass
  is '密码';
comment on column STD_REG_INFO_LOG.std_eng_name
  is '英文名';
comment on column STD_REG_INFO_LOG.std_family_reg
  is '户籍';
comment on column STD_REG_INFO_LOG.std_birth_place
  is '籍贯';
comment on column STD_REG_INFO_LOG.std_sex
  is '性别';
comment on column STD_REG_INFO_LOG.political_status
  is '政治面貌';
comment on column STD_REG_INFO_LOG.std_nation
  is '民族';
comment on column STD_REG_INFO_LOG.std_edu
  is '学历';
comment on column STD_REG_INFO_LOG.std_occupation
  is '职业';
comment on column STD_REG_INFO_LOG.std_healthy
  is '健康状况';
comment on column STD_REG_INFO_LOG.major_id
  is '报考专业';
comment on column STD_REG_INFO_LOG.major_code
  is '报考专业代码';
comment on column STD_REG_INFO_LOG.std_birth
  is '出生日期';
comment on column STD_REG_INFO_LOG.std_mobile
  is '手机号码';
comment on column STD_REG_INFO_LOG.std_postcode
  is '邮编';
comment on column STD_REG_INFO_LOG.std_address
  is '住址';
comment on column STD_REG_INFO_LOG.std_email
  is '邮件';
comment on column STD_REG_INFO_LOG.remark
  is '备注';
comment on column STD_REG_INFO_LOG.std_source
  is '考生来源：社会考生和助学类考生(助学班)';
comment on column STD_REG_INFO_LOG.enrol_county_city
  is '报名县市';
comment on column STD_REG_INFO_LOG.belong_city_id
  is '所属地市';
comment on column STD_REG_INFO_LOG.enrol_place
  is '报名点';
comment on column STD_REG_INFO_LOG.assist_id
  is '助学班属性';
comment on column STD_REG_INFO_LOG.school_id
  is '主考院校ID';
comment on column STD_REG_INFO_LOG.learn_form
  is '学习形式';
comment on column STD_REG_INFO_LOG.old_ticket_no1
  is '老考号1';
comment on column STD_REG_INFO_LOG.old_ticket_no2
  is '老考号2';
comment on column STD_REG_INFO_LOG.exam_area_id
  is '考区ID';
comment on column STD_REG_INFO_LOG.photo_url
  is '考生头像地址';
comment on column STD_REG_INFO_LOG.school_code
  is '主考学校code';
comment on column STD_REG_INFO_LOG.junior_no
  is '专科毕业证';
comment on column STD_REG_INFO_LOG.undergradu_no
  is '本科毕业证';
comment on column STD_REG_INFO_LOG.status
  is '提交状态';
comment on column STD_REG_INFO_LOG.reg_time
  is '注册时间';
comment on column STD_REG_INFO_LOG.exam_time_id
  is '注册批次';
comment on column STD_REG_INFO_LOG.is_new_stu
  is '新旧考生：只针对新旧专业衔接，0：老考生，1：新考生';
comment on column STD_REG_INFO_LOG.operator
  is '操作人：操作账号+登录人姓名';
comment on column STD_REG_INFO_LOG.oper_time
  is '操作时间，精确到时分秒';
comment on column STD_REG_INFO_LOG.oper_type
  is '操作类型：0：新增1：修改、2：删除3：确认4：操作失败';
comment on column STD_REG_INFO_LOG.oper_time_temp
  is '临时字段，数据插入后，删除该字段';
-- Create/Recreate primary, unique and foreign key constraints 
alter table STD_REG_INFO_LOG
  add constraint PK_STD_REG_INFO_LOG_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

  
-- Create sequence 
create sequence SEQ_STD_REG_INFO_LOG
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;
  
  
 -- Add/modify columns 
alter table KJ_BY_INPUT_SCORE add course_code VARCHAR2(6);
alter table KJ_BY_INPUT_SCORE add major_code VARCHAR2(8);
-- Add comments to the columns 
comment on column KJ_BY_INPUT_SCORE.course_code
  is '课程代码';
comment on column KJ_BY_INPUT_SCORE.major_code
  is '专业代码';
 
alter table KJ_UNIFIED_SCORE_DETAIL modify origin_score VARCHAR2(8);
alter table KJ_UNIFIED_SCORE_DETAIL modify final_score VARCHAR2(8);