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

来源：互联网发布：程序员招聘信息编辑：程序博客网时间：2024/05/16 09:04

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 createdSQL> insert into price_list values(100,'$234.55');1 row insertedSQL> insert into price_list values(101,'$6,509.75');1 row insertedSQL> insert into price_list values(102,'$1,234');1 row insertedSQL> commit;Commit completeSQL>

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_LISTORA-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_LISTORA-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_LISTORA-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.5SQL> 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.5SQL> 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.1SQL> select to_number('123.1','000.00') from dual;TO_NUMBER('123.1','000.00')---------------------------                      123.1SQL>  select to_number('123.1','0000.00') from dual;select to_number('123.1','0000.00') from dualORA-01722: 无效数字SQL>

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

0 0