Oracle DUAL 虛擬表深度解析：原理、用法與奇妙現象

DUAL 表是什麼？

DUAL 是 Oracle 系統內建的特殊單列單行表，屬於 SYS 用戶，公開同義詞讓所有用戶可直接存取。

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM DBA_OBJECTS 
WHERE OBJECT_NAME = 'DUAL';

-- 結果：
-- SYS   DUAL   TABLE
-- PUBLIC DUAL  SYNONYM

結構極簡

DESC DUAL
-- Name   Type         Nullable Default
-- DUMMY  VARCHAR2(1)  Y

僅含一個 DUMMY 欄位，預設值 'X'，僅一行資料。

核心用法場景

1. 取得系統函數返回值

-- 當前日期時間
SELECT SYSDATE FROM DUAL;
SELECT CURRENT_TIMESTAMP FROM DUAL;

-- 日期運算
SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;      -- 本月第一天
SELECT TRUNC(LAST_DAY(SYSDATE)) FROM DUAL;     -- 本月最後一天

2. 當作計算器使用

SELECT 1+2 FROM DUAL;        -- 3
SELECT 100*0.05 FROM DUAL;   -- 5

3. 序列值獲取

CREATE SEQUENCE AAA INCREMENT BY 1 START WITH 1;
SELECT AAA.NEXTVAL FROM DUAL;  -- 1
SELECT AAA.NEXTVAL FROM DUAL;  -- 2
SELECT AAA.CURRVAL FROM DUAL;  -- 2

4. 字串操作與轉換測試

SELECT UPPER('hello') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

DUAL 的奇妙現象與機制

-- 插入資料
INSERT INTO SYS.DUAL VALUES ('V');
COMMIT;

-- 查詢筆數
SELECT COUNT(1) FROM DUAL;  -- 返回 2

-- 查詢內容
SELECT * FROM DUAL;
-- DUMMY
-- -----
-- X
-- V

-- 刪除
DELETE FROM DUAL;  -- 僅刪除 1 筆

-- 再次查詢
SELECT * FROM DUAL;
-- DUMMY
-- -----
-- V

現象解析

Oracle 內部機制強制保證 DUAL 永遠只有 1 行資料。插入後實際有 2 行，但查詢時透過內部邏輯過濾，只顯示預設行。刪除時優先移除預設行 'X'，導致看似「刪不掉自己插入的資料」。

官方說明：DUAL should ALWAYS have 1 and only 1 row !!!

最佳實踐建議

• 永遠使用 SELECT ... FROM DUAL 取代任意表，避免表被刪除導致應用異常
• 嚴禁對 DUAL 執行 DML（INSERT/UPDATE/DELETE），會破壞系統預期行為
• JDBC 連線測試常用 SELECT 1 FROM DUAL 驗證資料庫可用性

進階：DUAL 在 PL/SQL 中的應用

-- 變數賦值
SELECT SYSDATE INTO V_DATE FROM DUAL;

-- 條件判斷
SELECT CASE WHEN 1=1 THEN 'Y' ELSE 'N' END INTO V_FLAG FROM DUAL;