all-project/用户信息同步统一身份认证/3.在认证中间库建立表.sql

-- Create table
create table GXXX_YXSDWJBSJZL
(
  dwh    VARCHAR2(10) not null,
  dwmc   VARCHAR2(60),
  dwywmc VARCHAR2(180),
  dwjc   VARCHAR2(12),
  dwywjc VARCHAR2(8),
  dwjp   VARCHAR2(6),
  dwdz   VARCHAR2(180),
  lsdwh  VARCHAR2(10),
  dwlbm  VARCHAR2(1),
  dwbbm  VARCHAR2(1),
  dwyxbs VARCHAR2(1),
  sxrq   VARCHAR2(8),
  sfst   VARCHAR2(1),
  jlny   VARCHAR2(6),
  dwfzrh VARCHAR2(20),
  zhgxsj VARCHAR2(20) default to_char(sysdate,'yyyymmddhh24miss'),
  scbz   VARCHAR2(1),
  dwjb   VARCHAR2(8)
);

-- Add comments to the table 
comment on table GXXX_YXSDWJBSJZL  is 'GXXX0301院系所单位基本数据子类';
-- Add comments to the columns 
comment on column GXXX_YXSDWJBSJZL.dwh  is '单位号';
comment on column GXXX_YXSDWJBSJZL.dwmc  is '单位名称';
comment on column GXXX_YXSDWJBSJZL.dwywmc  is '单位英文名称';
comment on column GXXX_YXSDWJBSJZL.dwjc  is '单位简称';
comment on column GXXX_YXSDWJBSJZL.dwywjc  is '单位英文简称';
comment on column GXXX_YXSDWJBSJZL.dwjp  is '单位简拼';
comment on column GXXX_YXSDWJBSJZL.dwdz  is '单位地址';
comment on column GXXX_YXSDWJBSJZL.lsdwh  is '隶属单位号';
comment on column GXXX_YXSDWJBSJZL.dwlbm  is '单位类别码';
comment on column GXXX_YXSDWJBSJZL.dwbbm  is '单位办别码';
comment on column GXXX_YXSDWJBSJZL.dwyxbs  is '单位有效标识';
comment on column GXXX_YXSDWJBSJZL.sxrq  is '失效日期';
comment on column GXXX_YXSDWJBSJZL.sfst  is '是否实体';
comment on column GXXX_YXSDWJBSJZL.jlny  is '建立年月';
comment on column GXXX_YXSDWJBSJZL.dwfzrh  is '单位负责人号';
comment on column GXXX_YXSDWJBSJZL.zhgxsj  is '最后更新时间';
comment on column GXXX_YXSDWJBSJZL.scbz   is '删除标志：y/n';
comment on column GXXX_YXSDWJBSJZL.dwjb  is '单位级别';
-- Create/Recreate primary, unique and foreign key constraints 
alter table GXXX_YXSDWJBSJZL  add primary key (DWH);
/

-- Create table
create table GXJG_JZGJCSJZLB
(
  gh      VARCHAR2(20) not null,
  dwh     VARCHAR2(10) not null,
  xm      VARCHAR2(36),
  ywxm    VARCHAR2(60),
  xmpy    VARCHAR2(60),
  cym     VARCHAR2(36),
  xbm     VARCHAR2(1),
  csrq    VARCHAR2(8),
  csdm    VARCHAR2(6),
  jg      VARCHAR2(20),
  mzm     VARCHAR2(2),
  gjdqm   VARCHAR2(3),
  sfzjlxm VARCHAR2(1),
  sfzjh   VARCHAR2(20),
  hyztm   VARCHAR2(2),
  gatqwm  VARCHAR2(2),
  zzmmm   VARCHAR2(2),
  jkzkm   VARCHAR2(2),
  xyzjm   VARCHAR2(2),
  xxm     VARCHAR2(1),
  zp      BLOB,
  sfzjyxq VARCHAR2(17),
  xqh     VARCHAR2(2),
  zgxlm   VARCHAR2(3),
  whcdm   VARCHAR2(3),
  cjgzny  VARCHAR2(6),
  lxrq    VARCHAR2(8),
  qxrq    VARCHAR2(8),
  cjny    VARCHAR2(6),
  bzlbm   VARCHAR2(2),
  jzglbm  VARCHAR2(3),
  rkzkm   VARCHAR2(2),
  dabh    VARCHAR2(20),
  dawb    CLOB,
  dqztm   VARCHAR2(2),
  tc      VARCHAR2(100),
  xklbm   VARCHAR2(6),
  yjxkm   VARCHAR2(4),
  ejxkm   VARCHAR2(6),
  yjfx    VARCHAR2(100),
  zhgxsj  VARCHAR2(20) default to_char(sysdate,'yyyymmddhh24miss'),
  scbz    VARCHAR2(1),
  zhxw    VARCHAR2(32),
  zc      VARCHAR2(32),
  ygxs    VARCHAR2(2)
);

