日期:2014-05-17  浏览次数:20861 次

oracle with用法,怎么插入不了数据呢
1 with p as
  2 (select id,name from test where id=1),
  3 p2 as (select id ,name from test where id=2),
  4 p3 as
  5 (select * from p2
  6 union all
  7 select *from p)
  8* insert into test2 as select id,name from p3
SQL> /
  insert into test2 as select id,name from p3
  *
第 8 行出现错误:
ORA-00928: 缺失 SELECT 关键字

------解决方案--------------------
SQL code

INSERT INTO TEST2 AS
  WITH P AS
   (SELECT ID, NAME FROM TEST WHERE ID = 1),
  P2 AS
   (SELECT ID, NAME FROM TEST WHERE ID = 2),
  P3 AS
   (SELECT *
      FROM P2 UNIONUNION ALL
            SELECT * FROM P
    )
  SELECT ID, NAME FROM P3