Coin163

首页 > 90.Examine the structure and data in the PRICE_LIST table:

90.Examine the structure and data in the PRICE_LIST table:

相关标签: 1z0-051 structure

2020腾讯云10周年活动,优惠非常大!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1040

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
入口地址https://www.aliyun.com/minisite/goods

90.Examine the structure and data in the PRICE_LIST table:
name Null Type
------ --------- -------
PROD_ID NOT NULL NUMBER(3)
PROD_PRICE VARCHAR2(10)
PROD_ID PROD_PRICE
---------- ------------
100 $234.55
101 $6,509.75
102 $1,234

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?
A.SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
B.SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
C.SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
D.SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;

答案:C

SQL> create table price_list(prod_id number(3) not null,prod_price varchar2(10));
Table created

SQL> insert into price_list values(100,'$234.55');
1 row inserted

SQL> insert into price_list values(101,'$6,509.75');
1 row inserted

SQL> insert into price_list values(102,'$1,234');
1 row inserted

SQL> commit;
Commit complete

SQL> 
A:错误

SQL> SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST
ORA-01722: 无效数字
prod_price是varchar2类型,虽然他可以隐式转换为number类型,但是包含$,因此隐式无法直接转换

SQL> select '10'*0.25 from dual;
 '10'*0.25
----------
       2.5
B:错误

SQL> SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;
SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST
ORA-01722: 无效数字

这里虽然使用了to_number,但是包含$,to_number不知道应该怎样转换

C:正确

SQL> SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
TO_CHAR(TO_NUMBER(PROD_PRICE,'
------------------------------
     $58.64
  $1,627.44
    $308.50
D:错误
SQL> SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST
ORA-01722: 无效数字
这是因为最后的格式不正确,修改一下格式就可以成功了
SQL> select to_number(TO_NUMBER(prod_price,'$99,999.99')* .25,'9999.9999') from price_list;
TO_NUMBER(TO_NUMBER(PROD_PRICE
------------------------------
                       58.6375
                     1627.4375
                         308.5
SQL> select to_number(TO_NUMBER(prod_price,'$99,999.99')* .25,'9999.0000') from price_list;
TO_NUMBER(TO_NUMBER(PROD_PRICE
------------------------------
                       58.6375
                     1627.4375
                         308.5
SQL> select to_char(TO_NUMBER(prod_price,'$99,999.99')* .25,'9999.0000') from price_list;
TO_CHAR(TO_NUMBER(PROD_PRICE,'
------------------------------
   58.6375
 1627.4375
  308.5000
这里to_number 的格式0和9没有区别,都不会补0
SQL> select to_number('123.1','000.0') from dual;
TO_NUMBER('123.1','000.0')
--------------------------
                     123.1

SQL> select to_number('123.1','000.00') from dual;
TO_NUMBER('123.1','000.00')
---------------------------
                      123.1
SQL>  select to_number('123.1','0000.00') from dual;
select to_number('123.1','0000.00') from dual
ORA-01722: 无效数字

SQL> 
其实这里的fmt对于number来说只是转换的时候匹配原始需要转换的值的格式,但是转换后因为是数字,所以
显示的时候没有什么格式一说了,不同于to_char,fmt格式不同,显示是不同的


原文

90.Examine the structure and data in the PRICE_LIST table: name Null Type ------ --------- ------- PROD_ID NOT NULL NUMBER(3) PROD_PRICE VARCHAR2(10) PROD_ID PROD_PRICE ----------

------分隔线----------------------------
相关推荐