DUAL表如何支持日期格式？其为VARCHAR2(1)类型为何可支持日期函数？

Great question! Let's break this down step by step—DUAL is one of those quirky Oracle-specific features that trips up a lot of people at first, but once you get its purpose, it makes total sense.

First: How does DUAL support date formats when its only column is VARCHAR2(1)? #

• The critical realization here is that you're not pulling date data from DUAL's column. DUAL is a tiny, single-row, single-column virtual table with just a DUMMY column of type VARCHAR2(1) (usually holding the value 'X').
• When you run a query like SELECT SYSDATE FROM DUAL;, SYSDATE is an Oracle built-in function that returns a DATE type value directly. DUAL doesn't contribute any data to this result—it's just acting as a "syntax placeholder" to satisfy Oracle's requirement that SELECT statements need a FROM clause (in most versions, anyway).
• The data type of your query's result is determined by the expression you're selecting (like SYSDATE, or a date literal), not by the columns in DUAL. So even though DUAL's column is a string, your result can still be a date, number, or any other data type supported by Oracle.

Second: Why does DUAL work with date functions like EXTRACT(MONTH FROM SYSDATE)? #

• Date functions (and all Oracle built-in functions, for that matter) don't depend on table data to run. Functions like EXTRACT, SYSDATE, TO_DATE, etc., compute their results based on input values (or system values, in the case of SYSDATE) independently of any table.
• DUAL's only job here is to provide a valid target for the FROM clause. When you execute SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;, Oracle first evaluates the EXTRACT(...) expression (calculating the month from the current system date), then uses DUAL to return that single result row.
• Think of it this way: if Oracle allowed SELECT EXTRACT(MONTH FROM SYSDATE); without a FROM clause, you wouldn't need DUAL at all. But since the syntax requires a FROM for standalone selects, DUAL is the lightweight, optimized way to make that query legal.

A quick example to drive this home #

If you run:

SELECT SYSDATE, DUMMY FROM DUAL;

You'll get two columns: one with a DATE value (from SYSDATE) and one with the VARCHAR2(1) 'X' (from DUAL's column). The two data types coexist perfectly because they're separate expressions—DUAL's column type doesn't restrict what you can select alongside it.

And a bonus note: Oracle optimizes DUAL queries heavily. It doesn't actually read from a physical table on disk; it's treated as a special case that returns a single row instantly.

内容的提问来源于stack exchange，提问作者sygops