Oracle PL/SQL中CLOB转VARCHAR并适配IN子句的问题解决

方法1：将CLOB转为字符串并拆分为多行 #

IN子句需要离散的VARCHAR值，而你的othertext是CLOB类型的逗号分隔字符串。可以先将CLOB转为VARCHAR（注意：若CLOB内容超过VARCHAR最大长度，此方法不适用），再通过正则表达式拆分成独立行，最后用IN子句引用。

示例SQL：

SELECT *
FROM your_table
WHERE DM_CATEGORY IN (
    SELECT TRIM(regexp_substr(DBMS_LOB.SUBSTR(p.othertext, 4000), '[^,]+', 1, LEVEL))
    FROM Preslists p
    WHERE p.tablename='tablename' AND p.functionname='functionname'
    CONNECT BY LEVEL <= regexp_count(DBMS_LOB.SUBSTR(p.othertext, 4000), ',') + 1
    AND PRIOR p.preslist_id = p.preslist_id -- 替换为Preslists表的主键，避免笛卡尔积
    AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL -- 防止循环
);

• DBMS_LOB.SUBSTR(p.othertext, 4000)：将CLOB截取为前4000字符的VARCHAR（Oracle 12c+可改为32767）
• regexp_substr：按逗号拆分字符串，LEVEL生成行号遍历每个分隔值
• 主键和PRIOR DBMS_RANDOM.VALUE：避免Preslists返回多行时产生重复数据

方法2：使用LIKE匹配（适合短CLOB内容） #

无需拆分字符串，通过拼接逗号的方式，确保匹配完整的列表项，避免部分匹配（如防止"FG"误匹配"FGOH"）。

示例SQL：

SELECT *
FROM your_table t
WHERE EXISTS (
    SELECT 1
    FROM Preslists p
    WHERE p.tablename='tablename' AND p.functionname='functionname'
    AND DBMS_LOB.INSTR(',' || p.othertext || ',', ',' || t.DM_CATEGORY || ',') > 0
);

• ',' || p.othertext || ','：在CLOB内容前后添加逗号，保证匹配的是完整项
• DBMS_LOB.INSTR：在CLOB中查找子串，返回位置大于0表示存在匹配

方法3：PL/SQL集合方式（适合长CLOB内容） #

若CLOB内容长度超过VARCHAR限制，建议用PL/SQL先读取CLOB内容，拆分为集合，再用集合作为过滤条件。

示例PL/SQL代码：

DECLARE
    TYPE str_tab IS TABLE OF VARCHAR2(100); -- 根据DM_CATEGORY的实际长度调整
    v_values str_tab;
    v_clob CLOB;
BEGIN
    -- 读取目标CLOB内容
    SELECT othertext INTO v_clob
    FROM Preslists
    WHERE tablename='tablename' AND functionname='functionname';

    -- 拆分CLOB为字符串集合
    SELECT TRIM(regexp_substr(DBMS_LOB.SUBSTR(v_clob, 32767), '[^,]+', 1, LEVEL))
    BULK COLLECT INTO v_values
    FROM dual
    CONNECT BY LEVEL <= regexp_count(DBMS_LOB.SUBSTR(v_clob, 32767), ',') + 1;

    -- 使用集合过滤数据
    FOR rec IN (
        SELECT *
        FROM your_table
        WHERE DM_CATEGORY MEMBER OF v_values
    ) LOOP
        -- 按需处理查询结果
        DBMS_OUTPUT.PUT_LINE(rec.DM_CATEGORY);
    END LOOP;
END;
/

• BULK COLLECT：将拆分后的值批量存入集合
• MEMBER OF：判断值是否存在于集合中
• 若CLOB超过32767字符，需分段处理CLOB内容后再拆分

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