dz数据库操作-db是什么数据库

概述

HR提供组织结构人员主数据dz数据库操作，OA与HR组织结构人员主数据保持一致dz数据库操作， HR 为Oracledz数据库操作，OA SQL SERVER，实现方案：

HR不操作OA的数据库，HR只提供需要数据同步的人员和组织架构的数据源，OA取到数据源之后，根据获取到的数据的状态区分到底是做来新增，编辑还是删除操作，然后数据同步服务对OA的人员和组织机构数据库进行数据的操作。

1. HR数据提供方式

HR以本身的数据为基础，对中间表进行相应的处理。包括数据的新增、修改、删除。需要处理的表主要有人员表、组织架构表两个表。

具体中间表结构如下：

部门表 DZ_SYS_DeptIntegrate

人员表 DZ_SYS_UserIntegrate

人员表 DZ_SYS_UserIntegrate

注意：以上两个中间表数据，只允许插入不允许删除或编辑；数据处理使用“Oper”字段标识；

2. HR数据来源方式

HR人员中间表和组织机构中间表的来源方式是：HR提供触发器，当用户操作HR人员和组织机构时，就会触发触发器，无论是新增，编辑还是删除都会向中间表中插入数据，数据操作方式的状态以Oper字段标识。

HR组织同步触发器：

CREATE OR REPLACE TRIGGER "Tri_HRORGINFOAfterIorUorD"AFTER INSERT OR UPDATE OR DELETE ON HROrgInfo FOR EACH ROWdeclarebegin if updating then if :NEW.OrgName<>:OLD.OrgName or :NEW.OrgType<>:OLD.OrgType or :NEW.State<>:OLD.State or :NEW.Fatherid<>:OLD.Fatherid then if :NEW.State<>:OLD.State then if(:New.State='0' and :Old.State<>'0') then--停用dz数据库操作了 insert into DZ_SYS_DeptIntegrate(DeptCode,DeptName,ParentCode,IsCompany,UpdateTime,Oper,CreateTime,flag )values(:New.OrgCode, :New.OrgName, :New.Fatherid,:New.OrgType,SYSDATE ,'S',SYSDATE,'0' ); end if; if(:New.State='1' and :Old.State<>'1') then--启用了 insert into DZ_SYS_DeptIntegrate(DeptCode,DeptName,ParentCode,IsCompany,UpdateTime,Oper,CreateTime,flag )values(:New.OrgCode, :New.OrgName, :New.Fatherid,:New.OrgType,SYSDATE ,'Q',SYSDATE,'0' ); end if; else insert into DZ_SYS_DeptIntegrate(DeptCode,DeptName,ParentCode,IsCompany,UpdateTime,Oper,CreateTime,flag )values(:New.OrgCode, :New.OrgName, :New.Fatherid,:New.OrgType,SYSDATE ,'U',SYSDATE,'0' ); end if; end if; end if; if deleting then insert into DZ_SYS_DeptIntegrate(DeptCode,DeptName,ParentCode,IsCompany,UpdateTime,Oper,CreateTime,flag )values(:Old.OrgCode, :Old.OrgName, :Old.Fatherid,:Old.OrgType,SYSDATE ,'D',SYSDATE,'0' ); end if; if inserting then insert into DZ_SYS_DeptIntegrate(DeptCode,DeptName,ParentCode,IsCompany,UpdateTime,Oper,CreateTime,flag )values(:New.OrgCode, :New.OrgName, :New.Fatherid,:New.OrgType,SYSDATE ,'I',SYSDATE,'0' ); end if;EXCEPTIONWHEN Others THENRAISE_APPLICATION_ERROR(-20099,SQLERRM);end;

人员同步触发器：

