Oracle存儲過程數組遍歷與循環控制：FOR LOOP、WHILE LOOP實戰

PL/SQL集合類型對比

關鍵差異：Oracle集合默認從1開始，不同於Java/C/C++的0基索引。

FOR LOOP遍歷集合

基本語法

CREATE OR REPLACE PROCEDURE process_array (
    p_arr IN my_package.num_array_type  -- Package中定義的類型
) IS
BEGIN
    FOR i IN 1..p_arr.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('第' || i || '個元素: ' || p_arr(i));
    END LOOP;
END;

集合方法速查

• COUNT：當前元素數量
• FIRST/LAST：首/尾索引
• PRIOR(n)/NEXT(n)：前/後索引(稀疏數組必用)
• EXISTS(n)：索引是否存在
• DELETE(n)：刪除元素(稀疏化)
• TRIM(n)：移除尾部n個元素
• EXTEND(n)：擴展n個NULL元素(VARRAY/嵌套表)

稀疏數組安全遍歷

i := p_arr.FIRST;
WHILE i IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('索引' || i || ': ' || p_arr(i));
    i := p_arr.NEXT(i);
END LOOP;

WHILE LOOP條件循環

基本結構

WHILE condition LOOP
    statements;
    -- 必須包含改變condition的邏輯，否則死循環
END LOOP;

實戰示例：分頁處理大量數據

CREATE OR REPLACE PROCEDURE batch_process (
    p_batch_size IN NUMBER DEFAULT 1000
) IS
    v_offset NUMBER := 0;
    v_count  NUMBER;
BEGIN
    LOOP
        -- 分批查詢
        SELECT COUNT(*) INTO v_count
        FROM large_table
        WHERE processed = 'N' AND ROWNUM <= p_batch_size;
        
        EXIT WHEN v_count = 0;
        
        -- 處理邏輯
        UPDATE large_table
        SET processed = 'Y', process_time = SYSDATE
        WHERE rowid IN (
            SELECT rowid FROM large_table
            WHERE processed = 'N' AND ROWNUM <= p_batch_size
        );
        
        COMMIT;  -- 批次提交
        v_offset := v_offset + v_count;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('共處理 ' || v_offset || ' 條記錄');
END;

進階循環模式

游標FOR LOOP (隱式管理)

FOR rec IN (SELECT * FROM emp WHERE deptno = p_deptno) LOOP
    -- 自動OPEN/FETCH/CLOSE，rec為%ROWTYPE記錄
    process_emp(rec.empno);
END LOOP;

帶標籤的循環控制

<>
FOR i IN 1..10 LOOP
    <>
    FOR j IN 1..5 LOOP
        IF some_condition THEN
            EXIT outer_loop;  -- 直接跳出雙層循環
        END IF;
    END LOOP inner_loop;
END LOOP outer_loop;

FORALL批量DML (非循環但相關)

FORALL i IN 1..v_arr.COUNT
    INSERT INTO target_table VALUES (v_arr(i));

-- 帶異常處理
FORALL i IN 1..v_arr.COUNT SAVE EXCEPTIONS
    UPDATE target_table SET col = v_arr(i) WHERE id = i;
    
-- 異常檢查
FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('索引: ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                         ' 錯誤: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;

性能優化建議

• 大量DML必須用FORALL，禁用FOR LOOP逐條執行
• 查詢結果集大量處理用BULK COLLECT LIMIT分批獲取
• 避免循環中隱式轉換、函數調用，提取到循環外
• 適當COMMIT頻率：太頻繁增加Redo，太稀疏撤銷段壓力大
• 並行處理考慮DBMS_PARALLEL_EXECUTE框架 (11g+)