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

来源：互联网发布：反美颜软件ios 编辑：程序博客网时间：2024/05/18 00:19

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 createdSQL> insert into promotions values('post promotion #20-343',to_date('19-jun-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('post promotion #20-274',to_date('16-jun-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('TV promotion #12-530',to_date('13-APR-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('post promotion #17-157',to_date('29-JUN-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('TV promotion #12-481',to_date('05-JAN-00','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('newspaper promotion #19-4',to_date('16-AUG-98','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> insert into promotions values('everyday low price',to_date('01-JAN-99','DD-MON-RR'，'NLS_DATE_LANGUAGE = AMERICAN'));1 row insertedSQL> commit;Commit completeSQL>

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,1999post promotion #20-274                   Wednesday,June     16,1999TV promotion #12-530                     Tuesday  ,April    13,1999post promotion #17-157                   Tuesday  ,June     29,1999TV promotion #12-481                     Wednesday,January  05,2000newspaper promotion #19-4                Sunday   ,August   16,1998everyday low price                       Friday   ,January  01,19997 rows selectedSQL> --这里星期后面存在空格

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,1999post promotion #20-274                    Wednesday,June     16,1999TV promotion #12-530                      Tuesday  ,April    13,1999post promotion #17-157                    Tuesday  ,June     29,1999TV promotion #12-481                      Wednesday,January  05,2000newspaper promotion #19-4                 Sunday   ,August   16,1998everyday low price                        Friday   ,January  01,19997 rows selectedSQL> --这里星期后面存在空格

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, 1999post promotion #20-274               Wednesday,June16, 1999TV promotion #12-530                 Tuesday,April13, 1999post promotion #17-157               Tuesday,June29, 1999TV promotion #12-481                 Wednesday,January05, 2000newspaper promotion #19-4            Sunday,August16, 1998everyday low price                   Friday,January01, 19997 rows selectedSQL>

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, 1999post promotion #20-274              Wednesday,June 16, 1999TV promotion #12-530                Tuesday,April 13, 1999post promotion #17-157              Tuesday,June 29, 1999TV promotion #12-481                Wednesday,January 5, 2000newspaper promotion #19-4           Sunday,August 16, 1998everyday low price                  Friday,January 1, 19997 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                                   8SQL> 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/07SQL> SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUALORA-01861: 文字与格式字符串不匹配SQL> SELECT TO_CHAR(TO_DATE('02/07', 'fxmm/yy'), 'mm/yy') FROM DUAL;TO_CHAR(TO_DATE('02/07','FXMM/------------------------------02/07SQL> SELECT TO_CHAR(TO_DATE('02/07', 'mm/yy'), 'fxmmyy') from dual;TO_CHAR(TO_DATE('02/07','MM/YY------------------------------0207--最后一个fx没有起作用，因为第一个参数为date类型

0 0