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

一、原题

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