PL/SQL存储过程中读取表数据并转换为独热编码插入另一表的问题

看起来你遇到了两个核心问题：一是原代码的语法错误，二是逻辑上没处理同一个ID对应多个水果的情况，导致最终结果和预期不符。我来帮你一步步解决：

先说说你的代码为什么不工作 #

1. 语法错误：在INSERT语句的VALUES子句里，你给CASE表达式加了AS ORANGE这类别名，这是不允许的——VALUES里只需要传入值，列的对应关系由表的列顺序（或你显式指定的列列表）决定，别名在这里会直接导致编译失败，必须删掉。
2. 逻辑缺陷：你的游标是逐行读取ORIG_TABLE，每一行就插入一条新记录到ONE_HOT。但像ID=3这种在原表有两条记录（Mango和Orange）的情况，你的代码会插入两条ID=3的记录：一条是(3,1,0,0)，另一条是(3,0,0,1)，而不是合并成你预期的(3,1,0,1)。

最优解决方案：用纯SQL实现（比游标高效得多） #

这种行转列的聚合场景，用SQL的分组聚合就能完美解决，完全不需要写PL/SQL游标——SQL天生擅长批量数据处理，性能也比逐行循环好太多。

代码如下：

INSERT INTO ONE_HOT (ID, ORANGE, APPLE, MANGO)
SELECT 
    ID,
    -- 只要该ID下有Orange，就返回1，否则0
    MAX(CASE WHEN Fruit = 'Orange' THEN 1 ELSE 0 END) AS ORANGE,
    MAX(CASE WHEN Fruit = 'Apple' THEN 1 ELSE 0 END) AS APPLE,
    MAX(CASE WHEN Fruit = 'Mango' THEN 1 ELSE 0 END) AS MANGO
FROM ORIG_TABLE
-- 按ID分组，把同一个ID的所有水果记录合并成一行
GROUP BY ID;

原理：GROUP BY ID会把同一个ID的所有行聚合成一组，MAX(CASE...)会在组内取该水果对应的最大值——只要组内有一行是1（即该ID有这个水果），结果就是1，否则是0，正好符合你的独热编码需求。

如果一定要用PL/SQL游标（比如有额外业务逻辑） #

如果你因为某些原因必须用游标处理，那需要调整逻辑：先检查目标表中是否已有该ID的记录，没有就插入初始行，有就更新对应水果的列（设为1）。

修正后的PL/SQL代码：

DECLARE
    CURSOR fruit_cur IS SELECT * FROM orig_table;
    fruit_rec fruit_cur%ROWTYPE;
    v_id_exists NUMBER;
BEGIN
    -- 可选：如果是全量刷新，先清空目标表
    TRUNCATE TABLE one_hot;

    OPEN fruit_cur;
    LOOP
        FETCH fruit_cur INTO fruit_rec;
        EXIT WHEN fruit_cur%NOTFOUND;

        -- 检查当前ID是否已存在于目标表
        SELECT COUNT(1) 
        INTO v_id_exists 
        FROM one_hot 
        WHERE id = fruit_rec.id;

        IF v_id_exists = 0 THEN
            -- 不存在则插入初始记录，当前水果设为1，其他为0
            INSERT INTO one_hot (id, orange, apple, mango)
            VALUES (
                fruit_rec.id,
                CASE WHEN fruit_rec.fruit = 'Orange' THEN 1 ELSE 0 END,
                CASE WHEN fruit_rec.fruit = 'Apple' THEN 1 ELSE 0 END,
                CASE WHEN fruit_rec.fruit = 'Mango' THEN 1 ELSE 0 END
            );
        ELSE
            -- 存在则更新对应水果的列为1（只要该ID有这个水果，就设为1）
            CASE fruit_rec.fruit
                WHEN 'Orange' THEN
                    UPDATE one_hot SET orange = 1 WHERE id = fruit_rec.id;
                WHEN 'Apple' THEN
                    UPDATE one_hot SET apple = 1 WHERE id = fruit_rec.id;
                WHEN 'Mango' THEN
                    UPDATE one_hot SET mango = 1 WHERE id = fruit_rec.id;
            END CASE;
        END IF;
    END LOOP;
    CLOSE fruit_cur;
    -- 别忘了提交事务
    COMMIT;
END;
/

关键调整：

• 去掉了INSERT语句里的无效别名
• 增加了ID存在性检查，避免同一个ID插入多条记录
• 对已存在的ID，更新对应水果的列而不是插入新行，保证每个ID只有一条记录

内容来源于stack exchange