oracle中create table with as和insert into with as语句
发布时间：2020-07-07 22:26:28
来源：51CTO
阅读：751
作者：天黑顺路
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 )
[, clause_name1 as
(select query ) ...]
select column1,...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,NVL(sal,0)+NVL(comm,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,'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;
INSERT INTO w_test_18
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,NVL(sal,0)+NVL(comm,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 ;