尧图网页设计 
1. 为什么开发老手还要回头啃函数文档干Oracle这行十年我最深的体会是真正拉开SQL水平差距的不是那些花哨的架构设计而是对常用函数的熟练程度。很多看起来复杂的业务问题拆到根上就是几个函数的组合运用。前阵子我接手一个数据迁移项目上游给的Excel里电话号码有的是138****1234这种带星号的有的是前面带空格和86的还有的中间混了全角字符。当时组里新来的同事打算写存储过程循环清洗我拦住了让他先去看看TRANSLATE、REGEXP_REPLACE和TRIM这几个函数的组合用法。半小时后他跑过来说搞定了一条UPDATE语句三千行脏数据一次清完。这就是函数的威力。这篇东西是我多年用Oracle的真实积累面向的人群很明确刚入门想系统学Oracle函数的干了一两年遇到问题还要翻文档的以及写了好几年SQL但从来没深究过函数底层逻辑的。我会把最常用、最能解决实际问题的函数拆开讲每个都配上可以照抄的示例。提示本文所有示例都基于Oracle 11g及以上版本12c、19c都能直接跑。使用的是自带的EMP和DEPT演示表结构你手头没有这两张表的话用任何业务表替换即可。先说清楚一个概念Oracle里的函数分两大类单行函数和聚合函数。单行函数接受一行输入返回一个结果比如UPPER(abc)返回 ABC聚合函数对多行数据进行汇总比如SUM(salary)返回所有工资的总和。日常开发中单行函数用得最频繁也是本文的重点。还有个必须提的隐藏角色——DUAL表。刚入行的同事老问我查个SELECT 11为什么要带FROM dual因为Oracle的SELECT语句必须有FROM子句而DUAL是Oracle提供的一个伪表只有一行一列专门用来计算常量表达式、调用函数做测试。你可以把它理解成一个不用建表就能进行函数实验的工作台。SELECT SYSDATE FROM dual;返回当前日期SELECT UPPER(hello) FROM dual;返回 HELLO。这个习惯一定要养成记住它你后面调试任何函数都会方便很多。2. 字符串函数90%的SQL性能问题都出在错误使用上字符串处理是使用频率最高的函数类别。我见过太多人把字符串函数嵌套七层八层还不明白为什么慢也见过有人放着高效的INSTRSUBSTR不用非要用LIKE去暴力匹配。这里我把最核心的几个函数逐个过一遍。2.1 TRIM家族不只是去空格TRIM、LTRIM、RTRIM是三个专门处理字符串空白的函数。很多新手只知道TRIM能去两端的空格其实它有三件事能做去掉两端指定字符、只去左边、只去右边。-- 去掉两端空格最常见用法 SELECT TRIM( Hello World ) FROM dual; -- 结果: Hello World -- 只去左侧空格 SELECT LTRIM( Hello) FROM dual; -- 结果: Hello -- 只去右侧空格 SELECT RTRIM(Hello ) FROM dual; -- 结果: Hello -- 去掉指定字符注意只能指定一个字符 SELECT TRIM(x FROM xHellox) FROM dual; -- 结果: Hello这里有个使用误区TRIM默认去除的是空格包括普通空格不是制表符\t也不是换行符\n。如果数据里混入制表符TRIM是不会生效的需要配合REGEXP_REPLACE来去掉全部空白字符。另一个坑是我踩过好几次的LTRIM和RTRIM的第二个参数是字符集合不是字符串。比如LTRIM(abcxabc, abc)它会把左边出现的 a、b、c 任意字符全部去掉所以结果是 xabc。很多人以为它会像TRIM(abc FROM ...)那样去掉整个abc前缀结果查出来数据不对怎么调都不对。这个细节非常容易出生产事故尤其是做数据清洗脚本时。2.2 SUBSTR与INSTR坐标定位的黄金组合SUBSTR负责截取字符串片段INSTR负责定位子串位置。这俩配合可以实现几乎所有字符串抽取需求。-- 从第2位开始截取4个字符 SELECT SUBSTR(HelloWorld, 2, 4) FROM dual; -- 结果: ello -- 从倒数第3位开始截取2个字符 SELECT SUBSTR(HelloWorld, -3, 2) FROM dual; -- 结果: ld -- 查找字符串中o第一次出现的位置 SELECT INSTR(HelloWorld, o) FROM dual; -- 结果: 5 -- 从第6位开始查找o第二次出现的位置 SELECT INSTR(HelloWorld, o, 6, 2) FROM dual; -- 结果: 7INSTR的完整参数格式是INSTR(源字符串, 目标子串, 起始位置, 第几次出现)这个函数在分析复杂文本时极其有用。举个例子我要从身份证号里提取出生日期身份证号是18位的出生日期占第7到14位SELECT SUBSTR(110101199003076655, 7, 8) FROM dual; -- 结果: 19900307再比如URL地址https://example.com/api/v1/users里要提取路径部分可以用INSTR先找到/第二次出现的位置再配合SUBSTR截取。这就是坐标定位的思路——用INSTR找到坐标用SUBSTR截内容。我强烈建议你把这两个函数组合练熟因为实际开发中解析日志、拆分报表字段、处理接口返回数据全靠它们。用熟了之后你写SQL就像拿着坐标图经纬度找位置指哪打哪。2.3 REPLACE与TRANSLATE字符替换的天壤之别REPLACE做的是字符串替换TRANSLATE做的是字符映射。两者效果完全不同很多人混着用代码能跑结果永远是错的。-- REPLACE: 把ab替换成12 SELECT REPLACE(abcabc, ab, 12) FROM dual; -- 结果: 12c12c -- TRANSLATE: 字符集映射逐字符对应替换 SELECT TRANSLATE(abcabc, ab, 12) FROM dual; -- 结果: 12c12c单看这个例子两者结果一样容易误以为可以互相替代。看下一个例子-- 把a替换成xb替换成yc替换成z SELECT TRANSLATE(abc, abc, xyz) FROM dual; -- 结果: xyz REPLACE在这里做不到一一映射它只能替换整个子串实际开发中TRANSLATE有个杀手级用法批量删除字符。把第三个参数设为空串所有映射到的字符都会被删掉。比如清理手机号里的特殊符号SELECT TRANSLATE(138-1234-5678, - , ) FROM dual; -- 结果: 13812345678这里我把 - 和空格都映射到空串等于同时删掉了所有短横线和空格。这个技巧在数据清洗里特别好用一条SQL解决一批脏字符不需要写一堆嵌套的REPLACE。2.4 正则表达式REGEXP_LIKE与REGEXP_REPLACE如果你的SQL支持正则复杂字符串处理会从登山式嵌套变成一键直达。Oracle的正则函数主要有REGEXP_LIKE匹配判断、REGEXP_REPLACE正则替换、REGEXP_SUBSTR正则截取和REGEXP_INSTR正则定位。我最常用的是字符串清洗场景。比如很多外部系统导入的数据把手机号存成了各种乱七八糟的格式——有的是138 1234 5678有的是138-1234-5678有的是8613812345678甚至还有13812345678。全部归一化-- 只保留数字其他全部删除 SELECT REGEXP_REPLACE(138-1234-5678, [^0-9], ) FROM dual; -- 结果: 13812345678还比如过滤掉不能转为数字的字符串这个实际问题很多做数据接入的都遇到过。上游接口传来一个字段叫amount本应该是纯数字但总有几个角落里的记录混入了字母或者千分位逗号。你直接用TO_NUMBER(amount)会直接报ORA-01722: invalid number整条SQL直接挂掉。正则函数可以提前把不可转的识别出来、或预处理掉再转-- 找出amount里混了非数字字符的记录 SELECT * FROM payment_log WHERE REGEXP_LIKE(amount, [^0-9.]); -- 兼容性处理先把非数字字符删除再转 SELECT TO_NUMBER(REGEXP_REPLACE(amount, [^0-9.], )) AS clean_amount FROM payment_log;但注意这只是在函数层面做兜底生产环境我依然建议在数据接入层就把这类脏数据清洗掉而不是每次都靠SQL硬扛。至于有些字符是中文括号、全角数字之类的正则结合TRANSLATE也能做到。关于正则性能我多说一句正则函数看似方便但它是CPU密集型操作如果一张表几百万行数据每行都跑一遍REGEXP_REPLACE查询能慢到你怀疑人生。能不用正则解决的尽量用INSTR、SUBSTR、TRANSLATE这些基础函数确实需要再复杂匹配的时候优先考虑在数据写入阶段就标准化字段格式而不是查询阶段频繁跑正则。2.5 LPAD与RPAD格式化填充填充函数LPAD和RPAD使用场景偏少但一旦用到就是不可替代的。一个典型场景是生成固定长度的编号。比如订单号要求8位不够左边补零SELECT LPAD(123, 8, 0) FROM dual; -- 结果: 00000123还有打印报表时需要在数字后面补空格对齐的RPAD就很顺手。注意如果源字符串长度超过目标长度这两个函数是直接截断的不会补字符。3. 数值与日期函数坑最多也最容易被忽略数值和日期函数在财务核算、统计报表里是常客这里面的坑远比字符串函数多。我会把最常用的数值函数和日期函数分别展开日期部分尤其要讲清楚TRUNC和SYSDATE的配合用法。3.1 ROUND与TRUNC四舍五入和直接截断的天壤之别ROUND做四舍五入TRUNC做直接截断。这俩在金额计算上有本质区别。-- ROUND四舍五入 SELECT ROUND(1234.567, 2) FROM dual; -- 结果: 1234.57 SELECT ROUND(1234.567, -2) FROM dual; -- 结果: 1200 -- TRUNC直接截断 SELECT TRUNC(1234.567, 2) FROM dual; -- 结果: 1234.56 SELECT TRUNC(1234.567, -2) FROM dual; -- 结果: 1200第二个参数为负数时表示小数点左侧进位方向截断这个负的位参数很多人不知道。它俩在会计对账里必须谨慎少一分钱就是事故。涉及金额累加时建议先确认业务规则四舍五入还是截断跟财务确认清楚别自己拍脑袋。3.2 常用聚合函数COUNT、SUM、AVG的隐藏细节聚合函数用得最多但细节坑也不少。COUNT(1)和COUNT(*)在Oracle里性能区别不大COUNT(列名)却不统计NULL值。这个差异极具误导性SELECT COUNT(*), COUNT(commission_pct) FROM employees; -- 如果commission_pct列有NULL值这两个结果不一样SUM和AVG遇到NULL值会直接跳过而不是按0计算。这会导致AVG结果与直觉不符——比如你求10个人的平均工资其中5个人工资字段是NULLAVG只把5个非空值加起来除以5而不是除以10。如果业务上希望NULL当0处理需要先NVL转换SELECT AVG(NVL(salary, 0)) FROM employees;GROUP BY的使用有一个极易忽略的规则SELECT列表中出现的非聚合列必须全部出现在GROUP BY中。比如SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id;没问题但你多选一个employee_nameOracle直接报ORA-00979不是正确GROUP BY表达式。这是因为你无法确定每组返回哪一行除非用分析函数或子查询。3.3 日期运算SYSDATE、TRUNC与格式转换Oracle的日期类型DATE本质上是个数值整数部分是日期小数部分是时间。这就导致日期可以直接加减数字-- 现在时间 SELECT SYSDATE FROM dual; -- 昨天和明天 SELECT SYSDATE - 1, SYSDATE 1 FROM dual; -- 两小时后 SELECT SYSDATE 2/24 FROM dual; -- 30分钟后 SELECT SYSDATE 30/1440 FROM dual;这里有个隐藏背景Oracle日期加减单位是天所以小时要除以24分钟要除以1440秒要除以86400。刚入门的朋友总是忘记这个直接写SYSDATE 30以为加了30分钟实际上加的是30天。TRUNC用在日期上直接砍掉时间部分这个在当天数据统计里非常关键-- 去掉时分秒只保留日期 SELECT TRUNC(SYSDATE) FROM dual; -- 当月第一天 SELECT TRUNC(SYSDATE, MM) FROM dual; -- 当年第一天 SELECT TRUNC(SYSDATE, YYYY) FROM dual; -- 当前周的第一天周一 SELECT TRUNC(SYSDATE, IW) FROM dual;第三点是最容易出错的Oracle查询某一天的数据不要用WHERE date_col TO_DATE(2024-01-01, YYYY-MM-DD)。因为date_col里带有时间只要不是2024-01-01 00:00:00就匹配不上。正确写法-- 方式一TRUNC包裹列牺牲一点索引 WHERE TRUNC(date_col) DATE 2024-01-01 -- 方式二范围查询走索引更高效推荐 WHERE date_col DATE 2024-01-01 AND date_col DATE 2024-01-02关于TO_DATE、TO_CHAR的格式模型我把它单独列一节因为它牵涉到毫秒转日期、字符串转日期等极其常见的需求。3.4 毫秒级时间戳转换一张格式表Oracle的毫秒时间戳在很多系统对接中都会遇到。Java后端存了一个1696060800000这样的毫秒数要在数据库层直接转换成日期显示总不能每次都查出来再在Java里转。-- 毫秒转日期 SELECT TO_DATE(1970-01-01, YYYY-MM-DD) 1696060800000 / 1000 / 86400 FROM dual; -- 结果是 2023-09-30 16:00:00也就是UTC时间要注意时区 -- 转成中国标准时间东八区8小时 SELECT TO_DATE(1970-01-01, YYYY-MM-DD) 1696060800000 / 1000 / 86400 8/24 FROM dual;日期转毫秒就是逆运算SELECT (SYSDATE - TO_DATE(1970-01-01, YYYY-MM-DD)) * 86400 * 1000 FROM dual;常用格式模型我整理成一张速查表格式含义示例输出YYYY四位年份2024YY两位年份24MM两位月份11MON月份缩写11月DD两位日期23D周几1-71周日DY星期缩写星期六HH2424小时制小时17HH12小时制小时05MI分钟30SS秒45Q季度4WW当年第几周47使用规范上我提两点TO_CHAR转出来的全是字符串不要拿它再和数字比较TO_DATE对格式要求严格输入字符串和格式模型对不上就报ORA-01843。遇到ORA-01847、ORA-01830这类日期转换报错多半是字符串里混进了非法日期或者格式模型写错了。4. 转换函数与NULL处理报表不出错的关键底线数据类型转换和NULL值处理是移交给BI报表之前最容易出问题的一环。TO_NUMBER遇到非数字字符直接报错NVL和NVL2用得好能省一堆CASE WHEN。这一节我会把类型转换和NULL处理的完整套路都过一遍。4.1 TO_CHAR、TO_NUMBER、TO_DATE三大转换TO_CHAR可以把日期、数字转成字符串方便格式化显示TO_NUMBER把字符串转成数字TO_DATE把字符串转成日期。这三个是类型转换的核心工具。-- 数字格式化为货币样式 SELECT TO_CHAR(12345.678, L99G999D99) FROM dual; -- 结果: 12,345.68 -- 日期格式化为字符串 SELECT TO_CHAR(SYSDATE, YYYY年MM月DD日) FROM dual; -- 字符串转数字 SELECT TO_NUMBER(123.45) FROM dual; -- 字符串转日期 SELECT TO_DATE(2024-11-23, YYYY-MM-DD) FROM dual;注意TO_NUMBER转的字符串里不能有千分位逗号、货币符号等字符否则直接报错。所以前面提到过滤不可转数字的字符串核心思路是先判断再转换或者先清洗再转换。生产环境里我最常用的方案是写一个判断函数或者用CASE WHEN先判断-- 方案一REGEXP_LIKE先判断是否纯数字不含小数点 SELECT CASE WHEN REGEXP_LIKE(amount, ^[0-9]$) THEN TO_NUMBER(amount) ELSE 0 END FROM payment_log; -- 方案二允许小数和负数的写法 SELECT CASE WHEN REGEXP_LIKE(amount, ^-?[0-9](\.[0-9])?$) THEN TO_NUMBER(amount) ELSE NULL END FROM payment_log;如果amount列里还有千分位逗号先清理再判断SELECT CASE WHEN REGEXP_LIKE(REPLACE(amount, ,, ), ^[0-9](\.[0-9])?$) THEN TO_NUMBER(REPLACE(amount, ,, )) ELSE NULL END FROM payment_log;这个写法看着烦但在生产环境它就是能扛住脏数据的底线方案。另外提醒这些判断在查询大表时也会有明显性能消耗最好在数据入库阶段就完成清洗查询阶段只为历史脏数据兜底。4.2 NVL、NVL2、NULLIF与COALESCE的四兄弟Oracle里空值处理函数不少各有各的适用场景。NVL(a, b)a是NULL时返回b否则返回a。NVL要求两个参数类型一致否则Oracle会尝试隐式转换类型不兼容时直接报ORA-01790。所以NVL(commission, 0)没问题但NVL(commission, N/A)就会报错。NVL2(a, b, c)a不是NULL返回ba是NULL返回c。这个比NVL更灵活。比如佣金列有值就显示有佣金没值显示无佣金SELECT NVL2(commission_pct, 有佣金, 无佣金) FROM employees;NULLIF(a, b)a等于b时返回NULL否则返回a。常用于防止除数为0。比如求上个月和这个月的增长率如果两个月销售额相同NULLIF可以避免得到0/0的除法错误。COALESCE(a, b, c, ...)从左到右依次返回第一个非NULL值。和NVL的差别在它可以接收多个参数只要有一个不是NULL就返回。实际开发中这个函数非常实用一个字段为空取另一个再为空取默认值-- 手机号为空取联系电话再为空取紧急电话 SELECT COALESCE(mobile_phone, phone, emergency_phone, 无联系电话) FROM customer;NVL和COALESCE的行为看似一样区别是COALESCE是SQL标准而NVL是Oracle独有。做跨数据库迁移的时候尽量用COALESCE这能减少改动量。不过这俩执行计划上效率都差不多不用过度纠结性能。4.3 DECODE与CASE WHEN一文一武DECODE是Oracle特有的条件函数CASE WHEN是SQL标准。两者功能高度重叠但写法风格不同。-- DECODE写法 SELECT ename, DECODE(deptno, 10, 财务部, 20, 研发部, 30, 销售部, 其他部门) AS dept_name FROM emp; -- CASE WHEN写法 SELECT ename, CASE WHEN deptno 10 THEN 财务部 WHEN deptno 20 THEN 研发部 WHEN deptno 30 THEN 销售部 ELSE 其他部门 END AS dept_name FROM emp;个人建议新代码都写CASE WHEN理由是它的可读性更好、跨数据库兼容性更强。但老系统里到处都是DECODE你得能读懂它。DECODE还有个小特性对NULL做等值判断时有特殊行为DECODE(col, NULL, 0, col)能正确处理NULL而CASE WHEN col NULL的写法永远是假必须写成CASE WHEN col IS NULL。这是一道很经典的面试坑题实际开发中也特别容易中招。5. 分析函数与分页从会用到用得漂亮分析函数是Oracle区别于MySQL这类数据库的重要能力窗口计算、排名、分组汇总都可以在SQL内完成。这一节把最常用的分析函数和经典分页写法讲透。5.1 ROWNUM分页与ROW_NUMBER() OVER两种常用分页方式Oracle分页的传统方案是ROWNUM。ROWNUM是Oracle在结果集生成时赋予每行的序号但它有个特性只在行被返回时才会分配序号。所以你想查第11条到第20条不能直接写成WHERE ROWNUM BETWEEN 11 AND 20因为这永远不会返回任何行——第1条在赋值给第11之前就被过滤掉了。正确做法是先查出来再套一层SELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY sal DESC ) e WHERE ROWNUM 20 ) WHERE rn 11;内层先按工资倒序再取前20行外层再过滤出第11到20行。这个三层嵌套是老一代Oracle工程师的基本功。后来Oracle增加了ROW_NUMBER()分析函数分页顺手多了SELECT * FROM ( SELECT emp.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM emp ) WHERE rn BETWEEN 11 AND 20;ROW_NUMBER()比ROWNUM灵活的地方在于支持PARTITION BY可以按组内编号。比如每个部门里按工资排名取每个部门工资最高的前三名SELECT * FROM ( SELECT emp.*, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn FROM emp ) WHERE rn 3;从使用经验看数据量小、一次性查询两种都无所谓数据量大、频繁分页更建议用ROW_NUMBER()结合绑定变量配合合理的索引设计。5.2 RANK与DENSE_RANK排名热的区别RANK()和DENSE_RANK()都是排名函数区别在于并列之后的下一位RANK()并列会跳号比如两个第1名下一个就是第3名。DENSE_RANK()并列不跳号两个第1名之后还是第2名。SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS rank_跳跃, DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_连续 FROM emp;排名场景用哪个取决于业务含义如果需求是前三名并且只关心有没有进前三RANK和DENSE_RANK都行如果需求是从第3名开始取5个人就涉及语义得算清楚并列有没有占掉名额。奖金排行榜通常用RANK因为并列之后隔过一名符合直觉补录名额时一般用DENSE_RANK。5.3 LAG与LEAD取前后行数据的利器LAG取前面第N行的值LEAD取后面第N行的值。这个在算环比、同比时极其好用。比如算每个人当月工资和上个月工资的差额SELECT ename, hire_date, sal, LAG(sal, 1, 0) OVER (ORDER BY hire_date) AS prev_sal, sal - LAG(sal, 1, 0) OVER (ORDER BY hire_date) AS diff FROM emp;LAG(sal, 1, 0)里的三个参数分别是目标列、往前偏移几行、偏移越界时的默认值。这个默认参数非常关键否则第一行数据没有上一条记录会返回NULL很多统计会直接把这个NULL列为异常实际思路是先补0再处理。LEAD用法对称只是往后看。自连接也能实现同样的效果但分析函数写法简洁得多执行效率通常也更好。5.4 LISTAGG与WM_CONCAT字符串聚合Oracle里拼接多行值为一个字符串最早的经典写法是WM_CONCAT但它是非公开函数说不清什么时候可能被移除新系统不建议再用。官方推荐用LISTAGG-- 把同一部门的员工姓名拼成一个字符串 SELECT deptno, LISTAGG(ename, ,) WITHIN GROUP (ORDER BY ename) AS emp_names FROM emp GROUP BY deptno;LISTAGG有个注意点拼接结果超过4000字符会报ORA-01489: result of string concatenation is too long。处理方式是用XMLAGG替代或者分批拼接或者干脆在程序层拼好再入库。我通常的做法是限制条数或者用SUBSTR截断按业务可接受的最大长度来收敛。-- 超过4000字符时的替代方案XMLAGG SELECT deptno, RTRIM(XMLAGG(XMLELEMENT(e, ename || ,) ORDER BY ename).EXTRACT(//text()), ,) AS emp_names FROM emp GROUP BY deptno;这条复合写法执行效率略低但解决了长度限制的问题。用到这个场景的基本都是报表或导出场景量不大可以接受。6. 存储过程与常见错误排查把这些坑记牢能省三天工时函数写进存储过程之后有一些Oracle特有的执行细节和错误排查点弄明白能少走很多弯路。6.1 SQL%ROWCOUNT与异常处理存储过程里的必备肌肉记忆存储过程里最常用的两个变量就是SQL%FOUND、SQL%ROWCOUNT。我第一次写存储过程时不知道这两个只能先SELECT COUNT(*)再判断大表场景下性能堪忧而且并发修改场景下极不可靠。BEGIN UPDATE emp SET sal sal * 1.1 WHERE deptno 20; DBMS_OUTPUT.PUT_LINE(更新了 || SQL%ROWCOUNT || 行); IF SQL%FOUND THEN COMMIT; ELSE ROLLBACK; END IF; END;关键点是SQL%ROWCOUNT必须紧跟对应的DML语句中间不要插入其他SQL否则它的值会被下一次操作覆盖。这是新手最容易犯的低级错误。还有一个约定俗成的技巧做完DML后用SQL%ROWCOUNT判断是否影响到了预期行数完全没影响到说明数据有异常适合回滚并且记录日志很多状态机更新逻辑这样做能有效避免并发脏写。异常处理常用块BEGIN ...业务逻辑... EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(查无记录); WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(出错: || SQLERRM); END;WHEN OTHERS永远放在最后。有些新手在前面写WHEN OTHERS后面的特定异常就永远不会被捕获了。而且要记得WHEN OTHERS捕获后如果只是DBMS_OUTPUT打印却不ROLLBACK事务可能处于不可预期状态这个我经历过调试时出了半天汗。6.2 SQLPLUS登录缓慢或错误基本排查顺序登录sqlplus缓慢或报错原因常见的有这几类监听没起来、网络不通、密码验证延迟、数据库负载过高。排查顺序建议先tnsping看监听再查看lsnrctl status确认服务状态然后看登录账号的认证方式是否触发了延迟。有一点容易被忽略Linux环境下/etc/hosts解析慢或者DNS不通会导致sqlplus登录卡很久。这个问题的特征是tnsping秒通但登录要十几秒甚至超时。解决办法是把数据库服务器的hostname和IP写到/etc/hosts里。这个坑我帮别人排查过不止一次问题根源往往不在数据库而在系统域名解析。还有一个低级但高频的坑输错了用户名密码Oracle为了安全默认设置了登录失败延迟多次失败后会越来越慢这种时候检查FAILED_LOGIN_ATTEMPTS配置即可。6.3 常量陷阱DUAL表与隐式转换很多开发者习惯直接写WHERE number_col 123Oracle有隐式转换机制多数时候能跑通。但一旦number_col里有非规范数据或者字段类型是VARCHAR2而内容带了空格隐式转换就会引起性能问题甚至结果错误。关于DUAL再补充一个重要场景很多存储过程迟迟调不通是因为在SELECT语句里没有FROM dual。比如在PL/SQL块里写v_date : SYSDATE;没问题但写SELECT SYSDATE INTO v_date;就是不合法必须加上FROM dual。这是Oracle语法和MySQL的一个显著差异。MySQL允许没有FROM的SELECTOracle不允许。跨库迁移代码时这条差异会制造大量小错误需要提前安排批量检查。从数据库迁移角度看Oracle和MySQL的函数体系差异很大分页一个是ROWNUM/OFFSET FETCH一个是LIMIT字符串拼接一个是||一个是CONCAT日期函数一个用SYSDATE一个用NOW()和CURDATE()。如果你在做数据库迁移或同步建议先建一张函数映射对照表把两边命名和语法差异列清楚能省很多调试时间。还有字符串截取在MySQL里叫SUBSTRINGOracle叫SUBSTR细节差异铺开能写很长一篇我这里是先提醒有这个坑。6.4 查看会话与监听常用DBA级命令速查排查数据库性能问题、查看当前会话和监听状态是Oracle从业者绕不开的基本功这里列几个高频命令命令/语句作用使用场景SELECT sid, serial#, username, status FROM v$session;查看当前会话找出阻塞源、清理异常会话ALTER SYSTEM KILL SESSION sid,serial#;杀掉指定会话解决死锁和长时间占用的会话lsnrctl status查看监听状态排查连接不上数据库的问题lsnrctl start/stop启动/停止监听监听挂了之后重新拉起SELECT * FROM v$locked_object;查看锁对象快速定位哪些表被锁了我提一个具体场景开发环境经常出现某张表被锁导致后续更新全部堵塞。先查v$locked_object定位对象再关联v$session拿到sid和serial#最后ALTER SYSTEM KILL SESSION干掉源头。这套动作熟练后处理锁问题基本在一分钟以内。注意在生产环境杀会话前要慎重先确认会话对应的业务最好能联系到发起方盲目杀会话可能把正在跑的批量任务搞挂。7. 30天函数熟练度提升计划最后分享一个我常用的函数学习路线给想系统提升的人一个参考。第一周聚焦字符串函数。每天手写一遍TRIM、LTRIM、RTRIM、SUBSTR、INSTR、REPLACE、TRANSLATE、LPAD、RPAD的常用用例配合DUAL表反复测试。重点理解LTRIM是字符集合而非字符串前缀。第二周攻数值和日期函数。重点练习ROUND和TRUNC的区别把TO_CHAR(SYSDATE, 格式)的常用格式模型背下来特别是YYYY、MM、DD、HH24、MI、SS。日期加减单位换算要练到条件反射。第三周练转换和NULL处理。把TO_CHAR、TO_NUMBER、TO_DATE的报错场景都故意触发一遍记录报错代码。用NVL、NVL2、COALESCE、NULLIF、DECODE、CASE WHEN写一批条件逻辑重点记住DECODE对NULL的处理方式。第四周练分析函数。刻意用ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、LISTAGG各写一个业务场景比如分组TopN、环比计算、字符串聚合。这一周结束时你应该能不看文档写出大多数常用分析函数。我个人实际带人时还会做一件事每个阶段末尾拿一张真实业务表来做一次函数体检比如订单表里找出手机号不规范的数据、清洗掉地址里的脏字符、统计每个区域每个月的销售额环比。因为函数这东西光看文档记不牢只有在真实数据上踩过坑才会真正变成肌肉记忆。Oracle函数体系很大但常用核心就那么几十个先把上面这些吃透日常工作里真正让你卡壳的问题已经能解决80%。剩下的边界情况遇到时再查Oracle SQL Language Reference也来得及——但那应该是定位查询而不是从头学起。 Oracle常用函数实战指南：从字符串到日期处理的效率提升
1. 为什么开发老手还要回头啃函数文档干Oracle这行十年我最深的体会是真正拉开SQL水平差距的不是那些花哨的架构设计而是对常用函数的熟练程度。很多看起来复杂的业务问题拆到根上就是几个函数的组合运用。前阵子我接手一个数据迁移项目上游给的Excel里电话号码有的是138****1234这种带星号的有的是前面带空格和86的还有的中间混了全角字符。当时组里新来的同事打算写存储过程循环清洗我拦住了让他先去看看TRANSLATE、REGEXP_REPLACE和TRIM这几个函数的组合用法。半小时后他跑过来说搞定了一条UPDATE语句三千行脏数据一次清完。这就是函数的威力。这篇东西是我多年用Oracle的真实积累面向的人群很明确刚入门想系统学Oracle函数的干了一两年遇到问题还要翻文档的以及写了好几年SQL但从来没深究过函数底层逻辑的。我会把最常用、最能解决实际问题的函数拆开讲每个都配上可以照抄的示例。提示本文所有示例都基于Oracle 11g及以上版本12c、19c都能直接跑。使用的是自带的EMP和DEPT演示表结构你手头没有这两张表的话用任何业务表替换即可。先说清楚一个概念Oracle里的函数分两大类单行函数和聚合函数。单行函数接受一行输入返回一个结果比如UPPER(abc)返回 ABC聚合函数对多行数据进行汇总比如SUM(salary)返回所有工资的总和。日常开发中单行函数用得最频繁也是本文的重点。还有个必须提的隐藏角色——DUAL表。刚入行的同事老问我查个SELECT 11为什么要带FROM dual因为Oracle的SELECT语句必须有FROM子句而DUAL是Oracle提供的一个伪表只有一行一列专门用来计算常量表达式、调用函数做测试。你可以把它理解成一个不用建表就能进行函数实验的工作台。SELECT SYSDATE FROM dual;返回当前日期SELECT UPPER(hello) FROM dual;返回 HELLO。这个习惯一定要养成记住它你后面调试任何函数都会方便很多。2. 字符串函数90%的SQL性能问题都出在错误使用上字符串处理是使用频率最高的函数类别。我见过太多人把字符串函数嵌套七层八层还不明白为什么慢也见过有人放着高效的INSTRSUBSTR不用非要用LIKE去暴力匹配。这里我把最核心的几个函数逐个过一遍。2.1 TRIM家族不只是去空格TRIM、LTRIM、RTRIM是三个专门处理字符串空白的函数。很多新手只知道TRIM能去两端的空格其实它有三件事能做去掉两端指定字符、只去左边、只去右边。-- 去掉两端空格最常见用法 SELECT TRIM( Hello World ) FROM dual; -- 结果: Hello World -- 只去左侧空格 SELECT LTRIM( Hello) FROM dual; -- 结果: Hello -- 只去右侧空格 SELECT RTRIM(Hello ) FROM dual; -- 结果: Hello -- 去掉指定字符注意只能指定一个字符 SELECT TRIM(x FROM xHellox) FROM dual; -- 结果: Hello这里有个使用误区TRIM默认去除的是空格包括普通空格不是制表符\t也不是换行符\n。如果数据里混入制表符TRIM是不会生效的需要配合REGEXP_REPLACE来去掉全部空白字符。另一个坑是我踩过好几次的LTRIM和RTRIM的第二个参数是字符集合不是字符串。比如LTRIM(abcxabc, abc)它会把左边出现的 a、b、c 任意字符全部去掉所以结果是 xabc。很多人以为它会像TRIM(abc FROM ...)那样去掉整个abc前缀结果查出来数据不对怎么调都不对。这个细节非常容易出生产事故尤其是做数据清洗脚本时。2.2 SUBSTR与INSTR坐标定位的黄金组合SUBSTR负责截取字符串片段INSTR负责定位子串位置。这俩配合可以实现几乎所有字符串抽取需求。-- 从第2位开始截取4个字符 SELECT SUBSTR(HelloWorld, 2, 4) FROM dual; -- 结果: ello -- 从倒数第3位开始截取2个字符 SELECT SUBSTR(HelloWorld, -3, 2) FROM dual; -- 结果: ld -- 查找字符串中o第一次出现的位置 SELECT INSTR(HelloWorld, o) FROM dual; -- 结果: 5 -- 从第6位开始查找o第二次出现的位置 SELECT INSTR(HelloWorld, o, 6, 2) FROM dual; -- 结果: 7INSTR的完整参数格式是INSTR(源字符串, 目标子串, 起始位置, 第几次出现)这个函数在分析复杂文本时极其有用。举个例子我要从身份证号里提取出生日期身份证号是18位的出生日期占第7到14位SELECT SUBSTR(110101199003076655, 7, 8) FROM dual; -- 结果: 19900307再比如URL地址https://example.com/api/v1/users里要提取路径部分可以用INSTR先找到/第二次出现的位置再配合SUBSTR截取。这就是坐标定位的思路——用INSTR找到坐标用SUBSTR截内容。我强烈建议你把这两个函数组合练熟因为实际开发中解析日志、拆分报表字段、处理接口返回数据全靠它们。用熟了之后你写SQL就像拿着坐标图经纬度找位置指哪打哪。2.3 REPLACE与TRANSLATE字符替换的天壤之别REPLACE做的是字符串替换TRANSLATE做的是字符映射。两者效果完全不同很多人混着用代码能跑结果永远是错的。-- REPLACE: 把ab替换成12 SELECT REPLACE(abcabc, ab, 12) FROM dual; -- 结果: 12c12c -- TRANSLATE: 字符集映射逐字符对应替换 SELECT TRANSLATE(abcabc, ab, 12) FROM dual; -- 结果: 12c12c单看这个例子两者结果一样容易误以为可以互相替代。看下一个例子-- 把a替换成xb替换成yc替换成z SELECT TRANSLATE(abc, abc, xyz) FROM dual; -- 结果: xyz REPLACE在这里做不到一一映射它只能替换整个子串实际开发中TRANSLATE有个杀手级用法批量删除字符。把第三个参数设为空串所有映射到的字符都会被删掉。比如清理手机号里的特殊符号SELECT TRANSLATE(138-1234-5678, - , ) FROM dual; -- 结果: 13812345678这里我把 - 和空格都映射到空串等于同时删掉了所有短横线和空格。这个技巧在数据清洗里特别好用一条SQL解决一批脏字符不需要写一堆嵌套的REPLACE。2.4 正则表达式REGEXP_LIKE与REGEXP_REPLACE如果你的SQL支持正则复杂字符串处理会从登山式嵌套变成一键直达。Oracle的正则函数主要有REGEXP_LIKE匹配判断、REGEXP_REPLACE正则替换、REGEXP_SUBSTR正则截取和REGEXP_INSTR正则定位。我最常用的是字符串清洗场景。比如很多外部系统导入的数据把手机号存成了各种乱七八糟的格式——有的是138 1234 5678有的是138-1234-5678有的是8613812345678甚至还有13812345678。全部归一化-- 只保留数字其他全部删除 SELECT REGEXP_REPLACE(138-1234-5678, [^0-9], ) FROM dual; -- 结果: 13812345678还比如过滤掉不能转为数字的字符串这个实际问题很多做数据接入的都遇到过。上游接口传来一个字段叫amount本应该是纯数字但总有几个角落里的记录混入了字母或者千分位逗号。你直接用TO_NUMBER(amount)会直接报ORA-01722: invalid number整条SQL直接挂掉。正则函数可以提前把不可转的识别出来、或预处理掉再转-- 找出amount里混了非数字字符的记录 SELECT * FROM payment_log WHERE REGEXP_LIKE(amount, [^0-9.]); -- 兼容性处理先把非数字字符删除再转 SELECT TO_NUMBER(REGEXP_REPLACE(amount, [^0-9.], )) AS clean_amount FROM payment_log;但注意这只是在函数层面做兜底生产环境我依然建议在数据接入层就把这类脏数据清洗掉而不是每次都靠SQL硬扛。至于有些字符是中文括号、全角数字之类的正则结合TRANSLATE也能做到。关于正则性能我多说一句正则函数看似方便但它是CPU密集型操作如果一张表几百万行数据每行都跑一遍REGEXP_REPLACE查询能慢到你怀疑人生。能不用正则解决的尽量用INSTR、SUBSTR、TRANSLATE这些基础函数确实需要再复杂匹配的时候优先考虑在数据写入阶段就标准化字段格式而不是查询阶段频繁跑正则。2.5 LPAD与RPAD格式化填充填充函数LPAD和RPAD使用场景偏少但一旦用到就是不可替代的。一个典型场景是生成固定长度的编号。比如订单号要求8位不够左边补零SELECT LPAD(123, 8, 0) FROM dual; -- 结果: 00000123还有打印报表时需要在数字后面补空格对齐的RPAD就很顺手。注意如果源字符串长度超过目标长度这两个函数是直接截断的不会补字符。3. 数值与日期函数坑最多也最容易被忽略数值和日期函数在财务核算、统计报表里是常客这里面的坑远比字符串函数多。我会把最常用的数值函数和日期函数分别展开日期部分尤其要讲清楚TRUNC和SYSDATE的配合用法。3.1 ROUND与TRUNC四舍五入和直接截断的天壤之别ROUND做四舍五入TRUNC做直接截断。这俩在金额计算上有本质区别。-- ROUND四舍五入 SELECT ROUND(1234.567, 2) FROM dual; -- 结果: 1234.57 SELECT ROUND(1234.567, -2) FROM dual; -- 结果: 1200 -- TRUNC直接截断 SELECT TRUNC(1234.567, 2) FROM dual; -- 结果: 1234.56 SELECT TRUNC(1234.567, -2) FROM dual; -- 结果: 1200第二个参数为负数时表示小数点左侧进位方向截断这个负的位参数很多人不知道。它俩在会计对账里必须谨慎少一分钱就是事故。涉及金额累加时建议先确认业务规则四舍五入还是截断跟财务确认清楚别自己拍脑袋。3.2 常用聚合函数COUNT、SUM、AVG的隐藏细节聚合函数用得最多但细节坑也不少。COUNT(1)和COUNT(*)在Oracle里性能区别不大COUNT(列名)却不统计NULL值。这个差异极具误导性SELECT COUNT(*), COUNT(commission_pct) FROM employees; -- 如果commission_pct列有NULL值这两个结果不一样SUM和AVG遇到NULL值会直接跳过而不是按0计算。这会导致AVG结果与直觉不符——比如你求10个人的平均工资其中5个人工资字段是NULLAVG只把5个非空值加起来除以5而不是除以10。如果业务上希望NULL当0处理需要先NVL转换SELECT AVG(NVL(salary, 0)) FROM employees;GROUP BY的使用有一个极易忽略的规则SELECT列表中出现的非聚合列必须全部出现在GROUP BY中。比如SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id;没问题但你多选一个employee_nameOracle直接报ORA-00979不是正确GROUP BY表达式。这是因为你无法确定每组返回哪一行除非用分析函数或子查询。3.3 日期运算SYSDATE、TRUNC与格式转换Oracle的日期类型DATE本质上是个数值整数部分是日期小数部分是时间。这就导致日期可以直接加减数字-- 现在时间 SELECT SYSDATE FROM dual; -- 昨天和明天 SELECT SYSDATE - 1, SYSDATE 1 FROM dual; -- 两小时后 SELECT SYSDATE 2/24 FROM dual; -- 30分钟后 SELECT SYSDATE 30/1440 FROM dual;这里有个隐藏背景Oracle日期加减单位是天所以小时要除以24分钟要除以1440秒要除以86400。刚入门的朋友总是忘记这个直接写SYSDATE 30以为加了30分钟实际上加的是30天。TRUNC用在日期上直接砍掉时间部分这个在当天数据统计里非常关键-- 去掉时分秒只保留日期 SELECT TRUNC(SYSDATE) FROM dual; -- 当月第一天 SELECT TRUNC(SYSDATE, MM) FROM dual; -- 当年第一天 SELECT TRUNC(SYSDATE, YYYY) FROM dual; -- 当前周的第一天周一 SELECT TRUNC(SYSDATE, IW) FROM dual;第三点是最容易出错的Oracle查询某一天的数据不要用WHERE date_col TO_DATE(2024-01-01, YYYY-MM-DD)。因为date_col里带有时间只要不是2024-01-01 00:00:00就匹配不上。正确写法-- 方式一TRUNC包裹列牺牲一点索引 WHERE TRUNC(date_col) DATE 2024-01-01 -- 方式二范围查询走索引更高效推荐 WHERE date_col DATE 2024-01-01 AND date_col DATE 2024-01-02关于TO_DATE、TO_CHAR的格式模型我把它单独列一节因为它牵涉到毫秒转日期、字符串转日期等极其常见的需求。3.4 毫秒级时间戳转换一张格式表Oracle的毫秒时间戳在很多系统对接中都会遇到。Java后端存了一个1696060800000这样的毫秒数要在数据库层直接转换成日期显示总不能每次都查出来再在Java里转。-- 毫秒转日期 SELECT TO_DATE(1970-01-01, YYYY-MM-DD) 1696060800000 / 1000 / 86400 FROM dual; -- 结果是 2023-09-30 16:00:00也就是UTC时间要注意时区 -- 转成中国标准时间东八区8小时 SELECT TO_DATE(1970-01-01, YYYY-MM-DD) 1696060800000 / 1000 / 86400 8/24 FROM dual;日期转毫秒就是逆运算SELECT (SYSDATE - TO_DATE(1970-01-01, YYYY-MM-DD)) * 86400 * 1000 FROM dual;常用格式模型我整理成一张速查表格式含义示例输出YYYY四位年份2024YY两位年份24MM两位月份11MON月份缩写11月DD两位日期23D周几1-71周日DY星期缩写星期六HH2424小时制小时17HH12小时制小时05MI分钟30SS秒45Q季度4WW当年第几周47使用规范上我提两点TO_CHAR转出来的全是字符串不要拿它再和数字比较TO_DATE对格式要求严格输入字符串和格式模型对不上就报ORA-01843。遇到ORA-01847、ORA-01830这类日期转换报错多半是字符串里混进了非法日期或者格式模型写错了。4. 转换函数与NULL处理报表不出错的关键底线数据类型转换和NULL值处理是移交给BI报表之前最容易出问题的一环。TO_NUMBER遇到非数字字符直接报错NVL和NVL2用得好能省一堆CASE WHEN。这一节我会把类型转换和NULL处理的完整套路都过一遍。4.1 TO_CHAR、TO_NUMBER、TO_DATE三大转换TO_CHAR可以把日期、数字转成字符串方便格式化显示TO_NUMBER把字符串转成数字TO_DATE把字符串转成日期。这三个是类型转换的核心工具。-- 数字格式化为货币样式 SELECT TO_CHAR(12345.678, L99G999D99) FROM dual; -- 结果: 12,345.68 -- 日期格式化为字符串 SELECT TO_CHAR(SYSDATE, YYYY年MM月DD日) FROM dual; -- 字符串转数字 SELECT TO_NUMBER(123.45) FROM dual; -- 字符串转日期 SELECT TO_DATE(2024-11-23, YYYY-MM-DD) FROM dual;注意TO_NUMBER转的字符串里不能有千分位逗号、货币符号等字符否则直接报错。所以前面提到过滤不可转数字的字符串核心思路是先判断再转换或者先清洗再转换。生产环境里我最常用的方案是写一个判断函数或者用CASE WHEN先判断-- 方案一REGEXP_LIKE先判断是否纯数字不含小数点 SELECT CASE WHEN REGEXP_LIKE(amount, ^[0-9]$) THEN TO_NUMBER(amount) ELSE 0 END FROM payment_log; -- 方案二允许小数和负数的写法 SELECT CASE WHEN REGEXP_LIKE(amount, ^-?[0-9](\.[0-9])?$) THEN TO_NUMBER(amount) ELSE NULL END FROM payment_log;如果amount列里还有千分位逗号先清理再判断SELECT CASE WHEN REGEXP_LIKE(REPLACE(amount, ,, ), ^[0-9](\.[0-9])?$) THEN TO_NUMBER(REPLACE(amount, ,, )) ELSE NULL END FROM payment_log;这个写法看着烦但在生产环境它就是能扛住脏数据的底线方案。另外提醒这些判断在查询大表时也会有明显性能消耗最好在数据入库阶段就完成清洗查询阶段只为历史脏数据兜底。4.2 NVL、NVL2、NULLIF与COALESCE的四兄弟Oracle里空值处理函数不少各有各的适用场景。NVL(a, b)a是NULL时返回b否则返回a。NVL要求两个参数类型一致否则Oracle会尝试隐式转换类型不兼容时直接报ORA-01790。所以NVL(commission, 0)没问题但NVL(commission, N/A)就会报错。NVL2(a, b, c)a不是NULL返回ba是NULL返回c。这个比NVL更灵活。比如佣金列有值就显示有佣金没值显示无佣金SELECT NVL2(commission_pct, 有佣金, 无佣金) FROM employees;NULLIF(a, b)a等于b时返回NULL否则返回a。常用于防止除数为0。比如求上个月和这个月的增长率如果两个月销售额相同NULLIF可以避免得到0/0的除法错误。COALESCE(a, b, c, ...)从左到右依次返回第一个非NULL值。和NVL的差别在它可以接收多个参数只要有一个不是NULL就返回。实际开发中这个函数非常实用一个字段为空取另一个再为空取默认值-- 手机号为空取联系电话再为空取紧急电话 SELECT COALESCE(mobile_phone, phone, emergency_phone, 无联系电话) FROM customer;NVL和COALESCE的行为看似一样区别是COALESCE是SQL标准而NVL是Oracle独有。做跨数据库迁移的时候尽量用COALESCE这能减少改动量。不过这俩执行计划上效率都差不多不用过度纠结性能。4.3 DECODE与CASE WHEN一文一武DECODE是Oracle特有的条件函数CASE WHEN是SQL标准。两者功能高度重叠但写法风格不同。-- DECODE写法 SELECT ename, DECODE(deptno, 10, 财务部, 20, 研发部, 30, 销售部, 其他部门) AS dept_name FROM emp; -- CASE WHEN写法 SELECT ename, CASE WHEN deptno 10 THEN 财务部 WHEN deptno 20 THEN 研发部 WHEN deptno 30 THEN 销售部 ELSE 其他部门 END AS dept_name FROM emp;个人建议新代码都写CASE WHEN理由是它的可读性更好、跨数据库兼容性更强。但老系统里到处都是DECODE你得能读懂它。DECODE还有个小特性对NULL做等值判断时有特殊行为DECODE(col, NULL, 0, col)能正确处理NULL而CASE WHEN col NULL的写法永远是假必须写成CASE WHEN col IS NULL。这是一道很经典的面试坑题实际开发中也特别容易中招。5. 分析函数与分页从会用到用得漂亮分析函数是Oracle区别于MySQL这类数据库的重要能力窗口计算、排名、分组汇总都可以在SQL内完成。这一节把最常用的分析函数和经典分页写法讲透。5.1 ROWNUM分页与ROW_NUMBER() OVER两种常用分页方式Oracle分页的传统方案是ROWNUM。ROWNUM是Oracle在结果集生成时赋予每行的序号但它有个特性只在行被返回时才会分配序号。所以你想查第11条到第20条不能直接写成WHERE ROWNUM BETWEEN 11 AND 20因为这永远不会返回任何行——第1条在赋值给第11之前就被过滤掉了。正确做法是先查出来再套一层SELECT * FROM ( SELECT e.*, ROWNUM rn FROM ( SELECT * FROM emp ORDER BY sal DESC ) e WHERE ROWNUM 20 ) WHERE rn 11;内层先按工资倒序再取前20行外层再过滤出第11到20行。这个三层嵌套是老一代Oracle工程师的基本功。后来Oracle增加了ROW_NUMBER()分析函数分页顺手多了SELECT * FROM ( SELECT emp.*, ROW_NUMBER() OVER (ORDER BY sal DESC) rn FROM emp ) WHERE rn BETWEEN 11 AND 20;ROW_NUMBER()比ROWNUM灵活的地方在于支持PARTITION BY可以按组内编号。比如每个部门里按工资排名取每个部门工资最高的前三名SELECT * FROM ( SELECT emp.*, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) rn FROM emp ) WHERE rn 3;从使用经验看数据量小、一次性查询两种都无所谓数据量大、频繁分页更建议用ROW_NUMBER()结合绑定变量配合合理的索引设计。5.2 RANK与DENSE_RANK排名热的区别RANK()和DENSE_RANK()都是排名函数区别在于并列之后的下一位RANK()并列会跳号比如两个第1名下一个就是第3名。DENSE_RANK()并列不跳号两个第1名之后还是第2名。SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS rank_跳跃, DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_连续 FROM emp;排名场景用哪个取决于业务含义如果需求是前三名并且只关心有没有进前三RANK和DENSE_RANK都行如果需求是从第3名开始取5个人就涉及语义得算清楚并列有没有占掉名额。奖金排行榜通常用RANK因为并列之后隔过一名符合直觉补录名额时一般用DENSE_RANK。5.3 LAG与LEAD取前后行数据的利器LAG取前面第N行的值LEAD取后面第N行的值。这个在算环比、同比时极其好用。比如算每个人当月工资和上个月工资的差额SELECT ename, hire_date, sal, LAG(sal, 1, 0) OVER (ORDER BY hire_date) AS prev_sal, sal - LAG(sal, 1, 0) OVER (ORDER BY hire_date) AS diff FROM emp;LAG(sal, 1, 0)里的三个参数分别是目标列、往前偏移几行、偏移越界时的默认值。这个默认参数非常关键否则第一行数据没有上一条记录会返回NULL很多统计会直接把这个NULL列为异常实际思路是先补0再处理。LEAD用法对称只是往后看。自连接也能实现同样的效果但分析函数写法简洁得多执行效率通常也更好。5.4 LISTAGG与WM_CONCAT字符串聚合Oracle里拼接多行值为一个字符串最早的经典写法是WM_CONCAT但它是非公开函数说不清什么时候可能被移除新系统不建议再用。官方推荐用LISTAGG-- 把同一部门的员工姓名拼成一个字符串 SELECT deptno, LISTAGG(ename, ,) WITHIN GROUP (ORDER BY ename) AS emp_names FROM emp GROUP BY deptno;LISTAGG有个注意点拼接结果超过4000字符会报ORA-01489: result of string concatenation is too long。处理方式是用XMLAGG替代或者分批拼接或者干脆在程序层拼好再入库。我通常的做法是限制条数或者用SUBSTR截断按业务可接受的最大长度来收敛。-- 超过4000字符时的替代方案XMLAGG SELECT deptno, RTRIM(XMLAGG(XMLELEMENT(e, ename || ,) ORDER BY ename).EXTRACT(//text()), ,) AS emp_names FROM emp GROUP BY deptno;这条复合写法执行效率略低但解决了长度限制的问题。用到这个场景的基本都是报表或导出场景量不大可以接受。6. 存储过程与常见错误排查把这些坑记牢能省三天工时函数写进存储过程之后有一些Oracle特有的执行细节和错误排查点弄明白能少走很多弯路。6.1 SQL%ROWCOUNT与异常处理存储过程里的必备肌肉记忆存储过程里最常用的两个变量就是SQL%FOUND、SQL%ROWCOUNT。我第一次写存储过程时不知道这两个只能先SELECT COUNT(*)再判断大表场景下性能堪忧而且并发修改场景下极不可靠。BEGIN UPDATE emp SET sal sal * 1.1 WHERE deptno 20; DBMS_OUTPUT.PUT_LINE(更新了 || SQL%ROWCOUNT || 行); IF SQL%FOUND THEN COMMIT; ELSE ROLLBACK; END IF; END;关键点是SQL%ROWCOUNT必须紧跟对应的DML语句中间不要插入其他SQL否则它的值会被下一次操作覆盖。这是新手最容易犯的低级错误。还有一个约定俗成的技巧做完DML后用SQL%ROWCOUNT判断是否影响到了预期行数完全没影响到说明数据有异常适合回滚并且记录日志很多状态机更新逻辑这样做能有效避免并发脏写。异常处理常用块BEGIN ...业务逻辑... EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(查无记录); WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE(出错: || SQLERRM); END;WHEN OTHERS永远放在最后。有些新手在前面写WHEN OTHERS后面的特定异常就永远不会被捕获了。而且要记得WHEN OTHERS捕获后如果只是DBMS_OUTPUT打印却不ROLLBACK事务可能处于不可预期状态这个我经历过调试时出了半天汗。6.2 SQLPLUS登录缓慢或错误基本排查顺序登录sqlplus缓慢或报错原因常见的有这几类监听没起来、网络不通、密码验证延迟、数据库负载过高。排查顺序建议先tnsping看监听再查看lsnrctl status确认服务状态然后看登录账号的认证方式是否触发了延迟。有一点容易被忽略Linux环境下/etc/hosts解析慢或者DNS不通会导致sqlplus登录卡很久。这个问题的特征是tnsping秒通但登录要十几秒甚至超时。解决办法是把数据库服务器的hostname和IP写到/etc/hosts里。这个坑我帮别人排查过不止一次问题根源往往不在数据库而在系统域名解析。还有一个低级但高频的坑输错了用户名密码Oracle为了安全默认设置了登录失败延迟多次失败后会越来越慢这种时候检查FAILED_LOGIN_ATTEMPTS配置即可。6.3 常量陷阱DUAL表与隐式转换很多开发者习惯直接写WHERE number_col 123Oracle有隐式转换机制多数时候能跑通。但一旦number_col里有非规范数据或者字段类型是VARCHAR2而内容带了空格隐式转换就会引起性能问题甚至结果错误。关于DUAL再补充一个重要场景很多存储过程迟迟调不通是因为在SELECT语句里没有FROM dual。比如在PL/SQL块里写v_date : SYSDATE;没问题但写SELECT SYSDATE INTO v_date;就是不合法必须加上FROM dual。这是Oracle语法和MySQL的一个显著差异。MySQL允许没有FROM的SELECTOracle不允许。跨库迁移代码时这条差异会制造大量小错误需要提前安排批量检查。从数据库迁移角度看Oracle和MySQL的函数体系差异很大分页一个是ROWNUM/OFFSET FETCH一个是LIMIT字符串拼接一个是||一个是CONCAT日期函数一个用SYSDATE一个用NOW()和CURDATE()。如果你在做数据库迁移或同步建议先建一张函数映射对照表把两边命名和语法差异列清楚能省很多调试时间。还有字符串截取在MySQL里叫SUBSTRINGOracle叫SUBSTR细节差异铺开能写很长一篇我这里是先提醒有这个坑。6.4 查看会话与监听常用DBA级命令速查排查数据库性能问题、查看当前会话和监听状态是Oracle从业者绕不开的基本功这里列几个高频命令命令/语句作用使用场景SELECT sid, serial#, username, status FROM v$session;查看当前会话找出阻塞源、清理异常会话ALTER SYSTEM KILL SESSION sid,serial#;杀掉指定会话解决死锁和长时间占用的会话lsnrctl status查看监听状态排查连接不上数据库的问题lsnrctl start/stop启动/停止监听监听挂了之后重新拉起SELECT * FROM v$locked_object;查看锁对象快速定位哪些表被锁了我提一个具体场景开发环境经常出现某张表被锁导致后续更新全部堵塞。先查v$locked_object定位对象再关联v$session拿到sid和serial#最后ALTER SYSTEM KILL SESSION干掉源头。这套动作熟练后处理锁问题基本在一分钟以内。注意在生产环境杀会话前要慎重先确认会话对应的业务最好能联系到发起方盲目杀会话可能把正在跑的批量任务搞挂。7. 30天函数熟练度提升计划最后分享一个我常用的函数学习路线给想系统提升的人一个参考。第一周聚焦字符串函数。每天手写一遍TRIM、LTRIM、RTRIM、SUBSTR、INSTR、REPLACE、TRANSLATE、LPAD、RPAD的常用用例配合DUAL表反复测试。重点理解LTRIM是字符集合而非字符串前缀。第二周攻数值和日期函数。重点练习ROUND和TRUNC的区别把TO_CHAR(SYSDATE, 格式)的常用格式模型背下来特别是YYYY、MM、DD、HH24、MI、SS。日期加减单位换算要练到条件反射。第三周练转换和NULL处理。把TO_CHAR、TO_NUMBER、TO_DATE的报错场景都故意触发一遍记录报错代码。用NVL、NVL2、COALESCE、NULLIF、DECODE、CASE WHEN写一批条件逻辑重点记住DECODE对NULL的处理方式。第四周练分析函数。刻意用ROW_NUMBER、RANK、DENSE_RANK、LAG、LEAD、LISTAGG各写一个业务场景比如分组TopN、环比计算、字符串聚合。这一周结束时你应该能不看文档写出大多数常用分析函数。我个人实际带人时还会做一件事每个阶段末尾拿一张真实业务表来做一次函数体检比如订单表里找出手机号不规范的数据、清洗掉地址里的脏字符、统计每个区域每个月的销售额环比。因为函数这东西光看文档记不牢只有在真实数据上踩过坑才会真正变成肌肉记忆。Oracle函数体系很大但常用核心就那么几十个先把上面这些吃透日常工作里真正让你卡壳的问题已经能解决80%。剩下的边界情况遇到时再查Oracle SQL Language Reference也来得及——但那应该是定位查询而不是从头学起。