PLSQL客户查询单字段如何支持多个CUSTOMER_ID输入多值检索

核心修改思路 #

替换原查询的WHERE条件，通过正则拆分用户输入的逗号分隔字符串，同时保留原有LIKE模糊匹配能力，无需改动其他查询逻辑即可同时支持3种查询场景：

• 单个精确ID查询（如输入DL100056）
• 模糊匹配查询（如输入IN%）
• 多个逗号分隔ID混合查询（如输入DL100056,DL1000365,IN%）

完整修改后代码 #

SELECT 
DISTINCT CUSINFO.CUSTOMER_ID AS DEALER_CODE,
CUSINFO.name AS DEALER_NAME,
CUSINFO.CORPORATE_FORM AS FORM_OF_BUSINESS,
C.corporate_form_desc AS FORM_OF_BUSINESS_DESCRIPTION,
ifsapp.Customer_Info_Address_Api.Get_Address1(CUSINFO.customer_id,1) "ADDRESS 1",
ifsapp.Customer_Info_Address_Api.Get_Address2(CUSINFO.customer_id,1) "ADDRESS 2",
CREDTIN.credit_limit AS CREDIT_LIMIT,
CREDTIN.allowed_due_amount AS ALLOWED_DUE_AMOUNT,
CREDTIN.allowed_due_days AS ALLOWED_DUE_DAYS,
ORDERC.salesman_code AS SALESMAN_CODE,
BDA.name AS SALESMAN_NAME,
ORDERC.discount AS DISCOUNT,
(select LISTAGG(c.value,' / ') within group (order by c.value) from ifsapp.COMM_METHOD c 
 where c.method_id_db='PHONE' and c.identity=CUSINFO.customer_id ) "Phone",
(select LISTAGG(c.value,' / ') within group (order by c.value) from ifsapp.COMM_METHOD c 
 where c.method_id_db='MOBILE' and c.identity=CUSINFO.customer_id) "Mobile",
 (select LISTAGG(c.value,' / ') within group (order by c.value) from ifsapp.COMM_METHOD c 
where c.method_id_db='FAX' and c.identity=CUSINFO.customer_id) "Fax",
CUSINFO.identifier_reference as IDENTIFIER_REFERENCE

FROM
ifsapp.CUSTOMER_INFO CUSINFO
LEFT JOIN
ifsapp.CUSTOMER_INFO_ADDRESS CUSADD ON CUSINFO.customer_id=CUSADD.customer_id
LEFT JOIN
ifsapp.CUSTOMER_INFO_CONTACT CUSCONT ON CUSINFO.customer_id=CUSCONT.customer_id
LEFT JOIN
ifsapp.CUSTOMER_CREDIT_INFO_CUST CREDTIN ON CUSINFO.customer_id=CREDTIN.identity
LEFT JOIN
ifsapp.CUST_ORD_CUSTOMER_ENT ORDERC ON CUSINFO.customer_id=ORDERC.customer_id
LEFT JOIN
ifsapp.Comm_Method COMM ON CUSINFO.customer_id=COMM.customer_id
LEFT JOIN
ifsapp.CORPORATE_FORM C ON CUSINFO.corporate_form=C.CORPORATE_FORM
LEFT JOIN
ifsapp.SALES_PART_SALESMAN_LOV BDA ON ORDERC.salesman_code=BDA.salesman_code

-- 替换原WHERE条件为以下逻辑
WHERE EXISTS (
    SELECT 1
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR('&CUSTOMER_ID', '[^,]+', 1, LEVEL) IS NOT NULL
    HAVING CUSINFO.CUSTOMER_ID LIKE TRIM(REGEXP_SUBSTR('&CUSTOMER_ID', '[^,]+', 1, LEVEL))
)

注意事项 #

• 多个ID之间用英文逗号分隔，支持ID前后带空格，TRIM函数会自动去除多余空格
• 每个拆分后的ID段都支持单独使用通配符（%、_），可实现灵活的混合查询
• 完全兼容原有使用习惯，原有查询场景不受任何影响

内容的提问来源于stack exchange，提问作者smc develpments