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

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格式不同，显示是不同的