数据库中with as 用法

1.with A as (select ....)
select * from A

2.WITH A1 AS ( SELECT * FROM table1 ),
     A2 AS ( SELECT * FROM table2 ) 
SELECT *  FROM A1, A2;

3.insert into tableA
with
    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...;

4.with
s1 as (select to_char(a) s_name from test_tempa),
s2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from s1
union all
select * from s2
union all
select ‘no records’ from dual
where not exists (select s_name from s1 where rownum=1)
and not exists (select s_name from s2 where rownum=1);