存储过程：在FOR循环中嵌入IF语句实现条件遍历

你原来的写法无法直接在FOR循环的游标定义中嵌入IF语句，以下是两种可行的实现方式，满足「当digit等于'only one'时仅遍历1行，否则遍历全部3行」的需求：

方案一：将条件整合到查询语句中 #

通过给后续查询添加过滤条件，直接控制返回的行数，写法更简洁：

FOR snippets IN (
    SELECT 'one' AS number FROM dual
    UNION ALL
    SELECT 'two' AS number FROM dual WHERE digit != 'only one'
    UNION ALL
    SELECT 'three' AS number FROM dual WHERE digit != 'only one'
) LOOP
    -- 此处编写循环内的业务逻辑，例如打印当前行数据
    DBMS_OUTPUT.PUT_LINE(snippets.number);
END LOOP;

当digit为'only one'时，后两个SELECT因WHERE条件不成立返回空结果集，最终仅遍历'one'这一行；其他情况则返回全部三行数据。

方案二：分支定义游标后遍历 #

适合逻辑更复杂的场景，先根据条件选择对应的游标查询，再手动遍历：

DECLARE
    TYPE num_cursor IS REF CURSOR;
    v_cursor num_cursor;
    v_number VARCHAR2(10);
BEGIN
    IF digit = 'only one' THEN
        OPEN v_cursor FOR SELECT 'one' AS number FROM dual;
    ELSE
        OPEN v_cursor FOR 
            SELECT 'one' AS number FROM dual
            UNION ALL
            SELECT 'two' AS number FROM dual
            UNION ALL
            SELECT 'three' AS number FROM dual;
    END IF;
    
    LOOP
        FETCH v_cursor INTO v_number;
        EXIT WHEN v_cursor%NOTFOUND;
        -- 循环内业务逻辑
        DBMS_OUTPUT.PUT_LINE(v_number);
    END LOOP;
    CLOSE v_cursor;
END;

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