oracle临时表 WITH AS用法

follow_me!

于 2023-09-22 15:39:11 发布

阅读量1k

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_38837032/article/details/130576599

版权

本文介绍了Oracle数据库中的会话级临时表和事务级临时表的区别，以及在两个级别下创建和操作临时表的方法。通过实际案例展示了如何在SQL查询中使用这两种临时表来组织和筛选数据。

摘要由CSDN通过智能技术生成

临时表分类：

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'

案例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