在 Oracle 中插入多行 [英] Inserting multiple rows into Oracle

问题描述

在讨论中关于多个演示了将行插入到 Oracle 中的两种方法:

In the discussion about multiple row insert into the Oracle two approaches were demonstrated:

第一:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

第二:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

有人可以争论使用其中一种优于另一种吗?

Could anyone argue the preference of using one over another?

附:我自己没有做任何研究(甚至是解释计划)，所以任何信息或意见都将不胜感激.

P.S. I didn't do any research myself (even explanation plan), so any information or opinion would be appreciated.

谢谢.

推荐答案

从性能的角度来看，这些查询是相同的.

From performance's point of view, these queries are identical.

UNION ALL 不会影响性能，因为 Oracle 仅在需要时估计 UNION 的查询，它不会先缓存结果.

UNION ALL won't hurt performance, since Oracle estimates the UNION'ed query only when it needs it, it doesn't cache the results first.

SELECT 语法在这个意义上更灵活，如果您想更改某些内容，您可以更轻松地操作 SELECT 查询.

SELECT syntax is more flexible in that sense that you can more easuly manupulate the SELECT query if you want to change something.

例如，这个查询:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

可以改写为

INSERT
INTO    pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
SELECT  7999 + level, 0, 'Multi ' || 7999 + level, 1
FROM    dual
CONNECT BY
        level <= 2

通过将 2 替换为适当的数字，您可以获得所需的任意数量的行.

By replacing 2 with appropriate number, you can get any number of rows you want.

在 INSERT ALL 的情况下，您将不得不复制目标表描述，如果您需要 40 行，则可读性较差.

In case of INSERT ALL, you would have to duplicate the destination table description, which is less readable if you need, say, 40 rows.

这篇关于在 Oracle 中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！