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

一、原题

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