mysql和oracle数据库批量插入数据的不同

Arglllllllllllllll

已于 2022-07-25 17:33:46 修改

阅读量220

点赞数 1

文章标签：数据库 mysql oracle

于 2022-07-25 17:29:56 首次发布

本文链接：https://blog.csdn.net/filwllz/article/details/125979540

版权

这篇博客探讨了MySQL和Oracle两种数据库在插入数据时的不同语法。MySQL使用INSERT INTO语句，而Oracle则采用INSERT ALL INTO配合多个INTO子句。示例中展示了如何根据配置名称和目标ID获取配置ID，并插入到configfeeitem表中，插入的数据包括纸张类型ID、灰度费用和彩色费用。

摘要由CSDN通过智能技术生成

mysql：

INSERT INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee)
        VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '1', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '2', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '3', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '4', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '5', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '6', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '7', '0.1', '0.5'),
               ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = #{serverid}), '8', '0.1', '0.5');

Oracle：

INSERT ALL 
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '1', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '2', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '3', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '4', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '5', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '6', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '7', '0.1', '0.5')
    INTO configfeeitem (ConfigID, PaperTypeID, GrayFee, ColorFee) VALUES ((SELECT ConfigID FROM configs WHERE ConfigName = 'ServerConfig' AND TargetID = 1), '8', '0.1', '0.5')
    SELECT 1 FROM DUAL;

SELECT 1 FROM DUAL;的作用？

这是oracle中自带的一个虚拟表，用它来可以查看一些系统信息；比如系统时间：

select sysdate from dual;

dual是虚拟表，这句话的意思就是返回一个值，这里的值就是1;