后台
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;