Oracle 数据类型梳理

官方文档

Data Types【Oracle 12.1】

Data Types【Oracle 19】

数据类型梳理

CHAR

Oracle Data Types

CHAR 类型最多保存 2000 字节的内容。

VARCHAR

Oracle Data Types【Oracle 11.1】

Oracle 中的 VARCHAR 实际上就是 VARCHAR2 的同义词，但是为了避免未来 Oracle 对于 VARCHAR 的语义做一些调整，Oracle 建议用户使用 VARCHAR2 而非 VARCHAR。

VARCHAR2 最多存储 4000 字节的数据。能够实际存储的字符数量根据字符集的不同会有所不同。

NCHAR

NCHAR 最多保存 2000 字节的内容。

NVARCHAR2

NVARCHAR2 最多保存 4000 字节的内容。

CLOB

可以保存最多 8T 的字符数据。

NCLOB

可以保存最多 8T 的字符数据。

LONG

LONG 类型最大保存 2 GB 的数据。

LONG 和 CLOB 的区别

database - Oracle: LONG or CLOB? - Stack Overflow

database - Why is long an issue with Oracle? - Stack Overflow

Is it possible to read a CLOB from a remote Oracle database? - Stack Overflow

• 之前有人说使用 LONG 是因为 dblink 不支持 CLOB，但是从 Oracle 12.2 开始，dblink 已经支持了。所以从这一点来讲不需要 CLOB。
• LONG 操作起来也不方便。

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead. LONG columns are supported only for backward compatibility.

Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases.

——Native Datatypes

根据上边的这段，Oracle 官方也不建议再使用 LONG 类型。

LONG 的一些限制：

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

——Data Types

RAW

二进制数据，大小限制：

• 32767 bytes if MAX_STRING_SIZE = EXTENDED
• 2000 bytes if MAX_STRING_SIZE = STANDARD

LONG RAW

二进制数据，最大 2 GB。

Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns. See TO_LOB for more information.

——Data Types

一个表只能有一个 LONG 类型的列。

A table can contain only one LONG column.

——Data Types

BOOLEAN

Boolean Data Type

BIANRY_FLOAT

Data Types【在这个页面搜 BIANRY_FLOAT】

32 位浮点型数字。

BINARY_DOUBLE

Data Types【在这个页面搜 BINARY_DOUBLE】

NUMBER

Oracle Data Types【Oracle 11.1】

NUMBER - Data Types【Oracle 12.1】

Numeric Data Types

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.

存储大小：消耗 1~22 个字节

范围：$[1.0 \times 10^{-130}, 1.0 \times 10^{-126})$

1

NUMBER(p, s)

p 取值范围 1-38

s 取值范围 -84 到 127

Floating-Point Numbers

Floating-point numbers can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. An exponent may optionally be used following the number to increase the range, for example, 1.777 e-20. A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.

Binary floating-point numbers differ from NUMBER in the way the values are stored internally by Oracle Database. Values are stored using decimal precision for NUMBER. All literals that are within the range and precision supported by NUMBER are stored exactly as NUMBER. Literals are stored exactly because literals are expressed using decimal precision (the digits 0 through 9). Binary floating-point numbers are stored using binary precision (the digits 0 and 1). Such a storage scheme cannot represent all values using decimal precision exactly. Frequently, the error that occurs when converting a value from decimal to binary precision is undone when the value is converted back from binary to decimal precision. The literal 0.1 is such an example.

Oracle Database provides two numeric data types exclusively for floating-point numbers:

二进制浮点数和 NUMBER 不同之处在于 Oracle 底层对于数据的存储方式。NUMBER 底层使用十进制。所有在 NUMBER 的 precision 和 scale 范围内的数字常量都是被准确保存的。数字常量被准确保存是因为数字常量是通过十进制精度表达的。

Oracle Number型的深入理解 - 编程宝库

未指定 precision 和 scale 的情况

Specify a floating-point number using the following form:

1

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

不指定的 precision 和 scale 的情况下，该类型能够保存最大的数值范围。即 $[1.0 \times 10^{-130}, 1.0 \times 10^{-126})$。注意，最大范围并不是 precision 和 scale 分别能够指定的最大值（NUMBER(38, 127)），实际能存储的比这个更大。

参考资料：

database - What is the default Precision and Scale for a Number in Oracle? - Stack Overflow

oracle - What is the difference between precision and scale? - Stack Overflow

sql - Is “NUMBER” and “NUMBER(*,0)” the same in Oracle? - Stack Overflow

ORACLE 中NUMBER类型默认的精度和Scale问题 - 潇湘隐者 - 博客园【深入研究了 precision 问题】

NUMBER、NUMERIC 和 DECIMAL 的区别

The NUMERIC and DECIMAL data types can specify only fixed-point numbers. For those data types, the scale (s) defaults to 0.

——Data Types

NUMERIC 和 DECIMAL 和 NUMBER 并不是完全等价的。NUMERIC 和 DECIMAL 只能声明定点数，不能声明浮点数。直接声明 NUMERIC 或 DECIMAL 不加 precision 和 scale 相当于 NUMBER(38,0)。

NUMBER 存储大小计算

占据字节数：（有效数字位数 / 2）+ 1

可以通过 VSIZE 函数确定一个 NUMBER 类型占据的大小。

示例：

1
2
3

select n, vsize(n), dump(n)
from t42
order by n;

Oracle NUMBER(p) storage size? - Stack Overflow

