oracle临时表WITH AS用法详解

发布者: 网神之王 | 发布时间: 2025-6-18 09:28| 查看数: 112| 评论数: 0|帖子模式

临时表分类

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

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源：https://www.jb51.net/database/330973jvz.htm
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

oracle临时表WITH AS用法详解

最新评论

新人须知

常见问题

维权申诉

关注微博

扫描微信

关于合作

通讯联系