oracle臨時(shí)表WITH?AS用法詳解

更新時(shí)間：2024年11月19日 11:06:47 作者：follow_me!

文章介紹了Oracle數(shù)據(jù)庫中的臨時(shí)表,包括會(huì)話級(jí)臨時(shí)表和事務(wù)級(jí)臨時(shí)表的區(qū)別,以及它們的創(chuàng)建和使用方法,會(huì)話級(jí)臨時(shí)表數(shù)據(jù)僅在當(dāng)前會(huì)話存在,而事務(wù)級(jí)臨時(shí)表數(shù)據(jù)僅在當(dāng)前事務(wù)有效,文中還提供了創(chuàng)建和插入數(shù)據(jù)的示例,并簡(jiǎn)要介紹了實(shí)際應(yīng)用中的案例

臨時(shí)表分類

oracle臨時(shí)表分為會(huì)話級(jí)臨時(shí)表和事務(wù)級(jí)臨時(shí)表；

會(huì)話級(jí)的臨時(shí)表只與當(dāng)前會(huì)話相關(guān)，只要當(dāng)前會(huì)話還存在，臨時(shí)表中的數(shù)據(jù)就還存在，一旦退出當(dāng)前會(huì)話，臨時(shí)表中的數(shù)據(jù)也隨之被丟棄；

而且不同會(huì)話中臨時(shí)表數(shù)據(jù)是不同的，當(dāng)前會(huì)話只能對(duì)當(dāng)前會(huì)話的數(shù)據(jù)進(jìn)行操作，無法對(duì)別的會(huì)話的數(shù)據(jù)進(jìn)行操作。

而事務(wù)級(jí)臨時(shí)表，只在當(dāng)前事務(wù)有效，一旦進(jìn)行commit事務(wù)提交之后，臨時(shí)表內(nèi)的數(shù)據(jù)就會(huì)隨著前一個(gè)事務(wù)的結(jié)束而刪除。

會(huì)話級(jí)臨時(shí)表

–創(chuàng)建會(huì)話級(jí)臨時(shí)表

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

–向臨時(shí)表中插入數(shù)據(jù)

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

preserve rows：表示在會(huì)話結(jié)束后清除臨時(shí)表的數(shù)據(jù)。

注：會(huì)話級(jí)臨時(shí)表在當(dāng)前會(huì)話插入的數(shù)據(jù)，只在當(dāng)前會(huì)話可以進(jìn)行操作。

事務(wù)級(jí)臨時(shí)表

–創(chuàng)建事務(wù)級(jí)臨時(shí)表

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

–向事務(wù)級(jí)臨時(shí)表內(nèi)插入數(shù)據(jù)

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

注：在數(shù)據(jù)沒有提交時(shí)，表示該事務(wù)還沒有結(jié)束，此時(shí)是可以查到表內(nèi)數(shù)據(jù)的：

實(shí)際使用案例

案例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 = '測(cè)控中心' THEN 'B' WHEN j.PRO_MEMBER_ORG_NAME = '保障設(shè)備中心' 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