Oracle 11g:使用 WITH 语句插入 SELECT [英] Oracle 11g: INSERT SELECT with WITH statement

问题描述

我用WITH语句编写了这个简单的例子DML:

I've written this simple example DML with WITH statement:

WITH seq AS
 (SELECT to_char(LEVEL - 1) p FROM dual CONNECT BY LEVEL <= 10)
SELECT old_value,
       new_value
  FROM (SELECT to_char(t1.p) old_value,
               to_char(t2.p) new_value
          FROM (SELECT row_number() over(ORDER BY p) rn,
                       p
                  FROM seq) t1,
               (SELECT row_number() over(ORDER BY dbms_random.random) rn,
                       p
                  FROM seq) t2
         WHERE t1.rn = t2.rn);

有没有办法在INSERT 语句中使用WITH 语句?

Is there any way to use WITH statement in INSERT statement?

小提琴

推荐答案

如此处所述:https://forums.oracle.com/thread/696477在这里:插入带有无效标识符 oracle 子句的语句

as mentioned here : https://forums.oracle.com/thread/696477 and here : insert statement with with clause invalid identifier oracle

WITH 子句是 select statemant 的一部分，所以只需尝试使用 INSERT 语句将其全部包装如下:

the WITH clause is part of the select statemant , so just try to wrap it all with an INSERT statement as below :

INSERT INTO SOME_TABLE    
WITH seq AS
     (SELECT to_char(LEVEL - 1) p FROM dual CONNECT BY LEVEL <= 10)
    SELECT old_value,
           new_value
      FROM (SELECT to_char(t1.p) old_value,
                   to_char(t2.p) new_value
              FROM (SELECT row_number() over(ORDER BY p) rn,
                           p
                      FROM seq) t1,
                   (SELECT row_number() over(ORDER BY dbms_random.random) rn,
                           p
                      FROM seq) t2
             WHERE t1.rn = t2.rn);

这篇关于Oracle 11g:使用 WITH 语句插入 SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！