[Go] about Oracle with as usage

Original address: https://www.cnblogs.com/linjiqin/archive/2013/06/24/3152667.html

With AS syntax
– For an alias
With TMP as (SELECT * from Tb_name)

– For multiple aliases
With
TMP as (SELECT * from Tb_name),
TMP2 as (SELECT * from Tb_name2),
Tmp3 as (SELECT * from Tb_name3),
...

123456789

--相当于建了个e临时表with e as (select * from scott.emp e where e.empno=7499)select * from e;--相当于建了e、d临时表with     e as (select * from scott.emp),     d as (select * from scott.dept)select * from e, d where e.deptno = d.deptno;

In fact, it is to put a lot of repeated SQL statements in with as inside, take an alias, the following query can use it, so for a large number of SQL statements to play an optimization role, and clearly clear.

Insert data to a table with as usage

12345

insert into table2with    s1 as (select rownum c1 from dual connect by rownum <= 10),    s2 as (select rownum c2 from dual connect by rownum <= 10)select a.c1, b.c2 from s1 a, s2 b where...;

Select S1.sid, s2.sid from S1, S2 need the associated condition, otherwise the result will be a Cartesian product.
With as corresponds to a virtual view.

The with as phrase, also called the subquery section (subquery factoring), allows you to do many things, defining a SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all as part of providing data.
　　
Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again. If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained from the with as phrase into a temp table, if it is called only once. The hint materialize, however, is to force the data in the with as phrase into a global temporary table. Many queries can improve speed in this way.

12345678910

with    sql1 as (select to_char(a) s_name from test_tempa),    sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where  select * from sql1union allselect * from sql2union allselect ‘no records‘ from dual       where not exists (select s_name from sql1 where rownum=1)       and not exists (select s_name from sql2 where rownum=1);

With AS advantages
The readability of SQL is increased, and if many sub-queries are constructed, the structure will be clearer;
More importantly: "One analysis, multiple use", which is why the performance of the place, to achieve the "less read" goal

The WITH syntax for update is special.

The wrong wording:

With SQL1 as (select substr (sequence,5,28) as sequence,

Checkresultflag from Dz_resultfromboss_dz dz1

where dz1.innetcode=006

and scpdateandtime>20130726000000)

Update sj_fileprocresult_0803 SJ Set sj.dzstatus = (select Checkresultflag from SQL1

where sj.sequence=sequence
);

The above notation will be reported ORA-00928: missing SELECT keyword

Modify the following:

Sql> Update sj_fileprocresult_0803 SJ Set sj.dzstatus = (
With SQL1 as (select substr (sequence,5,28) as sequence,

Checkresultflag from Dz_resultfromboss_dz dz1

where dz1.innetcode=006

and scpdateandtime>20130726000000)

Select Checkresultflag from SQL1

where sj.sequence=sequence
);

[Go] about Oracle with as usage