Oracle 19c常用函数实战：从日期字符到分析函数的避坑指南

简介Oracle数据库19c版SQL语言参考手册PDF面向数据库开发者、管理员及数据分析师提供从标准SQL到Oracle特有函数的完整说明。手册按功能类别系统整理数学、字符串、日期时间、转换、加密、集合等常用函数并涉及大数据、云计算及人工智能相关函数。资源为单个PDF文件大小14MB内容为2024年发布的19c版官方文档便于离线查阅。目前已有183人学习可作为日常开发、SQL调优及理论学习的常备技术手册。书中对各函数的语法、参数及示例进行了详细讲解开发者可对照使用高效完成数据库查询与数据处理。1. Oracle 19c 版 SQL 语言参考手册摊到桌面上常用函数大全不是背完就能直接用不少 DBA 的收藏夹里放着 Oracle 数据库 19c 版 SQL 语言参考手册真到写报表、调存储过程、接数据同步工具时还是要一遍遍翻常用函数大全的举例页。原因很简单手册按函数名字典式排不按业务场景排而大多数人在生产环境遇到的问题恰恰是「日期差一天、字符串多一个空格、TO_NUMBER 报 ORA-01722」这类边界问题。这篇笔记会把《Oracle Database SQL Language Reference》里 19c 常用函数按场景重排一遍每一节都给出可直接落到 SQL 里的写法、参数和失败时的排错方向。适合正在写报表、刚接手 19c 单实例、或者把老库往 19c 迁的开发者与运维不适合只看概念不动手的人。2. 先看懂 19c 参考手册的函数分类骨架与版本差异再决定查哪一页直接去翻函数大全很容易迷路字符、数值、日期、转换、大对象、聚合、分析、集合、JSON19c 的手册把函数分了十几类每一类还带着若干同义词和废弃提示。我一般拿到新环境后的第一件事不是背函数名而是先建立一张「按用途查函数」的索引把最常写的三类场景钉死单行处理、聚合统计、窗口分析。这样再看手册才知道哪些函数值得精读哪些只要知道存在即可。2.1 字符、数值、日期、转换四类函数哪个分类最值得先背实际项目里按出现频率排序日期函数是最大的坑源。字符函数排第二因为数据清洗、姓名拆分、地址规整都靠它们。数值函数本身不难难在隐式转换规则。转换函数是「看似简单、一用就错」的重灾区尤其是 TO_NUMBER 和 TO_CHAR 配合格式串时NLS 参数一换结果就变。下表是我在工作笔记里留的速查骨架对应到 19c 手册的「Functions」章节时按这个顺序检索最快分类代表函数手写 SQL 时最常用在哪字符SUBSTR, INSTR, REPLACE, TRANSLATE, TRIM, LPAD/RPAD, REGEXP_REPLACE字段拆分、去空格、脱敏、格式规整数值ROUND, TRUNC, MOD, CEIL, FLOOR, ABS, POWER金额精度、余数判断、数值边界日期SYSDATE, TRUNC, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY, EXTRACT月初年末、时间段生成、报表周期转换TO_DATE, TO_NUMBER, TO_CHAR, CAST, CONVERT类型转换、格式化输出、接口数据转换记忆诀窍是字符函数记「截取、定位、替换」三个动作日期函数记「截断、加月、取末日」三个场景转换函数记「格式串 NLS 参数」两个变量。把这张表放进脑子里再去翻手册的具体函数条目速度和准确率都会明显提升。2.2 19c 相对旧版本的行为差异隐式转换、JSON 与空字符串处理19c 是 12.2 代码线的长期支持版本常用标量函数没有爆炸性重写但有几个行为差异值得在切库前先验证。第一个是隐式转换变得更保守在 19c 里TO_NUMBER 对前导空格的容忍度与 NLS_NUMERIC_CHARACTERS 设置强相关之前 11g 上能跑过的TO_NUMBER( 123 )换到 19c 的某些会话参数下可能直接报错。第二个是空字符串Oracle 中永远被当成 NULL这在 19c 里依旧成立但新接手项目的同事如果从 SQL Server 迁移过来最容易在这里翻车。第三个是 JSON 函数的返回值类型JSON_VALUE 默认返回 VARCHAR2遇到长文本要显式指定 RETURNING CLOB否则超出 4000 字节就报错。处理这些差异的正确姿势不是背差异列表而是把会话级 NLS 参数固定下来。我一般在新环境里先执行一次 nls_session_parameters 检查再把关键的NLS_DATE_FORMAT、NLS_NUMERIC_CHARACTERS、NLS_SORT在登录触发器中统一设置。这样即使手册描述没变实际行为也稳定可预期。等到踩坑时再回查手册的同义词和隐式转换规则会比从头到尾读一遍效率高得多。2.3 不靠猜用 V$VERSION、NLS 参数与 DUMP 验证手册结论手册里的描述是通用的但你的实例是具体的。验证当前环境行为我通常会开一个测试会话把下面这几条按顺序跑一遍SELECT * FROM V$VERSION; -- 确认当前版本区分 19.3 与 19.21 等补丁级别 SELECT parameter, value FROM NLS_SESSION_PARAMETERS WHERE parameter IN (NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_LANGUAGE); SELECT DUMP(123.45), DUMP(123.45) FROM dual; -- 看数据类型和内部字节表示第一条确认版本分支第二条看会话级格式比如 NLS_DATE_FORMAT 如果是DD-MON-RR那么直接查日期列时显示的字符串会严重影响你对数据的判断第三条用 DUMP 函数看一个值的类型和内部存储能避免很多「看起来是数字其实是字符」的误会。DUMP 的第二个参数如果不写默认按十进制字节输出字符和数值在字节形态上明显不同一眼就能分辨。这套验证做完再去读手册里对应的函数说明结论才不会跑偏。3. 把常用函数拆成可抄的例子TRUNC(SYSDATE) 到字符串清洗的取值边界这一章不做词典翻译把三组高频函数按场景拆开给你可以直接抄去改的 SQL 片段。每组都标注参数含义和最容易踩的边界条件。日期、字符、数值转换这三组基本覆盖日常报表和接口处理 80% 的书写需求。3.1 日期函数TRUNC(SYSDATE) 的格式模型与月初、季初边界写日报、月报时TRUNC(SYSDATE) 几乎每个报表都会出现。它的第二个参数是格式模型决定了把当前时间截断到什么粒度。以下是我在生产环境验证过的常见组合SELECT TRUNC(SYSDATE) AS 当日零点, TRUNC(SYSDATE, MM) AS 本月第一天, TRUNC(SYSDATE, YEAR) AS 本年第一天, TRUNC(SYSDATE, IW) AS 本周一, -- ISO 周按周一为一周开始 LAST_DAY(SYSDATE) AS 本月最后一天, ADD_MONTHS(SYSDATE, -1) AS 上月同日, MONTHS_BETWEEN(SYSDATE, DATE 2024-01-01) AS 相距月数 FROM dual;这里的边界点有两个。第一格式模型MM和YEAR分别代表月首和岁首而IW是 ISO 周规则周一为一周开始如果你的业务每周从周日算起就不能用IW要自己写NEXT_DAY(TRUNC(SYSDATE), SUNDAY)之类的取法。第二ADD_MONTHS 在月末日期上会做「月末对齐」1 月 31 日加一个月返回 2 月 28 日或 29 日而不是 3 月 2 日。这在计算账期时是常见套路但如果业务要求「完整经过自然月」你得自己判断要不要用 LAST_DAY 修正。再补充一个高频陷阱TRUNC 函数只是把时间部分截掉并没有改变数据完整性的语义。有人会在 WHERE 里写TRUNC(hire_date) TRUNC(SYSDATE)来查当天入职的人这在数据量小的时候没问题量大之后会挡掉 hire_date 上的索引属于慢 SQL 常见病灶。下一章的改写思路会专门处理。3.2 字符函数SUBSTR、INSTR、REGEXP_REPLACE 组合出清洗 SQL字符串清洗是另一个高频现场。姓名中间带多个空格、电话号格式混乱、地址里混入全角字符都需要组合使用。先列出最常用的几个参数细节SELECT TRIM(BOTH FROM ABC ) AS 去空格, SUBSTR(ABCDE, 3) AS 从第三位截取, -- 返回 CDE SUBSTR(ABCDE, 2, 2) AS 从第二位取两位, -- 返回 BC INSTR(A-B-C, -, 1, 2) AS 第二次出现位置, -- 返回 4 REPLACE(JACK and JUE, J, BL) AS 替换全部, TRANSLATE(12345, 123, abc) AS 逐字符映射, -- 返回 abc45 REGEXP_REPLACE(a b c, [[:space:]], ) AS 多空格压成单空格 FROM dual;四个边界点要记牢。第一Oracle 字符串下标从 1 开始SUBSTR(str, 0, n)和SUBSTR(str, 1, n)结果相同很多人从别的语言带过来的「0 开头」习惯在这里容易出偏差第二INSTR 的第四个参数表示第几次出现不写默认是第一次用于拆分第二个连字符之后的子串很顺手第三REPLACE 是整串替换而 TRANSLATE 是一个字符映射一个字符比如把手机号里的全角数字转半角只能用 TRANSLATE 逐字映射REPLACE 做不到第四REGEXP_REPLACE 默认是贪婪匹配把多个空格压成一个空格时字符类加量词是稳定写法后面避坑章还会展开。清洗姓名的典型场景可以这样抄SELECT trim(REGEXP_REPLACE(full_name, [[:space:]], )) AS 标准化姓名, SUBSTR(full_name, 1, INSTR(full_name, , 1, 1) - 1) AS 姓氏, SUBSTR(full_name, INSTR(full_name, , 1, 1) 1) AS 名 FROM member_info WHERE full_name IS NOT NULL;注意 INSTR 找不到空格时返回 0SUBSTR 的第二个参数如果算出来是 0会被当成 1 处理所以上面两段在单名、无名情况下会取错实际使用时应当先加 CASE 判断。3.3 数值与转换函数ROUND、MOD、TO_NUMBER 对 NULL 和格式串的敏感点数值函数本身老实真正阴险的是 NULL 和类型转换。先看一组容易出错的组合SELECT ROUND(123.456, 2) AS 四舍五入两位, TRUNC(123.456, 2) AS 截断两位, MOD(17, 5) AS 求余, CEIL(2.1), FLOOR(2.9), ABS(-3) AS 向上取整向下取整绝对值, TO_NUMBER(1,234.56, 9,999.99) AS 带千分位转数值, TO_CHAR(1234.5, FM999,999.00) AS 数值格式化 FROM dual;TO_NUMBER 的格式串有三个坑第一格式串里的9表示可有可无的位0表示必须有的位9,999.99对应1,234.56没问题但如果你把数据从接口读进来它带的可能是中文逗号或全角数字这时需要配合 NLS_NUMERIC_CHARACTERS 参数第二TO_CHAR 默认会在最前面留一个空格给符号位加上FM前缀可以去掉这个前导空格这在拼接报表字符串时经常救命第三NULL 参与任何算术运算都返回 NULLNULL 1还是 NULL所以求和字段建议先写NVL(amount, 0)。这个特性像玄学一样害人凡是看到统计结果异常变小的报表第一步就该检查原始列里有没有 NULL。另一个容易忽略的点是 ROUND 和 TRUNC 在负数上的行为ROUND(-1.5)返回 -2TRUNC(-1.5)返回 -1方向不一样。写结转、计费逻辑时如果金额可能为负要把这个差异写清楚否则月底对账会多出几笔出入。3.4 分析函数ROW_NUMBER 实现排行榜与 Top-N 查询手册里把分析函数单独列类它们和普通聚合函数最大的区别是不合并行每行都保留同时能在组内计算排名、累计值。最常用的是 ROW_NUMBER、RANK、LAG/LEAD、SUM 开窗。先看一个分组取 Top-N 的典型写法SELECT staff_no, dept_id, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rn FROM sales_order;把这条语句包一层就能取每个部门金额最大的前三条记录SELECT dept_id, staff_no, amount, rn FROM ( SELECT staff_no, dept_id, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rn FROM sales_order ) WHERE rn 3;这里有一个和 ROWNUM 伪列不同的关键点ROW_NUMBER()的排序发生在分析函数内部你在外层用rn 3过滤时不会丢掉数据而直接写WHERE ROWNUM 3在没有子查询的情况下会先取前 3 行再排序结果完全不对。RANK和DENSE_RANK与 ROW_NUMBER 的差别在于并列名次RANK 的并列名次会跳号DENSE_RANK 不跳号。做排行榜时如果产品要求「第二名并列后下一名是第三名」用 DENSE_RANK否则用 RANK。4. 函数在存储过程、Oracle 分页与慢 SQL 优化里的三个真实用途单独写函数是一回事把函数放进存储过程、分页查询和 WHERE 条件里就涉及执行计划、索引使用和 PL/SQL 上下文切换。这一章选三个最常见的落地现场每个都给方案和参数说明。4.1 存储过程里调用函数DETERMINISTIC、上下文切换与避免 SELECT 套 SELECTOracle 存储过程里的函数主要分两类一类是业务函数比如算税、算折扣一类是工具函数比如解析字符串。在 19c 里业务函数如果输入相同输出一定相同建议显式加 DETERMINISTIC 声明CREATE OR REPLACE FUNCTION fx_calc_tax(p_amount IN NUMBER) RETURN NUMBER DETERMINISTIC AS BEGIN RETURN ROUND(p_amount * 0.06, 2); END fx_calc_tax;DETERMINISTIC 不是让你白写的。它告诉优化器这个函数在相同入参下结果恒定可以用在基于函数的索引和物化视图查询重写上。但要注意两点第一DETERMINISTIC 只是一个承诺如果函数内部读了表或者依赖会话变量结果不稳定却声明了 DETERMINISTIC会让基于函数的索引产生错误数据这是血泪经验第二SQL 里的用户自定义函数每次调用都涉及 SQL 与 PL/SQL 引擎的上下文切换数据量一大性能比内建函数慢得多。所以存储过程里能先算完再传入的就不要在 SQL 里包一层。另一个典型问题是过程里习惯性写SELECT ... INTO连表查询导致 SQL 里套 SQL。比如为了带出用户名先查用户表再查订单表实际上完全可以一条 JOIN 加分析函数解决。优化方向是尽量把数据集一次取全把字符串拼接、条件判断放到 PL/SQL 循环里处理而不是在 SQL 里反复调用函数。4.2 Oracle 分页三种写法ROWNUM、ROW_NUMBER() 与 19c 推荐 FETCH FIRSTOracle 分页是搜索热词也是老开发和新开发写法冲突最明显的地方。三种写法各有适用场景。第一种是 ROWNUM 伪列必须嵌套两层才能取到中间页SELECT * FROM ( SELECT a.*, ROWNUM AS rn FROM (SELECT * FROM emp ORDER BY sal DESC) a ) WHERE rn BETWEEN 21 AND 40;内层先排序中间层加行号外层过滤区间。ROWNUM 在 WHERE 里直接写ROWNUM 20会返回 0 行因为 ROWNUM 是结果集的先后顺序号不排序就无法稳定跳过。第二种是用 ROW_NUMBER() 分析函数SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (ORDER BY sal DESC) AS rn FROM emp e ) WHERE rn BETWEEN 21 AND 40;这种写法更清晰排序逻辑在分析函数里维护适合在存储过程里拼动态 SQL。第三种是 19c 推荐的 ANSI 写法SELECT * FROM emp ORDER BY sal DESC OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;FETCH FIRST 语法是 12.1 引入的19c 上表现稳定语义直白跳过 20 行取接下来 20 行。要注意 OFFSET 的值越大数据库仍要先扫描并丢弃前 20 行无法像传统分页那样利用索引直接定位所以在超大表上别指望它比 ROWNUM 快。我的建议是新代码统一用 FETCH FIRST老代码别为了统一而重写ROWNUM 能跑就留着。4.3 WHERE 里包函数引发的慢 SQL索引失效的改写思路慢 SQL 优化里最常见的翻车点就是在 WHERE 条件中对索引列套函数。典型写法是查某一天的数据SELECT * FROM emp WHERE TRUNC(hire_date) DATE 2024-01-15;只要 hire_date 上有索引这个写法就会让索引失效因为数据库无法直接从 B-tree 索引里比较 TRUNC(hire_date) 的值只能全表扫描后在每一行上算函数。改写方案是把函数从列上挪走变成范围条件SELECT * FROM emp WHERE hire_date DATE 2024-01-15 AND hire_date DATE 2024-01-16;这样既保持了「查 1 月 15 日全天数据」的语义又能用上 hire_date 索引。同类改写还包括WHERE TO_CHAR(amount) 123改成WHERE amount TO_NUMBER(123)以及WHERE UPPER(name) A考虑改用函数索引。函数索引能救部分场景但维护成本高优先做列不包函数的改写。判断改写是否有效的验证方式是看执行计划的谓词部分出现FILTER或全表扫描说明还没到位出现INDEX RANGE SCAN才算成功。5. 常用函数使用避坑19c 里最容易反复踩的 5 个行为差异这一章全部来自一线排障记录每条按现象、原因、解决三个层次写。前两条属于「换了数据库才发作」的认知差后三条属于「手册写了但没人细看」的边界问题。5.1 空字符串不是空值Oracle 与其他数据库的认知差现象从 SQL Server 迁移过来的同事写WHERE name 过滤空姓名结果所有空姓名记录都没被过滤掉反过来写WHERE name 一条也查不到。原因Oracle 把空字符串当作 NULL IS NULL恒为真所以name 等价于name IS NOT NULL但 NULL 参与比较的结果是 UNKNOWN过滤不掉 NULL 行。解决统一判空逻辑字段既可能为空字符串又可能为 NULL 时写成WHERE NVL(name, ) 或者直接WHERE name IS NOT NULL。建表时尽量给字符列默认值不依赖空字符串语义。5.2 SUBSTR、INSTR 的起始位置0、负数和按字符三种错觉现象SUBSTR(ABCDE, 0, 3)返回了ABC和预期一致但代码里写SUBSTR(str, 0, LENGTH(str))的同事觉得下标从 0 开始是安全的接着SUBSTR(ABCDE, -2, 2)返回DE又把负数理解成从后往前数的下标。两者混在一起清洗逻辑就乱了。原因Oracle 的 SUBSTR 把 0 当作 1 处理负数表示从字符串末尾向前定位二者含义完全不同。INSTR 的第三个参数同样是起始位置负数会从尾部向头部找第四参数才是出现次数。解决在项目规范里写明「Oracle 字符串下标从 1 开始0 不报错但等于 1」并在代码评审时重点检查 SUBSTR、INSTR 的负数参数。如果要做从右往左截取建议先用 LENGTH 算好位置再截逻辑更好读。5.3 TO_NUMBER 转换报错格式串与 NLS 参数一起调现象接口表里金额字段是1,234.56直接 TO_NUMBER 报 ORA-01722把格式串写成TO_NUMBER(1,234.56, 9,999.99)后某些会话正常某些会话继续报错。原因默认 NLS_NUMERIC_CHARACTERS 是.,即千分位是逗号、小数点句点但如果会话被改成了.,以外的组合比如很多欧洲系统是.,反过来的同一个格式串在另一个会话里会解析失败。手册里提到显式指定 NLS 参数可以规避但实际项目很少有人会加。解决写成完整三参数形式把千分位和小数点钉死SELECT TO_NUMBER(1.234,56, 9.999,99, NLS_NUMERIC_CHARACTERS,.) FROM dual;在写数据同步脚本和转换接口时这种写法能避免同一份代码在不同会话参数下行为漂移。5.4 REGEXP 默认贪婪匹配替换结果比预期多现象想去掉标签里的一对括号写REGEXP_REPLACE(text, \\(.*\\), )结果从第一对括号到最后一对括号中间的内容全部被删掉只剩开头和结尾。原因.*是贪婪匹配会尽量向右延伸到最后一个闭括号而不是最近的闭括号。Oracle 的正则引擎在 19c 里完全支持 Perl 式量词可以追加?变成非贪婪但很多人没意识到。解决需要匹配最近闭括号时写成SELECT REGEXP_REPLACE(a(b)c(de)f, \\(.*?\\), ) FROM dual;结果只去掉(b)和(de)。字符类方案更稳REGEXP_REPLACE(text, \\([^)]*\\), )明确限定括号内不能有右括号不依赖非贪婪标记建议作为默认写法。5.5 分析函数分页重排ROW_NUMBER 缺少稳定排序列现象一张表用ROW_NUMBER() OVER (ORDER BY create_time)分页某一次联调时发现第 2 页和第 3 页出现了同一行数据且刷新后每页内容还变。原因create_time存在大量相同值排序不唯一Oracle 在同一组相同排序键上的行序不保证稳定因此相邻两次查询的行号可能互换。分析函数本身没错错在排序键没有唯一性兜底。解决排序键追加主键或唯一 ID写成ROW_NUMBER() OVER (ORDER BY create_time, id) AS rn这样能在时间相同的情况下保证顺序稳定。做分页和排行榜时这条应该当成默认规范不光是 Oracle换个数据库同样适用。6. 把函数大全做成一张团队速查表用 SELECT 当回归测试手册是死的但函数行为可以固化成自动核对脚本。我习惯的做法是建一个fn_check.sql每行一个函数断言输入固定样例输出期望值跑完后人工比对。SELECT TRUNC_MM AS fn, TRUNC(DATE 2025-03-18, MM) AS got, DATE 2025-03-01 AS exp FROM dual UNION ALL SELECT LAST_DAY AS fn, LAST_DAY(DATE 2025-03-18) AS got, DATE 2025-03-31 AS exp FROM dual UNION ALL SELECT SUBSTR_0 AS fn, SUBSTR(ABCDE, 0, 3) AS got, ABC AS exp FROM dual UNION ALL SELECT TO_NUMBER_GRP AS fn, TO_NUMBER(1.234,56,9.999,99,NLS_NUMERIC_CHARACTERS,.) AS got, 1234.56 AS exp FROM dual;这套脚本的价值在升级和换环境时最明显。19c 补丁升级、字符集变更、或者从旧库数据泵导入后在测试环境跑一遍把got和exp不一致的行筛出来就能提前暴露行为差异不用等业务侧报表出数了才发现。做法也很简单不存在的函数会在准备阶段报错结果不等于期望值的行一眼可见找开发确认哪一种行为符合业务口径再决定改函数还是改数据。我自己的习惯是每次接手一个新库先跑这套核对脚本再顺手看一下第 2 章的 NLS 参数查询结果两分钟时间能省掉后面一整天的排障。函数大全不是背下来的而是用这套断言脚本沉淀成团队公共资产的。照着这个方向把常用函数逐条补进去三个月后你手里那本 19c 手册才真正变成能用、可验证的落地工具。希望帮到你。本文还有配套的精品资源点击获取