由分隔符组成的ID字符串作为存储过程入参放入IN语句内

后台

	public void batchChooseMixInsert(String orderId, String[] flids,String[] deviceIds, String[] partsIds) {
		// 根据选择的功能位置批量插入明细
		for (int i = 0; i < flids.length; i++) {
			secnEquipRetiredAppService.batchChooseInsert(orderId, flids[i]);
		}
		// 数组转换成IN语句参数
		String strDeviceIds = StringUtils.join(deviceIds, ",");
		String strPartsIds = StringUtils.join(partsIds, ",");
		// 根据选择的设备和部件批量插入明细
		if (StringUtils.isNotBlank(strDeviceIds.toString()) || StringUtils.isNotBlank(strPartsIds.toString())) {
			secnEquipRetiredAppService.batchChooseInsertByDDAndDP(orderId,strDeviceIds, strPartsIds);
		}
	}

    public void batchChooseInsertByDDAndDP(String orderId, String deviceIds,String partsIds) {    	
        StringBuffer sb = new StringBuffer();
        sb.append("call P_DM_BATCH_RETIRE_BY_DDDP('");
        sb.append(orderId);
        sb.append("','");
        sb.append(deviceIds);
        sb.append("','");
        sb.append(partsIds);        
        sb.append("')");
        super.executeBySql(sb.toString());
    }

存储过程：

CREATE OR REPLACE PROCEDURE P_DM_BATCH_RETIRE_BY_DDDP(I_DEVICE_IDS VARCHAR2, --设备IDS
                                                      I_PARTS_IDS  VARCHAR2) IS --部件IDS
BEGIN
  --通过计算匹配到几个分隔符，然后截取几次
  SELECT *
    FROM DM_DEVICE D
   WHERE D.ID IN
         (SELECT REGEXP_SUBSTR(I_DEVICE_IDS, '[^,]+', 1, LEVEL) AS STR
            FROM DUAL
          CONNECT BY LEVEL <=
                     LENGTH(I_DEVICE_IDS) -
                     LENGTH(REGEXP_REPLACE(I_DEVICE_IDS, ',', '')) + 1)
  
  --或通过截取到不能截取为止
  
    SELECT *
            FROM DM_DEVICE D
           WHERE D.ID IN
                 (select regexp_substr(I_PARTS_IDS, '[^,]+', 1, level)
                    from dual
                  connect by regexp_substr(I_PARTS_IDS, '[^,]+', 1, level) is not null)
           EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

  ROLLBACK;
END;