oracle支持使用with as 子句来创建表 语法： create table table_name as with clause_name as （select query ） [,clause_name1 as (select query ) ...] select column1,...columnn from clause_name；

语法： insert into table_name with clause_name as （select query ） [,...columnn from clause_name；

例子： --create with 语句

CREATE TABLE w_test_20 AS 

WITH  data_info_19 AS 
  (SELECT 
         NVL(ename,'unknow employee') AS ename,DECODE(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS','no this department') AS dname,NVL(job,'unkown job') AS job,substr(to_char(hiredate,'yyyy-mm-dd'),1,7) AS hiredate,CASE WHEN sal <= 1000 THEN 'D' 
              WHEN sal > 1000 AND sal <= 2000 THEN 'C'
              WHEN sal > 2000 AND sal <= 3000 THEN 'B'
              WHEN sal > 3000  THEN 'A'
              ELSE 'unkown grade' 
         END AS grade,ROUND(sal,2) AS sal,FLOOR(nvl(comm,0)) AS comm,NVL(sal,0)+NVL(comm,0) AS total_sal
     FROM emp WHERE deptno IN 
     (SELECT DISTINCT deptno FROM dept))
 
 SELECT * FROM data_info_19;

CREATE TABLE w_test_18 AS 
WITH dept_info AS 
(SELECT deptno AS deptno_1,dname FROM dept),emp_info AS
(SELECT empno AS empno_1,ename,job,deptno FROM emp),other_info AS
(SELECT empno,hiredate,0) AS total_sal FROM emp )

SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno ;

INSERT INTO w_test_19
WITH  data_info_19 AS 
  (SELECT 
         NVL(ename,0) AS total_sal
     FROM emp WHERE deptno IN 
     (SELECT DISTINCT deptno FROM dept))
 
 SELECT * FROM data_info_19;

INSERT INTO  w_test_18 
WITH dept_info AS 
(SELECT deptno AS deptno_1,0) AS total_sal FROM emp )

SELECT * FROM dept_info d LEFT JOIN emp_info e ON d.deptno_1=e.deptno RIGHT JOIN other_info o ON e.empno_1=o.empno ;