oracle 中 dual 詳解

基本上oracle引入dual為的就是符合文法

1. 我們先從名稱來說，dual不是縮寫詞，本身就是完整的單詞。dual名詞意思是對數，做形容詞時是指二重的，二進制的。

2. Oracle中的dual表是一個單行單列的虛拟表。

3. Dual表是oracle與資料字典一起自動建立的一個表，這個表隻有1列：DUMMY，資料類型為VERCHAR2(1)，dual表中隻有一個資料'X', Oracle有内部邏輯保證dual表中永遠隻有一條資料。

4. Dual表主要用來選擇系統變量或求一個表達式的值。

5. 更具體的說，我們見過這樣一個簡單的例子： 

      SELECT sysdate FROM daul

    而Oracle的SELECT文法的限制為:

SELECT * | [column1 [AS alias1], column2 [AS alias2]] FROM table

是以，沒有表名就沒有辦法查詢，而時間日期并不存放在任何表中，于是這個dual虛拟表的概念就被引入了。

1、DUAL表的用途

Dual 是 Oracle中的一個實際存在的表，任何使用者均可讀取，常用在沒有目标表的Select語句塊中

--檢視目前連接配接使用者

SQL> select user from dual;

USER

------------------------------

SYSTEM

--檢視目前日期、時間

SQL> select sysdate from dual;

SYSDATE

-----------

2007-1-24 1

SQL> select to_char(sysdate,''yyyy-mm-dd hh24:mi:ss'') from dual;

TO_CHAR(SYSDATE,''YYYY-MM-DDHH2

2007-01-24 15:02:47

--當作電腦用

SQL> select 1+2 from dual;

1+2

----------

3

--檢視序列值

SQL> create sequence aaa increment by 1 start with 1;

SQL> select aaa.nextval from dual;

NEXTVAL

1

SQL> select aaa.currval from dual;

CURRVAL

2、關于DUAL表的測試與分析

DUAL就是個一行一列的表，如果你往裡執行insert、delete、truncate操作，就會導緻很多程式出問題。結果也因sql*plus、pl/sql dev等工具而異。

--檢視DUAL是什麼OBJECT

--DUAL是屬于SYS schema的一個表,然後以PUBLIC SYNONYM的方式供其他資料庫USER使用.

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

--檢視表結構，隻有一個字段DUMMY，為VARCHAR2(1)型

SQL> desc dual

Name Type Nullable Default Comments

----- ----------- -------- ------- --------

DUMMY VARCHAR2(1) Y

--DUAL表的結構：

create table SYS.DUAL

(

DUMMY VARCHAR2(1)

)

tablespace SYSTEM

pctfree 10

pctused 40

initrans 1

maxtrans 255

storage

initial 16K

next 16K

minextents 1

maxextents 505

pctincrease 50

);

/*

很是困惑，ORACLE為什麼要用VARCHAR(1)型，用CHAR(1)難道不好麼？從這樣的表結構來看，DUAL表設計的目的就是要盡可能的簡單，以減少檢索的開銷。

還有，DUAL表是建立在SYSTEM表空間的，第一是因為DUAL表是SYS這個使用者建的，本來預設的表空間就是SYSTEM；第二，把這個可能經常被查詢的表和使用者表分開來存放，對于系統性能的是有好處的。

有了建立了表、建立了同義詞還是不夠的。DUAL在SYS這個Schema下面，是以用别的使用者登入是無法查詢這個表的，是以還需要授權：

grant select on SYS.DUAL to PUBLIC with grant option;

将Select 權限授予公衆。

接下來看看DUAL表中的資料，事實上，DUAL表中的資料和ORACLE資料庫環境有着十分重要的關系（ORACLE不會為此癱瘓，但是不少存儲過程以及一些查詢将無法被正确執行）。

*/

--查詢行數

--在建立資料庫之後，DUAL表中便已經被插入了一條記錄。個人認為：DUMMY字段的值并沒有什麼關系，重要的是DUAL表中的記錄數

SQL> select count(*) from dual;

COUNT(*)

SQL> select * from dual;

DUMMY

-----

X

