批量增加岗位以及分配该岗位到具有某一岗位的语句[分享]**

dragonsoft

--1.生成岗位信息
insert into tposition select sys_guid() fguid,
       'JUSTEP.COM' FCLIENTACCOUNT,
       'PCSGLY' FID,
       '物品保管员' FDISPLAYNAME,
       '0' FDELETELEVEL,
       '' FPROPERTY,
       '物品保管' EMAINTRUST,
       '0' ESECRETLEVELID,
       '' EDESCRIPTION
  from dual;
--2.更新岗位信息到torgsystem表
insert into torgsystem
  select  FGUID,
   FCLIENTACCOUNT,
  (select fparentguid from torgsystem where FPATH='\POSITION\' and rownum=1) FPARENTGUID,
--需要先找个岗位找出岗位的父类GUID
    'PCSGLY.ptn' FFILENAME,
    FID,
  '.PTN'  FKIND,
   '\POSITION\' FPATH,
  '\POSITION\'  FSPACE,
   FDELETELEVEL,
  FDISPLAYNAME  FDISPLAYNAME,
   null FSEQUENCE ,
   '' FFLAG,
  ''  FDEPT,
   'PCSGLY' FPOSITION,
   '' FPERSON,
   '' FDUMMY from tposition  where FID='PCSGLY';
commit;


--增加岗位后执行
--最后执行这一语句
insert into torgattribute
  select  FGUID,
'DISPLAYNAME' FID,
   FDISPLAYNAME  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;
insert into torgattribute
  select  FGUID,
'EMAINTRUST' FID,
   '物品保管'  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;
  insert into torgattribute
  select  FGUID,
'ESECRETLEVELID' FID,
   '0'  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;
    insert into torgattribute
  select  FGUID,
'SIZE' FID,
   '00000000000000000000000000000000'  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;
     insert into torgattribute
  select  FGUID,
'CREATETIME' FID,
   '00000000200811071141000968000000'  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;
       insert into torgattribute
  select  FGUID,
'LASTWRITETIME' FID,
   '00000000200811071141000968000000'  FVALUE,
     FCLIENTACCOUNT
  from tposition  where FID='PCSGLY' ;
commit;

--2分配岗位到单位
drop table torgsystem_temp;
--创建临时表
create table torgsystem_temp
    as select *   from torgsystem
where fflag IS NOT NULL
   AND FPOSITION = 'BAMJ';

--插入岗位信息
insert into torgsystem
select (sys_guid()) FGUID,
       FCLIENTACCOUNT,
        FPARENTGUID,
       'PCSGLY.ptm' FFILENAME,
       'PCSGLY' FID,
       '.PTM' FKIND,
        FPATH,
        FSPACE,
       0 FDELETELEVEL,
       '物品保管员' FDISPLAYNAME,
       null FSEQUENCE,
       '11000000000000000000000000000000' FFLAG,
        FDEPT,
       'PCSGLY' FPOSITION,
       '' FPERSON,
       '' FDUMMY
  from torgsystem_temp
commit;

drop table torgsystem_temp;

--分配岗位到单位执行
insert into torgattribute
  select  FGUID,
'DISPLAYNAME' FID,
   FDISPLAYNAME  FVALUE,
     FCLIENTACCOUNT
  from torgsystem  where FID='PCSGLY' and FFLAG is not null;
commit;
    insert into torgattribute
  select  FGUID,
'SIZE' FID,
   '00000000000000000000000000000000'  FVALUE,
     FCLIENTACCOUNT
  from torgsystem  where FID='PCSGLY' and FFLAG is not null;
commit;
     insert into torgattribute
  select  FGUID,
'CREATETIME' FID,
   '00000000200811071141000968000000'  FVALUE,
     FCLIENTACCOUNT
  from torgsystem  where FID='PCSGLY' and FFLAG is not null;
commit;
       insert into torgattribute
  select  FGUID,
'LASTWRITETIME' FID,
   '00000000200811071141000968000000'  FVALUE,
     FCLIENTACCOUNT
  from  torgsystem  where FID='PCSGLY' and FFLAG is not null;
commit;
       insert into torgattribute
  select  FGUID,
'FLAG' FID,
   '11000000000000000000000000000000'  FVALUE,
     FCLIENTACCOUNT
  from torgsystem  where FID='PCSGLY' and FFLAG is not null;
commit;

dragonsoft

哈哈，花了我好长时间研究这个关系，其实如果你们能够把这些后台创建的这些东东的语句保留下来，相信会很方便用户的尤其是这种批量性的玩意儿，说白了，就是要工业化