How do I determine how much storage will be required for NUM… - Ask TOM

DECIMAL

DECIMAL

FLOAT

NUMBER 的一种子类型。

Data Types

A subtype of the NUMBER data type having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.

语法：``FLOAT [(p)]`

存储大小：消耗 1~22 个字节

INTEGER

INTEGER 等价于 INT 和 SMALLINT，相当于 NUMBER(38,0)。

BFILE

BIFLE 类型用来引用保存在数据库外部，但是在 Oracle 数据库服务器文件系统上的文件。

DATE

Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

——Data Types

存储大小：7 字节

TIMESTAMP

Datetime Data Types and Time Zone Support

没有时区信息，时间直接原样保存到字段中。

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_TIMESTAMP_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is 7 or 11 bytes, depending on the precision. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

——Data Types

存储大小：7~11 字节（随毫秒精度而变）

TIMESTAMP WITH TIMEZONE

Datetime Data Types and Time Zone Support

时区信息会和时间信息一起保存起来，不同字段的时区可能是不一样的，以保存时候写入进去的为准。

如果写入的时间没有提供时区，会使用 SESSION 的时区作为写入的时间的时区。

All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default date format for the TIMESTAMP WITH TIME ZONE data type is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. The size is fixed at 13 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

——Data Types

存储大小：13 字节。

TIMESTAMP WITH LOCAL TIMEZONE

Datetime Data Types and Time Zone Support

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle Database returns it in the users’ local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time).

There is no literal for TIMESTAMP WITH LOCAL TIME ZONE, but TIMESTAMP literals and TIMESTAMP WITH TIME ZONE literals can be inserted into a TIMESTAMP WITH LOCAL TIME ZONE column.

The default date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT initialization parameter.

TIMESTAMP WITH LOCAL TIME ZONE does not store time zone information internally, but you can see local time zone information in SQL output if the TZH:TZM or TZR TZD format elements are specified.

这个字段类型本身不保存时区，但是会把写入的时间根据时区转换为服务器时区时间之后保存。

写入的时间如果本身带有时区，则使用这个时区，如果写入的时间本身不带时区，会使用 SESSION 的时区。

这个类型相比 TIMESTAMP 的优点是，如果客户端时区和服务器时区不一样，会先做一个转换，保证保存到服务器的时间是准确的。

客户端从服务器查询这种类型的字段的时候，会把时间转换为客户端时区展示。假如服务器是 UTC 时区，客户端是东八区，那么数据库中保存的 2023-01-01 00:00:00，客户端查询出来就是 2023-01-01 00:08:00 +08:00。

存储大小：7~11 字节（随毫秒精度而变）

对 ANSI 类型的兼容

地理类型

SDO_GEORASTER

GeoRaster默认不启用的。

Oracle spatial(GeoRaster)在创建栅格表时遇到的坑_tiger-hcx的博客-CSDN博客

关于时间类型

Date Data Types

Datetime Datatypes and Time Zone Support

时间类型的格式（NLS_TIMESTAMP_FORMAT 等）

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

格式字符串的具体规则：Format Models

示例：

1
2
3
4
5

ALTER SESSION SET
    NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
    NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9'
    NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM'
    NLS_NUMERIC_CHARACTERS = '.,';

字符集问题

Difference between AL32UTF8 and UTF8 — oracle-tech

Oracle 类型和 JDBC 类型映射关系表

注意：

1. 在 JDBC 中，Oralce 的 DATE 类型和 TIMESTAMP 类型的 code 都是 93。

和 JDBC 的一些关联

在 Oracle 的 JDBC 驱动中，Oracle 中特有的数据类型在 oracle.jdbc.OracleTypes 这个类中。

常见问题

NUMERIC 问题

Oracle 中建表时某个列是 NUMBER 的话，读取出来的 precision 和 scale 分别是 0 和 -127。如果直接拿这个值映射到目标库中，那么建表时是会失败的。

所以需要把这个 precision 和 scale 转换成正确的值。

NUMBER 定义：

A decimal number with up to 38 significant digits in the range of -(10125) to +(10125).

NUMBER [ (p [, s]) ]

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes.

虽然 p 的取值为 1~38，但是实际上，存储类型为 NUMBER 的时候，有效位数甚至能达到 39，暂时不太清楚为什么能保存到 39，有篇文章有点线索（Number Datatype - Ask TOM），但并不是很明确。

Numeric Data Types

Data Types

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/tables-and-table-clusters.html#GUID-AB604FF0-D321-44CC-956E-62A7791EE2FA

Oracle—number数据类型 - 偶my耶 - 博客园

GreenPlum 中的 NUMERIC

其实就是 PostgreSQL 中的 NUMERIC：

PostgreSQL: Documentation: 10: 8.1. Numeric Types

Greenplum Database Data Types | Pivotal Greenplum Docs

SQL Server 中的 NUMERIC

decimal and numeric (Transact-SQL) - SQL Server | Microsoft Docs

最多支持 38 位精度，但是存储范围只有 -10^38 +1 到 10^38 - 1

Oracle 中的数据传输过来，有可能会长度溢出。

debezium 中是如何处理的？

Debezium Connector for Oracle :: Debezium Documentation

VARCHAR 和 VARCHAR2 的区别？

建议参考：sqldatatypes - What is the difference between varchar and varchar2 in Oracle? - Stack Overflow

参考资料

1. ORACLE基本数据类型总结 - 潇湘隐者 - 博客园