Oracle函数

一、数据类型函数

1、数学函数

1）绝对值

ABS(num)

2）向上取整

CEIL(2.1)	-- 3

3）向下取整

FLOOR(2.6)	-- 2

4）四舍五入

-- precision：保留几位小数
ROUND(value, precision)

ROUND(2.66, 1)	-- 2.7
ROUND(2.66, 0)	-- 3

5）截取

• 处理数值，不会四舍五入

-- decimals：截取的位数
TRUNC(value, decimals)

TRUNC(123.5678, 1)	-- 123.5
TRUNC(123.5678, 0)	-- 123

-- 截取的位数为负数时，从整数位开始，把数值直接变为0
TRUNC(123.5678, -1)	-- 120
TRUNC(123.5678, -2)	-- 100

6）幂运算

-- exponent：几次方
POWER(value, exponent)

POWER(3, 2)	-- 9

7）平方根

SQRT(9)	-- 3

8）取余

MOD(10, 3)	-- 1

9）判断正负数

-- 正数返回1；负数返回-1；0返回0
SIGN(NUM)

2、字符串函数

1）字符串长度

-- 字符长度
LENGTH('abc')	-- 3

-- 字节长度
LENGTHB('中国')	-- 4

2）字符串拼接

CONCAT('ab', 'cd')	-- abcd
'ab' || 'cd'	-- abcd

3）指定字符在字符串左边填充

LPAD('abc', 5, '+')	-- ++abc

4）指定字符在字符串右边填充

RPAD('abc', 5, '+')	-- abc++

5）转大小写

UPPER('abc')	-- ABC
LOWER('ABc')	-- abc

6）字符串第1位变大写，其它变小写

INITCAP('aBc')	-- Abc

7）去除字符串两端内容

-- 去除两端空白字符
TRIM(' abc ')	-- abc

-- 去除两端a
TRIM('a' from 'abc')	-- bc

8）去除字符串左侧内容

-- 去除左侧空白字符
LTRIM(' abc')	-- abc

-- 去除左侧a
LTRIM('aba', 'a')	-- ba
LTRIM('ba', 'a')	-- ba

9）去除字符串右侧内容

-- 去除右侧空白字符
RTRIM('abc ')	-- abc

-- 去除右侧a
RTRIM('aba', 'a')	-- ab
RTRIM('bc', 'a')	-- bc

10）截取字符串

-- start：从第几位开始，负数表示从尾部开始
-- length：截取几位，不写截取到末尾
SUBSTR(value, start, length)

SUBSTR('abcd', 1, 2)	-- ab
SUBSTR('abcd', 2, 2)	-- bc

SUBSTR('abcd', 2)	-- bcd

SUBSTR('abcd', -2, 2)	-- cd

11）获取字串在字符串的位置

• 不包含返回0

-- 开始位置：不写从1开始
-- 第几次出现：不写=1
INSTR(value, 子串, 开始位置, 第几次出现)	-- 等效于INSTR2

INSTR('abcdab', 'a')	-- 1
INSTR('abcd', 'e')		-- 0
INSTR('abcab','a', 1, 2)	-- 4

12）字符串反转

REVERSE('abc')	-- cba

13）替换

REPLACE(value, 需要替换的字符串, 替换成的字符串)

REPLACE('abbab', 'a', 'b')	-- bbbbb
REPLACE('abab', 'ab', 'b')	-- bb

REGEXP_REPLACE(value, 正则表达式, 替换成的字符串)

REGEXP_REPLACE('12b', '[0-9]+', 'b')	-- bb

3、日期时间函数

1）当前日期时间

-- 系统时间（服务器时间）
SYSDATE	-- 2024-06-02 15:51:04
SYSTIMESTAMP --	2024-06-02 15:51:04.320000 +08:00

-- 当前会话时间
CURRENT_DATE -- 2024-06-02 15:51:04

2）提取

