oracle临时表WITH AS用法详解

–创建会话级临时表

create global temporary table temp_session(
id number,
ename varchar2(15)
)on commit preserve rows;

–向临时表中插入数据

insert into temp_session values(1001,‘张三');
select * from temp_session;

–创建事务级临时表

create global temporary table temp_trans(
id number,
ename varchar2(15)
)on commit delete rows;

–向事务级临时表内插入数据

insert into temp_trans values(1001,‘李四');
select * from temp_trans;

with temp as
 (select * from pl_plan_info
where pl_project_main_id = '1639112109721649152')

select * from temp
connect by prior order_no = parent_id
start with order_no = '1'

with temp001 as (
select
	main.pl_project_main_id,
	info.pl_plan_info_id,
	info.task_name,
	info.order_no,
	detail.begin_time,
	detail.over_time 
from
	pl_project_main main
	left join pl_plan_info info on main.pl_project_main_id = info.pl_project_main_id 
	and info.parent_id = '0'
	left join pl_plan_detail detail on info.pl_plan_info_id = detail.pl_plan_info_id 
where
	main.project_phase not in ( '1', '2', '3' ) 
	and info.order_no = '1' 
	),
	temp002 as (
select
	main.pl_project_main_id,
	info.pl_plan_info_id,
	info.task_name,
	info.order_no,
	detail.begin_time,
	detail.over_time 
from
	pl_project_main main
	left join pl_plan_info info on main.pl_project_main_id = info.pl_project_main_id 
	and info.parent_id = '0'
	left join pl_plan_detail detail on info.pl_plan_info_id = detail.pl_plan_info_id 
where
	main.project_phase not in ( '1', '2', '3' ) 
	and info.order_no = '2' 
	),
	temp003 as (
select
	main.pl_project_main_id,
	info.pl_plan_info_id,
	info.task_name,
	info.order_no,
	detail.begin_time,
	detail.over_time 
from
	pl_project_main main
	left join pl_plan_info info on main.pl_project_main_id = info.pl_project_main_id 
	and info.parent_id = '0'
	left join pl_plan_detail detail on info.pl_plan_info_id = detail.pl_plan_info_id 
where
	main.project_phase not in ( '1', '2', '3' ) 
	and info.order_no = '3' 
	),
	temp004 as (
select
	main.pl_project_main_id,
	info.pl_plan_info_id,
	info.task_name,
	info.order_no,
	detail.begin_time,
	detail.over_time 
from
	pl_project_main main
	left join pl_plan_info info on main.pl_project_main_id = info.pl_project_main_id 
	and info.parent_id = '0'
	left join pl_plan_detail detail on info.pl_plan_info_id = detail.pl_plan_info_id 
where
	main.project_phase not in ( '1', '2', '3' ) 
	and info.order_no = '4' 
	),
	temp005 as (
select
	main.pl_project_main_id,
	info.pl_plan_info_id,
	info.task_name,
	info.order_no,
	detail.begin_time,
	detail.over_time 
from
	pl_project_main main
	left join pl_plan_info info on main.pl_project_main_id = info.pl_project_main_id 
	and info.parent_id = '0'
	left join pl_plan_detail detail on info.pl_plan_info_id = detail.pl_plan_info_id 
where
	main.project_phase not in ( '1', '2', '3' ) 
	and info.order_no = '5' 
	) 
	
	
	
	select distinct
	
	        (
                case

                    when to_char(begin_time1,'yyyymmdd') <= to_char(sysdate,'yyyymmdd') and to_char(over_time1,'yyyymmdd') >= to_char(sysdate,'yyyymmdd') then task_name1
                    when to_char(begin_time2,'yyyymmdd') <= to_char(sysdate,'yyyymmdd') and to_char(over_time2,'yyyymmdd') >= to_char(sysdate,'yyyymmdd') then task_name2
                    when to_char(begin_time3,'yyyymmdd') <= to_char(sysdate,'yyyymmdd') and to_char(over_time3,'yyyymmdd') >= to_char(sysdate,'yyyymmdd') then task_name3
                    when to_char(begin_time4,'yyyymmdd') <= to_char(sysdate,'yyyymmdd') and to_char(over_time4,'yyyymmdd') >= to_char(sysdate,'yyyymmdd') then task_name4
                    when to_char(begin_time5,'yyyymmdd') <= to_char(sysdate,'yyyymmdd') and to_char(over_time5,'yyyymmdd') >= to_char(sysdate,'yyyymmdd') then task_name5
                    end
                ) as taskname,
								
								
	
	a.pl_project_main_id,
	a.pl_project_name,
	a.pl_project_no,
	(
case
	
	when a.pl_project_attribute = '1' then
	b.busi_info_id 
	when a.pl_project_attribute = '2' then
	c.busi_info_id 
	when a.pl_project_attribute = '7' then
	d.busi_info_id 
	when a.pl_project_attribute = '6' then
	e.busi_info_id 
	when a.pl_project_attribute = '3' then
	f.busi_info_id 
	when a.pl_project_attribute = '4' then
	g.busi_info_id 
	when a.pl_project_attribute = '8' then
	h.busi_info_id 
	when a.pl_project_attribute = '5' then
	i.busi_info_id 
end 
	) as busiinfoid,
	(
	case
			
			when ( a.project_phase not in ( '1', '2', '3', '8' ) and a.change_status not in ( '5', '6' ) ) then
			'0' 
			when ( a.project_phase != '8' and a.change_status = '5' ) then
			'1' 
			when ( a.project_phase != '8' and a.change_status = '6' ) then
			'2' 
			when project_phase = '8' then
			'3' 
		end 
		) as plprojectstatus,
		j.pro_member_org_id as sysorgid,
		j.pro_member_org_name as sysorgname,
		j.pro_member_name,
		j.pro_member_id,
		k.creation_date,
		a.pl_project_real_over_time as proovertime,
		nvl(
			n.calculate_type,
		( case when j.pro_member_org_name = '测控中心' then 'b' when j.pro_member_org_name = '保障设备中心' then 'b' else 'a' end )) as calculatetype,
		n.delivery_limit,
		n.contract_end,
		n.busi_contract_out_info_id,
		n.adjust_sum 
	from
		pl_project_main a
		left join pl_pro_info_tech b on a.pl_project_main_id = b.pl_project_main_id
		left join pl_pro_info_repair c on a.pl_project_main_id = c.pl_project_main_id
		left join pl_pro_info_planem d on a.pl_project_main_id = d.pl_project_main_id
		left join pl_pro_info_measure e on a.pl_project_main_id = e.pl_project_main_id
		left join pl_pro_info_goods f on a.pl_project_main_id = f.pl_project_main_id
		left join pl_pro_info_app g on a.pl_project_main_id = g.pl_project_main_id
		left join pl_pro_info_airrepair h on a.pl_project_main_id = h.pl_project_main_id
		left join pl_pro_info_airborne i on a.pl_project_main_id = i.pl_project_main_id
		left join pl_pro_member j on j.pl_project_main_id = a.pl_project_main_id 
		and j.pro_role = 0
		left join wf_flow_client_run k on j.pl_project_main_id = k.business_key_
		left join pl_plan_info l on l.parent_id = '0' 
		and l.pl_project_main_id = a.pl_project_main_id
		left join pl_plan_detail m on l.pl_plan_info_id = m.pl_plan_info_id
		left join pl_pro_pay_info n on n.pl_project_main_id = a.pl_project_main_id 
		and n.pay_status = 1
		left join (
		select
			                        temp001.pl_project_main_id,
temp001.task_name task_name1,temp001.order_no order_no1,temp001.begin_time begin_time1,temp001.over_time over_time1,
temp002.task_name task_name2,temp002.order_no order_no2,temp002.begin_time begin_time2,temp002.over_time over_time2,
temp003.task_name task_name3,temp003.order_no order_no3,temp003.begin_time begin_time3,temp003.over_time over_time3,
temp004.task_name task_name4,temp004.order_no order_no4,temp004.begin_time begin_time4,temp004.over_time over_time4,
temp005.task_name task_name5,temp005.order_no order_no5,temp005.begin_time begin_time5,temp005.over_time over_time5
 
		from
			temp001
			left join temp002 on temp001.pl_project_main_id = temp002.pl_project_main_id
			left join temp003 on temp001.pl_project_main_id = temp003.pl_project_main_id
			left join temp004 on temp001.pl_project_main_id = temp004.pl_project_main_id
			left join temp005 on temp001.pl_project_main_id = temp005.pl_project_main_id 
		) temp on a.pl_project_main_id = temp.pl_project_main_id 
	where
		a.project_phase not in ( '1', '2', '3' ) 
	order by
	nlssort( a.pl_project_name, 'nls_sort = schinese_pinyin_m' ),
a.pl_project_no