数字格式模型 | OceanBase 文档中心

数字格式模型

更新时间：2023-07-18 17:26:00

数字格式模型（Number Format Models）指定了存储在数据库中的定点数和浮点数的格式。当您需要将 SQL 语句中出现 NUMBER、BINARY_FLOAT 或 BINARY_ DOUBLE 值转换为 VARCHAR2 数据类型时，可以使用函数中的数字格式模型。

函数中的数字格式模型

数字格式模型可以在以下的函数中使用：

当表达式、条件、SQL 函数和 SQL 语句中出现 NUMBER、BINARY_FLOAT 或 BINARY_ DOUBLE 时，如果需要将它们的值转换为 VARCHAR2 数据类型，可以使用 TO_CHAR 函数的参数指定这些数值的格式。
当表达式、条件、SQL 函数和 SQL 语句中出现 CHAR 或 VARCHAR2 时，如果需要将它们的值转换为 NUMBER 数据类型，可以使用 TO_NUMBER 函数的参数（暂不支持 NLS_NUMERIC_CHARACTERS）指定这些数值的格式。
当表达式、条件、SQL 函数和 SQL 语句中出现 CHAR 或 VARCHAR2 时，如果需要将它们的值转换为 BINARY_FLOAT 或 BINARY_DOUBLE，可以使用 TO_BINARY_FLOAT 和 TO_BINARY_DOUBLE 函数的参数指定数值格式。

数字格式模型会将数值进行四舍五入，并取有效数字位数。如果某个值的小数位数，左边的有效位数比格式中指定的位数高，则用 # 代替该值。

如果 NUMBER 的正值非常大且无法以指定的格式表示，则使用无穷大符号（〜）替换该值。如果负 NUMBER 值非常小且无法用指定的格式表示，则使用负无穷大符号（-〜）替换该值。

数字格式模型的元素

数字格式元素

数字格式模型由一个或多个数字格式元素组成。负数返回值自动包含前导负号，正值会自动包含前导空格，除非格式模型包含了 MI、S 或 PR 等表示数字正负的格式元素。

下表列出了数字格式模型包含的元素以及具体的用法。

数字格式元素	示例	描述
, （逗号）	9,999	在指定位置返回逗号。可以在数字格式模型中指定多个逗号。限制如下：逗号元素不能出现在数字格式模型的开头。在数字格式模型中，逗号不能出现在十进制小数点或句号的右侧。
. (小数点)	99.99	返回小数点，即在指定位置的句点（.）。限制如下：在数字格式模型中只能指定一个小数点。
$	$9999	返回带前导美元符号的值。
0	0999 9990	返回前导的零。前导零通常表示为空格，进行格式化的值为 0 时除外，此时前导零表示为字符零。返回尾随的零。
9	9999	返回指定位数的结果，进行格式化的值为正数时，结果包含前导空格，否则结果包含前导负号。
B	B9999	当结果为零时，返回空格。
C	C999	在指定位置返回 ISO 货币符号(当前 `NLS_ISO_CURRENCY` 参数所代表的值)。
D	99D99	跟"."的作用相同，只能出现一次。所不同的是，该元素会使用参数 `NLS_NUMERIC_CHARACTER` 的默认值。
EEEE	9.9EEEE	返回使用科学计数法表示的值。
G	9G999	跟"."的作用相同，所不同的是，该元素会使用参数 `NLS_NUMERIC_CHARACTER` 的默认值。
L	L999	在指定位置返回本地货币符号，会使用参数 `NLS_CURRENCY` 当前所代表的值。
Mi	9999MI	返回带有尾随减号（-）的负值。返回带尾随空格的正值。限制如下： MI 格式元素只能出现在数字格式模型的最后一个位置。
PR	9999PR	返回尖括号 <> 中的负值。返回带前导和尾随空格的正值。限制如下： PR 格式元素只能出现在数字格式模型的最后一个位置。
RN(rn)	RN rn	以大写罗马数字形式返回值。以小写罗马数字形式返回值。限制如下：取值可以是介于 1 和 3999 之间的整数。
S	S9999 9999S	返回带前导减号（-）的负值。返回带前导加号（+）的正值。返回带有尾随减号（-）的负值。返回带尾随加号（+）的正值。限制如下： S 格式元素只能出现在数字格式模型的第一个或最后一个位置。
TM	TME TM9	默认值为 `TM9`，除非输出超过64 个字符，否则以固定符号返回数字。如果输出超过 64 个字符，将自动以科学记数法返回该数字。当数字长度超过 64 位时候，`TM9` 的输出等同于 `TME` 的输出。
U	U9999	在指定位置返回由`NLS_dual_currency` 参数代表的当前值确定的欧元（或其他）双货币符号。
V(v)	999V99	返回一个乘以 10^n^ 的值（如有必要，将其四舍五入），其中 n 是 V 后面的数字 9 的个数。
X(x)	XXXX xxxx	返回指定位数的十六进制值。如果指定的数字不是整数，则将其舍入为整数。限制如下：此元素只接受正值或 0。负值返回错误。只能在该元素前面加 0（返回前导零）或 FM。任何其他元素都会返回错误。如果使用 X 既不指定 0 也不指定 FM，则返回值始终有一个前导空格。
FM(fm)	FM999	去除前面的空格。

格式化元素转换示例

下表中的示例展示了对不同数值按照格式化元素的参数 fmt，通过执行 SQL 命令 SELECT TO_CHAR(number, 'fmt') FROM DUAL; 得到的转换结果。如果省略格式模型参数 fmt，会将数值转为足够长的 VARCHAR2 以保留其所有的有效数字。

数值	'fmt'	结果
0	99.99	'.00'
+0.1	99.99	'.10'
-0.2	99.99	'-.20'
0	90.99	'0.00'
+0.1	90.99	'0.10'
-0.2	90.99	'-0.20'
0	9999	'0'
1	9999	'1'
+123.456	999.999	'123.456'
-123.456	999.999	'-123.456'

示例

将数值 0 转换为带有小数点的值，小数点后有两位有效数字。

obclient> SELECT TO_CHAR(0, '99.99') FROM DUAL;
+--------------------+
| TO_CHAR(0,'99.99') |
+--------------------+
|    .00             |
+--------------------+
1 row in set

省略 fmt 参数，保留该值所有的有效数字。

obclient> SELECT TO_CHAR(123.456) FROM DUAL;
+------------------+
| TO_CHAR(123.456) |
+------------------+
| 123.456          |
+------------------+
1 row in set

当 TO_CHAR 结果超过 40 字节，或者传入参数为 BINARY_DOUBLE 或者 BINARY_FLOAT 类型时，结果将被转为科学计数法。

obclient> SELECT TO_CHAR(12355555555555555555555555555555555555555555555555) FROM DUAL;
+-------------------------------------------------------------+
| TO_CHAR(12355555555555555555555555555555555555555555555555) |
+-------------------------------------------------------------+
| 1.2355555555555555555555555555555556E+49                    |
+-------------------------------------------------------------+
1 row in set