OCP-1Z0-051 补充题库第9题 to_char将数字转成字符的格式应用

最新推荐文章于 2021-01-20 14:21:21 发布

hello_holly

最新推荐文章于 2021-01-20 14:21:21 发布

阅读量3.2k

点赞数

分类专栏： OCP-1Z0-051 文章标签： OCP-1Z0-051 TO_CHAR V G D

本文链接：https://blog.csdn.net/holly2008/article/details/27967297

版权

OCP-1Z0-051 专栏收录该内容

220 篇文章

订阅专栏

本文主要介绍了Oracle数据库中TO_CHAR函数在转换数字为货币格式时的使用，详细解析了选项G（组分隔符）、D（小数点）和V（乘以10的幂）的作用，并通过实例展示了如何正确使用这些选项来格式化数字为美元样式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原题

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
E.SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;

答案: A,C,D

二、题目翻译

下面哪三个会把1890.55显示成$1,890.55?

三、测试

SQL> SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;

TO_CHAR(1890.55,'$99G9
----------------------
$1,890.55

SQL> SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;

TO_CHAR(1890.55,'$
------------------
$1,89055

SQL> SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

TO_CHAR(1890.55,'$99G9
----------------------
$1,890.55

SQL> SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;

TO_CHAR(1890.55,'$99G9
----------------------
$1,890.55

SQL> SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;
SELECT TO_CHAR(1890.55,'$9,999D99')
*
ERROR at line 1:
ORA-01481: invalid number format model

详细情况见联机文档:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51075

摘录如下:
D
99D99
Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).
Restriction: You can specify only one decimal character in a number format model.
返回指定位置的小数符号，符号由NLS_NUMERIC_CHARACTER决定，默认是点(.)。

G
9G999
Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.
Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
返回指定位置的组分隔符，(当前值由NLS_NUMERIC_CHARACTER参数决定)，可以指定多组分隔。

V
999V99
Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the V.
V用于做计算
返回一个值剩以10n(如果需要，则四舍五入),n是V后面9的个数。
例如:
TO_CHAR(6,'9V')=6*1=6;
TO_CHAR(6,'9V9')=6*10=60
TO_CHAR(6,'9V99')=6 * 100 =600