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

-- Add/modify columns 
alter table PL_MAJOR add is_new_major NUMBER(1);
-- Add comments to the columns 
comment on column PL_MAJOR.is_new_major
  is '是否新专业，0：否 1：是';
  -- Add/modify columns 
alter table PL_MAJOR modify major_code VARCHAR2(8);
alter table PL_MAJOR modify major_standard_code VARCHAR2(8);

-- Create table
create table PL_MAJOR_OLD_NEW
(
  id           NUMBER(10) not null,
  old_major_id NUMBER(10),
  new_major_id NUMBER(10)
)
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 PL_MAJOR_OLD_NEW
  is '新旧专业对应表';
-- Add comments to the columns 
comment on column PL_MAJOR_OLD_NEW.id
  is '主键';
comment on column PL_MAJOR_OLD_NEW.old_major_id
  is '旧专业ID 和pl_major表关联';
comment on column PL_MAJOR_OLD_NEW.new_major_id
  is '新专业ID 和pl_majo表关联';
-- Create/Recreate primary, unique and foreign key constraints 
alter table PL_MAJOR_OLD_NEW
  add constraint PK_PL_MAJOR_OLD_NEW_ID primary key (ID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
  
drop table pl_school_major;
drop sequence SEQ_pl_school_major;
create table  pl_school_major
(
       ID                NUMBER(10) not null,
       school_id         NUMBER(10),
       major_code        VARCHAR2(8),
       status            NUMBER(2)
);
alter  table pl_school_major
       add constraint PK_pl_school_major_ID primary key (ID);
comment on table pl_school_major is '专业对应的主考学校 主考学校开考了哪些专业';
comment on column pl_school_major.ID is 'ID';
comment on column pl_school_major.school_id is '主考学校ID 和机构表中的ID关联';
comment on column pl_school_major.major_code is '专业code 专业表中的major_code';
comment on column pl_school_major.status is '状态 0：禁用；1：启用';
create sequence SEQ_pl_school_major;


alter table PL_SCHOOL_MAJOR add school_code VARCHAR2(5);
  
  
create table  std_resume
(
       ID                NUMBER(10),
       std_id            NUMBER(10),
       start_date        DATE,
       end_date          DATE,
       work_exp          VARCHAR2(120),
       position          VARCHAR2(60),
       by_apply_id       NUMBER(10)
);
comment on table std_resume is '学生毕业简历';
comment on column std_resume.ID is '主键';
comment on column std_resume.std_id is '考生ID';
comment on column std_resume.start_date is '开始日期';
comment on column std_resume.end_date is '截止日期';
comment on column std_resume.work_exp is '工作经验';
comment on column std_resume.position is '职务';
comment on column std_resume.by_apply_id is '毕业ID';
create sequence SEQ_std_resume;  

create table  sys_operate_log
(
       ID                NUMBER(10) not null,
       method_name       VARCHAR2(35),
       user_id           NUMBER(10),
       oper_uri          VARCHAR2(150),
       request_time      DATE,
       oper_params       VARCHAR2(1024),
       session_id        VARCHAR2(61),
       remote_ip         VARCHAR2(15)
);
alter  table sys_operate_log
       add constraint PK_sys_operate_log_ID primary key (ID);
comment on table sys_operate_log is '操作日志表';
comment on column sys_operate_log.ID is 'ID';
comment on column sys_operate_log.method_name is '方法名';
comment on column sys_operate_log.user_id is '操作用户ID';
comment on column sys_operate_log.oper_uri is 'uri';
comment on column sys_operate_log.request_time is '请求时间';
comment on column sys_operate_log.oper_params is '参数内容';
comment on column sys_operate_log.session_id is '用户会话ID';
comment on column sys_operate_log.remote_ip is '用户ip';
create sequence SEQ_sys_operate_log;

drop sequence SEQ_by_apply;
drop table by_apply;
create table  by_apply
(
       ID                NUMBER(10) not null,
       std_id            NUMBER(10),
       enrol_id          NUMBER(10),
       major_id          NUMBER(10),
       by_time_config_id NUMBER(10),
       ticket_no         VARCHAR2(12),
       apply_date       DATE,
       std_source        NUMBER(10),
       commend           VARCHAR2(100),
       skill             VARCHAR2(100),
       apply_type        NUMBER(1),
       school_id         NUMBER(10),
       city_org_id       NUMBER(10),
       file_path         VARCHAR2(150),
       is_passed         NUMBER(1),
       status            NUMBER(1),
       is_auth_back      NUMBER(1),
       remark            VARCHAR2(150),
       STD_SEX           NUMBER(1),
       NATIVE_PROV       VARCHAR2(150),
       NATIVE_CITY       VARCHAR2(10),
       NATION            NUMBER(1),
       POLITICAL         NUMBER(1),
       PRE_EDU           VARCHAR2(30),
       WORK_UNIT         VARCHAR2(60),
       ADDRESS           VARCHAR2(150),
       POST_CODE         VARCHAR2(10),
       DIAPLMA_NO        VARCHAR2(30),
       TELEPHONE         VARCHAR2(12),
       STD_BIRTH         DATE
);
alter  table by_apply
       add constraint PK_by_apply_ID primary key (ID);
comment on table by_apply is '毕业申请表';
comment on column by_apply.ID is '主键';
comment on column by_apply.std_id is '学生注册ID';
comment on column by_apply.enrol_id is '报考ID';
comment on column by_apply.major_id is '专业ID';
comment on column by_apply.by_time_config_id is '毕业时间配置ID';
comment on column by_apply.ticket_no is '准考证';
comment on column by_apply.std_source is '学生来源 0-社会考生,1-其他网络助学，助学班';
comment on column by_apply.commend is '嘉奖';
comment on column by_apply.skill is '技能';
comment on column by_apply.apply_type is '申请类型 0-考生正常申请;1-后台手工办证;2-考生手工办证';
comment on column by_apply.school_id is '主考学校';
comment on column by_apply.city_org_id is '市考办ID 社会考生';
comment on column by_apply.file_path is '文件路径';
comment on column by_apply.is_passed is '是否通过';
comment on column by_apply.status is '状态 状态 0-新建,1-待一级审核,2-待终审,3-待发放,4-待上报,5-已上报';
comment on column by_apply.is_auth_back is '是否驳回';
comment on column by_apply.remark is '备注';
comment on column by_apply.STD_SEX is '性别';
comment on column by_apply.NATIVE_PROV is '毕业申请-籍贯-省市自治区';
comment on column by_apply.NATIVE_CITY is '毕业申请-籍贯-市';
comment on column by_apply.NATION is '毕业申请-民族';
comment on column by_apply.POLITICAL is '毕业申请-政治面貌';
comment on column by_apply.PRE_EDU is '毕业申请-考前学历';
comment on column by_apply.WORK_UNIT is '毕业申请-工作单位';
comment on column by_apply.ADDRESS is '毕业申请-家庭住址';
comment on column by_apply.POST_CODE is '毕业申请-邮政编码';
comment on column by_apply.DIAPLMA_NO is '毕业申请-毕业证书号';
comment on column by_apply.TELEPHONE is '毕业申请-联系电话';
comment on column by_apply.STD_BIRTH is '毕业申请-出生年月';
create sequence SEQ_by_apply;

create table  pl_major_apply_audit_file
(
       ID                NUMBER(10) not null,
       apply_form        NUMBER(1),
       year_code         NUMBER(6),
       file_path         VARCHAR2(120)
);
alter  table pl_major_apply_audit_file
       add constraint PK_pl_majoile_ID5E18 primary key (ID);
comment on table pl_major_apply_audit_file is '申报审批批文';
comment on column pl_major_apply_audit_file.ID is '主键';
comment on column pl_major_apply_audit_file.apply_form is '申报形式';
comment on column pl_major_apply_audit_file.year_code is '年度码';
comment on column pl_major_apply_audit_file.file_path is '审批批文';
create sequence SEQ_pl_major_apply_audit_file;