Oracle批量插入数据的三种方式【推荐】

以下是详细讲解“Oracle批量插入数据的三种方式【推荐】”的完整攻略，包含两条示例说明。

引言

在日常开发中，数据插入是非常常见的操作，单行插入数据使用比较广泛的是INSERT INTO语句；但在批量插入数据的时候，使用INSERT INTO语句则效率很低，执行时间会随着数据量的增大而加长。所以，本文将详细介绍三种推荐的Oracle批量插入数据的方式，以提高数据插入效率。

一、使用MULTI INSERT语句

MULTI INSERT语句是Oracle 9i以上版本提供的一种批量插入数据的方式。其语法格式如下：

INSERT ALL
INTO table_name(column1, column2, ...) VALUES (value1, value2, ...)
INTO table_name(column1, column2, ...) VALUES (value1, value2, ...)
INTO table_name(column1, column2, ...) VALUES (value1, value2, ...)
...
SELECT 1 FROM DUAL;

其中，INTO子句指定插入的表名，并且后面紧跟着需要插入的字段及其取值，可以对同一个表执行多次插入操作。SELECT语句必须出现在MULTI INSERT语句的末尾，并且添加一个假的行到临时表中。

示例：

假设表名为employee，有两个字段id和name，我们需要同时插入多条记录，其语法如下：

INSERT ALL
INTO employee(id,name) VALUES(1,'Tom')
INTO employee(id,name) VALUES(2,'Jerry')
INTO employee(id,name) VALUES(3,'Lucy')
INTO employee(id,name) VALUES(4,'Linda')
SELECT * FROM DUAL;

执行结果：

4 rows created.

SQL> select * from employee;

        ID NAME
---------- ----------
         1 Tom
         2 Jerry
         3 Lucy
         4 Linda

二、使用INSERT INTO ... SELECT语句结合UNION ALL语句

在Oracle中，也可以使用INSERT INTO ... SELECT语句结合UNION ALL语句来批量插入数据。其语法格式如下：

INSERT INTO table_name(column1,column2,...)
SELECT value1, value2, ...
UNION ALL
SELECT value1, value2, ...
UNION ALL
SELECT value1, value2, ...
...

示例：

假设表名为student_scores，有三个字段name、subject、score，需要批量插入多条记录。同样地，我们可以添加多个SELECT语句并使用UNION ALL语句连接，其语法如下：

INSERT INTO student_scores(name,subject,score)
SELECT 'Tom', 'math', 90 FROM DUAL
UNION ALL
SELECT 'Jerry', 'English', 95 FROM DUAL
UNION ALL
SELECT 'Lucy', 'Chinese', 92 FROM DUAL
UNION ALL
SELECT 'Linda', 'music', 80 FROM DUAL

执行结果：

4 rows created.

SQL> select * from student_scores;

NAME       SUBJECT          SCORE
---------- ---------- ----------
Tom        math               90
Jerry      English            95
Lucy       Chinese            92
Linda      music              80

三、使用PL/SQL语句批量插入数据

使用PL/SQL语句批量插入数据，需要先创建包含插入数据的集合类型，再通过FOR循环遍历集合，逐个插入数据，从而批量插入数据。

示例：

假设表名为student，有两个字段id和name，需要批量插入多条记录。我们先创建一个存储学生姓名的数组，然后使用FOR循环遍历数组，将每个元素插入student表中，其语法如下：

DECLARE
  TYPE name_list_t IS TABLE OF VARCHAR2(20);
  name_list name_list_t := name_list_t('Tom', 'Jerry', 'Lucy', 'Linda');
BEGIN
  FOR i in 1..name_list.COUNT LOOP
    INSERT INTO student(id,name) VALUES (i,name_list(i));
  END LOOP;
  COMMIT;
END;

执行结果：

4 rows created.

SQL> select * from student;

        ID NAME
---------- -----
         1 Tom
         2 Jerry
         3 Lucy
         4 Linda

结论

通过MULTI INSERT语句、INSERT INTO ... SELECT结合UNION ALL语句、PL/SQL语句等方式，可以提高Oracle批量插入数据的效率。在实际应用中，开发人员可以根据实际需求选择最适合自己的方式进行批量插入数据操作。