Oracle SQL查询实现单参数传递多个逗号分隔值方法

原有逻辑说明 #

现有查询定义了COUNTRY_REGION、COST_CENTER两个入参，初始版本仅支持传入单个参数值、或仅传入任意一个参数的查询场景，原有代码如下：

SELECT
 dwg.GEOGRAPHY_ID         as geographyId
,INITCAP (lower (dwc.COUNTRY_REGION))       as countryRegion
,INITCAP (lower (dwc.COUNTRY_NAME))         as countryName
,dp.PROJECT_ID            as projectId
FROM 
DATALAKE.DWL_GEOGRAPHIES dwg
,DATALAKE.DWB_PROJECT dp
,DATALAKE.DWL_GEOGRAPHY_COUNTRIES dwgc
,DATALAKE.DWL_COUNTRY dwc
,DATALAKE.DWB_PROJECT_FINANCIAL dpf
,DATALAKE.DWL_GOLIVE dgl
where dwg.geography_id = dp.project_geography_id
and   dwg.geography_id = dwgc.geography_id
and   dwc.country_id   = dwgc.country_id
and   dp.PROJECT_ID =  dpf.PROJECT_ID
and   dpf.PROJECT_ID = dgl.PROJECT_ID
and   dpf.FLAG_ACTIVE = 1
and  ((dp.cost_center = (:costCenter) and INITCAP (lower (dwc.COUNTRY_REGION)) = (:countryRegion))
        or (:costCenter IS null and INITCAP (lower (dwc.COUNTRY_REGION)) = (:countryRegion))
        or (dp.cost_center = (:costCenter) and :countryRegion IS null)
     )
order by dwc.COUNTRY_REGION

需求为扩展参数能力：支持单个参数传入2个及以上逗号分隔的值，例如COUNTRY_REGION传入Peru, Chile, Argentina、COST_CENTER传入10500, 1000这类多值场景，同时完全兼容原有单值、参数为空的查询逻辑。

可行实现方案 #

核心思路为将逗号分隔的入参拆分为独立值列表，通过IN匹配字段值，同时保留参数为空时跳过对应条件的逻辑，自动处理入参值前后的多余空格、大小写不一致问题。

通用兼容版（支持Oracle 11g及以上所有版本） #

直接替换原有WHERE条件中的多参数判断逻辑即可，修改后的完整SQL如下：

SELECT
 dwg.GEOGRAPHY_ID         AS geographyId
,INITCAP(LOWER(dwc.COUNTRY_REGION)) AS countryRegion
,INITCAP(LOWER(dwc.COUNTRY_NAME))   AS countryName
,dp.PROJECT_ID            AS projectId
FROM 
DATALAKE.DWL_GEOGRAPHIES dwg
,DATALAKE.DWB_PROJECT dp
,DATALAKE.DWL_GEOGRAPHY_COUNTRIES dwgc
,DATALAKE.DWL_COUNTRY dwc
,DATALAKE.DWB_PROJECT_FINANCIAL dpf
,DATALAKE.DWL_GOLIVE dgl
WHERE dwg.geography_id = dp.project_geography_id
AND   dwg.geography_id = dwgc.geography_id
AND   dwc.country_id   = dwgc.country_id
AND   dp.PROJECT_ID =  dpf.PROJECT_ID
AND   dpf.PROJECT_ID = dgl.PROJECT_ID
AND   dpf.FLAG_ACTIVE = 1
-- 成本中心参数匹配：兼容空、单值、多逗号分隔值
AND  (
  :costCenter IS NULL 
  OR dp.cost_center IN (
    SELECT TRIM(REGEXP_SUBSTR(:costCenter, '[^,]+', 1, LEVEL)) 
    FROM DUAL 
    CONNECT BY LEVEL <= REGEXP_COUNT(:costCenter, ',') + 1
  )
)
-- 国家区域参数匹配：兼容空、单值、多逗号分隔值，保留原有大小写归一逻辑
AND (
  :countryRegion IS NULL
  OR INITCAP(LOWER(dwc.COUNTRY_REGION)) IN (
    SELECT TRIM(INITCAP(LOWER(REGEXP_SUBSTR(:countryRegion, '[^,]+', 1, LEVEL))))
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(:countryRegion, ',') + 1
  )
)
ORDER BY dwc.COUNTRY_REGION

高性能版（适用于Oracle 12c R2及以上版本） #

如果数据库版本支持，可使用JSON_TABLE替代正则递归拆分字符串，传入值较多时性能提升明显，只需将上述SQL中拆分入参的子查询替换为如下写法即可：

-- 成本中心拆分逻辑替换为
SELECT TRIM(COLUMN_VALUE) 
FROM JSON_TABLE('["' || REPLACE(:costCenter, ',', '","') || '"]', '$[*]')

-- 国家区域拆分逻辑替换为
SELECT TRIM(INITCAP(LOWER(COLUMN_VALUE)))
FROM JSON_TABLE('["' || REPLACE(:countryRegion, ',', '","') || '"]', '$[*]')

方案特性 #

• 完全向下兼容原有逻辑：支持双参数传入、仅传单个参数、双参数为空的所有场景
• 自动适配入参格式：自动处理逗号前后的多余空格、入参大小写不一致问题，不会出现格式导致的匹配失败
• 执行效率更高：相比原版本多OR拼接的写法，逻辑更清晰，Oracle优化器可正常生成索引匹配的执行计划，避免不必要的全表扫描

内容的提问来源于stack exchange，提问作者Madara Uchiha