函数是「送进去一个值、还回来一个值」。Oracle 内置几百个，常用的就几十个。
按教材分五类：字符、数字、日期时间、转换、聚合。
这一篇的重点不是背函数名，是记住两条：① 聚合函数统一跳过 NULL；
② 同一个名字在不同引擎上规则可能不同（round 就是）。
3.4 节讲 COLUMN FORMAT 时见过数字格式串，
这一篇的 to_char 用的是同一套——
区别只在位置：一个是显示时排，一个是取出来就已经是字符串。
聚合函数在这里先打个照面，第 9 章讲「分组与聚合」时会正式展开，包括 rollup、
cube 这些高级分组。
一、字符类：切、找、补
最常用的四个：substr 切、instr 找、length 数、|| 拼。
再加 lpad／rpad 补位、upper／lower 换大小写、replace 替换。
substr(s, 1, 3) 从第 1 个字符取 3 个。Oracle 的下标从 1 开始，不是 0。
substr(s, 0, 3) 和 substr(s, 1, 3) 结果一样，
这是 Oracle 特意兼容的，别指望别的库也这样。
instr 找不到时返回 0，不是 -1，也不是 NULL。
所以判「包不包含」要写 instr(x, 'A') > 0，写成 = 1 只能匹配「在第一位」。
这个 0 是 Oracle 的历史包袱（早期版本就这样），改不了。
lpad(7, 3, '0') 得 007，是做编号最常用的手法。
第一个参数得是字符串，传数字 Oracle 会自动转，但带上引号更明确。
二、数字类：round 与 trunc 的分工
round 会进位，trunc 直接砍掉。
这是它俩唯一的区别，也是全部的意义所在。
算工资、算税额用 round（该进的位要进）；
算「满几年」「满几件」「用了几个整月」用 trunc（不满一年的不算）。
Oracle 的 round 是「四舍五入、远离零」：2.5→3、-2.5→-3。
Python 内置的 round 是「银行家舍入」：round(2.5) 给 2、round(3.5) 给 4。
同一个词，两套规则。
跨语言算钱的时候，这类差异必须显式处理，不能指望它一致。
mod(a, b) 的符号跟着 a 走：mod(-7, 3) 得 -1。
这跟某些语言「结果永远非负」的约定不同，做分片取模时要留意。
三、日期类：日期其实就是一个数
Oracle 的日期是「从公元前 4712 年 1 月 1 日起的第几天」，带小数表示时分秒。
所以日期能加减、能比较大小——它本质是个数。
add_months 会自动处理月末：1 月 31 日加一个月，
得 2 月 28（或 29）日，不会给你 3 月 3 日。
这个行为是 Oracle 特意做的，比自己算天数靠谱得多。
months_between 返回带小数的月数，除以 12 就是年。
但它是按「日历月」算的，不是按 30 天——跨月边界时结果会跳一下。
算工龄够用，算精确天数不能用它。
sysdate 是唯一一个不带括号就能用的「函数」——严格说它是关键字，不是函数。
在 SQLite 里必须写 sysdate()，不带括号会被当成列名直接报错。
这个区别在你写跨库脚本时会立刻显出来。
to_date('2026-10-30','YYYY-MM-DD') 一定要写明格式。
where hiredate = '1982-01-23' 也能跑，是因为 Oracle 拿会话的日期格式去猜。
换一个客户端、换一个会话参数，同一条 SQL 就可能报错或查错——
隐式转换是最阴的坑，因为它平时都对。
四、转换类：同一套格式串的三种用法
to_char 数字转文本、to_date 文本转日期、to_number 文本转数字。
三个函数共用一套格式串，就是 3.4 节那套 999,990.00。
to_char 之后结果变文本，排序规则也跟着变。
to_char(sal) 排出来是「1000、800、900」这种（按字符比），不是数值顺序。
要在文本上排数值，要么补零对齐，要么干脆别转。
FM 前缀去掉前导空格。
Oracle 的数字格式默认按位补齐，短数字前面会留一串空格用来右对齐；
FM999990 就把这些空格去掉。导数据时几乎一定要加。
五、聚合类：统一跳过 NULL
count、sum、avg、max、min，就这五个。
它们有一条统一规则：跳过 NULL。
count(*) 数行，count(列) 只数非空。
差别全在 NULL 上——14 行里有 10 个空，两个数就差 10。
avg(sal) 的分母是非空个数，不是总行数。
所以「给空值补 0 再平均」和「直接平均」是两个完全不同的口径。
报表上必须写清楚用的是哪个，否则同一个部门能算出两个数。
空表上 count(*) 给 0，其他四个给 NULL——不是 0，是 NULL。
所以 sum() 的结果直接参与运算，整个表达式会变成 NULL。
要兜底就写 nvl(sum(sal), 0)。
六、动手 Demo：五类函数各跑一组，重点看两套引擎的差异
这一段按五类各跑几条。
真正值得看的是两处：round 在负数上的行为，以及 sysdate 在两套引擎里长得不一样。
聚合那条配了一张空表——bonus 是空的，正好用来演「空表上聚合返回什么」。
import sqlite3, datetime
c = sqlite3.connect(":memory:")
c.create_function("round2", 2,
lambda x, n: (int(x * 10 ** n + (0.5 if x >= 0 else -0.5))) / 10 ** n)
# 一、字符类
print(c.execute("select substr('ALLEN',1,3), instr('ALLEN','A'), "
"length('ALLEN'), upper('allen')").fetchall())
print(c.execute("select lpad('7',3,'0'), rpad('AB',5,'*'), "
"replace('2026-10-30','-','/')").fetchall())
# 二、数字类：Oracle 的 round 是「四舍五入、远离零」
print(c.execute("select round(2.5), round(-2.5)").fetchall())
print(round(2.5), round(-2.5)) # Python 内置是银行家舍入
# 三、日期类：SQLite 没有 sysdate，用自己的函数凑
print(c.execute("select date('now'), time('now')").fetchall())
print(c.execute("select strftime('%Y-%m-%d', date('now','+3 month'))").fetchall())
# 四、转换类
print(c.execute("select cast('2975' as integer), printf('%,d', 2975)").fetchall())
# 五、聚合类：count(*) 数行，count(列) 只数非空
c.execute("create table emp (ename text, comm real, sal real)")
c.executemany("insert into emp values (?,?,?)",
[('A', 300, 1600), ('B', None, 2975), ('C', None, 800)])
print(c.execute("select count(*), count(comm), sum(sal), avg(sal) from emp").fetchall())
print(c.execute("select sum(sal), avg(sal) from emp where 1 = 0").fetchall())
实跑输出：
--------------------------------------------------------------------
第十八课：Oracle 常用系统函数——五类，一类一个真问题
--------------------------------------------------------------------
**函数是「送进去一个值、还回来一个值」。**
**Oracle 内置了几百个，常用的就那么几十个。**
**这一节按教材分五类：字符、数字、日期时间、转换、聚合。**
**每类我只挑几个真会用到的，重点是「它们在第几层执行」。**
一、字符类函数
--------------------------------------------------------------------
SQL> select ename, length(ename) as 长度, substr(ename, 1, 3) as 前三, instr(ename, 'A') as A的位置 from emp where deptno = 30 order by ename
ENAME 长度 前三 A的位置
------------------------
ALLEN 5 ALL 1
BLAKE 5 BLA 3
JAMES 5 JAM 2
MARTIN 6 MAR 2
TURNER 6 TUR 0
WARD 4 WAR 2
已选择 6 行
**`substr(ename, 1, 3)` 从第 1 个字符开始取 3 个。**
**注意 Oracle 的下标从 1 开始**（不是 0），`substr(ename, 0, 3)` 和 `substr(ename, 1, 3)` 一样。
**`instr` 找不到时返回 0，不是 -1，也不是 NULL。**
**所以判「包不包含」要写 `instr(x, 'A') > 0`，不能写 `= 1`。**
SQL> select lpad('7', 3, '0') as 补零, rpad('AB', 5, '*') as 右补, initcap('hello world') as 首字母大写, replace('2026-10-30', '-', '/') as 换分隔符 from dual
补零 右补 首字母大写 换分隔符
---------------------------------
007 AB*** Hello World 2026/10/30
已选择 1 行
**`lpad` 补零是做编号最常用的手法**（`LPAD(7, 3, '0')` 得 `007`）。
**注意它的第一个参数得是字符串**，传数字 Oracle 会自动转，但带上引号更明确。**
**`initcap` 只把每个单词首字母大写**，`'hello world'` 变成 `'Hello World'`。
**它不认识「不该大写的词」**，`initcap('ORACLE 19c')` 会给你 `'Oracle 19C'`。**
二、数字类函数
--------------------------------------------------------------------
SQL> select round(2975.567, 2) as 四舍五入, trunc(2975.567, 2) as 截断, mod(2975, 100) as 余数, abs(-2975) as 绝对值 from dual
四舍五入 截断 余数 绝对值
----------------------------
2975.57 2975.56 75 2975
已选择 1 行
**`round` 会进位，`trunc` 直接砍掉**——这是它俩唯一的区别，也是最常用的区别。
**算工资、算税额用 `round`；算「满几年」「满几件」用 `trunc`。**
SQL> select round(2.5) as 正数, round(-2.5) as 负数, round(3.5) as 三点五, round(4.5) as 四点五 from dual
正数 负数 三点五 四点五
-----------------------
3 -3 4 5
已选择 1 行
**Oracle 的 `round` 是「四舍五入、远离零」：`2.5→3`、`-2.5→-3`。**
**而 Python 内置的 `round` 是「银行家舍入」：`round(2.5)` 给 `2`、`round(3.5)` 给 `4`。**
**同一个词，两种规则**——跨语言算钱的时候，这类差异必须显式处理。
**`mod(a, b)` 的符号跟着 `a` 走**（`mod(-7, 3)` 得 `-1`），
**这跟某些语言「结果永远非负」的约定不同。**分页取模时要注意。
三、日期和时间类函数
--------------------------------------------------------------------
[Oracle 原码 · 本沙箱不执行]
| select sysdate from dual;
| select to_char(sysdate, 'YYYY-MM-DD') from dual;
Oracle 里 sysdate 不带括号。
SQL> select to_char(sysdate(), 'YYYY-MM-DD') as 日期, to_char(sysdate(), 'HH24:MI:SS') as 时刻, to_char(sysdate(), 'YYYY-MM-DD HH24:MI:SS') as 完整时间 from dual
日期 时刻 完整时间
---------------------------------------
2026-10-30 13:51:53 2026-10-30 13:51:53
已选择 1 行
**注意 `sysdate` 本身就是一个「带时分秒的时间戳」**，不带格式的时候原样打出。
**要只拿日期或只拿时刻，都得靠 `to_char` 拆。**
**上面这条是 SQLite 的等价写法：`sysdate()` 必须带括号。**
**不带括号时 SQLite 会把它当成「一个叫 sysdate 的列」，直接报「没有这一列」。**
**Oracle 的 `sysdate` 是唯一一个不带括号就能用的「函数」**，
**它是关键字不是函数**——这个区别在你写跨库脚本时会立刻显出来。
SQL> select ename, hiredate, to_char(add_months(hiredate, 3), 'YYYY-MM-DD') as 三个月后, to_char(last_day(hiredate), 'YYYY-MM-DD') as 当月最后一天, round(months_between(sysdate(), hiredate) / 12, 1) as 工龄年 from emp where deptno = 10 order by hiredate
ENAME HIREDATE 三个月后 当月最后一天 工龄年
------------------------------------------------
CLARK 1981-06-09 1981-09-09 1981-06-30 45.4
KING 1981-11-17 1982-02-17 1981-11-30 45
MILLER 1982-01-23 1982-04-23 1982-01-31 44.8
已选择 3 行
**`add_months` 会自动处理月末**：1 月 31 日加一个月得 2 月 28（或 29）日，
**不会给你 3 月 3 日。** 这个行为是 Oracle 特意做的，比自己算天数靠谱。
**`months_between` 返回带小数的月数**，除以 12 就是年。
**但它是按「日历月」算的，不是按 30 天**——跨月边界时结果会跳一下，
**算工龄够用，算精确天数不能用它。**
**日期在 Oracle 里就是一个数**（从公元前 4712 年起的第几天，带小数表示时分秒）。
**所以日期能加减、能比较大小**，`hiredate > to_date('1982-01-01','YYYY-MM-DD')` 是合法的。
**用字符串直接比较则是错的**——字符串比的是字符顺序，不是时间先后。
四、转换类函数
--------------------------------------------------------------------
SQL> select to_char(2975.5, '999,990.00') as 千分位, to_char(2975, 'FM999990') as 去前导空格, to_number('2975') as 转回数字, to_date('2026-10-30', 'YYYY-MM-DD') as 转日期 from dual
千分位 去前导空格 转回数字 转日期
---------------------------------------
2,975.50 2975 2975 2026-10-30
已选择 1 行
**这三个函数是「同一套格式串」的三种用法**，3.4 节讲 `COLUMN` 时见过。
**区别只在位置：`COLUMN` 是显示时排，`to_char` 是「取出来就已经是字符串」。**
**`to_char` 之后结果变文本，排序规则也跟着变。**
**`to_char(sal)` 排出来是 `1000, 800, 900` 这种（按字符比），不是数值顺序。**
**要在文本上排数值，要么补零对齐，要么干脆别转。**
**隐式转换是最阴的坑：** `where hiredate = '1982-01-23'` 能跑，
**是因为 Oracle 拿你的会话日期格式去猜。**
**换一个客户端、换一个会话参数，同一条 SQL 就可能报错或查错。**
**规矩：日期一律 `to_date(..., 'YYYY-MM-DD')` 显式转，别指望它猜。**
五、聚合函数：把多行压成一个值
--------------------------------------------------------------------
SQL> select count(*) as 行数, count(comm) as 非空个数, count(distinct deptno) as 部门数, sum(sal) as 工资总额, round(avg(sal), 2) as 平均工资, max(sal) as 最高, min(sal) as 最低 from emp
行数 非空个数 部门数 工资总额 平均工资 最高 最低
------------------------------------------------
14 4 3 29025 2073.21 5000 800
已选择 1 行
**`count(*)` 和 `count(列)` 的区别只在 NULL 上**：前者数行，后者只数非空。
**所以「有多少人有奖金」必须写 `count(comm)`。**
**`avg(sal)` 会自动忽略 NULL**，**分母是非空个数，不是总行数。**
**这是聚合函数统一的行为：除了 `count(*)`，它们都跳过 NULL。**
**想让 NULL 当 0 参与，得写 `avg(nvl(comm, 0))`——但要清楚这是另一个口径了。**
SQL> select count(*) as 空表的行数, sum(sal) as 空表的和, avg(sal) as 空表的平均 from bonus
空表的行数 空表的和 空表的平均
------------------------------
0 NULL NULL
已选择 1 行
**空表上 `count(*)` 给 0，`sum`／`avg`／`max`／`min` 全给 NULL。**
**不是 0，是 NULL。**
**所以 `select sum(sal) from 空表` 拿到 NULL 之后直接参与运算，整个结果会变成 NULL。**
**要兜底就写 `nvl(sum(sal), 0)`。**
字符那一组里 instr('ALLEN','A') 给 1，instr('TURNER','A') 给 0。
0 就是「没找到」——不是 -1。判包含必须写 > 0。
round(2.5) 给 3，round(-2.5) 给 -3。
这就是「远离零」的意思：正数往上、负数往下，都往远离 0 的方向走。
而 Python 的 round(-2.5) 给 -2（往偶数靠），两边不一致。
日期那条是本课唯一一处「SQL 写法被迫改动」的地方：
Oracle 写 sysdate，SQLite 必须写 sysdate()。
不带括号时 SQLite 会把它当成「一个叫 sysdate 的列」，直接报「没有这一列」。
to_char(sysdate(), 'HH24:MI:SS') 只取时刻，'YYYY-MM-DD' 只取日期。
格式串里的 HH24 是 24 小时制，写成 HH 是 12 小时制——
少个 24，下午三点会变成 03，这种错最难发现。
聚合那一组：count(*) 给 14、count(comm) 给 4。
差的 10 就是空的个数。
avg(sal) 用的是 14 做分母（sal 没有空值），
如果换成 avg(comm)，分母就只有 4——同样叫「平均」，口径完全不同。
最后那条查空表 bonus：count(*) 给 0，sum 和 avg 都给 NULL。
不是 0，是 NULL。
所以「本月奖金总额」这种报表，空表那一个月必须写 nvl(sum(comm), 0)，否则数字列会变成空白。
30 秒实验
- 把
round(2.5)换成round(2.4)和round(2.6)各跑一次，确认临界点在哪。 然后再用 Python 算一遍同样的数，对比两边结果。 trunc(2975.567, 2)给 2975.56，round(2975.567, 2)给 2975.57。 把第二个参数改成负数（round(2975.567, -2)）看会怎样—— 它会把百位之后全抹掉，得到 3000。substr('ALLEN', -2)试试负下标。 Oracle 里负数是「从右往左数」，给的是最后两个字符EN——这个特性在取扩展名时很好用。- 把
count(comm)改成count(nvl(comm, 0))，看结果从 4 变成 14。 这就是「跳过 NULL」和「把 NULL 当 0」的差别，一个数之差，口径全变。
常见误区
- 以为
round在所有语言里规则一样。 Oracle 是「远离零」，Python 是「银行家舍入」。 涉及钱的跨语言计算，必须显式规定舍入规则。 - 用
instr判包含时写= 1。 它给的是「第一次出现的位置」，不在第一位就不等于 1， 但也不等于 0。判包含永远写> 0。 - 日期用字符串直接比较。
'1982-1-5' > '1982-01-23'在字符串上是真， 在时间上是假。 日期一律to_date显式转。 - 以为空表上
sum()返回 0。 返回 NULL。 NULL 参与任何运算结果还是 NULL，报表上就是一片空白。
自测三问（点开对答案）
1. `round(-2.5)` 在 Oracle 里给多少？为什么和 Python 不一样？ —— 给 **-3**。Oracle 是「四舍五入、远离零」，负数往下走。 **Python 是银行家舍入（往偶数靠），给 -2。** 两套规则都对，但结果不同。 2. `instr` 找不到时返回什么？判包含该怎么写？ —— 返回 **0**（不是 -1，也不是 NULL）。 **判包含写 `instr(x,'A') > 0`**——写 `= 1` 只能匹配「在第一位」。 3. 空表上 `count(*)`、`sum()`、`avg()` 分别返回什么？ —— `count(*)` 给 **0**，`sum()` 和 `avg()` 给 **NULL**。 **NULL 参与运算结果仍是 NULL**，所以报表要 `nvl(sum(x), 0)` 兜底。下一篇讲子查询：
把一条查询的结果，当成另一条查询的输入。重点是一个不报错但结果全错的坑——not in 遇到 NULL。
- ☐ （本篇完，下一篇 4.5 子查询的用法）
相关笔记
- 检索数据
- 格式化查询结果
- SQL 语言简介
-
- 🦏 MySQL即学即用/00-索引
- 🏺 Oracle从入门到精通 · 索引