在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屋!