当前位置：文江博客话题详情

plsql使用一个立即执行命令插入多行

发布于 2024-10-19 22:22:40 字数 99 浏览 2 评论 0原文

有没有一种方法可以在一个EXECUTE IMMEDIATE中插入多行？而不是为每个插入编写 EXECUTE IMMEDIATE...

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南城旧梦 2024-10-26 22:22:40

很难说出你正在插入什么。您可以使用 EXECUTE IMMEDIATE 轻松地执行 INSERT...SELECT ，但我怀疑这不是您想要的，并且可能您不仅仅是想要围绕 EXECUTE IMMEDIATE 进行循环。

如果多表插入不是您想要的，您可以在 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;

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.

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

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;

回复收藏 0 原文

余生一个溪 2024-10-26 22:22:40

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 ) ;

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 ) ;

回复收藏 0 原文