oracle 之存储过程 begin ...... ; ...... end
点击查看代码beginmerge into ly_yjs_hxsj.T_XSGL_XSXX_CZRZ rz
using ( select a.XS_ID xsid, xh, xm, '02' as bglx,'修改学生：'||xm||':学位操作撤销学位证书号，原学位证书号：'|| BJYZSBH || '改为：'|| a.JYZSH as bgnr, #{editor} as czrzh,
#{editor} as czrxm ,'' as czip from (
<foreach collection="data" item="item" separator="union">
select #{item.studentId} XS_ID, #{item.studentCompletionNumber,jdbcType=VARCHAR} JYZSH from dual
</foreach>
) a join LY_YJS_HXSJ.T_BYGL_XSBYJG byjg on byjg.XS_ID = a.XS_ID and byjg.BYLXM = '08' and byjg.GDMC is null
join LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx on xjxx.XS_ID = byjg.XS_ID
<where>
<if test=" studentIdOrName != null and studentIdOrName != ''">and (instr(xjxx.XM, #{studentIdOrName}) > 0 or instr(xjxx.XS_ID, #{studentIdOrName}) > 0)</if>
<if test="studentCultureLevelCode != null and studentCultureLevelCode != ''">and xjxx.PYCCM = #{studentCultureLevelCode}</if>
<if test="degreeClassify != null and degreeClassify != ''">and xjxx.XWLXM = #{degreeClassify}</if>
<if test="educationCategory != null and educationCategory != ''">and xjxx.JYLXM = #{educationCategory}</if>
<if test="studyModeCode != null and studyModeCode != ''">and xjxx.XXFSM = #{studyModeCode}</if>
</where>
) prz on (prz.xsid = 'T_BYGL_XSBYJG')
when not matched then
insert(czrzid, xsid, xh, xm,bglx, bgnr, bgsj, czrzh, czrxm,czip) values(
sys_guid(),prz.xsid,prz.xh, prz.xm,prz.bglx,prz.bgnr,sysdate,prz.czrzh,prz.czrxm,prz.czip
);
merge into LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx using (
select a.XS_ID, a.JYZSH from (
<foreach collection="data" item="item" separator="union">
select #{item.studentId} XS_ID, #{item.studentCompletionNumber,jdbcType=VARCHAR} JYZSH from dual
</foreach>
) a join LY_YJS_HXSJ.T_BYGL_XSBYJG byjg on byjg.XS_ID = a.XS_ID and byjg.BYLXM = '08' and byjg.GDMC is null
join LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx on xjxx.XS_ID = byjg.XS_ID
<where>
<if test=" studentIdOrName != null and studentIdOrName != ''">and (instr(xjxx.XM, #{studentIdOrName}) > 0 or instr(xjxx.XS_ID, #{studentIdOrName}) > 0)</if>
<if test="studentCultureLevelCode != null and studentCultureLevelCode != ''">and xjxx.PYCCM = #{studentCultureLevelCode}</if>
<if test="degreeClassify != null and degreeClassify != ''">and xjxx.XWLXM = #{degreeClassify}</if>
<if test="educationCategory != null and educationCategory != ''">and xjxx.JYLXM = #{educationCategory}</if>
<if test="studyModeCode != null and studyModeCode != ''">and xjxx.XXFSM = #{studyModeCode}</if>
</where>
) t on (t.XS_ID = xjxx.XS_ID)
when matched then update set xjxx.BJYZSBH = t.JYZSH
;
end;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]