oracle 批处理，bulk collect

这是一个示例，问了文心一言，给出的答复，最简单的demo

 

BEGIN
  -- 禁用日志
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET events=''10046 trace name context off''';
 
  -- 执行你的批处理操作，比如插入或更新
  INSERT INTO your_table (column1, column2) VALUES (value1, value2);
  -- 或者
  UPDATE your_table SET column1 = value1 WHERE condition;
 
  -- 重新启用日志
  EXECUTE IMMEDIATE 'ALTER SYSTEM SET events=''10046 trace name context level 4''';
END;
/    



CREATE TABLE TBL_A (  
    column_1 VARCHAR2(100),  
    column_2 NUMBER,  
    column_3 DATE  
);  
  
CREATE TABLE TBL_B (  
    ID NUMBER PRIMARY KEY,  
    column_1 VARCHAR2(100),  
    column_2 NUMBER,  
    column_3 DATE  
);  
  
CREATE SEQUENCE SEQ_B START WITH 1 INCREMENT BY 1;


CREATE OR REPLACE PROCEDURE BULK_INSERT_FROM_A_TO_B IS  
    -- 定义用于批量收集的表类型  
    TYPE t_tbl_a_rows IS TABLE OF TBL_A%ROWTYPE INDEX BY PLS_INTEGER;  
    V_SOURCE_ROWS t_tbl_a_rows;  
      
    -- 定义一个用于存储TBL_B新ID的数组  
    TYPE t_ids IS TABLE OF TBL_B.ID%TYPE INDEX BY PLS_INTEGER;  
    v_ids t_ids;  
      
    -- 假设我们一次处理100条记录  
    v_limit CONSTANT PLS_INTEGER := 100;  
BEGIN  
    -- 使用BULK COLLECT批量从TBL_A中获取数据  
    SELECT * BULK COLLECT INTO V_SOURCE_ROWS FROM TBL_A WHERE ROWNUM <= v_limit;  
      
    -- 为每个记录分配ID  
    FOR i IN 1 .. V_SOURCE_ROWS.COUNT LOOP  
        v_ids(i) := SEQ_B.NEXTVAL;  
    END LOOP;  
      
    -- 批量插入到TBL_B  
    FORALL i IN 1 .. V_SOURCE_ROWS.COUNT  
        INSERT INTO TBL_B (ID, column_1, column_2, column_3)  
        VALUES (v_ids(i), V_SOURCE_ROWS(i).column_1, V_SOURCE_ROWS(i).column_2, V_SOURCE_ROWS(i).column_3);  
      
    -- 提交事务  
    COMMIT;  
      
    -- 可选：处理异常  
    EXCEPTION  
        WHEN OTHERS THEN  
            -- 回滚事务  
            ROLLBACK;  
            -- 抛出异常  
            RAISE;  
END BULK_INSERT_FROM_A_TO_B;  
/




CREATE OR REPLACE PROCEDURE BULK_INSERT_BY_YEAR IS  
    -- 定义记录类型以匹配TBL_A表的行  
    TYPE t_tbl_a_row IS RECORD (  
        column_1 VARCHAR2(100),  
        column_2 NUMBER,  
        month_num VARCHAR2(6)  
    );  
      
    -- 定义表类型以存储多个行  
    TYPE t_tbl_a_rows IS TABLE OF t_tbl_a_row INDEX BY PLS_INTEGER;  
    V_SOURCE_ROWS t_tbl_a_rows;  
      
    -- 遍历年份的游标  
    CURSOR c_years IS  
        SELECT DISTINCT SUBSTR(month, 1, 4) AS year  
        FROM TBL_A  
        ORDER BY year;  
      
    v_year TBL_A.month%TYPE;  
    v_sql_stmt VARCHAR2(4000);  
    v_id TBL_B_2023.ID%TYPE; -- 假设TBL_B_2023表有ID列，其他年份表类似  
BEGIN  
    -- 获取所有唯一的年份  
    OPEN c_years;  
    LOOP  
        FETCH c_years INTO v_year;  
        EXIT WHEN c_years%NOTFOUND;  
          
        -- 使用BULK COLLECT按年份收集数据  
        EXECUTE IMMEDIATE 'SELECT column_1, column_2, month FROM TBL_A WHERE SUBSTR(month, 1, 4) = :1'  
        BULK COLLECT INTO V_SOURCE_ROWS  
        USING v_year;  
          
        -- 为简化示例，假设所有表结构相同，且都有ID列作为主键  
        -- 实际情况中，您可能需要为每个表单独定义序列  
          
        -- 批量插入到对应的年份表  
        FOR i IN 1 .. V_SOURCE_ROWS.COUNT LOOP  
            -- 生成ID（这里简化为直接使用序列，但注意可能需要为每个年份表单独维护序列）  
            -- 假设所有年份表共享同一个序列SEQ_B，或者您有更复杂的逻辑来分配ID  
            v_id := SEQ_B.NEXTVAL;  
              
            -- 构建动态SQL语句以插入数据  
            v_sql_stmt := 'INSERT INTO TBL_B_' || v_year || ' (ID, column_1, column_2, month) VALUES (:1, :2, :3, :4)';  
              
            -- 执行动态SQL语句  
            EXECUTE IMMEDIATE v_sql_stmt  
            USING v_id, V_SOURCE_ROWS(i).column_1, V_SOURCE_ROWS(i).column_2, V_SOURCE_ROWS(i).month;  
        END LOOP;  
          
        -- 清除集合以准备下一次循环  
        V_SOURCE_ROWS.DELETE;  
    END LOOP;  
    CLOSE c_years;  
      
    -- 提交事务  
    COMMIT;  
      
    -- 异常处理（略）  
EXCEPTION  
    WHEN OTHERS THEN  
        -- 回滚事务  
        ROLLBACK;  
        -- 抛出异常或记录错误（这里只是简单回滚）  
        RAISE;  
END BULK_INSERT_BY_YEAR;  
/