sql 优化之前
<!-- 更新机构 用于同步"机构和用户"的数据 --><update id="updateOrgData">
update org_info o set (o.org_name, o.parent_org_id)
= (select v.org_name, v.parent_org_id
from v_cc_org_info v where o.org_id = v.org_id)
where o.org_id in (select org_id from v_cc_org_info)
</update>
<!-- 新增机构 用于同步"机构和用户"的数据 -->
<insert id="insertOrgData" parameterType="roleInfo">
insert into org_info(org_id,org_name, parent_org_id, org_code)
select org_id,org_name, parent_org_id, org_id
from V_CC_ORG_INFO v
where not exists (select * from org_info o where o.org_id = v.org_id)
</insert>
<!-- 更新用户 用于同步"机构和用户"的数据 -->
<update id="updateUserData">
update user_info u
set (u.login_name, u.last_name, u.password, u.can_login, u.data_org, u.org_id, u.email)
= (select v.login_name, v.last_name, v.password, is_active, v.data_org, v.org_id, v.email_addr
from v_cc_user_info v where u.user_id = v.user_id)
where u.user_id in (select user_id from v_cc_user_info)
</update>
<!-- 新增用户 用于同步"机构和用户"的数据 -->
<insert id="insertUserData" parameterType="roleInfo">
insert into user_info(user_id,login_name, last_name, password, can_login, data_org, org_id, email)
select user_id,login_name, last_name, password, is_active, data_org, org_id, email_addr
from V_CC_USER_INFO v
where not exists (select * from user_info u where u.user_id = v.user_id)
</insert>
之后:
<!-- 更新用户 用于同步"机构和用户"的数据 -->
merge into user_info u
using (select v.user_id, v.login_name, v.last_name, v.password, is_active, v.data_org, v.org_id, v.email_addr
from v_cc_user_info v ) v
on ( u.user_id = v.user_id)
when matched then
update set u.login_name = v.login_name, u.last_name= v.last_name, u.password=v.password,
u.can_login=v.is_active, u.data_org=v.data_org, u.org_id=v.org_id, u.email= v.email_addr
when not matched then
insert (user_id,login_name,last_name,password,can_login,data_org,org_id,email)
values (v.user_id, v.login_name, v.last_name, v.password, is_active, v.data_org, v.org_id, v.email_addr )
<!-- 更新机构 用于同步"机构和用户"的数据 -->
merge into org_info u
using (select v.org_id,v.org_name, v.parent_org_id
from v_cc_org_info v ) v
on ( u.org_id = v.org_id)
when matched then
update set u.org_name = v.org_name, u.parent_org_id= v.parent_org_id
when not matched then
insert (org_id,org_name, parent_org_id, org_code)
values ( v.org_id,v.org_name, v.parent_org_id, v.org_id )