七月 5, 2023
通常，表达式不能包含不同数据类型的值。例如，表达式不能将 5 乘以 10，然后加以“JAMES”。但是，PostgreSQL 和 Oracle 都支持将值从一种数据类型隐式和显式转换为另一种数据类型。
Oracle 数据类型优先级
Oracle 使用数据类型优先级来确定隐式数据类型转换。Oracle 数据类型具有以下优先级：
- 日期时间和间隔数据类型
BINARY_DOUBLEBINARY_FLOATNUMBER- 字符数据类型
- 所有其他内置数据类型
Oracle 隐式和显式数据转换
Oracle 建议您指定显式转换，而不是依赖于隐式或自动转换，原因如下：
- 使用显式数据类型转换函数时，SQL 语句更易于理解。
- 隐式数据类型转换可能会对性能产生负面影响，尤其是在将列值的数据类型转换为常量的数据类型而不是相反时。
- 隐式转换取决于它发生的上下文，并且可能并非在每种情况下都以相同的方式工作。例如，从日期时间值到
VARCHAR2值的隐式转换可能会返回意外的年份，具体取决于参数NLS_DATE_FORMAT的值。 - 隐式转换算法可能会因软件版本和 Oracle 产品而异。显式转化的行为更易于预测。
- 如果索引表达式中发生隐式数据类型转换，则 Oracle 数据库可能不会使用该索引，因为它是为转换前数据类型定义的。这可能会对性能产生负面影响。
Oracle 隐式数据转换
Oracle 数据库会自动将值从一种数据类型转换为另一种数据类型，前提是这种转换有意义。
表 1 是 Oracle 隐式转换的矩阵。该表显示了所有可能的转换，而不考虑转换的方向或进行转换的上下文。
单元格中的“X”表示第一列和标题行中命名的数据类型的隐式转换。
表 1 Oracle 隐式类型转换矩阵
| 数据类型 | 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（脚注 1） | – | – | – | – | 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 | – | – |
JSON |
– | X | – | – | – | – | – | – | – | – | – | – | X | X | – |
脚注 1：不能直接将LONG转换为INTERVAL，但可以使用TO_CHAR(interval)将LONG转换为VARCHAR2，然后将VARCHAR2结果值转换为INTERVAL。
隐式数据类型转换规则
- 在
INSERT和UPDATE操作期间，Oracle 会将值转换为受影响列的数据类型。 - 在
SELECT FROM操作期间，Oracle 会将列中的数据转换为目标变量的类型。 - 操作数值时，Oracle 通常会调整精度和小数位数以允许最大容量。在这种情况下，由此类操作生成的数值数据类型可能与基础表中的数值数据类型不同。
- 将字符值与数值进行比较时，Oracle 会将字符数据转换为数值。
- 字符值或
NUMBER值与浮点数值之间的转换可能不精确，因为字符类型和NUMBER使用十进制精度来表示数值，而浮点数使用二进制精度。 - 将
CLOB值转换为字符数据类型（如VARCHAR2）或转换BLOB为RAW数据时，如果要转换的数据超出目标数据类型的空间限制，则数据库将返回错误。 - 在从时间戳值转换为
DATE值的过程中，时间戳值的小数秒部分将被截断。此行为与早期版本的 Oracle 数据库不同，当时时间戳值的小数秒部分已舍入。 - 从
BINARY_FLOAT到BINARY_DOUBLE的转换是精确的。 - 如果
BINARY_DOUBLE值使用的精度位数多于BINARY_FLOAT，则从BINARY_DOUBLE到BINARY_FLOAT的转换不精确。 - 将字符值与
DATE值进行比较时，Oracle 会将字符数据转换为DATE。 - 当您将 SQL 函数或运算符与它接受的数据类型不同的参数一起使用时，Oracle 会将该参数转换为接受的数据类型。
- 进行赋值时，Oracle 会将等号 （=） 右侧的值转换为左侧赋值目标的数据类型。
- 在串联操作期间，Oracle 会从非字符数据类型转换为
CHAR或NCHAR。 - 在字符和非字符数据类型的算术运算和比较期间，Oracle 会根据需要从任何字符数据类型转换为数字、日期或 rowid。在
CHAR/VARCHAR2和NCHAR/NVARCHAR2之间的算术运算中，Oracle 会转换为NUMBER。 - 大多数 SQL 字符函数都支持接受
CLOB作为参数，Oracle 在CLOB和字符类型之间执行隐式转换。因此，尚未适配CLOB的函数可以通过隐式转换接受CLOB。在这种情况下，Oracle 会在调用函数之前将CLOB转换为CHAR或VARCHAR2。如果CLOB大于 4000 字节，则 Oracle 仅将前 4000 个字节转换为CHAR。 - 当将
RAW或LONG RAW数据转换为字符数据或从字符数据转换时，二进制数据以十六进制形式表示，每四位RAW数据有一个十六进制字符。有关详细信息，请参阅“RAW 和 LONG RAW 数据类型”。 CHAR和VARCHAR2类型之间，以及NCHAR和NVARCHAR2类型之间的比较可能需要不同的字符集。在这种情况下，默认的转换方向是从数据库字符集到区域字符集。表 2 显示了不同字符类型之间的隐式转换方向。
表 2 不同字符类型的转换方向
| 源数据类型 | 到 CHAR | 到 VARCHAR2 | 到 NCHAR | 到 NVARCHAR2 |
|---|---|---|---|---|
从 CHAR |
– | VARCHAR2 |
NCHAR |
NVARCHAR2 |
从 VARCHAR2 |
VARCHAR2 |
– | NVARCHAR2 |
NVARCHAR2 |
从 NCHAR |
NCHAR |
NCHAR |
– | NVARCHAR2 |
从 NVARCHAR2 |
NVARCHAR2 |
NVARCHAR2 |
NVARCHAR2 |
– |
用户自定义类型（如集合）不能隐式转换，必须使用CAST … MULTISET显式转换。
Oracle 隐式数据转换示例
文本文字示例
文本文字“10”具有数据类型CHAR。如果 Oracle 将其隐式转换为NUMBER数据类型（如果它出现在数值表达式中，如以下语句所示）：
SELECT salary + '10'
FROM employees;
字符和数字值示例
当条件比较字符值和NUMBER值时，Oracle 会将字符值隐式转换为NUMBER值，而不是将NUMBER值转换为字符值。在以下语句中，Oracle 隐式将“200”转换为 200：
SELECT last_name
FROM employees
WHERE employee_id = '200';
日期示例
在以下语句中，Oracle 使用默认日期格式 ‘DD-MON-YY’ 隐式将 ‘24-JUN-06’ 转换为DATE值：
SELECT last_name
FROM employees
WHERE hire_date = '24-JUN-06';
Oracle 显式数据转换
您可以使用 SQL 转换函数显式指定数据类型转换。表 3 显示了将值从一种数据类型显式转换为另一种数据类型的 SQL 函数。
在 Oracle 可以执行隐式数据类型转换的情况下，不能指定LONG和LONG RAW值。例如，LONG和LONG RAW值不能出现在带有函数或运算符的表达式中。有关限制LONG和LONG RAW数据类型的信息，请参阅 LONG 数据类型。
表 3 显式类型转换
| 源数据类型 | 到 CHAR,VARCHAR2,NCHAR,NVARCHAR2 | 到 NUMBER | 到 Datetime/Interval | 到 RAW | 到 ROWID | 到 LONG,LONG RAW | 到 CLOB, NCLOB,BLOB | 到 BINARY_FLOAT | 到 BINARY_DOUBLE |
|---|---|---|---|---|---|---|---|---|---|
从 CHAR, VARCHAR2, NCHAR, NVARCHAR2 |
TO_CHAR (char.)TO_NCHAR (char.) |
TO_NUMBER |
TO_DATETO_TIMESTAMPTO_TIMESTAMP_TZTO_YMINTERVALTO_DSINTERVAL |
HEXTORAW |
CHARTOROWID |
-- |
TO_CLOBTO_NCLOB |
TO_BINARY_FLOAT |
TO_BINARY_DOUBLE |
从 NUMBER |
TO_CHAR (number)TO_NCHAR (number) |
-- |
TO_DATENUMTOYMINTERVALNUMTODSINTERVAL |
-- |
-- |
-- |
-- |
TO_BINARY_FLOAT |
TO_BINARY_DOUBLE |
从 Datetime/ Interval |
TO_CHAR (date)TO_NCHAR (datetime) |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
从 RAW |
RAWTOHEXRAWTONHEX |
-- |
-- |
-- |
-- |
-- |
TO_BLOB |
-- |
-- |
从 ROWID |
ROWIDTOCHAR |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
从 LONG / LONG RAW |
-- |
-- |
-- |
-- |
-- |
-- |
TO_LOB |
-- |
-- |
从 CLOB, NCLOB, BLOB |
TO_CHARTO_NCHAR |
-- |
-- |
-- |
-- |
-- |
TO_CLOBTO_NCLOB |
-- |
-- |
从 CLOB, NCLOB, BLOB |
TO_CHARTO_NCHAR |
-- |
-- |
-- |
-- |
-- |
TO_CLOBTO_NCLOB |
-- |
-- |
从 BINARY_FLOAT |
TO_CHAR (char.)TO_NCHAR (char.) |
TO_NUMBER |
-- |
-- |
-- |
-- |
-- |
TO_BINARY_FLOAT |
TO_BINARY_DOUBLE |
从 BINARY_DOUBLE |
TO_CHAR (char.)TO_NCHAR (char.) |
TO_NUMBER |
-- |
-- |
-- |
-- |
-- |
TO_BINARY_FLOAT |
TO_BINARY_DOUBLE |
另请参阅：
转换函数，了解所有显式转换函数的详细信息。
PostgreSQL 隐式和显式类型转换
在 psql 命令行中输入\dC可以列出 PostgreSQL 支持的类型转换，如下所示。
postgres=# \dC
类型转换列表
来源类型 | 目标类型 | 函数 | 隐含的?
-----------------------------+-----------------------------+--------------------+----------
"char" | character | bpchar | 在指派中
"char" | character varying | text | 在指派中
"char" | integer | int4 | 否
"char" | text | text | 是
abstime | date | date | 在指派中
abstime | integer | (binary coercible) | 否
abstime | time without time zone | time | 在指派中
abstime | timestamp with time zone | timestamptz | 是
abstime | timestamp without time zone | timestamp | 是
bigint | bit | bit | 否
bigint | double precision | float8 | 是
bigint | integer | int4 | 在指派中
bigint | money | money | 在指派中
bigint | numeric | numeric | 是
bigint | oid | oid | 是
bigint | real | float4 | 是
bigint | regclass | oid | 是
bigint | regconfig | oid | 是
bigint | regdictionary | oid | 是
bigint | regnamespace | oid | 是
bigint | regoper | oid | 是
bigint | regoperator | oid | 是
bigint | regproc | oid | 是
bigint | regprocedure | oid | 是
bigint | regrole | oid | 是
bigint | regtype | oid | 是
bigint | smallint | int2 | 在指派中
bit | bigint | int8 | 否
bit | bit | bit | 是
bit | bit varying | (binary coercible) | 是
bit | integer | int4 | 否
bit varying | bit | (binary coercible) | 是
bit varying | bit varying | varbit | 是
boolean | character | text | 在指派中
boolean | character varying | text | 在指派中
boolean | integer | int4 | 否
boolean | text | text | 在指派中
box | circle | circle | 否
box | lseg | lseg | 否
box | point | point | 否
box | polygon | polygon | 在指派中
character | "char" | char | 在指派中
character | character | bpchar | 是
character | character varying | text | 是
character | name | name | 是
character | text | text | 是
character | xml | xml | 否
character varying | "char" | char | 在指派中
character varying | character | (binary coercible) | 是
character varying | character varying | varchar | 是
character varying | name | name | 是
character varying | regclass | regclass | 是
character varying | text | (binary coercible) | 是
character varying | xml | xml | 否
cidr | character | text | 在指派中
cidr | character varying | text | 在指派中
cidr | inet | (binary coercible) | 是
cidr | text | text | 在指派中
circle | box | box | 否
circle | point | point | 否
circle | polygon | polygon | 否
date | timestamp with time zone | timestamptz | 是
date | timestamp without time zone | timestamp | 是
double precision | bigint | int8 | 在指派中
double precision | integer | int4 | 在指派中
double precision | numeric | numeric | 在指派中
double precision | real | float4 | 在指派中
double precision | smallint | int2 | 在指派中
inet | character | text | 在指派中
inet | character varying | text | 在指派中
inet | cidr | cidr | 在指派中
inet | text | text | 在指派中
integer | "char" | char | 否
integer | abstime | (binary coercible) | 否
integer | bigint | int8 | 是
integer | bit | bit | 否
integer | boolean | bool | 否
integer | double precision | float8 | 是
integer | money | money | 在指派中
integer | numeric | numeric | 是
integer | oid | (binary coercible) | 是
integer | real | float4 | 是
integer | regclass | (binary coercible) | 是
integer | regconfig | (binary coercible) | 是
integer | regdictionary | (binary coercible) | 是
integer | regnamespace | (binary coercible) | 是
integer | regoper | (binary coercible) | 是
integer | regoperator | (binary coercible) | 是
integer | regproc | (binary coercible) | 是
integer | regprocedure | (binary coercible) | 是
integer | regrole | (binary coercible) | 是
integer | regtype | (binary coercible) | 是
integer | reltime | (binary coercible) | 否
integer | smallint | int2 | 在指派中
interval | interval | interval | 是
interval | reltime | reltime | 在指派中
interval | time without time zone | time | 在指派中
json | jsonb | (binary coercible) | 在指派中
jsonb | json | (binary coercible) | 在指派中
lseg | point | point | 否
macaddr | macaddr8 | macaddr8 | 是
macaddr8 | macaddr | macaddr | 是
money | numeric | numeric | 在指派中
name | character | bpchar | 在指派中
name | character varying | varchar | 在指派中
name | text | text | 是
numeric | bigint | int8 | 在指派中
numeric | double precision | float8 | 是
numeric | integer | int4 | 在指派中
numeric | money | money | 在指派中
numeric | numeric | numeric | 是
numeric | real | float4 | 是
numeric | smallint | int2 | 在指派中
oid | bigint | int8 | 在指派中
oid | integer | (binary coercible) | 在指派中
oid | regclass | (binary coercible) | 是
oid | regconfig | (binary coercible) | 是
oid | regdictionary | (binary coercible) | 是
oid | regnamespace | (binary coercible) | 是
oid | regoper | (binary coercible) | 是
oid | regoperator | (binary coercible) | 是
oid | regproc | (binary coercible) | 是
oid | regprocedure | (binary coercible) | 是
oid | regrole | (binary coercible) | 是
oid | regtype | (binary coercible) | 是
path | point | point | 否
path | polygon | polygon | 在指派中
pg_dependencies | bytea | (binary coercible) | 是
pg_dependencies | text | (binary coercible) | 是
pg_ndistinct | bytea | (binary coercible) | 是
pg_ndistinct | text | (binary coercible) | 是
pg_node_tree | text | (binary coercible) | 是
point | box | box | 在指派中
polygon | box | box | 否
polygon | circle | circle | 否
polygon | path | path | 在指派中
polygon | point | point | 否
real | bigint | int8 | 在指派中
real | double precision | float8 | 是
real | integer | int4 | 在指派中
real | numeric | numeric | 在指派中
real | smallint | int2 | 在指派中
regclass | bigint | int8 | 在指派中
regclass | integer | (binary coercible) | 在指派中
regclass | oid | (binary coercible) | 是
regconfig | bigint | int8 | 在指派中
regconfig | integer | (binary coercible) | 在指派中
regconfig | oid | (binary coercible) | 是
regdictionary | bigint | int8 | 在指派中
regdictionary | integer | (binary coercible) | 在指派中
regdictionary | oid | (binary coercible) | 是
regnamespace | bigint | int8 | 在指派中
regnamespace | integer | (binary coercible) | 在指派中
regnamespace | oid | (binary coercible) | 是
regoper | bigint | int8 | 在指派中
regoper | integer | (binary coercible) | 在指派中
regoper | oid | (binary coercible) | 是
regoper | regoperator | (binary coercible) | 是
regoperator | bigint | int8 | 在指派中
regoperator | integer | (binary coercible) | 在指派中
regoperator | oid | (binary coercible) | 是
regoperator | regoper | (binary coercible) | 是
regproc | bigint | int8 | 在指派中
regproc | integer | (binary coercible) | 在指派中
regproc | oid | (binary coercible) | 是
regproc | regprocedure | (binary coercible) | 是
regprocedure | bigint | int8 | 在指派中
regprocedure | integer | (binary coercible) | 在指派中
regprocedure | oid | (binary coercible) | 是
regprocedure | regproc | (binary coercible) | 是
regrole | bigint | int8 | 在指派中
regrole | integer | (binary coercible) | 在指派中
regrole | oid | (binary coercible) | 是
regtype | bigint | int8 | 在指派中
regtype | integer | (binary coercible) | 在指派中
regtype | oid | (binary coercible) | 是
reltime | integer | (binary coercible) | 否
reltime | interval | interval | 是
smallint | bigint | int8 | 是
smallint | double precision | float8 | 是
smallint | integer | int4 | 是
smallint | numeric | numeric | 是
smallint | oid | int4 | 是
smallint | real | float4 | 是
smallint | regclass | int4 | 是
smallint | regconfig | int4 | 是
smallint | regdictionary | int4 | 是
smallint | regnamespace | int4 | 是
smallint | regoper | int4 | 是
smallint | regoperator | int4 | 是
smallint | regproc | int4 | 是
smallint | regprocedure | int4 | 是
smallint | regrole | int4 | 是
smallint | regtype | int4 | 是
text | "char" | char | 在指派中
text | character | (binary coercible) | 是
text | character varying | (binary coercible) | 是
text | name | name | 是
text | regclass | regclass | 是
text | xml | xml | 否
time with time zone | time with time zone | timetz | 是
time with time zone | time without time zone | time | 在指派中
time without time zone | interval | interval | 是
time without time zone | time with time zone | timetz | 是
time without time zone | time without time zone | time | 是
timestamp with time zone | abstime | abstime | 在指派中
timestamp with time zone | date | date | 在指派中
timestamp with time zone | time with time zone | timetz | 在指派中
timestamp with time zone | time without time zone | time | 在指派中
timestamp with time zone | timestamp with time zone | timestamptz | 是
timestamp with time zone | timestamp without time zone | timestamp | 在指派中
timestamp without time zone | abstime | abstime | 在指派中
timestamp without time zone | date | date | 在指派中
timestamp without time zone | time without time zone | time | 在指派中
timestamp without time zone | timestamp with time zone | timestamptz | 是
timestamp without time zone | timestamp without time zone | timestamp | 是
xml | character | (binary coercible) | 在指派中
xml | character varying | (binary coercible) | 在指派中
xml | text | (binary coercible) | 在指派中
(219 行记录)
使用 CREATE CAST 语法可以定义一个新的转换。一个转换说明如何在两个类型之间进行转换。