Shulou(Shulou.com)06/01 Report--

Oracle supports the use of with as clauses to create tables

Syntax:

Create table table_name as

With clause_name as

(select query)

[, clause_name1 as

(select query).]

Select column1,...columnn from clause_name

Syntax:

Insert into table_name

With clause_name as

(select query)

[, clause_name1 as

(select query).]

Select column1,...columnn from clause_name

Example:

-- create with statement

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'), 1mae 7) AS hiredate CASE WHEN sal 1000 AND sal 2000 AND 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_19WITH 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'), 1mae 7) AS hiredate CASE WHEN sal 1000 AND sal 2000 AND 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.