Oracle存储过程与游标问题：TEMP表无数据及逻辑异常排查

1. 你的存储过程存在的错误 #

• 缺少事务提交：这是最核心的问题。Oracle中所有DML操作（比如INSERT）默认不会自动提交，你的存储过程执行完插入逻辑后没有添加COMMIT;语句，导致所有插入操作都停留在未提交的事务中。
• 无异常处理机制：当前代码没有捕获游标操作或插入过程中可能出现的异常，一旦执行过程中触发错误，会直接终止过程并回滚所有未完成的操作。
• 测试数据存在语法不严谨：比如第二条插入语句指定列时遗漏了TD列（虽然Oracle允许这种写法，但容易引发误解），不过这不是导致当前问题的直接原因。
• 关于控制台重复输出：大概率是你多次执行了存储过程，但因为未提交事务，之前的插入数据已经被回滚，每次执行都会重新输出相同内容，看起来像是重复输出。

2. 数据未写入TEMP表的原因 #

核心原因就是未提交事务。Oracle的事务是基于会话的，存储过程执行的INSERT操作只会在当前会话中临时可见：

• 如果你没有显式执行COMMIT;，当切换会话或关闭当前会话后，这些未提交的变更会被自动回滚，TEMP表自然看不到数据。
• 即使在同一会话中，如果没有提交，查询TEMP表时需要确保在同一个事务中才能看到临时变更，否则依然查询不到。

3. 更简洁的实现方式 #

相比于游标循环，纯SQL的INSERT ... SELECT语句效率更高、代码更简洁，也更容易维护。如果需要封装成存储过程供主过程调用，也能轻松实现。

方案一：纯SQL直接插入

INSERT INTO TEMP (DT, FLAG)
SELECT 
    ESD, 'E'
FROM TEST_DATA_SOVLP
WHERE TEST_SET = 2 AND ESD IS NOT NULL
UNION ALL
SELECT 
    TD, 
    CASE WHEN IS_DB = 1 THEN 'H' ELSE 'S' END
FROM TEST_DATA_SOVLP
WHERE TEST_SET = 2 AND TD IS NOT NULL;

-- 务必执行提交
COMMIT;

方案二：封装为简洁的存储过程

CREATE OR REPLACE PROCEDURE S_OVLP AS
BEGIN
    -- 如果需要每次执行前清空TEMP表，取消下面的注释
    -- TRUNCATE TABLE TEMP;
    
    INSERT INTO TEMP (DT, FLAG)
    SELECT 
        ESD, 'E'
    FROM TEST_DATA_SOVLP
    WHERE TEST_SET = 2 AND ESD IS NOT NULL
    UNION ALL
    SELECT 
        TD, 
        CASE WHEN IS_DB = 1 THEN 'H' ELSE 'S' END
    FROM TEST_DATA_SOVLP
    WHERE TEST_SET = 2 AND TD IS NOT NULL;
    
    -- 显式提交事务
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('数据插入完成，共插入' || SQL%ROWCOUNT || '条记录');
EXCEPTION
    WHEN OTHERS THEN
        -- 异常时回滚事务
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('执行出错：' || SQLERRM);
        RAISE;
END S_OVLP;
/

修正原存储过程的版本 #

如果你更倾向于保留游标循环的写法，只需要添加事务提交和异常处理即可：

CREATE OR REPLACE PROCEDURE S_OVLP AS
CURSOR cSH IS SELECT ID, ESD, TD, IS_DB, TEST_SET FROM TEST_DATA_SOVLP WHERE TEST_SET = 2;
rec_csh cSH%ROWTYPE;
BEGIN
    -- 如果需要清空表，取消注释
    -- DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TEMP');
    OPEN cSH;
    LOOP
        FETCH cSH INTO rec_csh;
        EXIT WHEN cSH%NOTFOUND;
        IF rec_csh.esd IS NOT NULL THEN
            INSERT INTO TEMP VALUES (rec_csh.esd, 'E');
            dbms_output.put_line(rec_csh.esd || ' E');
        END IF;
        IF rec_csh.td IS NOT NULL THEN
            IF rec_csh.is_db = 1 THEN
                INSERT INTO TEMP VALUES (rec_csh.td, 'H');
                dbms_output.put_line(rec_csh.td || ' H');
            ELSE
                INSERT INTO TEMP VALUES (rec_csh.td, 'S');
                dbms_output.put_line(rec_csh.td || ' S');
            END IF;
        END IF;
    END LOOP;
    CLOSE cSH;
    -- 添加提交语句
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        CLOSE cSH; -- 异常时关闭游标
        DBMS_OUTPUT.PUT_LINE('执行出错：' || SQLERRM);
        RAISE;
END S_OVLP;
/

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