ä¸ ä¸´æ¶è¡¨è¯æ³
临æ¶è¡¨åªå¨Oracle 8i 以å以ä¸äº§åä¸æ¯æã
è¯æ³ï¼
<textarea cols="50"
rows="15" name="code" class="java:showcolumns">create global
temporary table 临æ¶è¡¨å on commit preserve/delete rows
--preserve:SESSION级ç临æ¶è¡¨ï¼delete:TRANSACTION级ç临æ¶è¡¨.</textarea>
SESSION级ç临æ¶è¡¨æ°æ®å¨æ´ä¸ªSESSIONé½åå¨ï¼ç´å°ç»ææ¤æ¬¡SESSIONï¼èTRANSACTION级ç临æ¶è¡¨æ°æ®å¨
TRANACTIONç»æåæ¶å¤±ï¼å³COMMIT/ROLLBACKæç»æSESSIONé½ä¼æ¸
é¤TRANACTION临æ¶è¡¨æ°æ®ã
å½ä¼è¯éåºæè
ç¨æ·æ交commitååæ»rollbackäº
å¡çæ¶åï¼ä¸´æ¶è¡¨çæ°æ®èªå¨æ¸
空ï¼ä½æ¯ä¸´æ¶è¡¨çç»æ以åå
æ°æ®è¿åå¨å¨ç¨æ·çæ°æ®åå
¸ä¸ãå½ä¸ä¸ªä¼è¯ç»æ(ç¨æ·æ£å¸¸éåºãç¨æ·ä¸æ£å¸¸éåºãORACLEå®
ä¾å´©æº)æè
ä¸ä¸ªäºå¡ç»æçæ¶åï¼Oracle对è¿ä¸ªä¼è¯ç表æ§è¡ TRUNCATE
è¯å¥æ¸
空临æ¶è¡¨æ°æ®ï¼ä½ä¸ä¼æ¸
空å
¶å®ä¼è¯ä¸´æ¶è¡¨ä¸çæ°æ®ã临æ¶è¡¨å¯ä»¥ä½¿ç¨è§¦åå¨ã
äº ä¸´æ¶è¡¨ORACLEæ°æ®åºä¸sqlserverçåºå«
ORACLE临æ¶è¡¨ï¼transactionæè
sessionç»æï¼ä¼æ¸
空表æ°æ®ãä½æ¯è¡¨è¿åå¨ã
SQL SERVER临æ¶è¡¨ï¼åå¨å¨ tempdb ä¸ï¼å½ä¸å使ç¨æ¶ä¼èªå¨å é¤ã
1 SQL SERVER临æ¶è¡¨æ¯ä¸ç§âå
å表âï¼è¡¨æ¯åå¨å¨å
åä¸çãORACLE临æ¶è¡¨é¤éæ§è¡DROP TABLE,å¦å表å®ä¹ä¼ä¿çå¨æ°æ®åå
¸ä¸ï¼
2 SQL SERVER临æ¶è¡¨ä¸åå¨ç±»ä¼¼ORACLE临æ¶è¡¨äºå¡çº§å«ä¸çåè½ï¼
3 SQL SERVERæ¬å°ä¸´æ¶è¡¨(#) ä¸ ORACLEçä¼è¯çº§å«ä¸´æ¶è¡¨ç±»ä¼¼ï¼ä½æ¯å¨ä¼è¯éåºçæ¶åï¼SQL SERVERä¼èªå¨å é¤ï¼ORACLEä¸ä¼å é¤è¡¨ï¼
4 SQL SERVERçå
¨å±ä¸´æ¶è¡¨(##) æ¯æå¤ä¸ªè¿æ¥å
±äº«åä¸çå
åãå½æ²¡ææéå¼ç¨è¯¥å
ååºåæ¶ï¼SQL SERVERèªå¨éæ¾å
¨å±ä¸´æ¶è¡¨ã
ORACLEä¸æ¯ä¸ç§å
åä¸çæ°æ®åºï¼æ以å¦æORACLE类似SQL SERVER é¢ç¹ç对临æ¶è¡¨è¿è¡å»ºç«åå é¤ï¼å¿
å®ä¼å½±åæ§è½ãæ以ORACLEä¼ä¿ç临æ¶è¡¨çå®ä¹ç´å°ç¨æ·DROP TABLEã
å¨ORACLEä¸ï¼å¦æéè¦å¤ä¸ªç¨æ·å
±äº«ä¸ä¸ªè¡¨(类似
SQL
SERVERçå
¨å±ä¸´æ¶è¡¨##)ï¼åå¯ä»¥å©ç¨æ°¸ä¹
表ï¼å¹¶ä¸å¨è¡¨ä¸æ·»å ä¸äºå¯ä»¥å¯ä¸æ è¯ç¨æ·çåãå©ç¨è§¦åå¨åè§å¾ï¼å½ç¨æ·éåºçæ¶åï¼æ ¹æ®è¯¥ç»éç¨æ·çå¯
ä¸ä¿¡æ¯å é¤ç¸åºç表ä¸çæ°æ®ã ä½è¿ç§æ¹æ³ç»ORACLE带æ¥äºä¸å®éçè´è½½ã
ä¸ ä½¿ç¨
1 å¤æ表ä¸åå¨å°±å建表
<textarea cols="50" rows="15" name="code" class="java:showcolumns:firstline[1]">create ...
is
temptable_name varchar2(20);
v_count number(1);
begin
temptable_name := 'TEMP_TABLENAME';
select count(*) into v_count from tab where tname = temptable_name;
if(v_count = 0) then
execute immediate 'create table ...';
else
--å
¶ä»è¡¨åå¨çæä½
end if;</textarea>
2 注æ使ç¨æ¶ï¼å¯è½ä¼äº§ççé误ï¼ORA-08103: object no longer
existsï¼ORA-14452 attempt to create, alter or drop an index on temporary
table already in use
ORA-14452 ï¼ éè¿1çå¤æï¼ä¸è¬ä¼è§£å³è¯¥é®é¢ãå¦æå¨è¡¨ä½¿ç¨æ¶ï¼æ§è¡å é¤è¡¨æä½ï¼ä¼åºç°è¯¥é误ã
ORA-08103 ï¼ ä½¿ç¨äºå¡çº§ç临æ¶è¡¨æ¶ï¼å¦æï¼äºå¡æ交åæ§è¡å é¤è¡¨æä½æè
å¨äºå¡æ交以å使ç¨ï¼å°±ä¼äº§çè¿æ ·çé®é¢ãæ¹æä¼è¯çº§å«çå¯ä»¥è§£å³è¿ä¸ªé®é¢ï¼ä½æ¯å¨ä¼è¯çº§å«çæ°æ®çæ£ç¡®æ§ï¼å¯è½ä¼å 为使ç¨è¿æ¥æ± ï¼åä¸ä¸ªä¼è¯ä¸ä¸åæä½è产çé误ã
温馨提示:答案为网友推荐,仅供参考