CREATE OR REPLACE TRIGGER "Tri_HRZGZDAfterIorUorD" AFTER INSERT OR UPDATE OR DELETE ON HRZGZD FOR EACH ROWdeclare AN_ZZ NVARCHAR2(50); --在职 AN_LZ NVARCHAR2(50); --离职begin if updating then if :NEW.HRZGZD_ZGBH <> :OLD.HRZGZD_ZGBH or :NEW.LSZGZD_ZGXM <> :OLD.LSZGZD_ZGXM or :NEW.LSZGZD_EMAL <> :OLD.LSZGZD_EMAL or :NEW.HRZGZD_HRBMNM <> :OLD.HRZGZD_HRBMNM or :NEW.EmpStateCate <> :OLD.EmpStateCate or :NEW.UserPreDef_20 <> :OLD.UserPreDef_20 or :NEW.HRZGZD_HRZWNM <> :OLD.HRZGZD_HRZWNM or :NEW.HRZGZD_BGDH <> :OLD.HRZGZD_BGDH or :NEW.HRZGZD_YDDH <> :OLD.HRZGZD_YDDH or :NEW.HRZGZD_ZGXB <> :OLD.HRZGZD_ZGXB then if (:NEW.EmpStateCate <> :OLD.EmpStateCate) then SELECT A.ID INTO AN_LZ FROM HRSYEmpStateCategory A WHERE A.NAME = '离职'; SELECT A.ID INTO AN_ZZ FROM HRSYEmpStateCategory A WHERE A.NAME = '在职'; if (:New.EmpStateCate = AN_LZ and :Old.EmpStateCate <> AN_LZ) then --停用了 insert into DZ_SYS_UserIntegrate (UserGuId, UserName, RealName, Email, DeptCode, UpdateTime, Oper, CreateTime, flag, UserPreDef_20, PositionName, TelePhone, CellPhone, empstate, SEX) values (:New.HRZGZD_NM, :New.HRZGZD_ZGBH, :New.LSZGZD_ZGXM, :New.LSZGZD_EMAL, :New.HRZGZD_HRBMNM, SYSDATE, 'S', SYSDATE, '0', :New.UserPreDef_20, :New.HRZGZD_HRZWNM, :New.HRZGZD_BGDH, :New.HRZGZD_YDDH, :New.EmpStateCate, (CASE when :NEW.HRZGZD_ZGXB = '7E4E60FC-9990-4AF7-B54C-9EC35CFB4290' then '男' else '女' end)); end if; if (:New.EmpStateCate = AN_ZZ and :Old.EmpStateCate <> AN_ZZ) then --启用了 insert into DZ_SYS_UserIntegrate (UserGuId, UserName, RealName, Email, DeptCode, UpdateTime, Oper, CreateTime, flag, UserPreDef_20, PositionName, TelePhone, CellPhone, empstate, SEX) values (:New.HRZGZD_NM, :New.HRZGZD_ZGBH, :New.LSZGZD_ZGXM, :New.LSZGZD_EMAL, :New.HRZGZD_HRBMNM, SYSDATE, 'Q', SYSDATE, '0', :New.UserPreDef_20, :New.HRZGZD_HRZWNM, :New.HRZGZD_BGDH, :New.HRZGZD_YDDH, :New.EmpStateCate, (CASE when :NEW.HRZGZD_ZGXB = '7E4E60FC-9990-4AF7-B54C-9EC35CFB4290' then '男' else '女' end)); end if; else insert into DZ_SYS_UserIntegrate (UserGuId, UserName, RealName, Email, DeptCode, UpdateTime, Oper, CreateTime, flag, UserPreDef_20, PositionName, TelePhone, CellPhone, empstate, SEX) values (:New.HRZGZD_NM, :New.HRZGZD_ZGBH, :New.LSZGZD_ZGXM, :New.LSZGZD_EMAL, :New.HRZGZD_HRBMNM, SYSDATE, 'U', SYSDATE, '0', :New.UserPreDef_20, :New.HRZGZD_HRZWNM, :New.HRZGZD_BGDH, :New.HRZGZD_YDDH, :New.EmpStateCate, (CASE when :NEW.HRZGZD_ZGXB = '7E4E60FC-9990-4AF7-B54C-9EC35CFB4290' then '男' else '女' end)); end if; update DZ_SYS_UserIntegrate set DeptCode = (select HROrgInfo.orgcode from HROrgInfo where HROrgInfo.nm = :New.HRZGZD_HRBMNM) where UserGuId = :New.HRZGZD_NM AND DeptCode = :New.HRZGZD_HRBMNM; update DZ_SYS_UserIntegrate set empstate = (select hrsyempstatecategory.empstate from hrsyempstatecategory where hrsyempstatecategory.id = :New.EmpStateCate) where UserGuId = :New.HRZGZD_NM AND empstate = :New.EmpStateCate; update DZ_SYS_UserIntegrate set PositionName = (select hrorgpost.POSTNAME from hrorgpost where hrorgpost.NM = :New.HRZGZD_HRZWNM) where UserGuId = :New.HRZGZD_NM AND PositionName = :New.HRZGZD_HRZWNM; end if; end if; if deleting then insert into DZ_SYS_UserIntegrate (UserGuId, UserName, RealName, Email, DeptCode, UpdateTime, Oper, CreateTime, flag, UserPreDef_20, PositionName, TelePhone, CellPhone, empstate, SEX) values (:Old.HRZGZD_NM, :Old.HRZGZD_ZGBH, :Old.LSZGZD_ZGXM, :Old.LSZGZD_EMAL, :Old.HRZGZD_HRBMNM, SYSDATE, 'D', SYSDATE, '0', :Old.UserPreDef_20, :Old.HRZGZD_HRZWNM, :Old.HRZGZD_BGDH, :Old.HRZGZD_YDDH, :Old.EmpStateCate, (CASE when :OLD.HRZGZD_ZGXB = '7E4E60FC-9990-4AF7-B54C-9EC35CFB4290' then '男' else '女' end)); update DZ_SYS_UserIntegrate set DeptCode = (select HROrgInfo.orgcode from HROrgInfo where HROrgInfo.nm = :Old.HRZGZD_HRBMNM) where UserGuId = :Old.HRZGZD_NM AND DeptCode = :Old.HRZGZD_HRBMNM; update DZ_SYS_UserIntegrate set empstate = (select hrsyempstatecategory.empstate from hrsyempstatecategory where hrsyempstatecategory.id = :Old.EmpStateCate) where UserGuId = :Old.HRZGZD_NM AND empstate = :Old.EmpStateCate; update DZ_SYS_UserIntegrate set PositionName = (select hrorgpost.POSTNAME from hrorgpost where hrorgpost.NM = :Old.HRZGZD_HRZWNM) where UserGuId = :Old.HRZGZD_NM AND PositionName = :Old.HRZGZD_HRZWNM; end if; if inserting then insert into DZ_SYS_UserIntegrate (UserGuId, UserName, RealName, Email, DeptCode, UpdateTime, Oper, CreateTime, flag, UserPreDef_20, PositionName, TelePhone, CellPhone, empstate, SEX) values (:New.HRZGZD_NM, :New.HRZGZD_ZGBH, :New.LSZGZD_ZGXM, :New.LSZGZD_EMAL, :New.HRZGZD_HRBMNM, SYSDATE, 'I', SYSDATE, '0', :New.UserPreDef_20, :New.HRZGZD_HRZWNM, :New.HRZGZD_BGDH, :New.HRZGZD_YDDH, :New.EmpStateCate, (CASE when :NEW.HRZGZD_ZGXB = '7E4E60FC-9990-4AF7-B54C-9EC35CFB4290' then '男' else '女' end)); update DZ_SYS_UserIntegrate set DeptCode = (select HROrgInfo.orgcode from HROrgInfo where HROrgInfo.nm = :New.HRZGZD_HRBMNM) where UserGuId = :New.HRZGZD_NM AND DeptCode = :New.HRZGZD_HRBMNM; update DZ_SYS_UserIntegrate set empstate = (select hrsyempstatecategory.empstate from hrsyempstatecategory where hrsyempstatecategory.id = :New.EmpStateCate) where UserGuId = :New.HRZGZD_NM AND empstate = :New.EmpStateCate; update DZ_SYS_UserIntegrate set PositionName = (select hrorgpost.POSTNAME from hrorgpost where hrorgpost.NM = :New.HRZGZD_HRZWNM) where UserGuId = :New.HRZGZD_NM AND PositionName = :New.HRZGZD_HRZWNM; end if;EXCEPTION WHEN Others THEN RAISE_APPLICATION_ERROR(-20099, SQLERRM);end;

3. OA数据同步实现方式

HR将数据推送到中间表后，采用数据库触发器对中间表数据进行处理，分发到OA相应用户、部门及关系表中。

OA端数据取数操作步骤就不再叙述。

4.结束语：

异构系统之间的互联最好是通过接口实现，例如webapi webservice ,条件好的，可以通过MDM,不推荐使用数据库进行数据互联，出了问题不好排查。