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

点击查看代码

begin

        merge 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;