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;
Answer: C
答案解析：
实验环境：
scott@TEST0924> create table price_list
2 (prod_id number(3) not null,
3 prod_price varchar2(10));
Table created.
scott@TEST0924> insert into price_list values(100,'$234.55');
1 row created.
scott@TEST0924> insert into price_list values(101,'$6,509.75');
1 row created.
scott@TEST0924> insert into price_list values(102,'$1,234');
1 row created.
scott@TEST0924> select * from price_list;
PROD_ID PROD_PRICE
---------- ----------
100 $234.55
101 $6,509.75
102 $1,234
A，prod_price为字符，不能*.25
scott@TEST0924> SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;
SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST
*
ERROR at line 1:
ORA-01722: invalid number
B，TO_NUMBER(prod_price)里面需要转换格式
scott@TEST0924> 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
*
ERROR at line 1:
ORA-01722: invalid number
C正确
scott@TEST0924> SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST;
TO_CHAR(TO_
-----------
$58.64
$1,627.44
$308.50
D，本来(TO_NUMBER(prod_price,'$99,999.99')* .25已经是数值型，就不用再TO_NUMBER转换一回了。
scott@TEST0924> 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
*
ERROR at line 1:
ORA-01722: invalid number