oracle使用触发器进行用户部门同步

公司和另一个公司合作，将两个系统整合，整合时有个需求，为同库不同管理用户下的用户表进行同步，即A用户的用户表作为主维护表，在更新时，将用户信息同步到B用户的用户表中。

解决时，首先想到了触发器，在A用户的用户表上建立触发器，insert ，update ，delete时更新B用户的用户表，其中包括部门信息和一些其他杂七杂八的东西。

A用户下的部门信息是个树型结构，而且在B用户库中可能不存在这个部门，因此同步部门信息时，检查部门是否存在，并且还原树结构。

由于好久不写触发器和存储过程了，什么rowtype，游标都忘了怎么使了，这里记录一下，以供以后查阅。

刷新部门所用的存储过程

create or replace procedure iqs.avicit_refresh_dept is
begin
  dbms_output.put_line('刷新部门开始');
  --插入不存在的部门
  insert into ws_dept
    (dept_id, ws__dept_id, dept_name, avicit_dept_id)
    select seq_dept.nextval, 1, dept_name, id
      from hr.hr_dept
     where id not in (select nvl(avicit_dept_id, '0') from ws_dept);

  --执行一次重置树的操作 避免循环树的出现
  update ws_dept
     set ws__dept_id = 1
   where dept_id != 1
     and avicit_dept_id is not null;

  --重新建立树
  update ws_dept d
     set d.ws__dept_id =
         (select topid
            from (select nowid, nowpid, topid
                    from (select t.*,
                                 towd.dept_id   as topid,
                                 towd.dept_name as topname
                            from (select d.*,
                                         pd.dept_name  as pname,
                                         pd.id         as poid,
                                         wd.dept_id    as nowid,
                                         wd.dept_name  as nowname,
                                         pwd.dept_id   as nowpid,
                                         pwd.dept_name as nowpname
                                    from hr.hr_dept d
                                    left join hr.hr_dept pd
                                      on d.parent_dept_id = pd.id
                                    left join ws_dept wd
                                      on d.id = wd.avicit_dept_id
                                    left join ws_dept pwd
                                      on pwd.dept_id = wd.ws__dept_id
                                   where d.id != '0') t
                            left join ws_dept towd
                              on towd.avicit_dept_id = t.parent_dept_id)
                   where topid != nowpid) t
           where t.nowid = d.dept_id)
   where d.dept_id in (select nowid
                         from (select t.*,
                                      towd.dept_id   as topid,
                                      towd.dept_name as topname
                                 from (select d.*,
                                              pd.dept_name  as pname,
                                              pd.id         as poid,
                                              wd.dept_id    as nowid,
                                              wd.dept_name  as nowname,
                                              pwd.dept_id   as nowpid,
                                              pwd.dept_name as nowpname
                                         from hr.hr_dept d
                                         left join hr.hr_dept pd
                                           on d.parent_dept_id = pd.id
                                         left join ws_dept wd
                                           on d.id = wd.avicit_dept_id
                                         left join ws_dept pwd
                                           on pwd.dept_id = wd.ws__dept_id
                                        where d.id != '0') t
                                 left join ws_dept towd
                                   on towd.avicit_dept_id = t.parent_dept_id)
                        where topid != nowpid);

  --删除同步系统中删除的 存在本系统的部门
  delete from ws_dept t
   where nvl(t.avicit_dept_id, '0') not in (select id from hr.hr_dept)
     and dept_id != 1
     and avicit_dept_id is not null
     and dept_id not in
         (select dept_id from ws_user where common_Extend_1 = 1);
end avicit_refresh_dept;

更新用户所需存储过程

create or replace procedure iqs.avicit_delete_user(u_name in varchar2) is
begin
  dbms_output.put_line('删除用户开始');
  update ws_user set Common_Record_Status = 2 where user_name = u_name;
end avicit_delete_user;

