数据类型转换
当这种转换有意义时，Oracle 数据库会自动将值从一种数据类型转换为另一种数据类型。
表 2-8是 Oracle 隐式转换的矩阵。该表显示了所有可能的转换，不考虑转换的方向或进行转换的上下文。
单元格中的“X”表示第一列和标题行中命名的数据类型的隐式转换。
| DataType | CHAR | VARCHAR2 | NCHAR | NVARCHAR2 | DATE | DATETIME/INTERVAL | NUMBER | BINARY_FLOAT | BINARY_DOUBLE | LONG | RAW | ROWID | CLOB | BLOB | NCLOB |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
CHAR |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
|
VARCHAR2 |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
|
NCHAR |
X |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
|
NVARCHAR2 |
X |
X |
X |
-- |
X |
X |
X |
X |
X |
X |
X |
X |
X |
-- |
X |
|
DATE |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
DATETIME/ INTERVAL |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
-- |
|
NUMBER |
X |
X |
X |
X |
-- |
-- |
-- |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
|
BINARY_FLOAT |
X |
X |
X |
X |
-- |
-- |
X |
-- |
X |
-- |
-- |
-- |
-- |
-- |
-- |
|
BINARY_DOUBLE |
X |
X |
X |
X |
-- |
-- |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
LONG |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
X |
-- |
X |
|
|
RAW |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
X |
-- |
|
ROWID |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
CLOB |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
X |
|
BLOB |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
-- |
-- |
|
NCLOB |
X |
X |
X |
X |
-- |
-- |
-- |
-- |
-- |
X |
-- |
-- |
X |
-- |
-- |
不能直接将LONG转换为INTERVAL，但可以使用to_CHAR（INTERVAL）将LONG转化为VARCHAR2，然后将得到的VARCHAR2值转化为INTERVAL。
Implicit Data Type Conversion Rules
-
During
INSERTandUPDATEoperations, Oracle converts the value to the data type of the affected column. -
During
SELECTFROMoperations, Oracle converts the data from the column to the type of the target variable. -
When manipulating numeric values, Oracle usually adjusts precision and scale to allow for maximum capacity. In such cases, the numeric data type resulting from such operations can differ from the numeric data type found in the underlying tables.
-
When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.
-
Conversions between character values or
NUMBERvalues and floating-point number values can be inexact, because the character types andNUMBERuse decimal precision to represent the numeric value, and the floating-point numbers use binary precision. -
When converting a
CLOBvalue into a character data type such asVARCHAR2, or convertingBLOBtoRAWdata, if the data to be converted is larger than the target data type, then the database returns an error. -
During conversion from a timestamp value to a
DATEvalue, the fractional seconds portion of the timestamp value is truncated. This behavior differs from earlier releases of Oracle Database, when the fractional seconds portion of the timestamp value was rounded. -
Conversions from
BINARY_FLOATtoBINARY_DOUBLEare exact. -
Conversions from
BINARY_DOUBLEtoBINARY_FLOATare inexact if theBINARY_DOUBLEvalue uses more bits of precision that supported by theBINARY_FLOAT. -
When comparing a character value with a
DATEvalue, Oracle converts the character data toDATE. -
When you use a SQL function or operator with an argument of a data type other than the one it accepts, Oracle converts the argument to the accepted data type.
-
When making assignments, Oracle converts the value on the right side of the equal sign (=) to the data type of the target of the assignment on the left side.
-
During concatenation operations, Oracle converts from noncharacter data types to
CHARorNCHAR. -
During arithmetic operations on and comparisons between character and noncharacter data types, Oracle converts from any character data type to a numeric, date, or rowid, as appropriate. In arithmetic operations between
CHAR/VARCHAR2andNCHAR/NVARCHAR2, Oracle converts to aNUMBER. -
Most SQL character functions are enabled to accept
CLOBs as parameters, and Oracle performs implicit conversions betweenCLOBand character types. Therefore, functions that are not yet enabled forCLOBs can acceptCLOBs through implicit conversion. In such cases, Oracle converts theCLOBs toCHARorVARCHAR2before the function is invoked. If theCLOBis larger than 4000 bytes, then Oracle converts only the first 4000 bytes toCHAR. -
When converting
RAWorLONGRAWdata to or from character data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits ofRAWdata. Refer to "RAW and LONG RAW Data Types" for more information. -
Comparisons between
CHARandVARCHAR2and betweenNCHARandNVARCHAR2types may entail different character sets. The default direction of conversion in such cases is from the database character set to the national character set. Table 2-9 shows the direction of implicit conversions between different character types.
Table 2-9 Conversion Direction of Different Character Types
| SourceData Type | to CHAR | to VARCHAR2 | to NCHAR | to NVARCHAR2 |
|---|---|---|---|---|
|
from CHAR |
-- |
|
|
|
|
from VARCHAR2 |
|
-- |
|
|
|
from NCHAR |
|
|
-- |
|
|
from NVARCHAR2 |
|
|
|
-- |
User-defined types such as collections cannot be implicitly converted, but must be explicitly converted using CAST ... MULTISET.
