在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.我本人没有做任何研究(甚至没有说明计划)，所以任何信息或意见都将不胜感激.

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屋！