当前位置：首页 > 站长知识 > 数据库 > 正文内容

oracle临时表WITH AS的用法

2024-11-30数据库53

临时表分类

oracle临时表分为会话级临时表和事务级临时表；

会话级的临时表只与当前会话相关，只要当前会话还存在，临时表中的数据就还存在，一旦退出当前会话，临时表中的数据也随之被丢弃；

而且不同会话中临时表数据是不同的，当前会话只能对当前会话的数据进行操作，无法对别的会话的数据进行操作。

而事务级临时表，只在当前事务有效，一旦进行commit事务提交之后，临时表内的数据就会随着前一个事务的结束而删除。

会话级临时表

1
2
3
4
5
6
7
8
9
10
11
–创建会话级临时表
 
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：表示在会话结束后清除临时表的数据。

注：会话级临时表在当前会话插入的数据，只在当前会话可以进行操作。

事务级临时表

1
2
3
4
5
6
7
8
9
10
11
–创建事务级临时表
 
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：

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

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


相关文章

SQL中的INNER JOIN用法详解2024-11-29
SQL Server怎么导入Excel数据2024-11-29
SQL Server怎么创建用户只能访问指定数据库2024-11-29
sql 快速删除数百万行数据的方法2024-11-29
SQL中的窗口函数2024-11-29
sql中的regexp与like区别2024-11-29
SQL Server的Descending Indexes降序索引2024-11-29
SQL Server中数据类型转换的原理和使用方法2024-11-29
MS SQL Server排查多列之间的值是否重复的功能2024-11-29

在线制作 ICO 图标

本站不销售产品、不代购、不提供技术支持，仅分享信息，请遵纪守法、文明上网。

Copyright © 2015-2024 国外主机测评. 闽ICP备2024075899号-2