EXTRACT(YEAR FROM 日期)
EXTRACT(MONTH FROM 日期)
EXTRACT(DAY FROM 日期)
EXTRACT(HOUR FROM 日期)
EXTRACT(MINUTE FROM 日期)
EXTRACT(SECOND FROM 日期)

3）运算

-- 增加/减少几个月
-- count：正数-加；负数-减
ADD_MONTHS(日期时间, count)

-- 两个日期直接相减，返回相差的天数
date1 - date2

4）格式化

-- 日期时间 -> 字符串
TO_CHAR(日期, format)

TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
TO_CHAR(SYSDATE, 'AM-YYYY-MM-DD HH24:MI:SS')	-- PM-2024-06-02 16:24:07

-- 字符串 -> 日期时间
TO_DATE(字符串, format)

TO_DATE('2019-12-05 17:26:02', 'YYYY-MM-DD HH24:MI:SS')

5）最早/最晚的日期

LEAST(date1, date2, ......)
GREATEST(date1, date2, ......)

6）本月最后一天的此刻

LAST_DAY(SYSDATE)	-- 2024-06-30 16:28:28

7）两个日期相差的月数

MONTHS_BETWEEN(date1, date2)

8）下一个周几的日期

-- 1-7：星期日 - 星期六
NEXT_DAY(日期, 星期几)

-- 下一个星期一的日期
NEXT_DAY(SYSDATE, 2)	-- 2024-06-03 16:33:57

9）四舍五入

ROUND(SYSDATE, 'MONTH')

10）TRUNC

-- 本周第一天
TRUNC(SYSDATE, 'IW')
-- 本周最后一天
TRUNC(SYSDATE, 'IW') + 6

-- 本月第一天
TRUNC(SYSDATE, 'MONTH')
-- 本月最后一天
TRUNC(LAST_DAY(SYSDATE))

-- 本季度第一天
TRUNC(SYSDATE, 'Q')

-- 本年第一天
TRUNC(SYSDATE, 'YEAR')

4、其它

1）转换

-- 1、数值转字符串
TO_CHAR(123.12, '000.00')

-- 长度不够的用0补齐
TO_CHAR(123.12, '0000.000')	-- 0123.120
-- 千分位、小数位
TO_CHAR(100100.12, '$000,000.00')	-- $100,100.12

-- 2、字符串转数值
/*
format：
	9：代表一个数字
	0：强迫0显示
	$：美元符号
	L：当地货币符号
	.：小数点
	,：千位分隔符号
*/
TO_NUMBER(value, format)

TO_NUMBER('12345')
TO_NUMBER('$123,456.12', '$999,999.99')	-- 123456.12

CAST(value AS 数据类型)

CAST('123' AS NUMBER)
CAST('123.56' AS NUMBER)	-- 123.56
CAST('123.56' AS NUMBER(4, 1))	-- 123.6
CAST('123.56' AS INTEGER)	-- 124

二、聚合函数

1、平均值

-- 会忽略NULL值

AVG(column)

SELECT AVG(AGE) FROM T_USER

2、最大值

MAX(column)

3、最小值

MIN(column)

4、求和

-- 会忽略NULL值

-- 1、单字段
SUM(colnum)

-- 2、多字段的值全部相加
SUM(colnum+column+...)

5、统计数量

-- 该字段不为null的数量
COUNT(column)

-- 统计表中所有字段的数据的最大个数
COUNT(*)
-- 统计表中数量时，统计id最合适，因为主键不会为null
COUNT(id)

三、其它

1、NVL

-- 如果expression的值为NULL则返回value，不为NULL则返回expression
NVL(expression, value)

SELECT NVL(NAME, '未知') FROM T_USER;

2、NVL2

-- 如果expression的值为NULL则返回y，不为NULL则返回X
NVL2(expression, x, y)

SELECT NVL2(NAME, NAME, '未知') FROM T_USER;

3、NULLIF

-- 如果x=y则返回NULL，否则返回x
NULLIF(x, y)

SELECT NULLIF(1, 0) FROM DUAL;

4、DECODE

-- 如果a=b则返回c，否则返回d
DECODE(a, b, c, d)