OCP 1Z0 051 90

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; 

SQL> CREATE TABLE PRICE_LIST
  2  (
  3  PROD_ID NUMBER(3) NOT NULL,
  4  PROD_PRICE VARCHAR2(10)
  5  );
Table created

SQL> INSERT INTO PRICE_LIST(PROD_ID,PROD_PRICE)
  2  SELECT 100,'$234.55' FROM dual UNION ALL
  3  SELECT 101,'$6,509.75' FROM dual UNION ALL
  4  SELECT 102,'$1,234' FROM dual;
3 rows inserted

转为数字用to_number
格式化用to_char
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: 无效数字

b 转换格式不对

SQL> select TO_NUMBER(prod_price) from PRICE_LIST;
select TO_NUMBER(prod_price) from PRICE_LIST

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
3 rows selected

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: 无效数字

Answer: C