92.View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTI

最新推荐文章于 2016-03-23 11:25:33 发布

今年我18

最新推荐文章于 2016-03-23 11:25:33 发布

阅读量1.8k

点赞数

分类专栏： 1z0-051 文章标签： 1z0-051

本文链接：https://blog.csdn.net/dwj19830118/article/details/50387254

版权

1z0-051 专栏收录该内容

175 篇文章 1 订阅

订阅专栏

92.View the Exhibit and examine the data in the PROMO_NAME and PROMO_END_DATE columns of the PROMOTIONS table, and the required output format.

A.SELECT promo_name,
TO_CHAR(promo_end_date,'Day') ', 'TO_CHAR(promo_end_date,'Month') ' 'TO_CHAR(promo_end_date,'DD, YYYY') AS last_day
FROM promotions;
B.SELECT promo_name,
TO_CHAR (promo_end_date,'fxDay') ', ' TO_CHAR(promo_end_date,'fxMonth') ' 'TO_CHAR(promo_end_date,'fxDD, YYYY') AS last_day
FROM promotions;
C.SELECT promo_name,
TRIM(TO_CHAR(promo_end_date,'Day')) ', ' TRIM(TO_CHAR(promo_end_date,'Month')) ' 'TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
FROM promotions;
D.SELECTpromo_name,TO_CHAR(promo_end_date,'fmDay')','TO_CHAR(promo_end_date,'fmMonth') ' 'TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
FROM promotions;
答案:CD
这道题的默认日期格式为DD-MON-RR，并且NLS_DATE_LANGUAGE为AMERICAN，
字段之间连接使用的是空格而不是||,这个参数我查找了v$parameter没有找到，可能他不是一个参数，

是一个函数什么的，我没找到他的原理，如果有知道的麻烦告下，那么这里我们将连接字符串替换为||

