词条查询

select
       t2.oid,
       t2.industryname,
       t2.industrycode,
       t2.serialnumber,
       t1.ejoid,
       t1.companyoid,
       t1.diccode,
       t1.industryoid,
       t1.chwordname,
       t1.enwordname
    from ejsjindustry t,ejsjdictionarypub t1,ejsjindustry t2
    where  t.oid = t1.industryoid
         and substr(t.serialnumber,1,length( t2.serialnumber))=t2.serialnumber
         and t.companyoid=t2.companyoid
         and t2.struid is null
 
 /*在线词典查询
 */

/*词类表*/

create table EJSJINDUSTRY
(
  OID            NUMBER(10) not null,
  INDUSTRYNAME   VARCHAR2(50),
  INDUSTRYDES    VARCHAR2(200),
  INDUSTRYCODE   VARCHAR2(30),
  SERIALNUMBER   VARCHAR2(20),
  STRUID         VARCHAR2(20),
  ISLEAF         NUMBER(2),
  CREATETIME     DATE,
  CREATEP        NUMBER(10),
  STATUS         NUMBER(2),
  COMPANYOID     NUMBER(10),
  LASTUPDATEP    NUMBER(10),
  LASTUPDATETIME DATE,
  REMARK         VARCHAR2(100)
)
tablespace EOS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table EJSJINDUSTRY
  is '苏检_词条类别表';
-- Add comments to the columns
comment on column EJSJINDUSTRY.OID
  is '关键字';
comment on column EJSJINDUSTRY.INDUSTRYNAME
  is '词类名';
comment on column EJSJINDUSTRY.INDUSTRYDES
  is '词类描述';
comment on column EJSJINDUSTRY.INDUSTRYCODE
  is '词类代码';
comment on column EJSJINDUSTRY.SERIALNUMBER
  is '树型序列号';
comment on column EJSJINDUSTRY.STRUID
  is '树型结构码';
comment on column EJSJINDUSTRY.ISLEAF
  is '是否叶子节点';
comment on column EJSJINDUSTRY.CREATETIME
  is '创建时间';
comment on column EJSJINDUSTRY.CREATEP
  is '创建人';
comment on column EJSJINDUSTRY.STATUS
  is '状态';
comment on column EJSJINDUSTRY.COMPANYOID
  is '单位主键';
comment on column EJSJINDUSTRY.LASTUPDATEP
  is '最后修改人';
comment on column EJSJINDUSTRY.LASTUPDATETIME
  is '最后修改时间';
comment on column EJSJINDUSTRY.REMARK
  is '备注';
-- Create/Recreate primary, unique and foreign key constraints
alter table EJSJINDUSTRY
  add constraint PK_EJSJINDUSTRY primary key (OID)
  using index
  tablespace EOS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
// 词条表

create table EJSJDICTIONARY
(
  EJOID           NUMBER not null,
  DICCODE         VARCHAR2(20),
  INDUSTRYOID     NUMBER(10),
  CHWORDNAME      VARCHAR2(150),
  CHWORDSIMPLE    VARCHAR2(80),
  ENWORDNAME      VARCHAR2(150),
  ENWORDAB        VARCHAR2(50),
  COMPLEXNUMDESC  VARCHAR2(150),
  ENWORDDESC      VARCHAR2(150),
  FORMALNAME      VARCHAR2(150),
  CHEMICALNAME    VARCHAR2(150),
  LATIONNAME      VARCHAR2(150),
  GENERATENAME    VARCHAR2(150),
  WORDLEVEL       NUMBER(2),
  PROBLEMORNOT    NUMBER(2),
  WORDFROM        VARCHAR2(300),
  VERSION         VARCHAR2(10),
  WORDINPUTPERSON NUMBER(10),
  INPUTTIME       DATE,
  STATUS          NUMBER(2),
  ISVALID         NUMBER(2),
  INVALIDPERSON   NUMBER(10),
  INVALIDREASON   VARCHAR2(100),
  LASTUPDATETIME  DATE,
  LASTUPDATEP     NUMBER(10),
  COMPANYOID      NUMBER(10),
  REMARK          VARCHAR2(2000),
  PICTUREURL      VARCHAR2(200)
)
tablespace EOS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table
comment on table EJSJDICTIONARY
  is '电子词典数据模型';
-- Add comments to the columns
comment on column EJSJDICTIONARY.EJOID
  is '关键字';
comment on column EJSJDICTIONARY.DICCODE
  is '词条编码';
comment on column EJSJDICTIONARY.INDUSTRYOID
  is '词类主关键字';
comment on column EJSJDICTIONARY.CHWORDNAME
  is '中文词条';
comment on column EJSJDICTIONARY.CHWORDSIMPLE
  is '中文简称';
comment on column EJSJDICTIONARY.ENWORDNAME
  is '英文词条';
comment on column EJSJDICTIONARY.ENWORDAB
  is '缩写';
comment on column EJSJDICTIONARY.COMPLEXNUMDESC
  is '特殊复数显示';
comment on column EJSJDICTIONARY.ENWORDDESC
  is '商品名';
comment on column EJSJDICTIONARY.FORMALNAME
  is '相关网址';
comment on column EJSJDICTIONARY.CHEMICALNAME
  is '化学名';
comment on column EJSJDICTIONARY.LATIONNAME
  is '学名';
comment on column EJSJDICTIONARY.GENERATENAME
  is '通用名';
comment on column EJSJDICTIONARY.WORDLEVEL
  is '重要级别';
comment on column EJSJDICTIONARY.PROBLEMORNOT
  is '有无疑问';
comment on column EJSJDICTIONARY.WORDFROM
  is '资料来源1－专业词典2－法律、法规3－专业技术文献4－其他';
comment on column EJSJDICTIONARY.VERSION
  is '版本值';
comment on column EJSJDICTIONARY.WORDINPUTPERSON
  is '输入人';
comment on column EJSJDICTIONARY.INPUTTIME
  is '输入时间';
comment on column EJSJDICTIONARY.STATUS
  is '词条状态';
comment on column EJSJDICTIONARY.ISVALID
  is '是否作废';
comment on column EJSJDICTIONARY.INVALIDPERSON
  is '作废人';
comment on column EJSJDICTIONARY.INVALIDREASON
  is '作废原因';
comment on column EJSJDICTIONARY.LASTUPDATETIME
  is '最后修改时间';
comment on column EJSJDICTIONARY.LASTUPDATEP
  is '最后修改人';
comment on column EJSJDICTIONARY.COMPANYOID
  is '单位主键';
comment on column EJSJDICTIONARY.REMARK
  is '备注';
comment on column EJSJDICTIONARY.PICTUREURL
  is '图片路径';
-- Create/Recreate primary, unique and foreign key constraints
alter table EJSJDICTIONARY
  add constraint PK_EJSJDICTIONARY primary key (EJOID)
  using index
  tablespace EOS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );