這個(gè)是jrlee在帖子中轉(zhuǎn)載的關(guān)于DUAL的東西
看看這個(gè)可以對(duì)DUAL有個(gè)更深入的了解
引用：原帖由?"jrlee"?發(fā)表：

神秘的DUAL?black_snail（原作）?

關(guān)鍵字?ORACLE?DUAL?


DUAL???有什么神秘的??當(dāng)你想得到ORACLE系統(tǒng)時(shí)間,?簡(jiǎn)簡(jiǎn)單單敲一行SQL?

不就得了嗎??故弄玄虛….?

SQL>;?select?sysdate?from?dual;?

SYSDATE?

---------?

28-SEP-03?



哈哈,?確實(shí)DUAL的使用很方便.?但是大家知道DUAL倒底是什么OBJECT,?它有什么特殊的行為嗎??來(lái),我們一起看一看.?



首先搞清楚DUAL是什么OBJECT?:?

SQL>;?connect?system/manager?

Connected.?

SQL>;?select?owner,?object_name?,?object_type?from?dba_objects?where?object_name?like?'%DUAL%';?



OWNER?OBJECT_NAME?OBJECT_TYPE?

---------------?---------------?-------------?

SYS?DUAL?TABLE?

PUBLIC?DUAL?SYNONYM?



原來(lái)DUAL是屬于SYS?schema的一個(gè)表,然后以PUBLIC?SYNONYM的方式供其他數(shù)據(jù)庫(kù)USER使用.?

再看看它的結(jié)構(gòu):?

SQL>;?desc?dual?

Name?Null??Type?

-----------------------------------------?--------?----------------------------?

DUMMY?VARCHAR2(1)?



SQL>;?



只有一個(gè)名字叫DUMMY的字符型COLUMN?.?



然后查詢(xún)一下表里的數(shù)據(jù):?

SQL>;?select?dummy?from?dual;?

DUMMY?

----------?

X?



哦,?只有一條記錄,?DUMMY的值是’X’?.很正常啊,沒(méi)什么奇怪嘛.?好,下面就有奇妙的東西出現(xiàn)了!?

插入一條記錄:?

SQL>;?connect?sys?as?sysdba?

Connected.?

SQL>;?insert?into?dual?values?(?'Y');?

1?row?created.?

SQL>;?commit;?

Commit?complete.?

SQL>;?select?count(*)?from?dual;?

COUNT(*)?

----------?

2?

迄今為止,一切正常.?然而當(dāng)我們?cè)俅尾樵?xún)記錄時(shí),奇怪的事情發(fā)生了?

SQL>;?select?*?from?dual;?

DUMMY?

----------?

X?

剛才插入的那條記錄并沒(méi)有顯示出來(lái)?!?明明DUAL表中有兩條記錄,?可就是只顯示一條!?

再試一下刪除?,狠一點(diǎn),全刪光?!?

SQL>;?delete?from?dual;?/*注意沒(méi)有限定條件,試圖刪除全部記錄*/?

1?row?deleted.?

SQL>;?commit;?

Commit?complete.?



哈哈,也只有一條記錄被刪掉,?

SQL>;?select?*?from?dual;?

DUMMY?

----------?

Y?



為什么會(huì)這樣呢??難道SQL的語(yǔ)法對(duì)DUAL不起作用嗎?帶著這個(gè)疑問(wèn),?我查詢(xún)了一些ORACLE官方的資料.?原來(lái)ORACLE對(duì)DUAL表的操作做了一些內(nèi)部處理,盡量保證DUAL表中只返回一條記錄.當(dāng)然這寫(xiě)內(nèi)部操作是不可見(jiàn)的?.?

看來(lái)ORACLE真是蘊(yùn)藏著無(wú)窮的奧妙啊!?



附:?ORACLE關(guān)于DUAL表不同尋常特性的解釋?

There?is?internalized?code?that?makes?this?happen.?Code?checks?that?ensure?

that?a?table?scan?of?SYS.DUAL?only?returns?one?row.?Svrmgrl?behaviour?is?

incorrect?but?this?is?now?an?obsolete?product.?

The?base?issue?you?should?always?remember?and?keep?is:?DUAL?table?should?always?

have?1?ROW.?Dual?is?a?normal?table?with?one?dummy?column?of?varchar2(1).?

This?is?basically?used?from?several?applications?as?a?pseudo?table?for?

getting?results?from?a?select?statement?that?use?functions?like?sysdate?or?other?

prebuilt?or?application?functions.?If?DUAL?has?no?rows?at?all?some?applications?

(that?use?DUAL)?may?fail?with?NO_DATA_FOUND?exception.?If?DUAL?has?more?than?1?

row?then?applications?(that?use?DUAL)?may?fail?with?TOO_MANY_ROWS?exception.?

So?DUAL?should?ALWAYS?have?1?and?only?1?row