OCP-1Z0-051 第5题 to_char的数字的格式

最新推荐文章于 2023-05-04 09:36:45 发布

hello_holly

最新推荐文章于 2023-05-04 09:36:45 发布

阅读量7.6k

点赞数 1

分类专栏： OCP-1Z0-051 文章标签： OCP-1Z0-051 to_char 数字格式 V D 11g

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

版权

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

220 篇文章 6 订阅

订阅专栏

一、原题

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

答案：ADE

二、题目翻译
哪一个SQL语句可以把1890.55显示成$1,890.55 （选择三个正确的选项）

三、题目解析

B选项，不正确，V表示乘以10的n次方,这里 n 是跟在 V 后面的数字。
C选项，不正确，逗号与D不能一起使用，G与D一起使用，逗号与小数点一起使用。0表示补齐位数。

具体参照联机文档：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51075

摘录如下：

V
Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the V.

D
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.

四、实验

SQL> SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL;

TO_CHAR(1890.55,'$0G
--------------------
$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,'$99,999D99') FROM DUAL;
SELECT TO_CHAR(1890.55,'$99,999D99') FROM DUAL
*
ERROR at line 1:
ORA-01481: invalid number format model

SQL> SELECT TO_CHAR(1890.55,'$99G999D00') 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

--B选项，是想要小数点和千分位同时存在，可以用下面的方式实现
SQL> select to_char(1890.55,'9G999D999') from dual;

TO_CHAR(1890.55,'9G9
--------------------
1,890.550