oracle 之存储过程 begin ...... ; ...... end

点击查看代码

1. begin
3.         merge into ly_yjs_hxsj.T_XSGL_XSXX_CZRZ rz
4.         using ( select a.XS_ID xsid, xh, xm, '02' as bglx,'修改学生：'||xm||':学位操作撤销学位证书号，原学位证书号：'|| BJYZSBH || '改为：'|| a.JYZSH as bgnr, #{editor} as czrzh,
5.         #{editor} as czrxm ,'' as czip from (
6.         <foreach collection="data" item="item" separator="union">
7.             select #{item.studentId} XS_ID, #{item.studentCompletionNumber,jdbcType=VARCHAR} JYZSH from dual
8.         </foreach>
9.         ) 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
10.         join LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx on xjxx.XS_ID = byjg.XS_ID
11.         <where>
12.             <if test=" studentIdOrName != null and studentIdOrName != ''">and (instr(xjxx.XM, #{studentIdOrName}) > 0 or instr(xjxx.XS_ID, #{studentIdOrName}) > 0)</if>
13.             <if test="studentCultureLevelCode != null and studentCultureLevelCode != ''">and xjxx.PYCCM = #{studentCultureLevelCode}</if>
14.             <if test="degreeClassify != null and degreeClassify != ''">and xjxx.XWLXM = #{degreeClassify}</if>
15.             <if test="educationCategory != null and educationCategory != ''">and xjxx.JYLXM = #{educationCategory}</if>
16.             <if test="studyModeCode != null and studyModeCode != ''">and xjxx.XXFSM = #{studyModeCode}</if>
17.         </where>
18.          ) prz on (prz.xsid = 'T_BYGL_XSBYJG')
20.         when not matched then
21.         insert  (czrzid, xsid, xh, xm,bglx, bgnr, bgsj, czrzh, czrxm,czip) values(
22.         sys_guid(),prz.xsid,prz.xh, prz.xm,prz.bglx,prz.bgnr,sysdate,prz.czrzh,prz.czrxm,prz.czip
23.         );
24.         merge into LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx using (
25.             select a.XS_ID, a.JYZSH from (
26.                 <foreach collection="data" item="item" separator="union">
27.                     select #{item.studentId} XS_ID, #{item.studentCompletionNumber,jdbcType=VARCHAR} JYZSH from dual
28.                 </foreach>
29.             ) 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
30.             join LY_YJS_HXSJ.T_XSGL_XSXX_XJXX xjxx on xjxx.XS_ID = byjg.XS_ID
31.             <where>
32.                 <if test=" studentIdOrName != null and studentIdOrName != ''">and (instr(xjxx.XM, #{studentIdOrName}) > 0 or instr(xjxx.XS_ID, #{studentIdOrName}) > 0)</if>
33.                 <if test="studentCultureLevelCode != null and studentCultureLevelCode != ''">and xjxx.PYCCM = #{studentCultureLevelCode}</if>
34.                 <if test="degreeClassify != null and degreeClassify != ''">and xjxx.XWLXM = #{degreeClassify}</if>
35.                 <if test="educationCategory != null and educationCategory != ''">and xjxx.JYLXM = #{educationCategory}</if>
36.                 <if test="studyModeCode != null and studyModeCode != ''">and xjxx.XXFSM = #{studyModeCode}</if>
37.             </where>
38.         ) t on (t.XS_ID = xjxx.XS_ID)
39.         when matched then update set xjxx.BJYZSBH = t.JYZSH
40.         ;
41.         end;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！