在数据库这行待久了你会发现SQL写得好不好很大程度看你对常用函数的熟练度。前阵子我把手头Oracle相关的函数笔记整理成了HoRain云上一篇《Oracle数据库常用函数大全》不少朋友留言说实用也有人觉得干巴巴的想要更细的讲解。今天我干脆把这份笔记拆开揉碎把最常用的Oracle函数、几个容易踩坑的场景以及性能上需要特别注意的细节一次性讲清楚。这篇文章适合三类人刚接触Oracle的入门开发者想知道某个函数到底该怎么用、什么时候不能用的中级工程师以及平时要写报表SQL和存储过程的运维老手。目标只有一个——让你看完之后能直接在自己库里跑起来用的时候知道每个函数背后的原理而不只是复制粘贴。1. 整体设计思路Oracle函数为什么这么分类Oracle的函数数量非常庞大真要一个个列出来别说背光看官方文档就能看一整天。所以我在整理这份笔记的时候没有按字母顺序罗列而是按照实际业务处理数据的方式把常用函数分成了六大类字符处理、数值计算、日期时间、类型转换、聚合统计、窗口分析。这个分类思路不是我拍脑袋定的而是跟日常工作的场景直接挂钩。比如你从Excel导入数据第一步往往是清洗字符串里的空格和回车这时候用的是字符函数你算订单金额的分成比例要保留两位小数用的是数值函数你按月份汇总报表要在每个月底自动生成数据核心就是日期函数。把每个场景需要用到的函数对应好遇到问题时才知道该翻哪个口袋。选型上还有两个底层考量可读性和兼容性。可读性是指写出来的SQL要让人一眼看明白在干什么比如COALESCE表达“取第一个非空值”就比一串NVL嵌套清晰得多兼容性是指尽量用SQL标准或Oracle长期稳定保留的函数避免用一些冷门的、版本一升级可能被废弃的写法。记住这个大原则再看具体函数就不会迷失。分类典型场景代表函数字符函数清洗、截取、拼接、脱敏SUBSTRINSTRREPLACETRIM数值函数计算、取整、四舍五入ROUNDTRUNCCEILFLOORMOD日期函数统计周期、计算时差SYSDATETRUNCADD_MONTHSLAST_DAY转换函数类型匹配、格式化输出TO_CHARTO_DATETO_NUMBER聚合函数分组汇总、生成报表COUNTSUMAVGMAXMIN分析函数排行、同比、累计、分页ROW_NUMBERRANKLEADLAG2. 字符函数与数值函数数据清洗的两把刷子2.1 字符函数真正常用的就这几个别再背一堆没用的很多人刚学Oracle时会试图把ASCII、CHR、INITCAP这些函数全部背下来其实工作中绝大部分场景用到的字符函数不超过十个。SUBSTR负责截取子串INSTR负责查找子串位置REPLACE做替换LENGTH算长度TRIM/LTRIM/RTRIM处理空格LPAD/RPAD填充对齐CONCAT或||拼接字符串。把这几个吃透就已经覆盖了日常80%的字符处理需求。我举一个真实的例子。运营给了一张Excel表导出的手机号清单很多号码因为单元格格式问题变成了科学计数法比如13812345678变成了1.38123E10。清洗的时候就要先判断原始值是不是包含字母E再用INSTR定位位置、用SUBSTR拼接还原。类似这种场景靠的不是某个冷门函数而是基础函数的组合运用。还有字符串脱敏比如在页面上展示用户姓名时中间打星号。SQL里可以这么写取第一个字符拼三个星号再取最后一个字符。如果用SUBSTR处理中文时记得搭配字节和字符的概念LENGTH统计的是字符数LENGTHB统计的是字节数一个中文在UTF-8下占三个字节在GBK下占两个字节。搞混这两个函数最容易在定长导出的场景里翻车。-- 手机号脱敏保留前3位和后4位 SELECT 138****5678 AS mask_phone FROM dual; -- 更通用的写法根据原始手机号动态生成 SELECT SUBSTR(13812345678, 1, 3) || **** || SUBSTR(13812345678, 8, 4) FROM dual;2.2 过滤不可转为数字的字符串比你想的容易踩坑这次有很多人搜“oracle 过滤不可转为数字的字符串”这个话题确实值得单独说。最直接的思路是用REGEXP_LIKE做正则匹配只保留纯数字的记录。比如某个字段是VARCHAR2类型里面混了12345和abc123需要清洗掉后者SELECT col FROM my_table WHERE REGEXP_LIKE(col, ^[0-9]$);这个写法简单直观但有个隐患如果这张表数据量上千万正则表达式无法走普通索引必然全表扫描。性能敏感的生产环境更推荐使用Oracle 12c引入的VALIDATE_CONVERSION函数它能安全地判断某个字段能否转成指定类型不会因为转换失败直接报ORA-01722而且配合基于函数的索引还能优化性能。SELECT col FROM my_table WHERE VALIDATE_CONVERSION(col AS NUMBER) 1;注意VALIDATE_CONVERSION是12c的新功能老版本库没法用。如果是11g可以先用TRANSLATE把数字字符剔除看剩下是否为空来判断原理上可行但要注意空格和正负号等边界值。这个方法我实际用过要点是先把0123456789映射成10个空格再判断结果是否全为空。2.3 数值函数ROUND和TRUNC的区别一定要刻在脑子里数值函数里ROUND和TRUNC这对兄弟是出场率最高的也是最容易搞混的。ROUND(45.926, 2)结果是45.93会四舍五入TRUNC(45.926, 2)结果是45.92直接截断不进位。金额计算场景必须用ROUND而计算分页偏移量、取整数部分时用TRUNC更合适。除了这两个CEIL向上取整、FLOOR向下取整在处理库存、并发数这类“必须按整数个算”的场景里也很实用。比如计算数据库连接池需要的最小连接数如果每个实例需要2.5个连接那不管资源怎么分配连接数都得用CEIL向上取整到3。MOD取余数则常用于分片逻辑比如按用户ID分表时MOD(user_id, 16)可以把用户均匀分布到16张分表里。我在做金额计算时特别强调一个问题二进制浮点的精度坑。Oracle的NUMBER类型本质上是十进制存储但如果你把字段定义成了BINARY_DOUBLE计算0.1加0.2就可能得到0.30000000000000004这种结果。所以对账、计费这类系统字段类型尽量选择NUMBER别为了“性能”选浮点类型那点性能差异顶不住财务核对时的痛苦。3. 日期处理Oracle的强项也是重灾区3.1 必会的日期函数SYSDATE和TRUNC是入门第一关Oracle的日期处理能力极强却也坑过无数新手。最核心的两个函数是SYSDATE和TRUNC。SYSDATE返回服务器当前时间带时分秒TRUNC则可以对日期做截断TRUNC(SYSDATE)返回当天的零点TRUNC(SYSDATE, MM)返回当月第一天TRUNC(SYSDATE, YYYY)返回当年第一天。这个函数在按月、按年统计报表时特别好用。具体来说要统计今天的数据很多新手会这么写SELECT * FROM orders WHERE create_date TRUNC(SYSDATE);这段SQL看起来没问题但实际执行时如果create_date是DATE类型且不带时分秒那还好一旦带时分秒这个等值条件就查不到当天晚些时候下的单。正确写法是用区间SELECT * FROM orders WHERE create_date TRUNC(SYSDATE) AND create_date TRUNC(SYSDATE) 1;第二个坑是ADD_MONTHS。很多人在计算“上个月”时习惯用SYSDATE - 30但每个月的天数不一样2月和3月的差距尤其明显。正确做法是用ADD_MONTHS(TRUNC(SYSDATE, MM), -1)获取上个月第一天再用LAST_DAY获取上个月最后一天。这样不管这个月是28天还是31天统计区间永远准确。3.2 毫秒时间戳转日期格式Java和Oracle之间的桥热搜词里还有一个“oracle毫秒转换日期格式”这典型出现在Java应用把System.currentTimeMillis()毫秒值存到了数据库字段里。Oracle的常规日期类型精确到秒要处理毫秒可以用TIMESTAMP如果字段存的只是数字那就得手动转换。转换公式其实不复杂Unix时间戳是从1970年1月1日零点开始算的秒数。拿到毫秒值除以1000得到秒再除以86400得到天数然后加到基准日期上。-- 毫秒值1700000000000 SELECT TO_DATE(1970-01-01 00:00:00, YYYY-MM-DD HH24:MI:SS) 1700000000000 / 1000 / 86400 AS converted_date FROM dual;注意时区问题。这里用的是服务器所在时区的数据库时间如果应用服务器和数据库服务器时区不一致写日志时间时会出现8小时偏差。国内环境如果统一是北京时间一般没大问题但如果是跨时区的全球化应用建议在上层就把时间统一转换成UTC入库时再转北京时间展示。3.3 用EXTRACT和TO_CHAR格式化日期满足各种报表要求EXTRACT函数可以从日期中取出年份、月份、日、季度、星期等部分比如EXTRACT(YEAR FROM SYSDATE)、EXTRACT(MONTH FROM SYSDATE)。这个函数的优势是语义清晰缺点是不能用来取“季度”这一级别季度得用TO_CHAR(SYSDATE, Q)。TO_CHAR搭配日期格式符是另外一个高频场景。TO_CHAR(SYSDATE, YYYY-MM-DD HH24:MI:SS)是最常见的格式其中HH24表示24小时制MI表示分钟SS表示秒。如果你用HH而没有用HH24下午两点的数据会被格式化成02而不是14报表排序的时候就会出问题。日期格式符里还有个IW表示ISO周RR表示两位年份的滑移规则这两个很少用但真要处理跨世纪数据时是救命的。4. 转换函数与隐式转换有些坑不需要自己踩4.1 TO_CHAR、TO_NUMBER、TO_DATE三大金刚类型转换函数是日常开发中连接不同数据类型的桥梁。TO_CHAR把数字或日期转成字符串TO_NUMBER把字符串转成数字TO_DATE把字符串按指定格式转成日期。这三个函数本身不复杂复杂的是它们和NLS参数、格式模板的耦合。举一个真实的翻车案例。开发写了一段SQL处理订单金额展示TO_CHAR(amount)结果1250.5被转成了1250.5没问题但1000却被转成了1000展示层要求必须是小数的格式于是只能额外拼接.00。其实直接指定格式模板就能一劳永逸TO_CHAR(amount, FM999G999D00)其中FM去掉前导空格G是千位分隔符D是小数点。这里要提醒一下FM加0和9的组合要理解清楚0强制显示9有值才显示。日期转换更容易踩NLS的坑。TO_DATE(2024-03-15, YYYY-MM-DD)很安全因为格式模板指定了。但如果你写TO_DATE(2024-03-15)Oracle就会用会话的NLS_DATE_FORMAT参数来决定怎么解析一旦参数不是YYYY-MM-DD直接报ORA-01861或者更糟的是被解析成了错误含义的日期。别问我是怎么知道的都是吃过亏换来的教训。4.2 隐式转换的代价为什么SQL突然变慢了隐式转换是数据库自己偷偷做的类型转换。比如你有个VARCHAR2类型的字段存的是工号查询时你写WHERE emp_no 12345Oracle会尝试把emp_no隐式转成数字表面上SQL不报错但结果就是该字段上的索引没法正常使用性能损耗极其明显。看执行计划你就能发现Type列里出现了SYS_AUX或者TO_NUMBER(EMP_NO)说明Oracle对每一行都执行了转换操作。我的建议是日常查询中写条件时让字段本身的类型和比较值类型保持一致能省掉大量隐性性能问题。日期和字符串隐式转换的方向更隐蔽。比如WHERE create_date 2024-01-01如果create_date是DATE类型Oracle会默认把字符串转成日期能不能安全解析取决于NLS参数。更推荐的做法是显式写TO_DATE(2024-01-01, YYYY-MM-DD)这样既明确语义也不让Oracle猜。4.3 DUAL表的神奇之处单行单列也能玩出花说到转换函数和测试就绕不开DUAL表。很多初学者好奇为什么SELECT 11 FROM dual能执行dual到底是什么。简单说它是Oracle提供的一个特殊单行单列表专门用来保证SELECT语句有完整的FROM子句。当你不需要查询任何真实表又想调用函数或计算表达式时就写FROM dual。网上有人问“dual最多存多大”其实这是个概念误区它根本不是为了存业务数据设计的理论上无法也不需要往里塞大量记录。它的价值在于测试比如你想快速验证某个函数的行为不需要建一张临时表直接SELECT ROUND(3.14159, 2) FROM dual;就出结果。我排查问题时经常用它试TO_CHAR的格式符、测NVL的空值行为效率非常高。5. 聚合函数与窗口函数从普通报表到复杂统计5.1 聚合函数使用要点COUNT的细节能看出一个人的功力聚合函数大家都会用但细节未必清楚。COUNT(*)统计的是行数包括所有列都是NULL的行COUNT(1)和COUNT(*)在Oracle里效果基本一致不会因为多一个字段而额外读取但COUNT(某个字段)只统计该字段非NULL的行数。这就是为什么有时候你数行数用COUNT(字段)会比COUNT(*)少很可能就是这个字段存在空值。SUM和AVG天然忽略NULL值。比如三个订单的金额分别是100、NULL、200AVG(amount)的结果是150而不是100。很多做报表的人写出AVG才发现跟预期对不上其实问题就出在NULL的处理上。如果业务上NULL应该当成0参与计算就必须用NVL(amount, 0)包裹后再聚合。还有GROUP BY的一个经典报错ORA-00979不是GROUP BY表达式。出现这个错误多半是SELECT里出现的非聚合列没有全部放进GROUP BY。举个例子SELECT dept_id, emp_name, SUM(salary) FROM emp GROUP BY dept_id就是错的因为emp_name既没聚合也没分组。Oracle对SQL标准的限制比MySQL严格这种错误一报一个准但反过来也提醒你写报表时要用聚合函数就必须想清楚哪些列是明细维度哪些列是聚合结果别把两者混在一个查询里。5.2 ROLLUP、CUBE、GROUPING SETS报表小计总有更优雅的写法做月度、季度、年度汇总时最土的方式是写多个SQL再UNION数据量小还好数据量大了不仅代码冗长还会多次扫描同一张表。ROLLUP和CUBE能让数据库在一次扫描里同时算出小计和总计不仅省事执行效率也高出不少。ROLLUP(a, b)会按(a,b)、(a)、()三个层级分别聚合适合做带小计的报表CUBE(a, b)则会把所有维度组合都算一遍(a,b)、(a)、(b)、()四组适合多维交叉分析。GROUPING SETS则更灵活可以显式指定想要的组合层级不会像CUBE那样生成一堆用不到的行。实战中的体会是这类分组函数写出来的SQL初看有点难懂但把结果导出后你会发现结构非常规整每个层次用GROUPING函数做标记再配合DECODE把小计行显示成“小计”文字报表前端几乎不用做二次加工。如果你还在用UNION拼接多个统计结果建议尽早切换到这几个函数。5.3 窗口函数ROW_NUMBER不只是分页Oracle 12c之后分页有OFFSET ... FETCH语法但更常见的写法还是基于ROW_NUMBER()窗口函数或者在老版本里直接用ROWNUM伪列。窗口函数和普通聚合函数最大的区别它不会把多行合并成一行每一行仍然保留明细数据同时窗口内可以计算汇总、排名、前后行引用这是复杂报表里最需要的。-- 每个部门工资最高的员工ROW_NUMBER版 SELECT dept_id, emp_name, salary FROM ( SELECT dept_id, emp_name, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn FROM emp ) WHERE rn 1;RANK和DENSE_RANK则在排名场景有区别RANK遇到并列名次会跳过后续名次比如1、1、3DENSE_RANK则不会跳过1、1、2。具体选哪个就看业务需求是“获奖人数固定”还是“排名连续”。LEAD和LAG可以取当前行的前一行或后一行的值非常适合计算同环比、相邻时间点差值比自连接表干净得多。窗口函数的执行顺序也很关键它是在WHERE、GROUP BY之后、ORDER BY之前执行的。这意味着你没法在WHERE里直接过滤窗口函数的结果必须嵌套一层子查询。这个机制刚接触时很容易踩坑但只要记住“先过滤再开窗”这个顺序写起来就不会混乱。6. 常见问题与排查技巧实录6.1 SQL语句运行慢先看执行计划和隐式转换有位朋友问我为什么同样一段查询在测试库毫秒级返回到了生产库却要跑几十秒。我让他把执行计划发过来一眼就看到了隐式转换的标志。再一查原来生产库的字段类型是VARCHAR2但代码里传的是数字参数。这个问题的本质在于参数类型与列类型不匹配导致Oracle放弃了索引。排查这类问题的顺序建议是先看是否对索引列做了函数运算或隐式转换再看是否因OR条件导致索引合并失效最后看统计信息是否过期导致CBO选错执行计划。不要一上来就加索引很多时候索引没失效只是SQL写法让优化器无法用上。6.2 sqlplus登录缓慢或报错监听器和会话状态排查sqlplus登录慢很多情况下不是数据库本身的问题而是网络解析、监听注册和连接验证这几个环节。最常见的症状是敲完用户名密码后要等十几秒才出现SQL提示符排查时可以按这样的顺序先确认sqlnet.ora里的SQLNET.AUTHENTICATION_SERVICES配置再看监听器是否注册成功最后检查tnsnames.ora的地址解析。如果是域名方式还要确认DNS反解是否正常。Oracle的监听服务无法启动也是高频问题。我遇到过一个案例是listener.ora里的端口被别的进程占用了启动报错却提示的是权限不足。排查时先netstat看1521端口状态再用lsnrctl status确认监听状态最后检查监听日志。这里有个小技巧监听日志和告警日志都是排查数据库问题的第一手资料路径通常位于$ORACLE_HOME/diag/rdbms/dbname/sid/trace下遇到报错先翻日志比瞎猜靠谱得多。查看当前会话和活动SQL用v$session视图就够了。很多运维朋友说“oracle 查看会话sid”其实就是查这张视图。长时间运行的SQL、阻塞会话、当前连接数都能从这个视图获取关键信息配合v$sqltext和v$sqlarea还能定位正在执行的SQL内容。6.3 字符串判断、空值陷阱和Excel导入清洗的实战经验判断某个字符串是否包含目标子串核心函数是INSTR。INSTR(abc123, 123)返回4表示从第4个字符开始匹配如果不包含就返回0。所以逻辑判断要写成WHERE INSTR(column, 关键字) 0。有些同事习惯用LIKE %关键字%包含场景下两者效果类似但INSTR配合函数索引的优化空间更大而且语义更明确。空值陷阱则是NVL、NULLIF、COALESCE的舞台。NVL(a, 0)把NULL替换成0NULLIF(a, b)在a等于b时返回NULLCOALESCE(a, b, c, ...)返回第一个非NULL值。COALESCE是SQL标准函数参数数量不限推荐优先使用。还有一个容易忽视的点是字符串里的空字符串在Oracle里被当作NULL处理所以和NULL在判断时要统一考虑否则容易出现“查不到数据”或者“多出数据”的诡异结果。最后说Excel导入数据库。把Excel导入Oracle很多人用工具直接导结果数字变成文本、文本变成数字、日期乱套。我建议导入前先在Excel里做三步预处理第一把所有列格式统一成文本避免科学计数法和前导零丢失第二删除不可见字符尤其是CHAR(10)换行符和CHAR(13)回车符否则导入后字段里藏着换行会影响后续比对第三日期列统一用YYYY-MM-DD HH24:MI:SS格式字符串入库时再显式转换避免工具自动识别错误。这些前置工作看着繁琐却能把后期清洗SQL的时间省下一大半。我自己这些年写SQL有个习惯每做一个报表或接口都会把用到的函数和写法沉淀成笔记。整理HoRain云那份函数笔记的过程也是这样一边整理一边发现很多知识点——比如VALIDATE_CONVERSION、COALESCE、窗口函数的执行顺序——都是从实际故障里反推出来的。如果这篇文章能帮你少踩一个坑哪怕只有一个写出来也值了。