Oracle with alias as (select * from *)

With alias as (select * from *) select * from alias; equivalent to creating a temporary table syntax is with tempname as (select ....) select... example: with t as (select * from emp where depno = 10) select * from t where empno = xxx another simple example is with a as (select * from test) select * from a; in fact, it is to put a lot of repeated SQL statements in with as, get an alias, the subsequent query can be used to optimize a large number of SQL statements, and it is clear that it is a temporary storage, it is generally used in the stored procedure to connect multiple tables. Connection queries of result sets, such as querying an emp, dept, and work table, are used to store work information, eid, did, salary, and other requirements to query information about employees with higher salaries than their respective departments. At this time, a lot of connections are required, you can use with as to clearly connect several tables Assembly code with wd as (select did, arg (salary) average salary from work), em as (select emp. *, w. salary from emp left join work w on emp. eid = w. eid) select * from wd, em where wd. did = em. did and wd. average salary> em. salary; create table temp_zcy (content varchar2 (200); alter table temp_zcy add sid number; select * from temp_zcy order by sid; declare v_str varchar2 (200 ); v_ SQL varchar2 (200); begin for v_counter in 5 .. 1000 loop if mod (v_counter, 5) = 0 then -- dbms_output.put_line (v_counter + 5); -- v_str: = 'sum (case when. allamt> = '| v_counter; -- v_str: = 'sum (case when. allamt> = '| v_counter |' and. allamt <'| (v_counter + 5); v_str: = 'sum (case when. allamt> = '| v_counter |' and. allamt <'| (v_counter + 5) | 'Then. allamt else 0 end) as aaa' | v_counter | 'to' | (v_counter + 5) | 'wan bbb, '; -- dbms_output.put_line (v_str); v_ SQL: = 'insert into temp_zcy (sid, content) values ('| v_counter |', ''' | v_str | ''') '; execute immediate v_ SQL; commit; end if; end loop; end; from zcywell's column