create or replace procedure iqs.avicit_save_user(s_id       in varchar2,
                                                 d_id       in varchar2,
                                                 u_name     in varchar2,
                                                 u_realname in varchar2,
                                                 u_sex      in varchar2,
                                                 v_deleted  in varchar2) is
  v_count int;
begin
  dbms_output.put_line('修改或新建用户开始' || u_name);
  select count(1) into v_count from ws_user where user_name = u_name;
  dbms_output.put_line(u_name || ' 个数 ' || v_count);
  if v_count > 0 then
    --更新
    dbms_output.put_line('更新操作');
    update ws_user
       set user_realname        = u_realname,
           user_sex             = u_sex,
           dept_id              = d_id,
           sl_id                = s_id,
           common_record_status = v_deleted
     where user_name = u_name;
  else
    --新建
    dbms_output.put_line('新建操作');
    insert into ws_user
      (user_name,
       user_realname,
       user_sex,
       dept_id,
       sl_id,
       user_id,
       common_record_status)
    values
      (u_name, u_realname, u_sex, d_id, s_id, seq_user.nextval, v_deleted);
  end if;
end avicit_save_user;

A用户的用户表触发器

create or replace trigger iqs.avicit_user
  after insert or update or delete on appsys.sys_user
  for each row
declare
  --数值临时变量
  v_temp    int;
  v_sex     iqs.ws_user.user_sex%type;
  v_dept_id iqs.ws_dept.dept_id%type;
  v_deleted iqs.ws_user.common_record_status%type;
  cursor cur_employee is
    select * from hr.hr_employee where id = :new.employee_id;
  --员工表行变量
  v_employee hr.hr_employee%rowtype;
begin
  --表单存在性验证
  SELECT count(1)
    into v_temp
    FROM ALL_TABLES
   WHERE OWNER = 'IQS'
     and table_name = 'WS_USER';
  dbms_output.put_line('Ws_user表存在状态（大于0存在，等于0不存在）:' || v_temp);

  if v_temp > 0 then
    --更新开始
    case
    --插入
      when inserting or updating then
        dbms_output.put_line('inserting or updating start');
        --获取员工信息
        open cur_employee;
        loop
          fetch cur_employee
            into v_employee;
          exit when cur_employee%notfound;
        end loop;
        close cur_employee;
      
        dbms_output.put_line('用户帐号:' || :new.user_name);
        dbms_output.put_line('密级:' || :new.KMS_EXPERT_LEVEL);
        dbms_output.put_line('员工号:' || :new.employee_id);
        dbms_output.put_line('员工姓名:' || v_employee.name);
        dbms_output.put_line('员工部门id:' || v_employee.department_id);
        dbms_output.put_line('员工合法性:' || :new.valid_flag);
        --部门
        begin
          select dept_id
            into v_dept_id
            from iqs.ws_dept
           where avicit_dept_id = v_employee.department_id;
          dbms_output.put_line('质量部门id:' || v_dept_id);
        exception
          when no_data_found then
            dbms_output.put_line('创建部门:' || v_employee.department_id);
            --刷新部门
            iqs.avicit_refresh_dept();
            select dept_id
              into v_dept_id
              from iqs.ws_dept
             where avicit_dept_id = v_employee.department_id;
            dbms_output.put_line('质量系统部门id:' || v_dept_id);
        end;
        --有效性
        if :new.valid_flag = '1' then
          v_deleted := '1';
        else
          v_deleted := '2';
        end if;
        --员工性别
        if v_employee.gender = 'M' then
          v_sex := '1';
        else
          v_sex := '2';
        end if;
      
        iqs.avicit_save_user(:new.KMS_EXPERT_LEVEL,
                             v_dept_id,
                             :new.user_name,
                             v_employee.name,
                             v_sex,
                             v_deleted);
        dbms_output.put_line('inserting or updating end');
        --删除 用不到，貌似没用户删除功能
      when deleting then
        dbms_output.put_line('deleting start');
        iqs.avicit_delete_user(:new.user_name);
        dbms_output.put_line('deleting end');
    end case;
  end if;
end avicit_user;