Oracle PL/SQL生成20万行CSV过慢，求最优实现方案

你的代码性能瓶颈不是BULK COLLECT，而是逐行拼接CLOB的操作：v_csv_line := v_csv_line || ...。每次用||拼接CLOB时，Oracle都会复制整个已有CLOB内容并追加新字符串，20万条数据会导致O(n²)的时间复杂度，这是进程超时的核心原因。

方案1：用SQL直接生成CSV CLOB（最优选择） #

将CSV拼接工作交给Oracle SQL引擎，一次性生成完整CLOB，效率远高于PL/SQL循环。SQL引擎的字符串聚合逻辑经过优化，能大幅降低时间消耗。

代码示例 #

DECLARE
    -- 获取需要分组的source列表
    CURSOR c_source_types IS
    SELECT DISTINCT journal_source source
    FROM journals_fbdi_layout
    WHERE id_proceso_oic = '1';
    
    v_csv_clob     CLOB;
    g_zipped_blob  BLOB;
    v_blob_csv_line BLOB;
BEGIN
    FOR curr_source IN c_source_types LOOP
        g_zipped_blob := NULL;
        
        -- 方式1：用LISTAGG生成（注意：LISTAGG默认返回VARCHAR2，数据量过大时会报错，此时改用方式2）
        SELECT LISTAGG(
            status_code || ',' ||
            ledger_id || ',' ||
            effective_date_of_transaction || ',' ||
            journal_source || ',' ||
            journal_category || ',' ||
            currency_code || ',' ||
            journal_entry_creation_date || ',' ||
            actual_flag || ',' ||
            segment1 || ',' ||
            segment2 || ',' ||
            segment3,
            CHR(10)
        ) WITHIN GROUP (ORDER BY id_line)
        INTO v_csv_clob
        FROM journals_fbdi_layout
        WHERE journal_source = curr_source.source;
        
        -- 方式2：数据量过大时用XMLAGG生成CLOB（无长度限制）
        /*
        SELECT RTRIM(
            XMLAGG(
                XMLELEMENT(E,
                    status_code || ',' ||
                    ledger_id || ',' ||
                    effective_date_of_transaction || ',' ||
                    journal_source || ',' ||
                    journal_category || ',' ||
                    currency_code || ',' ||
                    journal_entry_creation_date || ',' ||
                    actual_flag || ',' ||
                    segment1 || ',' ||
                    segment2 || ',' ||
                    segment3 || CHR(10)
                ).EXTRACT('//text()') ORDER BY id_line
            ).GETCLOBVAL(),
            CHR(10)
        ) INTO v_csv_clob
        FROM journals_fbdi_layout
        WHERE journal_source = curr_source.source;
        */
        
        -- CLOB转BLOB（保留你原有的自定义函数）
        SELECT clob_to_blob_fn(v_csv_clob)
        INTO v_blob_csv_line
        FROM dual;
        
        -- 压缩并插入表
        as_zip.add1file(g_zipped_blob, 'GlInterface_' || curr_source.source || '1' || '.csv', v_blob_csv_line);
        as_zip.finish_zip(g_zipped_blob);
        
        INSERT INTO int_dat_journals_fbdi (
            file_name,
            file_content,
            id_proceso_oic
        ) VALUES (
            'FBDI_' || curr_source.source || '_1.zip',
            g_zipped_blob,
            '1'
        );
    END LOOP;
    COMMIT;
END;
/

方案2：优化PL/SQL拼接逻辑（如果必须用循环） #

如果业务逻辑要求必须在PL/SQL中逐行处理，不要用||拼接CLOB，改用DBMS_LOB.APPEND减少内存复制开销：

代码示例 #

DECLARE
    TYPE v_fbdilayout_rec IS RECORD (
        id_line NUMBER,
        status_code VARCHAR2(100),
        ledger_id VARCHAR2(100),
        effective_date_of_transaction VARCHAR2(100),
        journal_source VARCHAR2(100),
        journal_category VARCHAR2(100),
        currency_code VARCHAR2(100),
        journal_entry_creation_date VARCHAR2(100),
        actual_flag VARCHAR2(100),
        segment1 VARCHAR2(100),
        segment2 VARCHAR2(100),
        segment3 VARCHAR2(100)
    );
    TYPE v_fbdilayout_tab IS TABLE OF v_fbdilayout_rec;
    v_fbdilayout v_fbdilayout_tab;
    
    CURSOR c_source_types IS
    SELECT DISTINCT journal_source source
    FROM journals_fbdi_layout
    WHERE id_proceso_oic = '1';
    
    v_csv_line     CLOB;
    g_zipped_blob  BLOB;
    v_blob_csv_line BLOB;
    v_temp_line    VARCHAR2(4000); -- 临时存储单行CSV内容
BEGIN
    FOR curr_source IN c_source_types LOOP
        v_csv_line := EMPTY_CLOB(); -- 初始化空CLOB
        g_zipped_blob := NULL;
        v_blob_csv_line := NULL;
        
        SELECT * BULK COLLECT INTO v_fbdilayout
        FROM journals_fbdi_layout
        WHERE journal_source = curr_source.source;
        
        FOR i IN v_fbdilayout.first..v_fbdilayout.last LOOP
            -- 先拼接成VARCHAR2再追加到CLOB，减少CLOB操作次数
            v_temp_line := v_fbdilayout(i).status_code || ',' ||
                          v_fbdilayout(i).ledger_id || ',' ||
                          v_fbdilayout(i).effective_date_of_transaction || ',' ||
                          v_fbdilayout(i).journal_source || ',' ||
                          v_fbdilayout(i).journal_category || ',' ||
                          v_fbdilayout(i).currency_code || ',' ||
                          v_fbdilayout(i).journal_entry_creation_date || ',' ||
                          v_fbdilayout(i).actual_flag || ',' ||
                          v_fbdilayout(i).segment1 || ',' ||
                          v_fbdilayout(i).segment2 || ',' ||
                          v_fbdilayout(i).segment3 || CHR(10);
                          
            DBMS_LOB.APPEND(v_csv_line, v_temp_line);
        END LOOP;
        
        -- 后续转换、压缩、插入逻辑不变
        SELECT clob_to_blob_fn(v_csv_line) INTO v_blob_csv_line FROM dual;
        
        as_zip.add1file(g_zipped_blob, 'GlInterface_' || curr_source.source || '1' || '.csv', v_blob_csv_line);
        as_zip.finish_zip(g_zipped_blob);
        
        INSERT INTO int_dat_journals_fbdi (
            file_name,
            file_content,
            id_proceso_oic
        ) VALUES (
            'FBDI_' || curr_source.source || '_1.zip',
            g_zipped_blob,
            '1'
        );
    END LOOP;
    COMMIT;
END;
/

• 给journals_fbdi_layout表创建联合索引idx_jfl_idproc_source，包含id_proceso_oic和journal_source字段，加速分组查询和批量数据获取。
• 检查as_zip自定义包的实现，确保其支持流式写入，避免一次性加载大BLOB占用过多内存。
• 若单source数据量超100万，可考虑分批次BULK COLLECT（比如每次取5万条），进一步降低内存压力。

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