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
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值