oracle临时表 WITH AS用法

临时表 分类：

oracle临时表分为会话级临时表和事务级临时表；会话级的临时表只与当前会话相关，只要当前会话还存在，临时表中的数据就还存在，一旦退出当前会话，临时表中的数据也随之被丢弃；而且不同会话中临时表数据是不同的，当前会话只能对当前会话的数据进行操作，无法对别的会话的数据进行操作。而事务级临时表，只在当前事务有效，一旦进行commit事务提交之后，
临时表内的数据就会随着前一个事务的结束而删除。

会话级临时表：

–创建会话级临时表
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;
preserve rows:表示在会话结束后清除临时表的数据。
注：会话级临时表在当前会话插入的数据，只在当前会话可以进行操作。

事务级临时表

–创建事务级临时表
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;
注：在数据没有提交时，表示该事务还没有结束，此时是可以查到表内数据的：

实际使用案例：

案例1：

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'
1
2
3
4
5
6
7

案例2：

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192