公司和另一个公司合作,将两个系统整合,整合时有个需求,为同库不同管理用户下的用户表进行同步,即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;