insert后能用with不？

多次遇到有网友问这个问题。我们来测试下

测试语句为

DROP TABLE emp2 PURGE;
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
INSERT INTO emp2
SELECT * FROM (
WITH old_emp AS (SELECT * FROM emp)
SELECT * FROM old_emp
)

下面是测试结果

SQL> DROP TABLE emp2 PURGE;
Table dropped
SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
Table created
SQL> INSERT INTO emp2
  2  SELECT * FROM (
  3  WITH old_emp AS (SELECT * FROM emp)
  4  SELECT * FROM old_emp
  5  )

  6  /
14 rows inserted

SQL> 

可见是能用with的，只是需要一点小技巧而已