Oracle中如何使用单个inert语句实现插入多行

目录

• 前言
• 问题现象
• 问题分析
• 解决方法
• 1、insert into ... union all句式
• 2、insert all into ...select 1 from dual句式
• 总结

前言

最近项目中使用到了oracle数据库，由于Oracle数据库我已经好几年没用过了，最近几年用的几乎都是mysql数据库。

在使用Oracle数据库编写SQL语句的过程中，发现了有一些函数、语法、句式是不太熟悉的，或者说是和MySQL有些区别的；因此最近我打算复习一下Oracle，同时在接下来的一段时间内也会不定期更新一些关于Oracle数据库的学习日记（文章），结合我个人的学习心得，也算是温故而知新吧，希望能帮助到有需要的同伴。

问题现象

今天在学习中遇到一个问题：

如何在Oracle中，使用单个inert语句实现插入多行记录呢？

问题分析

由于最近几年都是使用MySQL数据库比较多，因此一开始遇到这个问题的时候，我其实是直接使用了MySQL中语法来解决这个问题的，下面将使用Oracle最经典的链接工具【PLSQL Developer】来进行测试，过程如下：

先简单建表：

-- 建表语句
CREATE TABLE BIZ_BREED_INFO (
    breed_id INTEGER PRIMARY KEY,
    biz_breed VARCHAR2(255),
    memo VARCHAR2(255)
);

COMMENT ON TABLE BIZ_BREED_INFO IS '业务品种表';
COMMENT ON COLUMN BIZ_BREED_INFO.breed_id IS '品种id';
COMMENT ON COLUMN BIZ_BREED_INFO.biz_breed IS '业务品种名称';
COMMENT ON COLUMN BIZ_BREED_INFO.memo IS '备注';

然后使用一个insert语句插入多行记录：

INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES 
(1, '稻谷', '用于粮食生产和饲料'),
(2, '小麦', '用于面粉生产和饲料'),
(3, '玉米', '用于饲料、淀粉和乙醇生产'),
(4, '大豆', '用于食用油和蛋白质饲料'),
(5, '棉花', 'hbmtvj用于纺织工业'),
(6, '花生', '用于食用油和蛋白质'),
(7, '马铃薯', '用于食品和工业淀粉'),
(8, '甘蔗', '用于制糖和乙醇生产'),
(9, '苹果', '用于鲜食和果汁'),
(10, '橙子', '用于鲜食和果汁'),
(11, '茶叶', '用于饮品和出口'),
(12, '蔬菜', '用于鲜食和加工'),
(13, '奶牛', '用于牛奶生产'),
(14, '猪', '用于肉类生产'),
(15, '鸡', '用于蛋和肉类生产'),
(16, '鱼类', '用于水产养殖'),
(17, '虾类', '用于水产养殖'),
(18, '蟹类', '用于水产养殖'),
(19, '蜂产品', '用于蜂蜜、蜂王浆等'),
(20, '烟草', '用于卷烟和其他烟草制品');

没想到一执行就报错：

查了一下发现，原来是Oracle不支持这样的写法。

虽然我们都知道可以执行多个inert语句来实现多行数据记录的插入，如下：

INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (1, '稻谷', '用于粮食生产和饲料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (2, '小麦', '用于面粉生产和饲料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (3, '玉米', '用于饲料、淀粉和乙醇生产');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (4, '大豆', '用于食用油和蛋白质饲料');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (5, '棉花', '用于纺织工业');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (6, '花生', '用于食用油和蛋白质');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (7, '马铃薯', '用于食品和工业淀粉');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (8, '甘蔗', '用于制糖和乙醇生产');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (9, '苹果', '用于鲜食python和果汁');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (10, '橙子', '用于鲜食和果汁');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (11, '茶叶', '用于饮品和出口');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (12, '蔬菜', '用于鲜食和加工');
INSERT INTO编程客栈 BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (13, '奶牛', '用于牛奶生产');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (14, '猪', '用于肉类生产');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (15, '鸡', '用于蛋和肉类生产');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (16, '鱼类', '用于水产养殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (17, '虾类', '用于水产养殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (18, '蟹类', '用于水产养殖');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (19, '蜂产品', '用于蜂蜜、蜂王浆等');
INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (20, '烟草', '用于卷烟和其他烟草制品');

但是，难道Oracle并不支持使用单个inert语句实现插入多行记录吗？

抱着这样的疑问，我再次搜集了相关资料，才发现其实方法还是有的，只是sql写起来会显得很冗余。

解决方法

1、insert into ... union all句式

句式规则如下：

INSERT INTO 表名（字段列表）
select 字段值列表 from dual
union all
select 字段值列表 from dual
union all

......

SQL示例如下：

INSERT INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO)
SELECT 1, '稻谷', '用于粮食生产和饲料' FROM DUAL UNION ALL
SELECT 2, '小麦', '用于面粉生产和饲料' FROM DUAL UNION ALL
SELECT 3, '玉米', '用于饲料、淀粉和乙醇生产' FROM DUAL UNION ALL
SELECT 4, '大豆', '用于食用油和蛋白质饲料' FROM DUAL UNION ALL
SELECT 5, '棉花', '用于纺织工业' FROM DUAL UNION ALL
SELECT 6, '花生', '用于食用油和蛋白质' FROM DUAL UNION ALL
SELECT 7, '马铃薯', '用于食品和工业淀粉' FROM DUAL UNION ALL
SELECT 8, '甘蔗', '用于制糖和乙醇生产' FROM DUAL UNION ALL
SELECT 9, '苹果', '用于鲜食和果汁' FROM DUAL UNION ALL
SELECT 10, '橙子', '用于鲜食和果汁' FROM DUAL UNION ALL
SELECT 11, '茶叶', '用于饮品和出口' FROM DUAL UNION ALL
SELECT 12, '蔬菜', '用于鲜食和加工' FROM DUAL UNION ALL
SELECT 13, '奶牛', '用于牛奶生产' FROM DUAL UNION ALL
SELECT 14, '猪', '用于肉类生产' FROM DUAL UNION ALL
SELECT 15, '鸡', '用于蛋和肉类生产' FROM DUAL UNION ALL
SELECT 16, '鱼类', '用于水产养殖' FROM DUAL UNION ALL
SELECT 17, '虾类', '用于水产养殖' FROM DUAL UNION ALL
SELECT 18, '蟹类', '用于水产养殖' FROM DUAL UNION ALL
SELECT 19, '蜂产品', '用于蜂蜜、蜂王浆等' FROM DUAL UNION ALL
SELECT 20, '烟草', '用于卷烟和其他烟草制品' FROM DUAL;

执行成功：

执行成功后记得提交事务：

表数据如下：

2、insert all into ...select 1 from dual句式

句式规则如下：

INSERT ALL
INTO 表名（字段列表） VALUES (字段值列表)
INTO 表名（字段列表） VALUES (字段值列表)
...
SELECT 1 FROM DUAL;

由于在测试完第1编程种方法后，表中已经插入了20行数据，所以在测试第2种方法之前，需要先删除掉，执行删除sql：

选择是，然后提交事务：

再次查询表数据：

然后使用第2种句式插python入数据，SQL示例如下：

INSERT ALL
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (1, '稻谷', '用于粮食生产和饲料')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (2, '小麦', '用于面粉生产和饲料')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (3, '玉米', '用于饲料、淀粉和乙醇生产')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (4, '大豆', '用于食用油和蛋白质饲料')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (5, '棉花', '用于纺织工业')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (6, '花生', '用于食用油和蛋白质')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (7, '马铃薯', '用于食品和工业淀粉')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (8, '甘蔗', '用于制糖和乙醇生产')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (9, '苹果', '用于鲜食和果汁')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (10, '橙子', '用于鲜食和果汁')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (11, '茶叶', '用于饮品和出口')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (12, '蔬菜', '用于鲜食和加工')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (13, '奶牛', '用于牛奶生产')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (14, '猪', '用于肉类生产')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (15, '鸡', '用于蛋和肉类生产')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (16, '鱼类', '用于水产养殖')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (17, '虾类', '用于水产养殖')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (18, '蟹类', '用于水产养殖')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (19, '蜂产品', '用于蜂蜜、蜂王浆等')
  INTO BIZ_BREED_INFO (BREED_ID, BIZ_BREED, MEMO) VALUES (20, '烟草', '用于卷烟和其他烟草制品')
SELECT 1 FROM DUAL;

执行成功：

提交事务：

表数据如下：

总结

可以看到在Oracle中，确实是可以通过单个inert语句实现插入多行记录的！！！

但是缺点也很明显，就是和MySQL的语句相比，SQL代码​​​很冗余。尤其是第2种句式，书写起来的代码，甚至比直接写20个insert语句还要多。当然第一种句式书写起来的代码和直接写20个insert语句其实也没差多少。

相信大家都已经了解并学会了在Oracle中使用单个inert语句实现插入多行记录。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。