词类下所有词条信息

/*oracle 存储过程*/

create or replace function GetParentNodeName(leafNode in number) return varchar2 is
  Result varchar2(100);
  i int;
  CompanyOID number;
  tmpSer varchar2(200);
  tmpStr varchar2(50);
  treeName1 varchar2(100);
begin
  select
         t.struid,t.industryname,t.companyoid
         into tmpStr,treeName1,CompanyOID
  from ejsjindustry t where t.oid=leafNode;
  i := length(tmpStr)/3;
  while(i>0)
        loop
           select t1.industryname into tmpSer
           from ejsjindustry t1
           where t1.serialnumber = substr(tmpStr,0,3*i) and t1.companyoid=321;
           Result := tmpSer||'－'||Result;
           i := i -1;
        end loop;
  Result := Result||treeName1;
  return(Result);
end GetParentNodeName;

//统计词类下词条
select
       getparentnodename(t.industryoid) as 三级词类,
       t.industryoid as 子目主键,
       t.chwordname as 中文词条,
       t.enwordname as 英文词条,
       decode(t.wordlevel,1,'一级',2,'二级') as 重要程度,
       decode(t.problemornot,0,'无',1,'有') as 有无疑问,
       t.remark as 同义词,
       t.chwordsimple as 中文简称,
       t.formalname as 相关网址,
       t.enwordab as 缩写,
       t.enworddesc as 商品名,
       t.chemicalname as 化学名,
       t.generatename as 通用名,
       t.lationname as 学名,
       t.complexnumdesc as 特殊复数显示,    
       decode(t.wordfrom,1,'专业词典',2,'法律、法规',3,'专业技术文献',4,'其他') as 资料来源
from ejsjdictionary t,ejsjindustry t1
where 
       t.industryoid(+)=t1.oid and
       t.companyoid=321 and
       t1.serialnumber like '013%' and
       t.status>1 and
       t.status<99
order by t1.industrycode,t.wordlevel,t.chwordname

统计词类下词条个数
select
      t.oid,
      t.industryname,
      t.serialnumber,
      decode(count(*),1,0,count(*)) as dictnum
from ejsjindustry t,ejsjdictionary t1
where
      t.oid=t1.industryoid(+) and
      t.companyoid=321
group by t.industryname,t.serialnumber,t.oid
order by t.industryname,t.serialnumber,t.oid