批量insert示例

最新推荐文章于 2025-06-16 14:44:23 发布

jgmydsai

最新推荐文章于 2025-06-16 14:44:23 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏：改写优化 pl/sql

本文链接：https://blog.youkuaiyun.com/jgmydsai/article/details/17438937

改写优化同时被 2 个专栏收录

28 篇文章

订阅专栏

pl/sql

15 篇文章

订阅专栏

本文介绍了一种使用Oracle数据库进行批量数据插入的优化方法。通过游标和条件判断结合FORALL语句实现高效的数据处理，同时展示了如何从一张表中选择特定记录插入到另一张表，并在每次达到预设数量时提交事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DROP TABLE test1 PURGE
/
DROP TABLE test2 PURGE
/
CREATE TABLE test1 AS SELECT * FROM Dba_Objects
/
CREATE TABLE test2 AS SELECT * FROM test1 WHERE 1=2
/
DECLARE
  v_limit           INT := 10000;
  v_idx             INT := 0;
  bl_notfound       BOOLEAN;
  v_object_id       NUMBER;
  v_object_name     VARCHAR2(30);
  object_id_table   dbms_sql.number_table;
  object_name_table dbms_sql.varchar2_table;
  CURSOR cur_test1 IS
    SELECT object_id, object_name FROM test1;
BEGIN
  DELETE FROM test2;
  COMMIT;
  OPEN cur_test1;
  LOOP
    FETCH cur_test1
      INTO v_object_id, v_object_name;
    bl_notfound := cur_test1%NOTFOUND;
    /*分条件赋值*/
    IF MOD(v_object_id, 7) = 0 THEN
      v_idx := v_idx + 1;
      object_id_table(v_idx) := v_object_id;
      object_name_table(v_idx) := v_object_name;
    END IF;
    /*到达限定条数就insert*/
    IF bl_notfound OR v_idx >= v_limit THEN
      v_idx := 0;
      FORALL i IN 1 .. object_id_table.count
        INSERT INTO test2
          (object_id, object_name)
        VALUES
          (object_id_table(i), object_name_table(i));
      COMMIT;
      object_name_table.delete;
      object_id_table.delete;
      IF bl_notfound THEN
        EXIT;
      END IF;
    END IF;
  END LOOP;
END;
/