Dual Table in Oracle / Dual表在Oracle中的应用

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users.
It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X.

Dual在Oracle中是一个虚拟表，它只有一个类型为VARCHAR2的子段，即DUMMY，值为X。

SELECT * FROM DUAL;

DU
—–
X

Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Please refer to “SQL Functions” for many examples of selecting a constant value from DUAL.

此外，DUAL表还有许多其他的用途：
1. 查看当前用户

SELECT user FROM DUAL;

USER
——
SCOTT

2. 调用系统函数，比如日期、随机获取数字等

-- 获取当前系统时间
SELECT sysdate FROM DUAL;
-- 获取当前locale
SELECT SYS_CONTEXT('USERENV','LANGUAGE') FROM DUAL;
-- 获取随机数字
SELECT dbms_tandom.random FROM DUAL;

3. 进行数学运算

SELECT 3+6 FROM DUAL;
SELECT 5*6.5 FROM DUAL;

4. 查看序列值

-- 创建一个从1开始，递增1的序列
CREATE sequence abc increment by 1 start with 1;
-- 获取序列abc下一个值
SELECT abc.nextval FROM DUAL; -- 得到1
SELECT abc.nextval FROM DUAL; -- 重复执行，得到2
-- 获取序列abc当前值
SELECT abc.currval FROM DUAL; -- 得到2

注：序列建立后，sequence.currval一定要在next.currval后面执行；如果序列建立后就获取sequence.currval会报错：sequence ABC.CURRVAL is not yet defined in this session。

文中引用出自：https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries009.htm