Oracle学习笔记（五）

• 函数的作用：方便数据的统计；处理查询结果。
• 函数的分类：数值函数、字符函数、日期函数、转换函数。
• 四舍五入函数：ROUND(n, [, m])
• 省略m：0（取整）；m > 0：小数点后m位；m < 0：小数点前m位。n表示要进行四舍五入的值，m表示保留小数点后几位或前几位。

-- dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。
SQL> SELECT ROUND(23.45), ROUND(23.45, 1), ROUND(23.45, -1) FROM dual;
ROUND(23.45) ROUND(23.45,1) ROUND(23.45,-1)
------------ -------------- ---------------
          23           23.5              20

• 取整函数：CEIL(n)：向上取整，FLOOR(n)：向下取整。

SQL> SELECT CEIL(23.45), FLOOR(23.45) FROM dual;
CEIL(23.45) FLOOR(23.45)
----------- ------------
         24           23

• 取绝对值函数：ABS(n)

SQL> SELECT ABS(23.45), ABS(-23), ABS(0) FROM dual;
ABS(23.45)   ABS(-23)     ABS(0)
---------- ---------- ----------
     23.45         23          0

• 取余数函数：MOD(m, n)：m为被除数，n为除数。若m或n为null值，则结果直接返回null值。

SQL> SELECT MOD(5, 2), MOD(5, null) FROM dual;
  MOD(5,2) MOD(5,NULL)
---------- -----------
         1

• 幂函数：POWER(m, n)：表示返回m的n次幂。

SQL> SELECT POWER(2, 3), POWER(null, 5) FROM dual;
POWER(2,3) POWER(NULL,5)
---------- -------------
         8

• 平方根函数：SQRT(n)

SQL> SELECT SQRT(16), SQRT(12) FROM dual;
  SQRT(16)   SQRT(12)
---------- ----------
         4 3.46410162

• 三角函数：SIN(n)、ASIN(n)、COS(n)、ACOS(n)、TAN(n)、ATAN(n)，其中n为弧度制形式

SQL> SELECT COS(0) FROM dual;
    COS(0)
----------
         1

• 大小写字母转换函数：UPPER(char)（将所有字母全部转化成大写字母）、LOWER(char)（将所有字母全部转化成小写字母）、INITCAP(char)（把首字母大写）、

SQL> SELECT UPPER('abc'), LOWER('ACD'), INITCAP('bas') FROM dual;
UPP LOW INI
--- --- ---
ABC acd Bas

• 获取子字符串函数：SUBSTR(char, [m, [, n]])，char表示源字符串，m表示截取子串的开始位置，n表示截取子串的位数。其中参数n可以省略，当n省略时表示从m的位置开始截取到源字符串末尾的子串。当m为负数时，表示从源字符串的尾部倒数第|m|个位置开始向后截取n位字符的子串。

SQL> SELECT SUBSTR('abcde', 2, 3), SUBSTR('abcde', 2), SUBSTR('abcde', -2, 2) FROM dual;
SUB SUBS SU
--- ---- --
bcd bcde de

• 获取字符串长度的函数：LENGTH(char)

SQL> SELECT LENGTH('abbsd') FROM dual;
LENGTH('ABBSD')
---------------
              5

• 字符串连接函数：CONCAT(char1, char2)，其与||操作符的作用相同。