SQL> create table promotions(promo_name varchar2(100),promo_end_date date);
Table created
SQL> insert into promotions values('post promotion #20-343',to_date('19-jun-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('post promotion #20-274',to_date('16-jun-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('TV promotion #12-530',to_date('13-APR-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('post promotion #17-157',to_date('29-JUN-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('TV promotion #12-481',to_date('05-JAN-00','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('newspaper promotion #19-4',to_date('16-AUG-98','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> insert into promotions values('everyday low price',to_date('01-JAN-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));
1 row inserted

SQL> commit;
Commit complete

SQL>

A:错误

SQL> SELECT promo_name,
  2                  TO_CHAR(promo_end_date,'Day')||','||TO_CHAR(promo_end_date,'Month')||''||TO_CHAR(promo_end_date,'DD,YYYY') AS last_day
  3            FROM promotions;
PROMO_NAME                               LAST_DAY
---------------------------------------- ----------------------------------
post promotion #20-343                   Saturday ,June     19,1999
post promotion #20-274                   Wednesday,June     16,1999
TV promotion #12-530                     Tuesday  ,April    13,1999
post promotion #17-157                   Tuesday  ,June     29,1999
TV promotion #12-481                     Wednesday,January  05,2000
newspaper promotion #19-4                Sunday   ,August   16,1998
everyday low price                       Friday   ,January  01,1999
7 rows selected

SQL> 
--这里星期后面存在空格

B:错误

SQL> SELECT promo_name,
  2               TO_CHAR (promo_end_date,'fxDay')||','||TO_CHAR(promo_end_date,'fxMonth')||''||TO_CHAR(promo_end_date,'fxDD,YYYY') AS last_day
  3        FROM promotions;
PROMO_NAME                                LAST_DAY
----------------------------------------- ----------------------------------
post promotion #20-343                    Saturday ,June     19,1999
post promotion #20-274                    Wednesday,June     16,1999
TV promotion #12-530                      Tuesday  ,April    13,1999
post promotion #17-157                    Tuesday  ,June     29,1999
TV promotion #12-481                      Wednesday,January  05,2000
newspaper promotion #19-4                 Sunday   ,August   16,1998
everyday low price                        Friday   ,January  01,1999
7 rows selected

SQL> 
--这里星期后面存在空格

C:正确

SQL> SELECT promo_name,
  2               TRIM(TO_CHAR(promo_end_date,'Day'))||','||TRIM(TO_CHAR(promo_end_date,'Month'))||''||TRIM(TO_CHAR(promo_end_date,'DD, YYYY')) AS last_day
  3        FROM promotions;
PROMO_NAME                           LAST_DAY
------------------------------------ -----------------------------------
post promotion #20-343               Saturday,June19, 1999
post promotion #20-274               Wednesday,June16, 1999
TV promotion #12-530                 Tuesday,April13, 1999
post promotion #17-157               Tuesday,June29, 1999
TV promotion #12-481                 Wednesday,January05, 2000
newspaper promotion #19-4            Sunday,August16, 1998
everyday low price                   Friday,January01, 1999
7 rows selected

SQL>

D:正确

SQL> SELECT promo_name,TO_CHAR(promo_end_date,'fmDay')||','||TO_CHAR(promo_end_date,'fmMonth')||' '||TO_CHAR(promo_end_date,'fmDD, YYYY') AS last_day
  2        FROM promotions;
PROMO_NAME                          LAST_DAY
----------------------------------- ------------------------------------
post promotion #20-343              Saturday,June 19, 1999
post promotion #20-274              Wednesday,June 16, 1999
TV promotion #12-530                Tuesday,April 13, 1999
post promotion #17-157              Tuesday,June 29, 1999
TV promotion #12-481                Wednesday,January 5, 2000
newspaper promotion #19-4           Sunday,August 16, 1998
everyday low price                  Friday,January 1, 1999
7 rows selected
--这道题其实考的是格式的问题

SQL> select TO_CHAR(sysdate,'Day')  day
  2         ,length(TO_CHAR(sysdate,'Day'))  len_day
  3         ,length(trim(TO_CHAR(sysdate,'Day'))) tr_len_day
  4         ,length(ltrim(TO_CHAR(sysdate,'Day'))) ltr_len_day
  5         ,length(rtrim(TO_CHAR(sysdate,'Day'))) rtr_len_day
  6  from dual;
DAY              LEN_DAY TR_LEN_DAY LTR_LEN_DAY RTR_LEN_DAY
------------- ---------- ---------- ----------- -----------
Saturday               9          8           9           8

说明最后一位多一个空格，为什么会多一个空格哪？这是因为to_char实际转换的结果为char类型，
Day是星期，那英文星期中最长的一个月就是Wednesday，长度是9，所以这里他都按照9来处理了，
由于是char所以，不够九的补空格了，因此可以通过trim或者fmDay，fmday使用了fm修饰符，
fm应该是format格式化的缩写，他的作用就是去掉多余的空格和0，比如下面的两个查询

SQL> select TO_CHAR(sysdate,'Day')  day
  2         ,length(TO_CHAR(sysdate,'fmDay')) from dual;
DAY           LENGTH(TO_CHAR(SYSDATE,'FMDAY'
------------- ------------------------------
Saturday                                   8

SQL> 
SQL> select to_char(add_months(sysdate,1),'mm') mm
  2         ,to_char(add_months(sysdate,1),'fmmm') fmmm
  3  from dual;
MM FMMM
-- ----
01 1

那么B选项的fx是干啥的哪？

fx官方解释为 Requires exact matching between the character data and the format model.

也就是说字符串和格式需要精确匹配

SQL> SELECT TO_CHAR(TO_DATE('0207', 'mm/yy'), 'mm/yy') FROM DUAL;
TO_CHAR(TO_DATE('0207','MM/YY'
------------------------------
02/07

SQL> SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL
ORA-01861: 文字与格式字符串不匹配

SQL> SELECT TO_CHAR(TO_DATE('02/07', 'fxmm/yy'), 'mm/yy') FROM DUAL;
TO_CHAR(TO_DATE('02/07','FXMM/
------------------------------
02/07

SQL> SELECT TO_CHAR(TO_DATE('02/07', 'mm/yy'), 'fxmmyy') from dual;
TO_CHAR(TO_DATE('02/07','MM/YY
------------------------------
0207
--最后一个fx没有起作用，因为第一个参数为date类型