oracle plsql使用一个立即执行命令插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5148324/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
plsql insert multiple rows using one execute immediate command
提问by Mariya
Is there a way to insert multiple rows in one EXECUTE IMMEDIATE? Rather than writing EXECUTE IMMEDIATEfor each insert...
有没有办法在一行中插入多行EXECUTE IMMEDIATE？而不是EXECUTE IMMEDIATE为每个插入编写...
回答by Gary Myers
Hard to tell what you are inserting. You can use EXECUTE IMMEDIATE to do an INSERT...SELECT easily enough, but I suspect that isn't what you are after, and probably you're not simply wanting a loop around the EXECUTE IMMEDIATE.
很难说你在插入什么。您可以使用 EXECUTE IMMEDIATE 轻松地执行 INSERT...SELECT，但我怀疑这不是您想要的，而且您可能不只是想要围绕 EXECUTE IMMEDIATE 进行循环。
If the multi-table insert isn't what you are looking for, you can use EXECUTE IMMEDIATE on a PL/SQL block and/or within a FORALL
如果多表插入不是您要找的，您可以在 PL/SQL 块和/或 FORALL 中使用 EXECUTE IMMEDIATE
create table test_forall_dyn (val varchar2(1));
declare
type tab_char is table of varchar2(1) index by binary_integer;
t_char tab_char;
begin
for i in 1..26 loop
t_char(i) := chr(64 + i);
end loop;
forall i in 1..26
execute immediate
'begin
insert into test_forall_dyn (val) values(:1);
insert into test_forall_dyn (val) values(:1);
end;'
using t_char(i);
end;
/
select count(*) from test_forall_dyn;
回答by Julius Musseau
EXECUTE IMMEDIATE
INSERT INTO table (col1, col2, col3) (
SELECT 1 AS col1, 2 AS col2, 3 AS col3 FROM dual
UNION ALL SELECT 4, 5, 6 FROM dual
UNION ALL SELECT 7, 8, 9 FROM dual ) ;
回答by ik_zelf
Mariya, why use dynamic sql in the first place? Most of the times scalability is not exactly improved using dynamic sql. The same is for readability. Debugging is harder .... In many cases there are also weird security issues.... I don't know why you use dynamic sql but if this is part of a production application I would reconsider using it.
玛丽亚，为什么首先使用动态 sql？大多数情况下，使用动态 sql 并不能完全提高可伸缩性。可读性也是如此。调试更难......在许多情况下，还有奇怪的安全问题......我不知道你为什么使用动态 sql 但如果这是生产应用程序的一部分，我会重新考虑使用它。
Ronald.
罗纳德。
回答by Avi
@maria First frame select quesry which gives u multiple row wich u are going to insert... You select stament shoud give data in order of data u want to insert in table
@maria 第一帧选择查询，它为您提供多行您要插入的行...您选择 stament 应该按照您要插入表中的数据顺序提供数据
Then use..
然后用..
Insert into Tabl1 (col1,col2,col3)(select name,address,phone from tabl2) commit;
Insert into Tabl1 (col1,col2,col3)(select name,address,phone from tabl2) commit;
回答by Md Jawed Shamshedi
Sure, you can go for batch insert...
当然，您可以进行批量插入...
