Redshift存储过程中Cursor与UPDATE结合使用示例及报错解决

你报错的核心原因是Redshift PL/pgSQL语法不支持直接在UPDATE语句中引用游标名称作为字段来源，必须先声明变量存储游标读取的字段值，通过循环遍历游标逐行处理数据。同时你的原代码还存在几处错误：

1. 游标未提前在DECLARE块中定义
2. 游标查询的字段名、UPDATE引用的字段名存在拼写错误
3. 缺失游标遍历的FETCH操作和循环逻辑

以下是符合Redshift语法要求的存储过程模板：

CREATE OR REPLACE PROCEDURE testing_procedure ()
AS 
$$ 
DECLARE
    -- 预定义游标
    cursor_testing CURSOR FOR
        SELECT 
            Ops.sfdc_contactId,
            Ops.sfdc_Opp_Id, 
            ROW_NUMBER () OVER(PARTITION BY Ops.sfdc_contactId ORDER BY sfdc_Opp_Id ) RWN
        FROM sfdc_tbl Ops
        INNER JOIN test_tbl tbl 
        ON Ops.sfdc_contactId = tbl.contactid;
    -- 定义变量存储游标读取的行数据
    v_contact_id VARCHAR(500);
    v_opp_id VARCHAR(500);
    v_rwn INT;
BEGIN 
    OPEN cursor_testing;
    -- 循环遍历游标所有行
    LOOP
        FETCH cursor_testing INTO v_contact_id, v_opp_id, v_rwn;
        -- 游标读取完毕后退出循环
        EXIT WHEN NOT FOUND;
        
        -- 按行号更新对应字段
        UPDATE test_tbl
        SET 
            sfdc_OppId_01 = CASE WHEN v_rwn = 1 THEN v_opp_id ELSE sfdc_OppId_01 END,
            sfdc_OppId_02 = CASE WHEN v_rwn = 2 THEN v_opp_id ELSE sfdc_OppId_02 END,
            sfdc_OppId_03 = CASE WHEN v_rwn = 3 THEN v_opp_id ELSE sfdc_OppId_03 END,
            sfdc_OppId_04 = CASE WHEN v_rwn = 4 THEN v_opp_id ELSE sfdc_OppId_04 END,
            sfdc_OppId_05 = CASE WHEN v_rwn = 5 THEN v_opp_id ELSE sfdc_OppId_05 END,
            sfdc_OppId_06 = CASE WHEN v_rwn = 6 THEN v_opp_id ELSE sfdc_OppId_06 END
        WHERE Contactid = v_contact_id;
    END LOOP;
    CLOSE cursor_testing;
END; 
$$ 
LANGUAGE plpgsql;

调用方式：CALL testing_procedure();

Redshift是列式数仓，游标逐行更新的性能极低，更推荐用聚合转列的方式一次完成全量更新，不需要存储过程：

UPDATE test_tbl t
SET
    sfdc_OppId_01 = o.opp1,
    sfdc_OppId_02 = o.opp2,
    sfdc_OppId_03 = o.opp3,
    sfdc_OppId_04 = o.opp4,
    sfdc_OppId_05 = o.opp5,
    sfdc_OppId_06 = o.opp6
FROM (
    SELECT
        sfdc_contactId,
        MAX(CASE WHEN rwn = 1 THEN sfdc_Opp_Id END) AS opp1,
        MAX(CASE WHEN rwn = 2 THEN sfdc_Opp_Id END) AS opp2,
        MAX(CASE WHEN rwn = 3 THEN sfdc_Opp_Id END) AS opp3,
        MAX(CASE WHEN rwn = 4 THEN sfdc_Opp_Id END) AS opp4,
        MAX(CASE WHEN rwn = 5 THEN sfdc_Opp_Id END) AS opp5,
        MAX(CASE WHEN rwn = 6 THEN sfdc_Opp_Id END) AS opp6
    FROM (
        SELECT
            sfdc_contactId,
            sfdc_Opp_Id,
            ROW_NUMBER() OVER(PARTITION BY sfdc_contactId ORDER BY sfdc_Opp_Id) rwn
        FROM sfdc_tbl
    ) t1
    GROUP BY sfdc_contactId
) o
WHERE t.Contactid = o.sfdc_contactId;

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