-- Add comments to the table 
comment on table GXJG_JZGJCSJZLB  is 'GXJG0101教职工基础数据子类表';
-- Add comments to the columns 
comment on column GXJG_JZGJCSJZLB.gh  is '工号';
comment on column GXJG_JZGJCSJZLB.dwh  is '单位号';
comment on column GXJG_JZGJCSJZLB.xm  is '姓名';
comment on column GXJG_JZGJCSJZLB.ywxm  is '英文姓名';
comment on column GXJG_JZGJCSJZLB.xmpy  is '姓名拼音';
comment on column GXJG_JZGJCSJZLB.cym  is '曾用名';
comment on column GXJG_JZGJCSJZLB.xbm  is '性别码';
comment on column GXJG_JZGJCSJZLB.csrq  is '出生日期';
comment on column GXJG_JZGJCSJZLB.csdm  is '出生地码';
comment on column GXJG_JZGJCSJZLB.jg  is '籍贯';
comment on column GXJG_JZGJCSJZLB.mzm  is '民族码';
comment on column GXJG_JZGJCSJZLB.gjdqm  is '国籍/地区码';
comment on column GXJG_JZGJCSJZLB.sfzjlxm  is '身份证件类型码';
comment on column GXJG_JZGJCSJZLB.sfzjh  is '身份证件号';
comment on column GXJG_JZGJCSJZLB.hyztm  is '婚姻状况码';
comment on column GXJG_JZGJCSJZLB.gatqwm  is '港澳台侨外码';
comment on column GXJG_JZGJCSJZLB.zzmmm  is '政治面貌码';
comment on column GXJG_JZGJCSJZLB.jkzkm  is '健康状况码[DM_GB_B_JKZKEWSZDM]';
comment on column GXJG_JZGJCSJZLB.xyzjm  is '信仰宗教码';
comment on column GXJG_JZGJCSJZLB.xxm  is '血型码';
comment on column GXJG_JZGJCSJZLB.zp  is '照片';
comment on column GXJG_JZGJCSJZLB.sfzjyxq  is '身份证件有效期';
comment on column GXJG_JZGJCSJZLB.xqh  is '校区号';
comment on column GXJG_JZGJCSJZLB.zgxlm  is '最高学历码';
comment on column GXJG_JZGJCSJZLB.whcdm  is '文化程度码';
comment on column GXJG_JZGJCSJZLB.cjgzny  is '参加工作年月';
comment on column GXJG_JZGJCSJZLB.lxrq  is '来校日期';
comment on column GXJG_JZGJCSJZLB.qxrq  is '起薪日期';
comment on column GXJG_JZGJCSJZLB.cjny  is '从教年月';
comment on column GXJG_JZGJCSJZLB.bzlbm  is '编制类别码';
comment on column GXJG_JZGJCSJZLB.jzglbm  is '教职工类别码';
comment on column GXJG_JZGJCSJZLB.rkzkm  is '任课状况码';
comment on column GXJG_JZGJCSJZLB.dabh  is '档案编号';
comment on column GXJG_JZGJCSJZLB.dawb  is '档案文本';
comment on column GXJG_JZGJCSJZLB.dqztm  is '当前状态码';
comment on column GXJG_JZGJCSJZLB.tc  is '特长';
comment on column GXJG_JZGJCSJZLB.xklbm  is '学科类别码';
comment on column GXJG_JZGJCSJZLB.yjxkm  is '一级学科码';
comment on column GXJG_JZGJCSJZLB.ejxkm  is '二级学科码';
comment on column GXJG_JZGJCSJZLB.yjfx  is '研究方向';
comment on column GXJG_JZGJCSJZLB.zhgxsj  is '最后更新时间';
comment on column GXJG_JZGJCSJZLB.scbz  is '删除标志：y/n';
comment on column GXJG_JZGJCSJZLB.zhxw  is '最后学位';
comment on column GXJG_JZGJCSJZLB.zc  is '职称';
comment on column GXJG_JZGJCSJZLB.ygxs  is '用工形式';
-- Create/Recreate primary, unique and foreign key constraints 
alter table GXJG_JZGJCSJZLB  add primary key (GH);
/

