mysql存儲過程

mapper裡原始sql

<code>select        tss.channel_id as channelId,        tssr.risk_id as riskId,        count(tssr.risk_id) as riskCount        from  ${riskTableName} tssr        right JOIN ${salesmanTableName} tss        on tssr.salesman_id = tss.salesman_idWHEREtss.organization_id like CONCAT('',#{organCode,jdbcType=VARCHAR},'%')                and tss.channel_id=#{channelId,jdbcType=VARCHAR}                group by tss.channel_id,tssr.risk_id        order by tss.channel_id,count(tssr.risk_id) DESC/<code>

寫成存儲過程後

<code>CREATE PROCEDURE pieProcedure(IN organizationId VARCHAR(200),IN riskTableName VARCHAR(200),IN channelId VARCHAR(200))BEGINset @sqlStr=concat('select         tssr.channel_code as channelId,        tssr.risk_id as riskId,        count(tssr.risk_id) as riskCount        from  ',riskTableName,' tssr       WHEREinstr(organization_code,',organizationId,') ',channelId,'        group by tssr.channel_code,tssr.risk_id        order by tssr.channel_code,count(tssr.risk_id) DESC');PREPARE stmt from @sqlStr;EXECUTE stmt; end;/<code>

mapper.xml中改成

<code><select>    call pieProcedure(#{organCode,jdbcType=VARCHAR},#{riskTableName},#{channelId})    /<select>/<code>

service中對mapper的調用改成

閱讀更多 微科技分享 的文章

關鍵字: 存儲 SQL 過程