Oracle实现往某表批量插入记录

目录

1. 使用INSERT INTO SELECT语句

2. 使用INSERT ALL语句

3. 使用INSERT INTO VALUES语句

4. 使用FORALL语句

---

Oracle如何实现往某表批量插入记录？可以使用以下方法：

1. 使用INSERT INTO SELECT语句

INSERT INTO SELECT语句允许我们从另一个表或子查询中选择数据，并将其插入到指定表中。

例如，我们有一个名为EMPLOYEE_TEMP的临时表，其中已经存储了3条记录，内容与上述相同。那么可以使用如下语句将这些数据批量插入到EMPLOYEE表中：

INSERT INTO EMPLOYEE (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM EMPLOYEE_TEMP;

或者将需要插入的记录先存储在一个临时表中，然后再将临时表中的记录插入到目标表中。例如：

CREATE TABLE temp_table AS SELECT * FROM source_table WHERE condition;
INSERT INTO target_table SELECT * FROM temp_table;

2. 使用INSERT ALL语句

INSERT ALL语句可以向一个或多个表中插入多行数据，它允许一次性执行多个INSERT INTO语句，可以有效地批量插入数据。一次性插入多条记录。例如：

INSERT ALL
INTO target_table (column1, column2) VALUES (value1, value2)
INTO target_table (column1, column2) VALUES (value3, value4)
SELECT * FROM dual;
-------------------------------------------------展示：
INSERT ALL 
INTO EMPLOYEE (ID, NAME, AGE) VALUES (1, 'John', 25)
INTO EMPLOYEE (ID, NAME, AGE) VALUES (2, 'Mary', 30)
INTO EMPLOYEE (ID, NAME, AGE) VALUES (3, 'Michael', 35)
SELECT 1 FROM DUAL;

3. 使用INSERT INTO VALUES语句

例如：

INSERT INTO target_table (column1, column2) 
       VALUES (value1, value2), (value3, value4);

很抱歉，这种方式在Oracle中无法实现，在经过多位网友的提示与指导，我也是再次进行了测试与验证，最终得出结果：

在Oracle数据库中，不支持使用该方法直接在VALUES子句中同时插入多行数据的写法；

实际上，MySQL是支持在VALUES子句中同时插入多行数据的写法的，可以实现一次插入多行数据。其他一些数据库（如SQL Server、PostgreSQL等）也支持类似的写法，下面是使用不同数据库采用该语句的的执行情况。

3.1Oracle

正确插入数据格式：

使用条例三的格式是错误的，执行会报错：

3.2MYSQL

3.3PostgreSQL

3.4SQL Server2008

感谢大家的提示，上述为错误描述的修改！

4. 使用FORALL语句

例如：

DECLARE
  TYPE t_record IS RECORD (column1 datatype, column2 datatype);
  TYPE t_table IS TABLE OF t_record;
  v_data t_table;
BEGIN
  SELECT column1, column2 BULK COLLECT INTO v_data 
  FROM source_table 
  WHERE condition;
  FORALL i IN 1..v_data.COUNT
    INSERT INTO target_table (column1, column2) 
                      VALUES (v_data(i).column1,v_data(i).column2);
END;

• 定义记录类型 t_record，包含两个字段 column1 和 column2。

• 定义表类型 t_table，用于存储 t_record 类型的记录。

• 声明 v_data 变量为 t_table 类型。

• 使用 BULK COLLECT INTO 语句从源表 source_table 中查询满足条件的数据，将结果集一次性存储到 v_data 表变量中。

• 使用 FORALL 语句将 v_data 表变量中的所有记录按顺序一次性插入到目标表 target_table 中，通过 index 变量 i 访问每个数组元素，并将其对应的字段值插入到目标表中

  再或者在 PL/SQL 中定义一个数组记录，循环插入到表 users 中： 

  DECLARE
      TYPE user_tab_type IS TABLE OF users%ROWTYPE;
      l_users user_tab_type := user_tab_type();
  BEGIN
      l_users.extend(2);
      l_users(1).id := 1;
      l_users(1).name := 'Alice';
      l_users(1).age := 20;
      l_users(2).id := 2;
      l_users(2).name := 'Bob';
      l_users(2).age := 25;
      
      FORALL i IN l_us.first .. l_users.last
          INSERT INTO users VALUES l_users(i);
  END;
  

  • 定义集合类型 user_tab_type，类型为表 users 的 ROWTYPE 类型，表示 l_users 集合包含多个用户记录。

  • 使用 extend 方法将 l_users 集合长度扩展到 2。

  • 分别为 l_users 集合中的两个元素赋值，并指定 id、name、age 三个字段的值。

  • 使用 FORALL 语句将 l_users 集合中所有元素一次性写入到表 users 中，通过 index 变量 i 访问每个数组元素，并在 INSERT INTO 语句中将每个元素对应的字段值插入到 users 表中。

    上述代码使用 PL/SQL 中的集合类型和 FORALL 语句 ，从而实现了批量插入数据到 users 表。

    备注：

    FORALL语句是一种用于批量操作的PL/SQL控制结构，可以大大提高数据插入的效率。它需要使用BULK COLLECT子句将数据集合化并存储到一个数组中，然后在循环内部执行DML操作。

    再使用FORALL语句创建一个类型为EMP_RECORD的对象用于存储员工的信息：

    DECLARE
        -- 定义 EMP_RECORD 记录类型，包含两个字段 EMP_ID 和 EMP_NAME
        TYPE emp_record IS RECORD (
            emp_id NUMBER,
            emp_name VARCHAR2(100)
        );
        
        -- 定义 EMP_RECORD 类型的数组变量，用于存储多个员工信息
        TYPE emp_tab_type IS TABLE OF emp_record;
        l_emp emp_tab_type := emp_tab_type();
    BEGIN
        -- 扩展 l_emp 数组长度到 3
        l_emp.extend(3);
        
        -- 按顺序为三个元素赋值
        l_emp(1).emp_id := 1;
        l_emp(1).emp_name := 'Alice';
        l_emp(2).emp_id := 2;
        l_emp(2).emp_name := 'Bob';
        l_emp(3).emp_id := 3;
        l_emp(3).emp_name := 'Charlie';
        -- 使用 FORALL 语句将所有员工信息一次性插入到 emp 表中
        FORALL i IN l_emp.first .. l_emp.last
            INSERT INTO emp VALUES l_emp(i).emp_id, l_emp(i).emp_name;
            
        -- 提交事务
        COMMIT;
    END;
    

     其实也可以使用简单的FOR循环和insert into 语句即可实现，如下所示：

    declare 
      i NUMBER;  --定义一个变量i，类型为number
    begin
      --用for循环插入数据，达到批量插入数据的效果
      for i in 5..204 loop
          insert into soption.tSO_YJDJ_ZKCS(bh,yyb,khh,khxm,khqz,zqlx,kpbz,fylb,wtfsfw,zkbl,zkxx,zksx,bdrq) 
    	  values(i,'01','010000000001','测试个人2','0A','ASH','E',i,8,0.5,1,5,'20160309');
    	 commit;   --记得要commit
      end LOOP;
    END;
    

    我在每次插入操作之后，都使用 commit 语句来提交数据库事务，将数据保存到数据库中。

    以上就是Oracle实现批量插入记录的几种方法，具体使用哪种方法要取决于具体情况和需求。但是在设计并执行SQL语句前，应该全面、仔细地考虑各种特殊情况，并在程序运行中仔细进行测试和监测，以保证程序稳定、高效、可靠。在这里我对具体的情况进行以下几个方面的评估点提出建议：

    1. 表结构和字段类型是否与插入的数据类型匹配，否则容易出现异常或错误；

    2. 批量插入的记录数量是否会对数据库性能产生影响，应该考虑到系统负荷，适当控制一次插入的数量；

    3. 循环插入数据的时候，一旦出现异常如重复记录等，则会导致插入操作终止，这可能需要添加异常处理机制。