Oracle的Dual表詳解

1、DUAL表的用途

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

（1）查看當前串連使用者

SQL> select user from dual;

USER

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

SYSTEM

（2）查看當前日期、時間

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

（3）當作計算機用

SQL> select 1+2 from dual;

1+2

----------

3

（4）查看序列值

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

----------

1

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（*）

----------

1

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''）；

1 row created.

SQL> insert into dual values （''Z''）；

1 row created.

SQL> commit;

Commit complete.

SQL> select count（*） from dual;

COUNT（*）

----------

4

SQL> select * from dual;

DUMMY

-----

X

/*

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

SQL> insert into dual values（''Y''）；

1 行 已插入

SQL> commit;

提交完成

SQL> select * from dual;

DUMMY

-----

X

Y

SQL> select sysdate from dual;

SYSDATE

-----------

2004-12-15

2004-12-15

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

select sysdate into v_sysdate from dual;

來擷取時間或者其他資訊的預存程序來說，ORACLE會拋出TOO_MANY_ROWS（ORA-01422）異常。

本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

因此，需要保證在DUAL表內有且僅有一條記錄。當然，也不能把DUAL表的UPDATE，INSERT，DELETE許可權隨意釋放出去，這樣對於系統是很危險的

*/

--把表截掉

SQL> truncate table dual;

Table truncated.

SQL> select count（*） from dual;

COUNT（*）

----------

0

SQL> select * from dual;

no rows selected

SQL> select sysdate from dual;

no rows selected

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

SQL> delete from dual;

1 行 已刪除

SQL> select * from dual;

DUMMY

-----

SQL> select sysdate from dual;

SYSDATE

-----------

/*

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

這個對於很多用

select sysdate into v_sysdate from dual;

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

*/

--對於DELETE操作來說，ORACLE對DUAL表的操作做了一些內部處理，盡量保證DUAL表中只返回一條記錄。當然這寫內部操作是不可見的

--不管表內有多少記錄（沒有記錄除外），ORACLE對於每次DELETE操作都只刪除了一條資料。

SQL> select count（*） from dual;

COUNT（*）

----------

2

SQL> delete from dual;

1 行 已刪除

SQL> commit;

提交完成

SQL> select count（*） from dual;

COUNT（*）

----------

1

/*

附： 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.