--插入資料，再查詢記錄，隻傳回一行記錄

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

1 row created.

SQL> commit;

Commit complete.

SQL> insert into dual values (''X'');

SQL> insert into dual values (''Z'');

4

--假我們插入一條資料，DUAL表不是傳回一行，而是多行記錄，那會是什麼結果呢？

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

1 行 已插入

送出完成

Y

2004-12-15

這個時候傳回的是兩條記錄，這樣同樣會引起問題。在通過使用

select sysdate into v_sysdate from dual;

來擷取時間或者其他資訊的存儲過程來說，ORACLE會抛出TOO_MANY_ROWS(ORA-01422)異常。

是以，需要保證在DUAL表内有且僅有一條記錄。當然，也不能把DUAL表的UPDATE，INSERT，DELETE權限随意釋放出去，這樣對于系統是很危險的

--把表截掉

SQL> truncate table dual;

Table truncated.

no rows selected

--試着把DUAL表中的資料删除，看看會出現什麼結果：

SQL> delete from dual;

1 行 已删除

我們便取不到系統日期了。因為，sysdate是個函數，作用于每一個資料行。現在沒有資料了，自然就不可能取出系統日期。

這個對于很多用

這種方式取系統時間以及其他資訊的存儲過程來說是緻命的，因為，ORACLE會馬上抛出一個NO_DATA_FOUND（ORA-01403）的異常，即使異常被捕獲，存儲過程也将無法正确完成要求的動作。

--對于DELETE操作來說，ORACLE對DUAL表的操作做了一些内部處理,盡量保證DUAL表中隻傳回一條記錄.當然這寫内部操作是不可見的

--不管表内有多少記錄（沒有記錄除外）,ORACLE對于每次DELETE操作都隻删除了一條資料。

2

附: ORACLE關于DUAL表不同尋常特性的解釋

There is internalized code that makes this happen. Code checks that ensurethat 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

DUAL表可以執行插入、更新、删除操作，還可以執行drop操作。但是不要去執行drop表的操作，否則會使系統不能用，資料庫起不了，會報Database startup crashes with ORA-1092錯誤。

3、如果DUAL表被“不幸”删除後的恢複：

用sys使用者登陸。

建立DUAL表。

授予公衆SELECT權限（SQL如上述，但不要給UPDATE，INSERT，DELETE權限）。

向DUAL表插入一條記錄（僅此一條）： insert into dual values(''X'');

送出修改。

--用sys使用者登陸。

SQL> create pfile=’d:\pfile.bak’ from spfile

SQL> shutdown immediate

--在d:\pfile.bak檔案中最後加入一條：replication_dependency_tracking = FALSE

--重新啟動資料庫：

SQL> startup pfile=’d:\pfile.bak’

SQL> create table “sys”.”DUAL”

( “DUMMY” varchar2(1) )

pctfree 10 pctused 4;

SQL> insert into dual values(‘X’);

SQL> Grant select on dual to Public;

授權成功。

D

-

資料庫已經關閉。

已經解除安裝資料庫。

ORACLE 例程已經關閉。

SQL> startup

ORACLE 例程已經啟動。

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

資料庫裝載完畢。

資料庫已經打開。

SQL>

--OK， 下面就可以正常使用了。

最後在啰嗦一下，因為今天突然要用到查每個月最後一天的需要，是以上網查到Oracle裡面有一個直接的函數就可以做到，後來我突然又想怎麼沒有最開始的一天的函數呢，可能是我沒有細心找吧 總之不管那麼多了，自己就随便寫了一下，下面把個SQL寫出來：

select to_char((last_day(to_date(to_char(sysdate,'mm')-1,'mm'))+1),'yyyy-mm-dd') as startday,to_char(last_day(sysdate),'yyyy-mm-dd') as lastday from dual;

裡面有些to_char其實是沒有用的，但是為了轉換成一緻的日期格式，是以還是用了，寫在此處主要是為了記憶

本文轉自xiaocao1314051CTO部落格，原文連結：http://blog.51cto.com/xiaocao13140/1979088 ，如需轉載請自行聯系原作者