临时表分类
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 |
