OCA/OCP Oracle 数据库12c考试指南读书笔记：第8章: Single-Row and Conversion Functions

描述和使用字符、数字和日期函数

函数时一个黑盒，包含3要素，入参，封装实现，返回一个值
函数分为一次处理一行和一次处理多行两种（aggregation 或group函数）。
函数可嵌套
大小写转换：lower, upper, initcap

SQL> select lower(sysdate), upper(sysdate), initcap('good') from dual;

LOWER(SYSDATE)     UPPER(SYSDATE)     INIT
------------------ ------------------ ----
15-sep-19          15-SEP-19          Good

字符串操作：concat，length，lpad，rpad，trim，instr，substr，replace

SQL> select concat('Today is: ', sysdate), length(sysdate) from dual;

CONCAT('TODAYIS:',SYSDATE)   LENGTH(SYSDATE)
---------------------------- ---------------
Today is: 15-SEP-19                        9
SQL> select lpad(sysdate, 16, '*') a, rpad(sysdate, 16, '*') b from dual;

A                        B
------------------------ ------------------------
*******15-SEP-19         15-SEP-19*******
SQL> select replace('1#2#', '#','->') from dual;

REPLAC
------
1->2->

数字函数：
round，trunc，mod

日期函数：ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, ROUND, and TRUNC
日期在数据库内部以数字形式存放，包含从世纪到秒的信息。
SYSDATE函数，返回的是服务器的时间
日期与日期的差值是数字类型，表示天数。
日期和数字之间的和或差为日期类型。

SQL> select months_between(sysdate, sysdate -31) from dual;

MONTHS_BETWEEN(SYSDATE,SYSDATE-31)
----------------------------------
                                 1
SQL> select add_months(sysdate, 2) from dual;

ADD_MONTH
---------
15-NOV-19

SQL> select add_months(to_date('31-DEC-2008', 'DD-MON-YYYY'), 2) from dual;

ADD_MONTH
---------
28-FEB-09
SQL> select next_day(to_date('30-SEP-2019', 'DD-MON-YYYY'), 'TUE') from dual;

NEXT_DAY(
---------
01-OCT-19
SQL> select last_day(to_date('30-SEP-2019', 'DD-MON-YYYY')) from dual;

LAST_DAY(
---------
30-SEP-19

round，执行时间为16日早8:41。简单来说，根据所处的日round到当月或下月，根据本月1日是周几round到上周几或下周几，因为一周的开始是周日。

SQL> select round(sysdate) day, round(sysdate, 'w') week, round(sysdate, 'month') month, round(sysdate, 'year') year from dual;

DAY       WEEK      MONTH     YEAR
--------- --------- --------- ---------
16-SEP-19 15-SEP-19 01-OCT-19 01-JAN-20
SQL> select round(to_date('17-APR-2009'), 'w') from dual;

ROUND(TO_
---------
15-APR-09


SQL> select round(to_date('19-APR-2009'), 'w') from dual;

ROUND(TO_
---------
22-APR-09

trunc, 执行时间为9月17日晚21:24，日月年都好理解。星期是这样的，如果这个月的1日是星期几，trunc就就是指定日期之前的星期几。例如在本例中，由于9月1日是星期日，因此trunc(‘w’)的结果就是9月15日，也是星期日。

SQL> select trunc(sysdate) day, trunc(sysdate, 'w') week, trunc(sysdate, 'month') month, trunc(sysdate, 'year') year from dual;

DAY       WEEK      MONTH     YEAR
--------- --------- --------- ---------
17-SEP-19 15-SEP-19 01-SEP-19 01-JAN-19

关于日期函数，可参见文章How to Find the Next Business Day and Add or Subtract N Working Days with SQL

不同类型的转换函数

TO_CHAR：数字或日期到字符串
TO_NUMBER: 字符串到数字
TO_DATE：字符串到日期
如果满足格式要求的话，这几种类型间会进行隐式转换，当然，最好还是显式转换。

SQL> select length(1234) from dual;

LENGTH(1234)
------------
           4

SQL> select length(sysdate) from dual;

LENGTH(SYSDATE)
---------------
              9
SQL> select mod('11', 2) from dual;

MOD('11',2)
-----------
          1
SQL> select TO_CHAR(TO_DATE('17-SEP-19','DD-MON-RR'),'Day') from dual;

TO_CHAR(TO_DATE('17-SEP-19','DD-MON-
------------------------------------
Tuesday

使用TO_CHAR、TO_NUMBER和TO_DATE 转换函数

NLS（国际语言支持）参数，会影响到输出的格式：

SQL> select * from nls_session_parameters;

PARAMETER                                VALUE
---------------------------------------- ----------------------------------------
NLS_LANGUAGE                             AMERICAN
NLS_TERRITORY                            AMERICA
NLS_CURRENCY                             $
NLS_ISO_CURRENCY                         AMERICA
NLS_NUMERIC_CHARACTERS                   .,
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                          DD-MON-RR
NLS_DATE_LANGUAGE                        AMERICAN
NLS_SORT                                 BINARY
NLS_TIME_FORMAT                          HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                     DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                       HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                  DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                        $
NLS_COMP                                 BINARY
NLS_LENGTH_SEMANTICS                     BYTE
NLS_NCHAR_CONV_EXCP                      FALSE

17 rows selected.

总之格式非常丰富，此处不再赘述。请参考文章：Working with Dates in PL/SQL和文章On Format, Negation, and Sliding

使用条件表达式

函数可以嵌套。
条件表达式遵循if-then-else的逻辑。
条件表达式相关的函数包括：
NVL：如果非空，返回原值，否则返回替代值

SQL> select nvl(substr('abc', 4), 'not find') from dual;

NVL(SUBS
--------
not find

SQL> select nvl(substr('abc', 2), 'not find') from dual;

NV
--
bc

NVL2：如果非空，返回A值，否则B值

SQL> select nvl2(substr('abc', 2), 'found', 'not find') from dual;

NVL2(
-----
found

SQL> select nvl2(substr('abc', 4), 'found', 'not find') from dual;

NVL2(SUB
--------
not find

NULLIF，两个检测值，如果相等，返回NULL，否则返回第一个检测值:

SQL> select nullif(10, 10) from dual;

NULLIF(10,10)
-------------


SQL> select nullif(10, 12) from dual;

NULLIF(10,12)
-------------
           10

COALESCE：返回第一个非空的参数

SQL> select coalesce(null, 'a', null, 'b') from dual;

C
-
a

SQL> select coalesce(null, null, null, 'b') from dual;

C
-
b

DECODE：返回第一个满足条件时指定的返回值：

SQL> select decode('123', '1234', 'OK') from dual;

D
-


SQL> select decode('1234', '1234', 'OK') from dual;

DE
--
OK

SQL> select decode('123', '1234', 'OK', '123', 'OKOK') from dual;

DECO
----
OKOK

CASE：