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