ORACLE结合mapper.xml讨论instr函数匹配时，若参数值长度超出了instr函数长度的限制解决方法

举例（假设wid的参数值格式是这样：{“wid”:“111,2222,3344,5555,…”}）

这是一个普通的sql

    SELECT A.STREET_NAME,A.TYPE_NAME,A.WID,A.FILL_ID,A."NAME",A.FILL_REWARD_NAME,A.FILL_REWARD_NUM , to_number(B.FILL_PERIOD) AS NUM,
        B.REPORT_CONTENT AS WRITE_CONTENT,B.UPDATE_TIME AS WRITE_TIME,B.NAMEANDTEL AS FILL_NAMEANDPHONE,B.TIME_YEAR AS YEAR,B.FLAG
        from V_ALL_FILL_ITEM_INFO_HZ A
        LEFT JOIN V_ALL_FILL_REWARD B ON
        A.STREET_ID=B.STREET_ID AND A."TYPE"=B."TYPE" AND A.WID=B.WID
        AND A.SYSTEM_ID=B.SYSTEM_ID AND A.FILL_ID=B.FILL_ID
        WHERE  1=1
        <if test="wids != null and wids.size !=0">
        AND ','||A .WID||',' IN(
        <foreach collection="wids" item="itm" open="" close="" separator=",">
            ','||#{itm}||','
        </foreach>
            )
        </if>
        <!--<if test="wids != null and wids !=''">-->
            <!--AND instr(#{wids },A.WID) > 0-->
        <!--</if>-->
        <if test="year != null and year !=''">
            AND TIME_YEAR = #{year}
        </if>
        ORDER BY A.STREET_ID ASC,A."TYPE" ASC,A.WID,A.ORDER_NO ASC

如上

1、首先我们的java代码需要对获取到wid的值进行逗号分割开，然后重新封装到wids:

    @Override
    public List<HashMap> queryStreetTable(HashMap params) {
        try{
            List<String> wids;
            String wid=String.valueOf(params.get("wid"));
            if(!(wid==null ||"".equals(wid))){
              wids=Arrays.asList(wid.split(",")) ;
                params.put("wids",wids);
            }
            List<HashMap> result = this.allReportMapper.queryStreetTable(params);

            return result;
        }catch(RuntimeException e){
            this.log.error("queryScenicAllTable：:",e);
            return null;
        }
    }

2，当我的wids 参数达到4000时，那么如果我用这句话AND instr(#{wids },A.WID) > 0去匹配，则会报错，超出长度，因此将他改成用 in函数，但是in函数没有逗号分隔，而instr会自动将wid的一串值逗号分隔开，因此我们用in函数需要做处理，将

   <if test="wids != null and wids !=''">
            AND instr(#{wids },A.WID) > 0
        </if>

就改成如下即可：

<if test="wids != null and wids.size !=0">
        AND ','||A .WID||',' IN(
        <foreach collection="wids" item="itm" open="" close="" separator=",">
            ','||#{itm}||','
        </foreach>
            )
        </if>