Oracle的dual表

經常在程序裡看到“select sysdate from dual;”
查了一下關於DUAL表的資料：

1. Dual 是什麼?
     select object_name ,object_type from dba_objects where object_name =\''DUAL\''
     結果:

OWNER          OBJECT_NAME           OBJECT_TYPE
----------- ------------------- ------------------
SYS            DUAL                  TABLE
PUBLIC         DUAL                  SYNONYM

可以看出 DUAL是 SYS用戶的一個TABLE.

2. 它有那些FIELD?

SQL> desc dual
Name    Type          Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y

它只有一個 DUMMY FIEld.

3. DUAL 能做什麼?
3.1 查找當天日期

SQL> select sysdate from dual;

SYSDATE
-----------
2004-4-13

3.2 查找當前日期的當月第一天

    SQL> select trunc(sysdate,\''MONTH\'') from dual;

TRUNC(SYSDATE,\''MONTH\'')
----------------------
2004-4-1

3.3 查找當前日期的當月最後一天

SQL> select trunc(last_day(sysdate)) from dual;

TRUNC(LAST_DAY(SYSDATE))
------------------------
2004-4-30

3.4 當作計算器用

SQL> select 1+2 from dual;
1+2
----------
3

3.5 查看序列值

SQL> create sequence aaa increment by 1 start with 1;
SQL> select aaa.nextval from dual;
SQL> select aaa.nextval from dual;
NEXTVAL
----------
1

SQL> select aaa.currval from dual;
CURRVAL
----------
1

4. DUAL奇妙現象

插入一條數據

SQL> insert into sys.dual values (\''V\'');

1 row inserted

SQL> commit;

Commit complete

查看記錄數
SQL> select count(1) from dual;

    COUNT(1)
----------
           2
查詢記錄

SQL> select * from dual;

DUMMY
-----
X
V
刪除記錄

SQL>    delete from dual;

1 row deleted

--- 呵呵,各位看官,為什麼我這樣刪除只能刪除一條記錄呢? 剛才不是查找出2條嗎?

再次查詢記錄

SQL> select * from dual;

DUMMY
-----
V

呵呵,刪除的記錄不是我開始插入的記錄.為什麼???

關於這個現象,Oracle 解釋是:    DUAL should ALWAYS have 1 and only 1 row    !!!

5.Dual的另一應用

Dual has another use when you try to test if you are connected with the DB remotely by JDBC in your AS such as Weblogic. This is a common table for any schema.

原先我取系統時間，向來是select sysdate from 隨便某個表，
想想也會有些隱患，
如果該表被刪除了，會出異常的。
今後也要專業一點，
select sysdate from dual! ^_^