萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

Oracle PL/SQL 存儲過程實戰:游標循環、批量提交與異常處理

業務場景

定期清理 cmpp_submit 表中已處理或超時重試的歷史數據,歸檔至備份表 cmpp_submit_backup,每 1000 條提交一次事務,異常時回滾並記錄日志。

完整存儲過程代碼 (Oracle PL/SQL)

CREATE OR REPLACE PROCEDURE cmpp_submit_remove_procedure
IS
    d_end_time   DATE;                      -- 處理時間切點
    i_id         NUMBER;                    -- 游標變量:記錄 ID
    i_count      NUMBER := 0;               -- 批次計數器
    
    -- 定義參數化游標:處理 5 分鐘前的非活躍/超時記錄
    CURSOR c_cmpp_submit (p_end_time DATE) IS
        SELECT id
        FROM cmpp_submit
        WHERE (ih_process <> 'insert_cmpp_submit' 
               AND ih_process <> 'wait_for_response')
           OR (ih_process = 'wait_for_response' AND ih_retry <= 0)
           AND ih_timestamp < p_end_time
           AND ROWNUM < 100001;             -- 單次最多處理 10 萬條
BEGIN
    -- 1. 確定處理時間點:截取到分鐘 (處理 5 分鐘前數據)
    -- d_end_time := TRUNC(SYSDATE - 1/288, 'MI');  -- 1/288 天 = 5 分鐘
    d_end_time := TRUNC(SYSDATE, 'MI');              -- 或處理當前分鐘前數據
    
    -- 2. 打開游標
    OPEN c_cmpp_submit(d_end_time);
    
    -- 3. 循環處理
    LOOP
        FETCH c_cmpp_submit INTO i_id;
        EXIT WHEN c_cmpp_submit%NOTFOUND;
        
        i_count := i_count + 1;
        
        -- 歸檔:複製到備份表
        INSERT INTO cmpp_submit_backup SELECT * FROM cmpp_submit WHERE id = i_id;
        
        -- 清理:刪除原表記錄
        DELETE FROM cmpp_submit WHERE id = i_id;
        
        -- 4. 每 1000 條批量提交 (減少 Undo 積壓、釋放鎖、控制事務大小)
        IF i_count = 1000 THEN
            COMMIT;
            i_count := 0;  -- 重置計數器
        END IF;
    END LOOP;
    
    -- 5. 提交剩餘未滿 1000 的記錄
    COMMIT;
    
    -- 6. 關閉游標
    CLOSE c_cmpp_submit;
    
    RETURN;
    
EXCEPTION
    WHEN OTHERS THEN
        -- 異常處理:回滾、關閉游標、記錄日志 (此處簡化為空處理)
        ROLLBACK;
        IF c_cmpp_submit%ISOPEN THEN
            CLOSE c_cmpp_submit;
        END IF;
        -- 實際項目中應記錄錯誤碼、錯誤信息、調用棧至日志表
        -- INSERT INTO proc_error_log VALUES (SYSDATE, 'cmpp_submit_remove', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        RAISE;  -- 重拋異常讓調用方感知
END cmpp_submit_remove_procedure;
/
SHOW ERRORS

關鍵技術點解析

1. 參數化游標

CURSOR c_cmpp_submit (p_end_time DATE) IS
    SELECT id FROM cmpp_submit WHERE ... AND ih_timestamp < p_end_time;
-- 打開時傳參:OPEN c_cmpp_submit(d_end_time);
-- 優勢:解析一次,多次執行;避免硬解析;防止 SQL 注入

2. 批量提交節奏控制

  • 計數器模式:每 N 行 COMMIT 一次,平衡事務大小與提交開銷
  • 推薦批次:1000-5000 行,視 Undo 表空間、Redo 生成速度調整
  • 避免:單行提交 (慢) / 單次大事務 (Undo 爆滿、鎖持久、回滾久)

3. 異常處理最佳實踐

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;                              -- 回滾未提交變更
        IF c_cmpp_submit%ISOPEN THEN           -- 安全關閉游標
            CLOSE c_cmpp_submit;
        END IF;
        -- 記錄錯誤上下文 (生產必備)
        INSERT INTO proc_error_log (proc_name, error_code, error_msg, call_stack, created_at)
        VALUES ('cmpp_submit_remove', SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, SYSDATE);
        RAISE;                                 -- 重拋,讓調度器/監控捕獲告警
END;

4. 大數據量優化:BULK COLLECT + FORALL

-- 替換循環 FETCH,批量獲取 ID 數組
DECLARE
    TYPE id_array_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    l_ids id_array_t;
BEGIN
    OPEN c_cmpp_submit(d_end_time);
    LOOP
        FETCH c_cmpp_submit BULK COLLECT INTO l_ids LIMIT 1000;
        EXIT WHEN l_ids.COUNT = 0;
        
        -- 批量歸檔
        FORALL i IN 1..l_ids.COUNT
            INSERT INTO cmpp_submit_backup 
            SELECT * FROM cmpp_submit WHERE id = l_ids(i);
        
        -- 批量刪除
        FORALL i IN 1..l_ids.COUNT
            DELETE FROM cmpp_submit WHERE id = l_ids(i);
        
        COMMIT;
    END LOOP;
    CLOSE c_cmpp_submit;
END;

性能提升:上下文切換從 N 次降為 N/1000 次,大表處理速度提升 10-50 倍。

5. 定時調度 (DBMS_SCHEDULER)

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_CMPP_SUBMIT_CLEANUP',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'cmpp_submit_remove_procedure',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',  -- 每 5 分鐘
        enabled         => TRUE,
        comments        => '清理 CMPP 提交表歷史數據,歸檔備份'
    );
END;
/
-- 查看執行歷史
SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'JOB_CMPP_SUBMIT_CLEANUP';

MySQL 對比實現

DELIMITER //
CREATE PROCEDURE sp_cmpp_submit_cleanup()
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_id BIGINT;
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_end_time DATETIME DEFAULT DATE_SUB(NOW(), INTERVAL 5 MINUTE);
    
    DECLARE cur CURSOR FOR
        SELECT id FROM cmpp_submit
        WHERE (ih_process NOT IN ('insert_cmpp_submit','wait_for_response')
               OR (ih_process = 'wait_for_response' AND ih_retry <= 0))
          AND ih_timestamp < v_end_time
        LIMIT 100000;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    START TRANSACTION;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_id;
        IF v_done THEN LEAVE read_loop; END IF;
        
        INSERT INTO cmpp_submit_backup SELECT * FROM cmpp_submit WHERE id = v_id;
        DELETE FROM cmpp_submit WHERE id = v_id;
        
        SET v_count = v_count + 1;
        IF v_count >= 1000 THEN
            COMMIT;
            START TRANSACTION;
            SET v_count = 0;
        END IF;
    END LOOP;
    COMMIT;
    CLOSE cur;
END //
DELIMITER ;

運行維護建議

  • 📊 監控:V$SESSION_LONGOPS 追蹤長運行過程;DBA_SCHEDULER_JOB_RUN_DETAILS 監控調度成功率
  • 📈 歸檔表分區:cmpp_submit_backup 按月分區,便於歷史數據歸檔/刪除
  • 🔍 索引:cmpp_submit(ih_process, ih_retry, ih_timestamp) 組合索引加速游標查詢
  • ⚠️ 避免:在高峰期運行、單次處理超 10 萬行、無監控無告警的「裸跑」
copyright © 萬盛學電腦網 all rights reserved