Oracle中DUAL表到底是什么❔
DUAL
是Oracle与数据字典一起自动创建的一个最小工作表,它只有一列:DUMMY
,其数据类型为:VARCHAR2(1)
。DUAL中只有一行数据:'X'
。DUAL属于SYS
模式,但所有用户都可以使用DUAL名称访问它。用SELECT计算常量表达式、伪列等值时常用该表,因为它只返回一行数据,而使用其它表时可能返回多个数据行。
🌼特性
-
1、Oracle提供的最小的表,不论进行何种操作(不要删除记录, 如果你往里执行insert、delete、truncate操作,就会导致很多程序出问题),它都只有一条记录——‘X’。
例如:执行
select * from dual
,里面只有一条记录;执行insert into dual values('Y')
后,再次查询dual表,仍然显示一条记录。 -
2、是sys用户下的一张内部表,所有用户都可以使用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
🌱相关用途
-- 无对象的算法操作
select 1+1
from DUAL;
-- 查询当前用户
select user
from DUAL;
-- 查看当前系统时间 trunc(不会截取到秒)
select trunc(sysdate)
from dual;
-- 精确到秒的时间查询
select sysdate
from DUAL;
-- 查询序列
select CB_BA_BANK_PRACTICE_SEQ.nextval from dual;
select CB_BA_BANK_PRACTICE_SEQ.currval from dual;
-- 查询记录条数
select count(*)
from DUAL;
-- 查询
select *
from DUAL;
--每个月第一天和最后一天
select to_char(trunc(add_months(last_day(sysdate), -1) + 1), 'yyyy-mm-dd') startday,
to_char(last_day(sysdate), 'yyyy-mm-dd') lastday from dual;
select trunc(sysdate) from dual ; -- 今天的日期
select add_months(last_day(sysdate), -1) + 1 from DUAL; --上个月的最后一天加一天(该月的第一天)
select trunc(sysdate, 'mm') from dual ;-- 返回当月第一天.
select trunc(sysdate,'yy') from dual ; --返回当年第一天
select trunc(sysdate,'dd') from dual ; -- 返回当前年月日
select trunc(sysdate,'yyyy') from dual ; -- 返回当年第一天
select trunc(sysdate,'d') from dual; -- 返回当前星期的第一天
select trunc(sysdate, 'hh') from dual; -- 当前时间(精确到小时)
select trunc(sysdate, 'mi') from dual ; -- 当前时间(精确到分钟)
select trunc(sysdate, 'ss') from dual ; -- TRUNC()函数没有秒的精确,这里会报错
-- 精确到秒的时间查询
select sysdate
from DUAL;
-- 按照自己格式将时间转换为字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from DUAL;