简介Oracle 19c SQL语言参考手册PDF文档面向Oracle数据库开发者、DBA及备考OCP/OCA的技术人员针对日常SQL编写时函数用法记不全、参数含义模糊、类型转换与日期计算易出错等问题提供官方权威速查依据。资源为1个PDF文件约14MB内容围绕标准SQL语法与Oracle特有函数展开涵盖数学、字符串、日期时间、转换、加密、集合等类别并涉及大数据、云计算及智能化场景下的新增函数说明可用于速查函数签名、理解返回类型与边界条件、核对语法细节。该文档已有185人学习适合作为案头工具书长期查阅。需注意手册含版权与许可限制使用时应遵守相关规定不得随意复制、修改或分发其中内容仅作个人学习与工作参考。1. Oracle 19c 的 SQL 函数手册不是拿来背的一次日期边界查询漏掉数据的教训有次对账两边差三笔翻来覆去查了两小时最后发现是BETWEEN TO_DATE(2024-03-01,YYYY-MM-DD) AND TO_DATE(2024-03-31,YYYY-MM-DD)把 3 月 31 日 00:00:00 之后的数据全放走了——DATE带时分秒而TO_DATE只给了日期部分补的默认时间就是零点。Oracle 19c 的 SQL 语言参考手册把TO_DATE、TRUNC、BETWEEN分开写在三个地方单看每一条都对拼起来才翻车。这篇不讲函数清单怎么背。19c 常用函数有几百个真正天天用的不到六十个剩下的是查手册五分钟能解决的。要讲的是哪些函数组合起来会出现问题参数怎么设才稳出了问题从哪查。适合手上有一套跑在 19c 上的业务 SQL、正准备做函数改造或者被一条慢查询折磨的人。2. 把常用函数拆成三层骨架单行、聚合、分析各管一段函数选错很多时候不是不认得这个函数而是没先想清楚一件事输入几行、输出几行。19c 的手册按字母序排列函数实际排查却要按“行数变化”来分类这三层的边界一旦模糊写出来的 SQL 要么多一层嵌套要么直接报ORA-00937。2.1 单行函数、聚合函数、分析函数的职责边界单行函数是“进一行、出一行”UPPER、SUBSTR、NVL、TO_CHAR都属于这一类它不改变结果集的行数只改变单元格的值。聚合函数是“进多行、出一行”SUM、COUNT、AVG、MAX、LISTAGG是典型一旦出现在SELECT列表里其余非聚合列就必须进GROUP BY。分析函数是“进多行、出多行”每一行都能看到所属分组的信息行数一行不少。层输入行输出行典型函数常见误用单行函数11SUBSTR、NVL、TO_CHAR、TRUNC在WHERE里对列做函数运算导致索引失效聚合函数N1SUM、COUNT、AVG、LISTAGG忘记GROUP BY报ORA-00937分析函数NNROW_NUMBER、RANK、SUM() OVER、LAG拿分析函数结果直接写在WHERE里得套一层子查询这个骨架最实用的一点是判断该用哪一层只看“我要的结果里原来那几行还在不在”。如果原来的行要保留、只是多一列排名或者累计值那就用分析函数如果原来的行要被折叠成汇总行那就用聚合。中间最容易出错的是把聚合结果硬塞进明细行做法是用自关联去“补”代码难读不说数据量一上来就是平方级。举个实际改造统计每个客户最近一笔订单金额同时保留订单明细行。老写法是GROUP BY取MAX(create_time)再拿这个时间去自关联回原表一个客户同一秒下两单就会炸出重复行。换成分析函数只需要一层-- 每个客户按时间倒序编号编号为 1 的就是最新一笔明细行完整保留 SELECT customer_id, order_id, amount, create_time, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time DESC, order_id DESC) AS rn FROM orders WHERE create_time DATE 2024-01-01;PARTITION BY customer_id决定分组边界等同于聚合里的GROUP BYORDER BY create_time DESC决定组内的排序order_id DESC是第二排序键作用是当时间完全相同时也能确定谁排第一避免结果不稳定。DATE 2024-01-01是 ANSI 日期字面量不依赖会话的NLS_DATE_FORMAT比写字符串安全。这个子查询不能直接在外面写WHERE rn 1因为WHERE在分析函数之前执行必须包一层SELECT * FROM ( SELECT customer_id, order_id, amount, create_time, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time DESC, order_id DESC) AS rn FROM orders WHERE create_time DATE 2024-01-01 ) t WHERE t.rn 1;外层过滤的时候数据量已经被分区编号压过一遍好的执行计划里能看到WINDOW SORT PUSHED RANK这一点可以用DBMS_XPLAN验证后面第 6 章会说怎么看。2.2 19c 里该避开的废弃写法与新增能力19c 本身是长期支持版本很多环境的 SQL 是从更早版本一路带过来的函数层面最典型的迁移项有下面这些。写新代码时别再用左边那列。老写法19c 推荐写法原因WM_CONCAT(col)LISTAGG(col, ,) WITHIN GROUP (ORDER BY col)前者是未文档化的内部函数升级后可能直接不可用LISTAGG结果去重靠子查询LISTAGG(DISTINCT col, ,) WITHIN GROUP (...)19c 起LISTAGG支持DISTINCT省掉一层SELECT DISTINCTROWNUM嵌套两层做分页OFFSET n ROWS FETCH NEXT m ROWS ONLY后者语义明确配合ORDER BY结果稳定老式()外连接LEFT JOIN/RIGHT JOIN19c 仍认()但和 ANSI JOIN 混写会报ORA-01468一类错误TO_NUMBER(字符串)直接转TO_NUMBER(x DEFAULT NULL ON CONVERSION ERROR)或VALIDATE_CONVERSION源数据有脏值时不至于整条语句崩掉依赖隐式转换比较日期显式TO_DATE/DATE字面量结果随会话NLS_DATE_FORMAT变化LISTAGG的ON OVERFLOW子句值得单独提一句拼接结果超过VARCHAR2上限时默认行为是抛ORA-01489加上ON OVERFLOW TRUNCATE ...就能截断而不是报错-- 拼接结果超长时截断并显示截断标记避免整条查询失败 SELECT dept_id, LISTAGG(emp_name, , ON OVERFLOW TRUNCATE ...) WITHIN GROUP (ORDER BY emp_name) AS names FROM employees GROUP BY dept_id;ON OVERFLOW TRUNCATE后面跟的字符串会追加到结果末尾不写则默认是...还可以加WITH COUNT显示被截掉的条数。参数层面要注意LISTAGG的拼接结果上限和数据库的MAX_STRING_SIZE有关标准模式下是 4000 字节扩展模式下才放宽改这个参数需要重启并且影响面很大一般业务侧的做法是截断或者拆成多列而不是动参数。2.3 一份能贴到工位的函数速查表怎么建按字母序背函数用处不大因为工作时想的是“我要干这件事”而不是“我要用 L 开头的函数”。速查表按场景组织效率会高一个数量级。我自己的表分四列场景、首选函数、备选、注意点。场景首选备选注意点取第一条/最新一条ROW_NUMBER()KEEP (DENSE_RANK FIRST ...)必须有确定性的第二排序键空值替换COALESCENVLNVL会做强类型转换COALESCE取最高优先级类型字符串截取SUBSTRREGEXP_SUBSTR按字符还是按字节见 3.1日期截断到月/周TRUNC(d, MM)/TRUNC(d, IW)TO_CHAR(d,YYYY-MM)后者返回字符串比较时又得转回来金额格式化TO_CHAR(n, FM999,990.00)—FM去掉前导空格0强制补位分组拼接LISTAGG—超长要处理ORA-01489行间差值LAG/LEAD自关联自关联在大数据量下代价高这张表的价值在于第二列和第四列首选函数大多有五个以上变体真正决定 SQL 稳不稳的是第四列那些限制条件。表建好之后放到团队的 SQL 规范里比发一份几百页的 PDF 有用。3. 字符串、日期、NULL、类型转换单行函数的四个高危区单行函数看起来最简单出问题也最隐蔽因为它不报错只是悄悄给出一个错误的数。19c 里最容易出问题的集中在四处字符长度语义、日期格式模型、空值传播、隐式类型转换。3.1 LENGTH 与 LENGTHB字符语义和字节语义的分叉Oracle 的字符串函数有一整套“字符版”和“字节版”的对应关系LENGTH/LENGTHB、SUBSTR/SUBSTRB、INSTR/INSTRB。没有 B 的按字符算带 B 的按字节算。在AL32UTF8字符集下一个汉字占 3 个字节一个英文字母占 1 个字节于是同样一个字符串两个函数能差出三倍。-- 同一个字符串字符语义和字节语义给出的长度完全不同 SELECT LENGTH(数据库函数) AS char_len, -- 5 LENGTHB(数据库函数) AS byte_len, -- 15AL32UTF8 下汉字 3 字节 SUBSTR(数据库函数, 2, 2) AS char_sub, -- 据库 SUBSTRB(数据库函数, 2, 3) AS byte_sub -- 可能截出半个汉字 FROM dual;SUBSTRB截到多字节字符中间时19c 不会报错而是返回一个替换字符或者乱码这种数据落进表里之后极难排查。实际项目里的规则很简单只要字段里可能存中文截取就用SUBSTR只有在明确要按固定字节数截断比如对接定长报文时才用SUBSTRB而且截完要用LENGTHB校验一下。第二个坑是空字符串。Oracle 里等价于NULL这和其他数据库完全不同-- 这三个表达式的结果都是 NULL不是 0 SELECT LENGTH() AS a, -- NULL NVL(LENGTH(), -1) AS b, -- -1 IS NULL AS c -- 1真 FROM dual;所以判断字符串是否为空只能用IS NULL或者NVL(col, ) 这类写法col 永远不成立。CONCAT(a, NULL)返回a因为CONCAT和||会跳过NULL这和NULL参与算术运算的规则不一致写拼接逻辑时要注意。第三个坑是LTRIM/RTRIM的第二个参数。很多人以为LTRIM(xyxzyx, xy)是删掉前缀xy实际是“删掉左侧所有属于{x,y}集合的字符”结果是zyx。要按整个子串删除得用REGEXP_REPLACE(str, ^xy)。3.2 TO_DATE 与 TO_CHAR格式模型和 NLS 会话依赖日期函数的问题几乎全部来自两处格式模型写错或者根本没写格式模型、靠会话参数兜底。格式模型里最容易写错的是这几个元素含义坑点YY两位年份固定当前世纪前缀跨世纪数据会错RR两位年份按 50 年规则推世纪和YY结果不同MM月份和MI分钟容易混淆HH/HH1212 小时制不配AM/PM会丢上下午HH2424 小时制推荐统一用这个DAY/MONTH星期/月名结果长度受NLS_DATE_LANGUAGE影响不能用来比较IWISO 周和WW1 月 1 日起算差一周以上最隐蔽的是省略格式模型的情况。TO_DATE(2024-03-01)这种写法在NLS_DATE_FORMAT是YYYY-MM-DD的会话里能跑换一个会话参数就报ORA-01861。这是真正的环境依赖问题开发机和生产库的NLS_DATE_FORMAT不一致同一条 SQL 两边结果不同。-- 显式指定格式模型结果与会话参数无关 SELECT TO_DATE(2024-03-01 14:30:00, YYYY-MM-DD HH24:MI:SS) AS d1, TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS) AS d2, TO_CHAR(SYSDATE, YYYY-QQ) AS d3 -- 字面量要用双引号包住 FROM dual;注意第三行格式模型里的普通字符必须用双引号包起来否则Q会被当成格式元素解析得到意料之外的结果。这一条在写报表标题时经常忘。日期运算还有两个易错点。第一两个DATE相减得到的是天数NUMBER两个TIMESTAMP相减得到的是INTERVAL DAY TO SECOND两者不能混着比较。第二MONTHS_BETWEEN在月末的表现不直观MONTHS_BETWEEN(DATE 2024-02-29, DATE 2024-01-31)返回 1但MONTHS_BETWEEN(DATE 2024-02-28, DATE 2024-01-31)返回的却不是整数。要做“相差几个月”的业务口径稳妥做法是用TRUNC到月之后再算或者直接用ADD_MONTHS反向验证。3.3 NVL 家族与 NULL 传播四种写法的选型NVL、NVL2、COALESCE、NULLIF这四个函数功能有重叠选错会在类型和求值时机上出问题。-- 四种空值处理的典型用法与差异 SELECT NVL(a, 0) AS nvl_val, -- 只接受两个参数结果类型取 a 的类型 NVL2(a, 有值, 无值) AS nvl2_val, -- 三参数判断不为空/为空 COALESCE(a, b, c, 0) AS coal_val, -- 多参数返回第一个非空短路求值 NULLIF(a, 0) AS nullif_val -- a 等于 0 时返回 NULL否则返回 a FROM t;关键差异有三条。第一NVL会把第二个参数隐式转换成第一个参数的类型如果a是NUMBER、第二个参数是字符串可能报ORA-01722COALESCE取的是所有参数里优先级最高的类型行为更可预期。第二COALESCE是短路求值后面的表达式不一定会被计算NVL则会把两个参数都算出来如果第二个参数是个耗时的函数调用性能差异很明显。第三DECODE和CASE对NULL的判断不同-- DECODE 认为两个 NULL 相等CASE 不这么认为 SELECT DECODE(NULL, NULL, eq, ne) AS decode_r, -- eq CASE WHEN NULL NULL THEN eq ELSE ne END AS case_r -- ne FROM dual;这个差异在写状态映射时是致命的用DECODE写了一套逻辑后来为了可读性改成CASENULL分支的判断结果直接反了。NULL在聚合里的传播也要记住所有聚合函数COUNT、SUM、AVG、MAX、MIN都忽略NULL。COUNT(*)数所有行COUNT(col)只数非空行AVG(col)的分母是非空行数不是总行数SUM在所有输入都是NULL时返回NULL而不是 0。报表口径里这几个数字对不上八成就是这里。排序时的NULL位置也由参数控制ORDER BY col默认NULLS LASTORDER BY col DESC默认NULLS FIRST要固定位置就显式写NULLS FIRST/NULLS LAST。3.4 可抄的日期区间、金额格式化与安全转换模板把上面几条拼成可以复制的模板。日期区间查询的正确写法是“左闭右开”不要用BETWEEN-- 左闭右开包含当天零点不包含次日零点DATE 和 TIMESTAMP 都适用 SELECT order_id, amount, create_time FROM orders WHERE create_time DATE 2024-03-01 AND create_time DATE 2024-04-01; -- 按自然月统计时用 TRUNC 对齐避免手工拼日期字符串 SELECT TRUNC(create_time, MM) AS month_start, COUNT(*) AS cnt, SUM(amount) AS total FROM orders WHERE create_time DATE 2024-01-01 GROUP BY TRUNC(create_time, MM) ORDER BY month_start;第一段的两个条件都要走索引create_time上有普通索引时能直接做范围扫描。第二段在GROUP BY里对列做TRUNC不会影响WHERE的索引使用因为过滤条件在WHERE里是裸列。这个区别很关键函数可以放在SELECT和GROUP BY但尽量不要放在WHERE的过滤列上。金额格式化的模板-- FM 去掉前导空格9 不补位0 强制补位L 取会话货币符号 SELECT TO_CHAR(1234.5, FM999,990.00) AS v1, -- 1,234.50 TO_CHAR(0.5, FM999,990.00) AS v2, -- 0.50 TO_CHAR(1234.5, FM999,999.99MI) AS v3, -- 负数用括号尾标 TO_CHAR(-1234.5,FM999,999.99PR) AS v4 -- 负数用尖括号 FROM dual;9和0的差别是9表示“有数字就显示没数字留空”0表示“没数字补 0”。FM必须写在最前面作用是去掉TO_CHAR自动加的前导空格不加的话导出的 CSV 每列前面都会多一个空格下游解析时会踩坑。源数据有脏值时的安全转换19c 有专门的语法-- 转换失败返回默认值而不是抛 ORA-01722 中断整条查询 SELECT TO_NUMBER(12,345, FM999,999 DEFAULT NULL ON CONVERSION ERROR) AS n1, TO_DATE(2024-13-01, YYYY-MM-DD DEFAULT NULL ON CONVERSION ERROR) AS d1, VALIDATE_CONVERSION(abc AS NUMBER) AS is_number -- 0 表示不能转 FROM dual;DEFAULT ... ON CONVERSION ERROR是 12c 之后的语法19c 完整支持代价是执行计划里会多一层保护逻辑全表扫描时比裸TO_NUMBER略慢适合放在清洗层而不是跑在热路径上。VALIDATE_CONVERSION更轻量适合先筛一遍再转。4. 分析函数与窗口帧把 GROUP BY 的活接过来分析函数是 19c 里最值得认真学的一段因为它能替掉大量自关联和临时表。但窗口帧的默认值、排序键的不确定性、函数嵌套的限制这三个点不搞清楚写出来的结果会“看起来对、实际不对”。4.1 三种排名函数去重取最新一条的固定写法ROW_NUMBER、RANK、DENSE_RANK三个函数的差别只在并列值上-- 同一分区内三个排名函数对并列值的处理不同 SELECT emp_id, score, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY score DESC) AS rn, RANK() OVER (PARTITION BY dept_id ORDER BY score DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY score DESC) AS drk FROM emp_scores;假设某部门分数是 95、95、90ROW_NUMBER给 1、2、3永不重复RANK给 1、1、3并列跳号DENSE_RANK给 1、1、2并列不跳号。取“前 N 名”时用哪个取决于业务要不要“并列都算进去”取前三名且并列都保留用DENSE_RANK 3只要三条数据用ROW_NUMBER 3。去重取最新是这几个函数最常用的场景标准写法在前面的 2.1 已经给过这里补充一个参数层面的细节ORDER BY里必须有能唯一确定顺序的键组合。如果只用create_time同一毫秒的两行谁排第一是不确定的可能出现“跑两次结果不一样”的情况。加上主键做第二排序键就好了代价几乎为零。另一个常被忽略的用法是KEEP子句配合聚合函数可以在一行里同时取“最新那条的某个字段”-- 取每个客户最后一笔订单的金额和下单时间不需要子查询 SELECT customer_id, MAX(amount) KEEP (DENSE_RANK LAST ORDER BY create_time) AS last_amount, MAX(create_time) KEEP (DENSE_RANK LAST ORDER BY create_time) AS last_time FROM orders GROUP BY customer_id;KEEP (DENSE_RANK LAST ORDER BY create_time)的含义是按create_time排序取最后一组再对这组做MAX。它比ROW_NUMBER子查询少一层嵌套但只能取聚合值取不到整行所以适用于只需要几个字段的报表场景。4.2 窗口帧的默认值ROWS 与 RANGE 差在哪窗口帧是分析函数里最容易想当然的部分。规则是OVER里写了ORDER BY但没写窗口帧时默认帧是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW没写ORDER BY时默认帧是整个分区。RANGE和ROWS的差别在于CURRENT ROW在ROWS下指“当前这一物理行”在RANGE下指“当前这一行的排序值相同的所有行”。排序值有重复时两者结果不同-- 同一份数据ROWS 和 RANGE 的累计结果不同 SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_rows, SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_range FROM orders;如果同一天有两笔订单sum_rows是逐行累加第一笔只加自己第二笔加两笔sum_range会在第一笔时就把同一天的两笔一起加上两行结果相同。做“按日累计”的业务口径时通常要的是sum_range的效果做“逐笔流水累计”时用sum_rows。这个区别不说清楚报表口径的讨论会扯很久。LAST_VALUE是另一个典型问题-- 不加窗口帧LAST_VALUE 取到的是当前行不是整个分区的最后一行 SELECT dept_id, emp_id, salary, LAST_VALUE(emp_id) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS wrong_last, LAST_VALUE(emp_id) OVER (PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS right_last FROM employees;第一列因为默认帧只到当前行LAST_VALUE永远等于当前行的emp_id看起来就是“函数没生效”。必须显式写ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。替代方案是直接用FIRST_VALUE配合反向排序语义更清楚。还有一个限制要知道分析函数不能嵌套SUM(ROW_NUMBER() OVER (...)) OVER (...)会报ORA-30483OVER里也不能用DISTINCT会报ORA-30482。需要嵌套时只能套子查询把内层结果先算出来。4.3 LAG/LEAD 与累计求和的三个模板LAG/LEAD用来取前后行做环比和行间差值最方便语法是LAG(expr, offset, default)offset默认 1default默认NULL。-- 模板一环比。用 LAG 取上一期值首行或无上期时返回 NULL SELECT month_start, amount, LAG(amount, 1) OVER (ORDER BY month_start) AS prev_amount, ROUND((amount - LAG(amount, 1) OVER (ORDER BY month_start)) / NULLIF(LAG(amount, 1) OVER (ORDER BY month_start), 0) * 100, 2) AS growth_pct FROM month_summary;这里用NULLIF(..., 0)把除数为 0 的情况转成NULL避免报ORA-01476。这是行间计算里必须加的保护不加的话只要有一期金额为 0整条查询就挂。-- 模板二分组内累计。PARTITION 定分组ORDER BY 定累计方向 SELECT dept_id, emp_id, salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY emp_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;显式写ROWS BETWEEN ...而不是依赖默认帧原因是默认帧在排序值重复时行为不同代码被后来人接手时没法一眼看出用的是哪种语义。显式写出来多打几个字省掉后面一堆解释。-- 模板三相邻行时间差。两个 DATE 相减是天数乘 24 换算成小时 SELECT session_id, event_time, ROUND((event_time - LAG(event_time) OVER (PARTITION BY session_id ORDER BY event_time)) * 24 * 60, 2) AS gap_minutes FROM session_events;注意PARTITION BY session_id不能省否则跨会话的第一行会拿到上一个会话的最后一行时间算出一个负值或者巨大的间隔。这类错误在数据量小的时候看不出来上线之后才会暴露。5. 排查清单函数写错的五类现场与定位手法函数类问题有个共同特征报错信息指向的位置往往不是真正的原因。下面五类是实际排查里重复率最高的。5.1 现象查询偶尔报 ORA-01722重跑又好了原因WHERE里拿字符串列和数字列做比较Oracle 按优先级把字符串隐式转成数字。多数行能转成功碰上一条带字母的脏数据就报错能不能触发取决于执行计划先扫到哪一行所以表现为“偶发”。解决先用VALIDATE_CONVERSION(col AS NUMBER)找出脏行再决定是清洗还是加DEFAULT ... ON CONVERSION ERROR。根治办法是把比较两侧的类型对齐别指望隐式转换。定位时看执行计划谓词里出现INTERNAL_FUNCTION就是隐式转换的信号。5.2 现象明细数据一条没少汇总报表的总数比明细少一截原因分母被NULL吃掉了。AVG、COUNT(col)、SUM(col)都忽略NULL明细里那些空值行在汇总时不计入分母比例、均值就会偏高。另一种常见情况是NOT IN的子查询里返回了NULL整个条件恒为UNKNOWN结果集直接为空。解决报表口径统一用COUNT(*)做分母或者显式NVL后再聚合。NOT IN一律改成NOT EXISTS后者对NULL的处理符合直觉。改完之后拿明细数对一遍两个数字必须能对上。5.3 现象按日期区间查每天固定少最后几秒的数据原因BETWEEN的右边界是个纯日期TO_DATE补的默认时间部分为 00:00:00当天零点之后的数据全部落在区间外。如果列是TIMESTAMP问题更明显。解决改成左闭右开 起始日 AND 次日。查询条件里不要对列做TRUNC那会同时让边界判断和索引失效。5.4 现象LISTAGG 报 ORA-01489接口直接 500原因拼接结果超过了VARCHAR2的 4000 字节上限。中文场景下每个字符 3 字节实际能拼的条数比想象中少得多。解决短期加ON OVERFLOW TRUNCATE长期改成“分页拼接”或者“只拼 ID 列表、详情走另一次查询”。同时检查LISTAGG的排序键缺ORDER BY时拼接顺序不确定同样的数据两次跑出不同字符串缓存和比对都会出问题。5.5 现象给某个字段加了 TO_CHAR 之后查询从 0.2 秒变成 8 秒原因WHERE TO_CHAR(create_time, YYYY-MM-DD) 2024-03-01对索引列做了函数运算普通 B-Tree 索引无法使用只能全表扫描。如果这个列上本来有基于函数的索引那还好但大多数环境没有。解决把函数从列上挪到常量上写成create_time DATE 2024-03-01 AND create_time DATE 2024-03-02。改完用EXPLAIN PLAN确认执行计划里出现了INDEX RANGE SCAN而不是TABLE ACCESS FULL。如果业务上确实需要按格式化后的字符串查那就建基于函数的索引并且保证查询表达式和索引表达式完全一致大小写、格式模型都要一样否则索引同样用不上。6. 用执行计划和一张用例表把函数手册变成可验证的本地知识函数的行为很多时候不能靠读手册确定尤其是涉及NLS参数、隐式转换、窗口帧默认值的地方。我现在的做法是两步先把关键函数的行为固化成一张用例表再用执行计划验证隐式转换有没有发生。用例表很简单一张两列的表左边是输入、右边是期望输出覆盖边界值空字符串、NULL、月末日期、跨年、多字节字符、超长拼接。每次数据库升级或者改NLS参数跑一遍就知道哪些行为变了-- 函数行为回归用例期望值与实际值不一致的行会被筛出来 WITH cases AS ( SELECT LENGTH_EMPTY AS case_name, LENGTH() AS actual, NULL AS expected FROM dual UNION ALL SELECT DECODE_NULL, DECODE(NULL, NULL, eq, ne), eq FROM dual UNION ALL SELECT CASE_NULL, CASE WHEN NULL NULL THEN eq ELSE ne END, ne FROM dual UNION ALL SELECT MONTHS_END, TO_CHAR(MONTHS_BETWEEN(DATE 2024-02-29, DATE 2024-01-31)), 1 FROM dual UNION ALL SELECT TRUNC_IW, TO_CHAR(DATE 2024-01-01, IW), 01 FROM dual ) SELECT case_name, actual, expected FROM cases WHERE NVL(TO_CHAR(actual), NULL) expected;这条语句跑完返回空集说明当前环境的函数行为符合预期返回哪一行哪一行的语义就变了。NVL(TO_CHAR(actual), NULL)这一层是把NULL显式转成字符串再比否则NULL xx恒为UNKNOWN异常行会被过滤掉这本身就是一个容易被忽略的判断细节。第二步是查隐式转换。执行计划里看到INTERNAL_FUNCTION包在列上基本可以确定发生了隐式类型转换索引大概率用不上-- 查看最近执行的某条 SQL 的真实执行计划重点看 Predicate Information SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, NULL, ALLSTATS LAST));ALLSTATS LAST会带上实际的A-Rows和Buffers能看出评估行数和实际行数差多少。谓词部分如果出现INTERNAL_FUNCTION(CREATE_TIME)说明WHERE里对这个列做了函数运算或者类型转换改成裸列比较再跑一次对比。最后一个习惯把NLS_SESSION_PARAMETERS里的NLS_DATE_FORMAT、NLS_DATE_LANGUAGE、NLS_NUMERIC_CHARACTERS三个值记在排查清单第一行。日期函数、TO_CHAR的月份名、小数点符号全都受它们影响跨环境行为不一致的时候先看这三个值能省掉很多时间。这三条是我踩过几次坑之后才加进流程的一开始总觉得“同一条 SQL 在哪儿跑不是跑”结果就是在测试库好好的报表到了另一个环境全乱。写 SQL 时把格式模型写全、把类型对齐比事后排查便宜得多。希望帮到你。本文还有配套的精品资源点击获取