-- Create table
create table GXXS_XSJBSJZL
(
  xh      VARCHAR2(20) not null,
  xm      VARCHAR2(60),
  ywxm    VARCHAR2(60),
  xmpy    VARCHAR2(60),
  cym     VARCHAR2(60),
  xbm     VARCHAR2(1),
  csrq    VARCHAR2(10),
  csdm    VARCHAR2(200),
  jg      VARCHAR2(60),
  mzm     VARCHAR2(2),
  gjdqm   VARCHAR2(50),
  sfzjlxm VARCHAR2(2),
  sfzjh   VARCHAR2(20),
  hyzkm   VARCHAR2(10),
  gatqwm  VARCHAR2(2),
  zzmmm   VARCHAR2(2),
  jkzkm   VARCHAR2(20),
  xyzjm   VARCHAR2(50),
  xxm     VARCHAR2(30),
  sfzjyxq VARCHAR2(17),
  sfdszn  VARCHAR2(1),
  zp      BLOB,
  zhgxsj  VARCHAR2(20) default to_char(sysdate,'yyyymmddhh24miss'),
  scbz    VARCHAR2(1),
  ksh     VARCHAR2(20),
  dwh     VARCHAR2(20) not null,
  szbh    VARCHAR2(20),
  sfzx    VARCHAR2(10),
  sfyby   VARCHAR2(6)
);

-- Add comments to the table 
comment on table GXXS_XSJBSJZL  is 'GXXS0101学生基本数据子类';
-- Add comments to the columns 
comment on column GXXS_XSJBSJZL.xh   is '学号';
comment on column GXXS_XSJBSJZL.xm   is '姓名';
comment on column GXXS_XSJBSJZL.ywxm  is '英文姓名';
comment on column GXXS_XSJBSJZL.xmpy  is '姓名拼音';
comment on column GXXS_XSJBSJZL.cym    is '曾用名';
comment on column GXXS_XSJBSJZL.xbm   is '性别码';
comment on column GXXS_XSJBSJZL.csrq    is '出生日期';
comment on column GXXS_XSJBSJZL.csdm  is '出生地码';
comment on column GXXS_XSJBSJZL.jg       is '籍贯';
comment on column GXXS_XSJBSJZL.mzm     is '民族码';
comment on column GXXS_XSJBSJZL.gjdqm   is '国籍/地区码';
comment on column GXXS_XSJBSJZL.sfzjlxm  is '身份证件类型码';
comment on column GXXS_XSJBSJZL.sfzjh     is '身份证件号';
comment on column GXXS_XSJBSJZL.hyzkm  is '婚姻状况码';
comment on column GXXS_XSJBSJZL.gatqwm  is '港澳台侨外码';
comment on column GXXS_XSJBSJZL.zzmmm  is '政治面貌码';
comment on column GXXS_XSJBSJZL.jkzkm  is '健康状况码';
comment on column GXXS_XSJBSJZL.xyzjm  is '信仰宗教码';
comment on column GXXS_XSJBSJZL.xxm     is '血型码';
comment on column GXXS_XSJBSJZL.sfzjyxq  is '身份证件有效期';
comment on column GXXS_XSJBSJZL.sfdszn  is '是否独生子女';
comment on column GXXS_XSJBSJZL.zp        is '照片';
comment on column GXXS_XSJBSJZL.zhgxsj  is '最后更新时间';
comment on column GXXS_XSJBSJZL.scbz   is '删除标志：y/n';
comment on column GXXS_XSJBSJZL.ksh    is '考生号';
comment on column GXXS_XSJBSJZL.dwh   is 'null';
comment on column GXXS_XSJBSJZL.szbh   is '所在班号';
comment on column GXXS_XSJBSJZL.sfzx    is '是否在校';
comment on column GXXS_XSJBSJZL.sfyby  is '是否已毕业';
-- Create/Recreate primary, unique and foreign key constraints 
alter table GXXS_XSJBSJZL  add primary key (XH);
/

-- Create table
create table GXXS_GRTXZL
(
  yzbm   VARCHAR2(6),
  txdz   VARCHAR2(180),
  dh     VARCHAR2(50),
  yddh   VARCHAR2(30),
  czdh   VARCHAR2(30),
  dzxx   VARCHAR2(40),
  wldz   VARCHAR2(60),
  jstxh  VARCHAR2(40),
  xh     VARCHAR2(20) not null,
  zhgxsj VARCHAR2(20) default to_char(sysdate,'yyyymmddhh24miss'),
  scbz   VARCHAR2(1)
);