SQL> SELECT CONCAT('ABCD', 'EFG') FROM dual;
CONCAT(
-------
ABCDEFG

SQL> SELECT 'ba' || 'cd' FROM dual;
'BA'
----
bacd

• 去除子串函数：TRIM(c2 FROM c1)：表示从字符串c1中去除所有为c2的字符（1个或多个）。LTRIM(c1 [, c2])：表示从字符串c1的头部开始去除连续相同的c2子串，若有中断则停止。RTRIM(c1, [, c2])：表示从字符串c1的尾部开始向前去除连续相同的c2子串，若有中断则停止。TRIM(c1)：表示去除字符串c1首尾的空格。LTRIM(c1)：表示去除字符串c1最左边的空格。RTRIM(c1)：表示去除字符串c1最右边的空格。

SQL> SELECT TRIM('d' FROM 'abcddddd') FROM dual;
TRI
---
abc

SQL> SELECT LTRIM('abcde', 'ab') FROM dual;
LTR
---
cde

SQL> SELECT RTRIM('ababcdcde', 'cd') FROM dual;
RTRIM('AB
---------
ababcdcde

SQL> SELECT RTRIM('ababcdcde', 'cde') FROM dual;
RTRI
----
abab

SQL> SELECT LTRIM('    ababcde ') FROM dual;
LTRIM('A
--------
ababcde

SQL> SELECT TRIM('    ababcde ') FROM dual;
TRIM('A
-------
ababcde

• 替换函数：REPLACE(char, s_string [, r_string])，char表示源字符串，s_string 表示源字符串中要替换的子串，若省略r_string则相当于直接删除子串s_string。

SQL> SELECT REPLACE('abced', 'a', 'AA') FROM dual;
REPLAC
------
AAbced

SQL> SELECT REPLACE('abced', 'a') FROM dual;
REPL
----
bced

• 获取系统日期：SYSDATE，默认格式为DD-MON-RR。

SQL> SELECT SYSDATE FROM dual;
SYSDATE
--------------
29-9月 -19

• ADD_MONTHS(date, i)：返回在指定日期上增加月份后的日期，其中i可以是任何整数；若i是小数，则截取整数部分；若i是负数，则相当于为原日期的月份值减去i。

SQL> SELECT ADD_MONTHS(SYSDATE, 3), ADD_MONTHS(SYSDATE, -3) FROM dual;
ADD_MONTHS(SYS ADD_MONTHS(SYS
-------------- --------------
29-12月-19     29-6月 -19

• NEXT_DAY(date, char)：若char的值为'星期一'，则返回date指定日期的下周一的具体日期。

SQL> SELECT NEXT_DAY(SYSDATE, '星期一') FROM dual;
NEXT_DAY(SYSDA
--------------
30-9月 -19

• LAST_DAY(date)：返回指定日期所在月份的最后一天。

SQL> SELECT LAST_DAY(SYSDATE) FROM dual;
LAST_DAY(SYSDA
--------------
30-9月 -19

• MONTHS_BETWEEN(date1, date2)：表示2个日期之间相隔的月份。

SQL> SELECT MONTHS_BETWEEN('20-5月-19', '10-1月-19') FROM dual;
MONTHS_BETWEEN('20-5月-19','10-1月-19')
---------------------------------------
                             4.32258065

• EXTRACT(date FROM datetime)：提取datetime日期中某一部分值，比如“年”。其中datetime可以是时间戳类型timestamp。

SQL> SELECT EXTRACT(year FROM SYSDATE) FROM dual;
EXTRACT(YEARFROMSYSDATE)
------------------------
                    2019

SQL> SELECT EXTRACT(month FROM SYSDATE) FROM dual;
EXTRACT(MONTHFROMSYSDATE)
-------------------------
                        9

SQL> SELECT EXTRACT(day FROM SYSDATE) FROM dual;
EXTRACT(DAYFROMSYSDATE)
-----------------------
                     29

SQL> SELECT EXTRACT(hour FROM timestamp '2019-9-29 17:25:13') FROM dual;
EXTRACT(HOURFROMTIMESTAMP'2019-9-2917:25:13')
---------------------------------------------
                                           17

• 日期转换成字符的函数：TO_CHAR(date [, fmt [, params]])。参数说明：date表示将要转换的日期，fmt表示转换的格式，params表示日期的语言。日期默认格式：DD-MON-RR（天月年）。常用格式如下：

YY、YYYY、YEAR（年）
MM、MONTH（月）
DD、DAY（天）
HH24、HH12（小时）
MI、SS（分钟、秒）

SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
TO_CHAR(SYSDATE,'YY
-------------------
2019-09-29 13:06:07

• 字符转换成日期的函数：TO_DATE(char [, fmt [, params]])，params用于指定日期的语言，此参数一般不适用。注意：to_date()函数按照系统默认格式显示日期。

SQL> SELECT TO_DATE('2019-09-29','YYYY-MM-DD') FROM dual;
TO_DATE('2019-
--------------
29-9月 -19

• 数字转换成字符的函数：TO_CHAR(number [, fmt])。参数说明：

9：显示数字并忽略前面的0
0：显示数字，位数不足，用0补齐
.或D：显示小数点
, 或G：显示千位符
$：美元符号
S：加正负号（前后都可以，但不能同时加上）

-- 格式占位符缺失，将用'#'字符显示结果
SQL> SELECT TO_CHAR(123456.98, '$99,999.999') FROM dual;
TO_CHAR(1234
------------
############

SQL> SELECT TO_CHAR(123456.98, '$999,999.99') FROM dual;
TO_CHAR(1234
------------
 $123,456.98

-- 四舍五入
SQL> SELECT TO_CHAR(123456.98, '$999,999') FROM dual;
TO_CHAR(1
---------
 $123,457

SQL> SELECT TO_CHAR(123456.98, 'S999,999.99') FROM dual;
TO_CHAR(123
-----------
+123,456.98

SQL> SELECT TO_CHAR(-123456.98, '999,999.99S') FROM dual;
TO_CHAR(-12
-----------
123,456.98-

• 将字符转换成数字的函数：TO_NUMBER(char [, fmt])。参数fmt是转换的格式，可以省略。

SQL> SELECT TO_NUMBER('$1,000', '$9999') FROM dual;
TO_NUMBER('$1,000','$9999')
---------------------------
                       1000