如何在Snowflake游标循环中处理逗号分隔值并用于IN子句

• 目标：编写Snowflake存储过程，遍历过滤条件表的游标获取规则，将逗号分隔的ITEM_LIST转为可用于IN语句的条件，从主数据表筛选符合条件的数据插入输出表。
• 涉及三张表：
  • FILTER_CRITERIA_TABLE：存储过滤规则，字段包含Country、ITEM_LIST、ORDER_QTY
  • MAIN_DATA_TABLE：待过滤的业务数据，字段包含Country、Order Number、Item、QTY
  • OUTPUT_TABLE：存储最终筛选结果
• 现有代码问题：无法正确解析ITEM_LIST的逗号分隔值，导致IN语句失效，需要用数组方式解决。

现有问题代码：

DECLARE 
    v_country STRING;
    v_item_list STRING;
    v_order_qty NUMBER;
    c1 CURSOR for (select * from FILTER_CRITERIA_TABLE);
    res RESULTSET;
    res_out RESULTSET;
BEGIN
    FOR rec IN c1 DO
            v_country := rec.COUNTRY ;
            v_item_list := rec.ITEM_LIST ;
            v_order_qty := rec.ORDER_QTY ;              
            res:=(
                    INSERT INTO OUTPUT_TABLE
                    
                    SELECT 
                        COUNTRY,
                        ORDER_NUMBER,
                        ITEM,
                        QTY,
                    FROM MAIN_DATA_TABLE
                    WHERE   
                        ITEM IN ( :v_item_list )
                        AND
                        COUNTRY = :v_country
                    );  
    END FOR;    
    res_out:=(select* from OUTPUT_TABLE);   
    RETURN TABLE(res_out); 
END;

核心思路 #

把逗号分隔的ITEM_LIST字符串转为Snowflake数组，再用ARRAY_CONTAINS函数实现多值匹配，替代原有的IN语句（原代码中直接传入字符串会被当作单个值，而非多个值列表）。

修正后的存储过程代码 #

DECLARE 
    v_country STRING;
    v_item_array ARRAY; -- 改为数组类型存储拆分后的Item
    v_order_qty NUMBER;
    c1 CURSOR FOR (SELECT * FROM FILTER_CRITERIA_TABLE);
    res RESULTSET;
    res_out RESULTSET;
BEGIN
    FOR rec IN c1 DO
        v_country := rec.COUNTRY;
        -- 拆分逗号分隔字符串为数组，同时清理首尾多余逗号和空格
        v_item_array := SPLIT(TRIM(rec.ITEM_LIST, ', '), ',');
        v_order_qty := rec.ORDER_QTY;              
        -- 使用ARRAY_CONTAINS实现Item的多值匹配
        res := (
            INSERT INTO OUTPUT_TABLE
            SELECT 
                COUNTRY,
                ORDER_NUMBER,
                ITEM,
                QTY
            FROM MAIN_DATA_TABLE
            WHERE   
                ARRAY_CONTAINS(ITEM::VARIANT, v_item_array)
                AND COUNTRY = :v_country
                AND QTY >= :v_order_qty -- 补充原需求中ORDER_QTY的过滤规则
        );  
    END FOR;    
    res_out := (SELECT * FROM OUTPUT_TABLE);   
    RETURN TABLE(res_out); 
END;

关键修改点 #

• 将v_item_list从STRING类型改为ARRAY，存储拆分后的Item列表
• 用SPLIT函数拆分字符串为数组，TRIM处理字符串首尾可能存在的逗号和空格
• 用ARRAY_CONTAINS(ITEM::VARIANT, v_item_array)替代ITEM IN (:v_item_list)，解决多值匹配问题
• 补充了原需求中提到的ORDER_QTY过滤条件（原代码未使用该变量）

更高效的无游标方案 #

如果过滤条件表数据量不大，推荐直接用SQL关联替代游标遍历，性能更优：

INSERT INTO OUTPUT_TABLE
SELECT 
    md.COUNTRY,
    md.ORDER_NUMBER,
    md.ITEM,
    md.QTY
FROM MAIN_DATA_TABLE md
JOIN FILTER_CRITERIA_TABLE fc
    ON md.COUNTRY = fc.COUNTRY
    AND ARRAY_CONTAINS(md.ITEM::VARIANT, SPLIT(TRIM(fc.ITEM_LIST, ', '), ','))
    AND md.QTY >= fc.ORDER_QTY;

-- 返回最终结果
SELECT * FROM OUTPUT_TABLE;

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