-- Add comments to the table 
comment on table GXXS_GRTXZL  is 'GXXS0109学生个人通讯子类';
-- Add comments to the columns 
comment on column GXXS_GRTXZL.yzbm  is '邮政编码';
comment on column GXXS_GRTXZL.txdz  is '通信地址';
comment on column GXXS_GRTXZL.dh  is '电话';
comment on column GXXS_GRTXZL.yddh  is '移动电话';
comment on column GXXS_GRTXZL.czdh  is '传真电话';
comment on column GXXS_GRTXZL.dzxx  is '电子信箱';
comment on column GXXS_GRTXZL.wldz  is '网络地址';
comment on column GXXS_GRTXZL.jstxh  is '即时通讯号';
comment on column GXXS_GRTXZL.xh  is '学号';
comment on column GXXS_GRTXZL.zhgxsj  is '最后更新时间';
comment on column GXXS_GRTXZL.scbz  is '删除标志：y/n';
-- Create/Recreate primary, unique and foreign key constraints 
alter table GXXS_GRTXZL  add primary key (XH);
/

-- Create table
create table GXJG_GRTXFSZL
(
  yzbm   VARCHAR2(6),
  txdz   VARCHAR2(180),
  dh     VARCHAR2(30),
  yddh   VARCHAR2(30),
  czdh   VARCHAR2(30),
  wldz   VARCHAR2(60),
  jstxh  VARCHAR2(40),
  gh     VARCHAR2(20) not null,
  dzxx   VARCHAR2(40),
  zhgxsj VARCHAR2(20) default to_char(sysdate,'yyyymmddhh24miss'),
  scbz   VARCHAR2(1),
  id     VARCHAR2(48) not null
);

-- Add comments to the table 
comment on table GXJG_GRTXFSZL  is 'GXJG0102教职工个人通讯方式子类';
-- Add comments to the columns 
comment on column GXJG_GRTXFSZL.yzbm  is '邮政编码';
comment on column GXJG_GRTXFSZL.txdz  is '通信地址';
comment on column GXJG_GRTXFSZL.dh  is '电话';
comment on column GXJG_GRTXFSZL.yddh  is '移动电话';
comment on column GXJG_GRTXFSZL.czdh  is '传真电话';
comment on column GXJG_GRTXFSZL.wldz  is '网络地址';
comment on column GXJG_GRTXFSZL.jstxh  is '即时通讯号';
comment on column GXJG_GRTXFSZL.gh  is '工号';
comment on column GXJG_GRTXFSZL.dzxx  is '电子信箱';
comment on column GXJG_GRTXFSZL.zhgxsj  is '最后更新时间';
comment on column GXJG_GRTXFSZL.scbz  is '删除标志：y/n';
-- Create/Recreate primary, unique and foreign key constraints 
alter table GXJG_GRTXFSZL  add primary key (ID);
/

-- Create table
create table ZFTAL_XTGL_ERRLOG
(
  YWXTMC VARCHAR2(100),
  YWXTBM VARCHAR2(100),
  YWXTCZ VARCHAR2(200),
  SJCFQ VARCHAR2(100),
  SJCCGC VARCHAR2(100),
  CWZDXX VARCHAR2(1000),
  CWSJ   VARCHAR2(20) default to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
);

-- Add comments to the table 
comment on table ZFTAL_XTGL_ERRLOG  is '系统错误日志表';
-- Add comments to the columns 
comment on column ZFTAL_XTGL_ERRLOG.YWXTMC  is '业务系统名称（例：认证系统）';
comment on column ZFTAL_XTGL_ERRLOG.YWXTBM  is '业务系统表名（例：ZFTAL_XTGL_YHB）';
comment on column ZFTAL_XTGL_ERRLOG.YWXTCZ  is '业务系统操作（例：修改学生用户联系电话,地址邮箱）';
comment on column ZFTAL_XTGL_ERRLOG.SJCFQ  is '操作涉及触发器（例：pro_gxxs_grtxzl）';
comment on column ZFTAL_XTGL_ERRLOG.SJCCGC  is '操作涉及存储过程（例：pro_gxxs_grtxzl）';
comment on column ZFTAL_XTGL_ERRLOG.CWZDXX  is '显示会导致报错的字段（例如： ZGH:200010）';
comment on column ZFTAL_XTGL_ERRLOG.CWSJ  is '错误时间';
/