简介这份《Oracle数据库19c版SQL语言参考手册》以PDF形式提供面向Oracle数据库开发者、DBA及需要系统查阅SQL语法的学习者重点解决日常开发中函数用法、语法细节和数据类型转换等查询需求。包内共1个PDF文件约14MB内容为官方SQL Language Reference文档适合离线检索与案头备查。手册按功能覆盖数学函数、字符串函数、日期时间函数、转换函数、加密函数和集合函数等常用类别可帮助读者快速定位求和取平均、字符串拼接与截取、日期格式转换、数据类型转换及聚合分组等操作的语法说明并对照参数、返回类型与使用限制排查问题。已有185人学习适合作为Oracle 19c开发、运维和备考过程中的常用函数工具书同时需留意文档的版权声明与许可限制在合规前提下使用。1. 从一份一千多页的 PDF 说起Oracle 19c SQL 参考手册到底该查什么凌晨两点一个跑了半年的存储过程突然报 ORA-01861翻遍注释也没人记得TO_DATE的格式串当初为什么那么写。这种时候搜索引擎给的常是三四手转述真正能救命的是一份躺在本地、按函数名就能翻到参数和示例的官方参考Oracle Database 19c 的 SQL Language Reference文档编号 E96310-262024 年 7 月更新。它不教你写 SQL它是字典——从数据类型、伪列、运算符、表达式、条件一路排到第 7 章的 SQL 函数大全ABS、ADD_MONTHS、JSON_TRANSFORM 按字母序摊开每个条目给语法图、参数语义、示例和 See Also。适合已经能写增删改查、但一碰格式串、时区、JSON 解析就得翻资料的人。2. 手册骨架七个章节怎么对应你日常写的每一条 SQL这份手册的组织方式跟大多数教程不一样它不按「入门—进阶—实战」排而是按 SQL 的语法层次从底往上垒第 2 章基本元素第 3 章伪列第 4 章运算符第 5 章表达式第 6 章条件第 7 章才是函数。理解这个分层比记住任何一个函数都重要因为一条写错的 SQL九成问题出在下面几层而不是函数本身写错了。我一般把它当四层检索先确认值的类型对不对再看伪列和运算符的求值顺序然后看表达式和条件怎么组合最后才去第 7 章找具体函数签名。下面按这个顺序拆。2.1 第 2 章基本元素数据类型与格式模型是函数报错的总源头第 2 章内容最杂但真正需要反复翻的是三块内置数据类型、数据类型转换规则、格式模型。数据类型这块19c 的清单是 CHAR/NCHAR、VARCHAR2/NVARCHAR2、NUMBER/FLOAT/BINARY_FLOAT/BINARY_DOUBLE、DATE、TIMESTAMP 及其带时区的两个变体、INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND、RAW/LONG RAW、LOBCLOB/BLOB/NCLOB/BFILE、ROWID/UROWID再加上扩展数据类型里 VARCHAR2 能到 32767 字节这一条。选型上最容易翻车的是 NUMBER 和 BINARY_FLOAT/BINARY_DOUBLE 的取舍前者是十进制精确存储做金额和计数必须用后者是二进制浮点算得快但0.1 0.2不等于0.3只适合科学计算类场景。格式模型是第二个高频区。数字格式元素里9表示「有值就显示、没值留空」0表示「没值补零」这两个符号混用是TO_CHAR输出对不齐的主要原因FM去掉前导空格L取本地货币符号MI把负号放到右边PR用尖括号表示负数。日期格式元素里YYYY是四位年RR是两位年的滑动窗口IW是 ISO 周FF是小数秒TZH:TZM是时区偏移。这些元素单独看都简单组合起来才是坑比如TO_CHAR(SYSDATE,YYYY-MM-DD HH24:MI:SS)里的MM是月份、MI是分钟写成HH24:MM:SS不会报错但输出的是「小时:月份:秒」。先跑三段验证把抽象规则落成看得见的结果-- 1) DUMP 看一个值在库里到底以什么类型、什么字节存着 SELECT DUMP(123.45, 1016) AS num_dump, DUMP(123.45, 1016) AS str_dump FROM dual; -- 2) 类型优先级NUMBER 高于 VARCHAR2比较时字符一侧被转成数字 SELECT 1 AS hit FROM dual WHERE 1 1; -- 3) 数字格式模型里 9 和 0 的差别直接影响报表对齐 SELECT TO_CHAR(12.5, 999.99) AS fmt_9, TO_CHAR(12.5, 000.00) AS fmt_0, TO_CHAR(-12.5, 999.99PR) AS fmt_pr FROM dual;第一段里DUMP的第二个参数1016表示「十进制 单字节字符集」输出会带上类型编号NUMBER 和 VARCHAR2 的编号不同一眼就能看出隐式转换发生在哪一侧。第二段的WHERE 1 1能返回一行说明 Oracle 把字符1转成了数字 1而不是反过来这个方向很关键它决定了后面索引会不会失效。第三段里fmt_9输出带前导空格fmt_0输出补零fmt_pr把负号换成尖括号——报表对不齐的时候先查这三个格式元素基本能定位。2.2 第 3 到 6 章伪列、运算符、表达式、条件四层拼出一条 SQL第 3 章伪列只有十几个条目但每一个都能单独写一篇踩坑记录。ROWNUM是在排序之前分配的所以ORDER BY之后再套ROWNUM分页一定乱ROWID是行的物理地址能用来做去重最快的手段但跨表迁移后失效LEVEL只在层次查询里有意义CONNECT_BY_ISLEAF判断当前节点是不是叶子CONNECT_BY_ISCYCLE配合NOCYCLE检测环ORA_ROWSCN拿的是行最后修改的 SCN默认是块级不是行级要行级得建表时开ROWDEPENDENCIES。第 4 章的运算符优先级是很多人忽略的一层。从高到低大致是算术运算、连接符||、比较运算、IS [NOT] NULL/LIKE/IN、[NOT] BETWEEN、NOT、AND、OR。WHERE a 1 OR b 2 AND c 3会先算AND再算OR跟直觉相反加括号不是可选项而是必选项。第 5 章表达式里CASE是标准写法DECODE是 Oracle 特有的简写但只支持等值比较标量子查询表达式在 SELECT 列表里能省一次 JOIN但返回多行就直接报错。第 6 章条件是 SQL 的门禁。除了常规的、、BETWEEN、IN、EXISTS、LIKE19c 还给了REGEXP_LIKE做正则匹配IS JSON、JSON_EXISTS、JSON_TEXTCONTAINS、JSON_EQUAL做 JSON 判断IS ANY、IS PRESENT用在模型查询里。这几种条件跟普通条件混用时优先级按第 6 章开头那张 Condition Precedence 表走记不住就加括号。层次查询最容易一次写对、也最容易在数据有环时卡死给一段可复现的模板-- 展开一棵虚构的组织树LEVEL 给层级CONNECT_BY_ISLEAF 标叶子 -- SYS_CONNECT_BY_PATH 拼出从根到当前节点的路径 SELECT LEVEL AS lvl, LPAD( , (LEVEL - 1) * 2) || emp_name AS tree, CONNECT_BY_ISLEAF AS is_leaf, SYS_CONNECT_BY_PATH(emp_name, /) AS path FROM demo_emp START WITH mgr_id IS NULL CONNECT BY PRIOR emp_id mgr_id ORDER SIBLINGS BY emp_name;START WITH指定根节点的筛选条件CONNECT BY PRIOR 子列 父列指定递归方向ORDER SIBLINGS BY保证同一层内有序而不破坏树形结构。如果数据里存在环这条语句会一直跑下去正确做法是加NOCYCLE再用CONNECT_BY_ISCYCLE把成环的那一行标出来。这套写法我用了很多年比在应用层递归查库快一个数量级但前提是PRIOR的方向不能写反——写反了不会报错只会返回一条链而不是一棵树。2.3 第 7 章函数从 ABS 到 XML 的检索路径第 7 章是整本手册最厚的部分也是「函数大全」这四个字的实体。它先给一张分类索引单行函数数值、返回字符的字符函数、返回数字的字符函数、字符集函数、排序规则函数、日期时间函数、通用比较函数、转换函数、LOB 函数、集合函数、层次函数、数据挖掘函数、XML 函数、JSON 函数、编码解码函数、NULL 相关函数、环境与标识符函数、聚合函数、分析函数、对象引用函数、模型函数、OLAP 函数、Data Cartridge 函数。每个函数条目的结构是固定的语法图、Purpose、Semantics、Examples、See Also。检索路径我一般分三步走。第一步按分类定大类比如要拼接多行字符串先想清楚是聚合还是分析聚合走LISTAGG分析走窗口第二步按字母序翻到条目看语法图里哪些参数是可选的、哪些是关键字必须写第三步看 See Also这一栏经常把一组配套函数串起来比如NVL的 See Also 会指向COALESCE、NULLIF、LNNVL一次能把 NULL 相关的函数全认全。把常用条目落成一张自建速查表比每次翻 PDF 快-- 自建函数速查表把手册里反复查的条目按分类落库字段留够注释位 CREATE TABLE sql_func_ref ( func_name VARCHAR2(64) PRIMARY KEY, category VARCHAR2(32) NOT NULL, since_ver VARCHAR2(8), note VARCHAR2(200) ); INSERT INTO sql_func_ref VALUES (LISTAGG, 聚合/分析, 11.2, WITHIN GROUP 必填19c 支持 DISTINCT); INSERT INTO sql_func_ref VALUES (JSON_TRANSFORM, JSON, 19c, 19c 新增直接改文档内容); INSERT INTO sql_func_ref VALUES (APPROX_PERCENTILE,近似聚合, 12.1, 误差随数据分布变化别用于对账); INSERT INTO sql_func_ref VALUES (VALIDATE_CONVERSION,转换, 12.2, 先校验再转换避免 ORA-01722); COMMIT;表结构里since_ver不是装饰19c 里不少函数是 12c 才进来的跨版本迁移时这一列能直接告诉你哪些语句在旧库上会报「无效标识符」。note一列写的是「不看手册就会错」的那半句比如LISTAGG的WITHIN GROUP是必填而不是可选。2.4 把 PDF 变成可检索的本地索引手册是 PDF直接在阅读器里搜函数名会被页眉页脚干扰尤其函数名后面跟页码的情况。我的做法是先转纯文本再切分命令行两三条就够# -layout 保留列对齐语法图的缩进才不会塌 pdftotext -layout Oracle19c_SQL_Reference.pdf sqlref.txt # 第 7 章的函数名在文本里独立成行用行首行尾锚定筛一遍 grep -n ^[A-Z][A-Z0-9_]*$ sqlref.txt | head -80 # 查单个函数时带上下文语法图和示例通常都在条目后 40 行内 grep -n -A 40 ^ADD_MONTHS$ sqlref.txt-layout是关键参数不加它多列排版会被打散语法图里的方括号和竖线全乱-n输出行号方便回 PDF 对照页码-A 40表示命中行之后再打 40 行正好覆盖一个函数的 Semantics 和 Examples。切完之后我还会按函数名拆成单文件这样grep一次只在一个函数条目里搜噪音小很多。这套流程不依赖任何商业工具pdftotext在常见的 Linux 发行版包管理器里都能装到。3. 函数大全怎么用四类高频函数的参数与边界第 7 章有几百个函数真正常用的不到六十个但每一个都有「手册写了、你不看就会错」的那一面。这一章按字符与数值、日期时间、转换与 NULL、聚合与分析四类拆每类给一段能直接跑的验证代码重点不在语法而在参数边界。3.1 字符与数值函数字节语义与舍入方向字符函数最容易混的是「字符」和「字节」两套语义。LENGTH返回字符数LENGTHB返回字节数SUBSTR按字符切SUBSTRB按字节切INSTR按字符找位置INSTRB按字节找。在 AL32UTF8 字符集下一个中文字符占三字节这两个数字能差三倍。做字段长度校验时如果用的是LENGTHB而建表用的是VARCHAR2(50 CHAR)就会出现「应用说超长、库说没超」的扯皮反过来更常见LENGTH校验通过、插入时报 ORA-12899。数值函数看似简单ROUND和TRUNC的第二个参数在负数时的行为很多人没验证过ROUND(1234.5, -2)是 1200TRUNC(1234.5, -2)也是 1200但ROUND(1250, -2)是 1300 而TRUNC(1250, -2)是 1200。金额计算里这两个函数用错方向对账时会差出分位。-- 同一份数据在字符语义和字节语义下的差异 SELECT LENGTH(数据库) AS char_len, -- 3 LENGTHB(数据库) AS byte_len, -- AL32UTF8 下为 9 SUBSTR(数据库, 2, 1) AS sub_char, -- 据 SUBSTRB(数据库, 4, 3) AS sub_byte -- 据第 4 字节起取 3 字节 FROM dual; -- 负数精度的舍入方向对账前必须确认 SELECT ROUND(1250, -2) AS rnd, -- 1300 TRUNC(1250, -2) AS trc, -- 1200 MOD(17, 5) AS md, -- 2 BITAND(13, 7) AS band -- 5按位与 FROM dual;SUBSTRB那段要特别注意起始字节位置必须落在字符边界上否则取出来的半个字符会变成乱码而且不报错。真正安全的做法是业务层统一用字符语义只有处理 BLOB 或固定字节协议时才动SUBSTRB。BITAND常用于权限位运算参数必须是非负整数传负数会得到跟直觉完全不同的结果。3.2 日期时间函数ADD_MONTHS、MONTHS_BETWEEN 与格式模型ADD_MONTHS的月末行为是经典考点1 月 31 日加一个月返回的是 2 月最后一天平年 28 号、闰年 29 号不会返回 3 月 3 日。这意味着「按月加」不是严格的 30 天做订阅到期计算时必须明确业务要的是「自然月」还是「固定天数」。MONTHS_BETWEEN返回的是带小数的数字两个日期同月同日时是整数否则按 31 天一月折算结果可能为负直接拿来取整会丢方向。TRUNC和ROUND用在日期上是截断到指定单位TRUNC(SYSDATE,MM)是本月一号TRUNC(SYSDATE,IW)是本周一ISO 周TRUNC(SYSDATE,Q)是本季度首日。NEXT_DAY的第二个参数是星期几的字符串受会话的NLS_DATE_LANGUAGE影响英文会话写MONDAY中文会话可能要写星期一这是跨环境脚本失效的常见原因。EXTRACT能从日期或时间戳里抠出年、月、日、时、分、秒和时区字段但它不能抠「周」要周数得用TO_CHAR(d,IW)。-- 月末滚动1 月 31 日加一个月落在 2 月最后一天 SELECT ADD_MONTHS(DATE 2024-01-31, 1) AS next_month, -- 2024-02-29 ADD_MONTHS(DATE 2023-01-31, 1) AS non_leap, -- 2023-02-28 LAST_DAY(DATE 2024-02-10) AS month_end, -- 2024-02-29 TRUNC(DATE 2024-05-17, MM) AS month_start, -- 2024-05-01 TRUNC(DATE 2024-05-17, IW) AS week_start -- 2024-05-13 FROM dual; -- 月份差值带小数取整前先确认业务方向 SELECT MONTHS_BETWEEN(DATE 2024-03-15, DATE 2024-01-15) AS m_int, -- 2 MONTHS_BETWEEN(DATE 2024-03-14, DATE 2024-01-15) AS m_frac -- 1.96... FROM dual;DATE 2024-01-31这种字面量写法不受会话日期格式影响比TO_DATE(2024-01-31,YYYY-MM-DD)更稳能写就写。MONTHS_BETWEEN的结果如果直接ROUND到整数2 月 28 日到 3 月 31 日会被算成 1 个月还是 1 个月多一点取决于具体日期账期计算必须先把规则写死在文档里再写 SQL。3.3 转换与 NULL 相关函数CAST、TO_CHAR、NVL、COALESCE 的取舍转换函数分显式和隐式两类。显式转换里CAST是标准语法TO_CHAR/TO_DATE/TO_NUMBER是 Oracle 特有但支持格式模型能控制输出形状。TO_CHAR的第三个参数NLS_DATE_LANGUAGE能临时改语言避免会话设置影响月份名和星期名。12.2 之后还有VALIDATE_CONVERSION先判断能不能转再转能省掉大量ORA-01722: 无效数字的异常捕获。NULL 相关函数有四个容易混NVL(a,b)两参数a为 NULL 时返回bNVL2(a,b,c)三参数a非 NULL 返回b、为 NULL 返回cNULLIF(a,b)相等时返回 NULLCOALESCE(a,b,c,...)返回第一个非 NULL 值。选型上COALESCE是标准 SQL 且支持多参数跨库迁移优先用它NVL在 Oracle 里对参数类型做隐式转换更宽松两边类型不一致时可能悄悄转换反而埋雷。LNNVL是给条件取反用的专门处理NULL参与比较返回 UNKNOWN 的情况写在WHERE里能把「不等于某值但包含 NULL」的行捞出来。-- 显式转换带语言参数输出不受会话 NLS 影响 SELECT TO_CHAR(DATE 2024-05-17, DD-MON-YYYY, NLS_DATE_LANGUAGEAMERICAN) AS en_fmt, TO_CHAR(DATE 2024-05-17, YYYY年MM月DD日) AS cn_fmt FROM dual; -- 先校验再转换避免异常流 SELECT VALIDATE_CONVERSION(123.45 AS NUMBER) AS ok_num, -- 1 VALIDATE_CONVERSION(12a.45 AS NUMBER) AS bad_num -- 0 FROM dual; -- 四种 NULL 处理的语义差异 SELECT NVL(NULL, 0) AS nvl_r, -- 0 NVL2(x, has, none) AS nvl2_r, -- has NULLIF(5, 5) AS nullif_r, -- NULL COALESCE(NULL, NULL, 3) AS coal_r -- 3 FROM dual;TO_CHAR那段里的中文格式串用了双引号把汉字包起来因为格式模型里汉字不是格式元素不包会被当元素解析。VALIDATE_CONVERSION返回 1 或 0可以直接放进CASE做分流比EXCEPTION WHEN OTHERS干净得多。NULLIF(5,5)返回 NULL 这一点常被用来把「0 当除数」转成 NULL再套NVL兜底避免ORA-01476。3.4 聚合与分析函数LISTAGG、RANK、LAG 的窗口定义聚合函数把多行压成一行分析函数在不减少行数的前提下加一列计算结果。LISTAGG是聚合里最常用的字符串拼接语法是LISTAGG(expr, delimiter) WITHIN GROUP (ORDER BY ...)WITHIN GROUP不是可选装饰漏写直接语法错误。19c 支持LISTAGG(DISTINCT expr, delimiter)去重拼接但去重后排序规则要自己保证稳定。拼接结果超过 4000 字节会报 ORA-01489需要换成XMLAGG或者先截断。分析函数的关键在窗口定义。ROW_NUMBER给唯一序号遇到并列也强行排 1、2、3RANK并列时跳号1、1、3DENSE_RANK并列不跳号1、1、2。取「每组最新一条」用ROW_NUMBER() OVER (PARTITION BY 组键 ORDER BY 时间 DESC)再外套一层筛rn 1这是最稳的写法。LAG/LEAD取前后行第三个参数是越界时的默认值不写默认值就返回 NULL做同比环比时这一列很容易全是 NULL 而没人发现。-- 按部门拼接姓名同时给出组内排名和上一行值 SELECT dept_id, LISTAGG(emp_name, ,) WITHIN GROUP (ORDER BY emp_name) AS name_list, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk, LAG(salary, 1, 0) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_sal FROM demo_emp GROUP BY dept_id, emp_name, salary;这段 SQL 有个陷阱LISTAGG是聚合RANK和LAG是分析两类函数写在同一个 SELECT 里必须先GROUP BY到能唯一确定分析函数输入的程度否则结果不可预期。生产里我一般拆成两层内层用分析函数算序号外层用聚合拼字符串可读性和执行计划都更稳。窗口帧默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW做累计求和时必须显式写ROWS BETWEEN ...因为RANGE在遇到重复排序键时会把并列行一起算进去累计值会跳。4. 19c 里值得单独翻手册的部分JSON 函数与近似聚合19c 手册里有两块内容跟早几个版本比明显加厚了一块是 JSON 函数族一块是近似聚合。这两块在业务里用得越来越多但网上的二手资料普遍停留在「能跑通」的层面误差和边界几乎没人讲而这恰恰是手册写得最清楚的地方。4.1 JSON 函数族JSON_VALUE、JSON_QUERY、JSON_TABLE 的分工三个函数的定位完全不同。JSON_VALUE取标量返回 VARCHAR2 或指定类型路径必须指向单个值指向对象或数组会返回 NULL 而不报错JSON_QUERY取对象或数组返回带引号的 JSON 片段JSON_TABLE把 JSON 数组展开成关系行是「JSON 转表」的正解。选型判断很简单要一个字段值用JSON_VALUE要一段结构用JSON_QUERY要把数组变成多行用JSON_TABLE。-- 一份虚构订单 JSON三种取法各取所需 WITH doc AS ( SELECT {orderId:A1001,amount:1280.50, items:[{sku:S1,qty:2},{sku:S2,qty:1}]} AS j FROM dual ) SELECT JSON_VALUE(j, $.orderId) AS order_id, -- 标量 JSON_QUERY(j, $.items) AS items_json, -- 数组片段 JSON_VALUE(j, $.amount RETURNING NUMBER) AS amt -- 显式返回类型 FROM doc; -- JSON_TABLE 把 items 数组展开成行做明细统计 SELECT t.sku, t.qty FROM demo_order o, JSON_TABLE(o.doc, $.items[*] COLUMNS (sku VARCHAR2(16) PATH $.sku, qty NUMBER PATH $.qty)) t;JSON_VALUE的RETURNING NUMBER很重要不写就返回字符串拿去求和会隐式转换遇到12a这种脏数据直接抛异常。JSON_TABLE里的$.items[*]是行路径COLUMNS里每一项的PATH是列路径行路径写错会返回 0 行且不报错调试时先用JSON_QUERY确认数组存在再展开。另外JSON_VALUE默认对超长返回值做截断需要TRUNCATE或ERROR ON ERROR明确行为这一点手册 Semantics 段写得很细。4.2 JSON_TRANSFORM 与 JSON_MERGEPATCH改文档而不是改行19c 新增的JSON_TRANSFORM允许直接在 JSON 文档上做SET、REMOVE、INSERT、APPEND等操作返回修改后的文档不用把整个 JSON 取到应用层解析再写回。JSON_MERGEPATCH走的是 RFC 7396 的合并补丁语义把补丁文档合并到目标文档上同名字段覆盖、null值删除字段。这两个函数解决的是同一个问题的两个方向前者是「精确改某几个路径」后者是「按补丁批量合并」。-- 精确改把 amount 改成 999并删掉 items 数组 SELECT JSON_TRANSFORM({orderId:A1001,amount:1280.5,items:[1,2]}, SET $.amount 999, REMOVE $.items) AS after_transform FROM dual; -- 补丁合并同名字段覆盖null 值删除字段 SELECT JSON_MERGEPATCH({a:1,b:2,c:3}, {b:20,c:null,d:4}) AS merged FROM dual;JSON_TRANSFORM的SET后面跟的是路径和值路径不存在时是新增还是报错取决于有没有加CREATE ON MISSING之类的修饰手册里JSON_TRANSFORM条目的 Semantics 段落列了一整张修饰符表用之前必须对着看一遍。JSON_MERGEPATCH的坑在于数组是整体替换而不是逐元素合并很多人以为是深合并结果补丁里少写一个元素就把原数组覆盖没了。这两个函数都必须保证输入是合法 JSON否则直接报错脏数据场景要先IS JSON过滤。4.3 近似聚合APPROX_COUNT_DISTINCT 与 APPROX_PERCENTILE 的误差边界APPROX_COUNT_DISTINCT基于 HyperLogLog手册给的典型误差在 1% 量级实际误差跟数据基数分布有关基数极小时反而可能完全准确基数极大时误差趋近上界。它的价值在海量数据的去重计数上亿行去重精确算要几十秒近似算通常在一秒内。APPROX_PERCENTILE和APPROX_MEDIAN走的是近似分位数返回的是「大概率落在这个位置」的值不保证是原始数据里真实存在的某个值这一点跟精确MEDIAN有本质区别。-- 精确 vs 近似同一份数据两种算法对比误差 SELECT COUNT(DISTINCT user_id) AS exact_cnt, APPROX_COUNT_DISTINCT(user_id) AS approx_cnt, APPROX_MEDIAN(amount) AS approx_med, APPROX_PERCENTILE(amount, 0.95) AS approx_p95 FROM demo_orders; -- 需要跨天合并近似结果时用 DETAIL 存中间态再 AGG SELECT APPROX_COUNT_DISTINCT_AGG(detail) AS merged_cnt FROM demo_hll_detail;对比时要在同一份数据、同一时刻跑否则误差里混进了数据漂移。APPROX_PERCENTILE的第二个参数是 0 到 1 之间的小数写95不会报错但语义完全错。DETAIL系列函数APPROX_COUNT_DISTINCT_DETAIL、APPROX_PERCENTILE_DETAIL用来把中间结果物化成 BLOB 存表后续用_AGG合并这是做增量统计的正确姿势直接对每天的结果再做APPROX_COUNT_DISTINCT会重复计数很多人在这里翻车。5. 避坑手册里写得清楚、现场却天天翻车的五个点手册的 Semantics 段落把规则写得很明白但规则是抽象的现场是具体的。下面五条是我这些年被问得最多、也是日志里出现频率最高的每条按现象、原因、解决三步走解决步骤都能直接照做。5.1 隐式转换1和1在索引上的差别现象一张几百万行的表字符型工号列上明明建了索引WHERE emp_no 1001却走全表扫执行计划里出现TO_NUMBER(EMP_NO)。原因字符列与数字比较时Oracle 按数据类型优先级把字符一侧转成数字等价于对索引列套了函数普通 B-Tree 索引直接失效。反过来写WHERE num_col 1001是字符转数字、数字列不动索引仍然可用。解决参数类型对齐字符列就传字符串如果业务上确实要按数字比较建函数索引CREATE INDEX idx_emp_no_num ON demo_emp(TO_NUMBER(emp_no))或者加虚拟列再建普通索引。上线前用EXPLAIN PLAN看一眼有没有TO_NUMBER包在列上比事后查慢 SQL 省事。5.2 日期格式模型RR与YY、NLS_DATE_FORMAT的会话级陷阱现象同一句TO_DATE(01-02-03,DD-MM-YY)在测试库跑出 2003 年在生产库跑出 1903 年换个客户端工具结果又变了。原因YY只取两位年世纪由数据库当前世纪补RR是滑动窗口按当前年份后两位决定归到本世纪还是上/下世纪NLS_DATE_FORMAT是会话级参数不同客户端连接的会话设置不同隐式转换TO_DATE(2024-05-17)这种不写格式串的写法完全依赖它。解决年份一律写四位YYYYTO_DATE/TO_CHAR一律显式给格式串跨语言环境再补NLS_DATE_LANGUAGEAMERICAN。应用连接池初始化时统一ALTER SESSION SET NLS_DATE_FORMATYYYY-MM-DD HH24:MI:SS别指望每个客户端都配对。5.3ROWNUM分页为什么ORDER BY之后行号是乱的现象SELECT * FROM (SELECT ... ORDER BY create_time DESC) WHERE ROWNUM 10取第一页正常取第二页时数据重复或者漏行。原因ROWNUM是在行被选出、但ORDER BY排序之前分配的套在排序外层时行号已经按未排序的顺序定死翻页条件ROWNUM BETWEEN 11 AND 20永远返回空因为ROWNUM从 1 开始逐行判定第 11 行到达时前面的行已经被过滤掉了。解决三层结构——最内层排序中间层生成ROWNUM rn最外层按rn过滤或者直接用 12c 以后的OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY语法更短也没有行号陷阱。分页 SQL 我现在的习惯是只写OFFSET/FETCH除非要兼容老版本。5.4 NULL 参与比较与聚合COUNT(*)与COUNT(col)不是一回事现象对账时两边行数差了几百条COUNT(*)和COUNT(status)结果不一样WHERE status NOT IN (SELECT status FROM ...)返回空集。原因COUNT(*)数所有行COUNT(col)跳过col为 NULL 的行NULL 参与任何比较返回 UNKNOWNNOT IN的子查询结果里只要有一个 NULL整个条件就恒为 UNKNOWN返回空集。解决统计口径先明确是「行数」还是「非空值数」NOT IN一律换成NOT EXISTS或者在子查询里加WHERE col IS NOT NULLGROUP BY里 NULL 会被归成一组报表要展示成「未知」就在SELECT里套NVL。5.5 函数索引与 Hints手册给了语法不保证走计划现象按手册语法建了函数索引查询里也写了/* INDEX(t idx_name) */执行计划还是全表扫。原因函数索引要求查询里的表达式跟索引定义逐字一致TO_CHAR(hire_date,YYYY)和TO_CHAR(hire_date,yyyy)在优化器看来是两个表达式Hint 只是建议索引不可用、查询被改写、统计信息过期、绑定变量窥探导致计划翻转任何一种都能让 Hint 失效。解决先用DBMS_XPLAN.DISPLAY_CURSOR看真实计划确认索引有没有被考虑表达式不一致就改写查询或重建索引统计信息用DBMS_STATS.GATHER_TABLE_STATS定期收集。Hint 我的用法是只做临时兜底验证完就删长期依赖 Hint 的 SQL 迟早会在版本升级后翻车。6. 把手册变成验证闭环三个我每次上线前都会跑的自检手册的价值不在于读而在于「查完之后能验证」。我现在的习惯是凡是新写的 SQL 里出现格式模型、隐式转换、分析函数窗口、JSON 路径这四类元素上线前强制跑一遍自检把手册里的 Semantics 段落变成可执行断言。自检项验证语句通过标准日期格式是否显式grep -nE TO_DATE\([^]*\) *.sql无命中所有TO_DATE都带格式串字符列是否被隐式转数字EXPLAIN PLAN后看TO_NUMBER是否包在列上列上无函数包裹分析函数窗口是否显式检查SUM() OVER是否写了ROWS BETWEEN累计类一律显式帧分页是否用行号grep -n ROWNUM *.sql仅允许在去重场景出现配套的验证 SQL 我只留一条最常用的判断隐式转换有没有发生在索引列上-- 跑完 EXPLAIN PLAN 后看谓词段重点找列名外面有没有函数 EXPLAIN PLAN FOR SELECT emp_name FROM demo_emp WHERE emp_no 1001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, BASIC PREDICATE));PREDICATE这个 format 参数是关键BASIC只给操作类型看不到谓词细节加上之后Predicate Information段落会把TO_NUMBER(EMP_NO)1001原样打出来有没有隐式转换一眼便知。这一步花不到十秒比上线后半夜被慢查询叫醒划算太多。血泪经验是有一年做数据迁移图省事在WHERE里直接拿字符型订单号和数字比较测试环境数据量小没暴露生产上线后核心查询从 200 毫秒涨到 40 秒回滚加建函数索引折腾了四个小时。从那以后我每次改带类型比较的 SQL都强制先跑一遍EXPLAIN PLAN看谓词段确认列名外面干干净净才提交。手册能告诉你规则但规则要落成动作才有意义。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/31 17:56:20 · 阅读 1.2k Oracle 19c SQL参考手册：函数速查与隐式转换避坑
简介这份《Oracle数据库19c版SQL语言参考手册》以PDF形式提供面向Oracle数据库开发者、DBA及需要系统查阅SQL语法的学习者重点解决日常开发中函数用法、语法细节和数据类型转换等查询需求。包内共1个PDF文件约14MB内容为官方SQL Language Reference文档适合离线检索与案头备查。手册按功能覆盖数学函数、字符串函数、日期时间函数、转换函数、加密函数和集合函数等常用类别可帮助读者快速定位求和取平均、字符串拼接与截取、日期格式转换、数据类型转换及聚合分组等操作的语法说明并对照参数、返回类型与使用限制排查问题。已有185人学习适合作为Oracle 19c开发、运维和备考过程中的常用函数工具书同时需留意文档的版权声明与许可限制在合规前提下使用。1. 从一份一千多页的 PDF 说起Oracle 19c SQL 参考手册到底该查什么凌晨两点一个跑了半年的存储过程突然报 ORA-01861翻遍注释也没人记得TO_DATE的格式串当初为什么那么写。这种时候搜索引擎给的常是三四手转述真正能救命的是一份躺在本地、按函数名就能翻到参数和示例的官方参考Oracle Database 19c 的 SQL Language Reference文档编号 E96310-262024 年 7 月更新。它不教你写 SQL它是字典——从数据类型、伪列、运算符、表达式、条件一路排到第 7 章的 SQL 函数大全ABS、ADD_MONTHS、JSON_TRANSFORM 按字母序摊开每个条目给语法图、参数语义、示例和 See Also。适合已经能写增删改查、但一碰格式串、时区、JSON 解析就得翻资料的人。2. 手册骨架七个章节怎么对应你日常写的每一条 SQL这份手册的组织方式跟大多数教程不一样它不按「入门—进阶—实战」排而是按 SQL 的语法层次从底往上垒第 2 章基本元素第 3 章伪列第 4 章运算符第 5 章表达式第 6 章条件第 7 章才是函数。理解这个分层比记住任何一个函数都重要因为一条写错的 SQL九成问题出在下面几层而不是函数本身写错了。我一般把它当四层检索先确认值的类型对不对再看伪列和运算符的求值顺序然后看表达式和条件怎么组合最后才去第 7 章找具体函数签名。下面按这个顺序拆。2.1 第 2 章基本元素数据类型与格式模型是函数报错的总源头第 2 章内容最杂但真正需要反复翻的是三块内置数据类型、数据类型转换规则、格式模型。数据类型这块19c 的清单是 CHAR/NCHAR、VARCHAR2/NVARCHAR2、NUMBER/FLOAT/BINARY_FLOAT/BINARY_DOUBLE、DATE、TIMESTAMP 及其带时区的两个变体、INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND、RAW/LONG RAW、LOBCLOB/BLOB/NCLOB/BFILE、ROWID/UROWID再加上扩展数据类型里 VARCHAR2 能到 32767 字节这一条。选型上最容易翻车的是 NUMBER 和 BINARY_FLOAT/BINARY_DOUBLE 的取舍前者是十进制精确存储做金额和计数必须用后者是二进制浮点算得快但0.1 0.2不等于0.3只适合科学计算类场景。格式模型是第二个高频区。数字格式元素里9表示「有值就显示、没值留空」0表示「没值补零」这两个符号混用是TO_CHAR输出对不齐的主要原因FM去掉前导空格L取本地货币符号MI把负号放到右边PR用尖括号表示负数。日期格式元素里YYYY是四位年RR是两位年的滑动窗口IW是 ISO 周FF是小数秒TZH:TZM是时区偏移。这些元素单独看都简单组合起来才是坑比如TO_CHAR(SYSDATE,YYYY-MM-DD HH24:MI:SS)里的MM是月份、MI是分钟写成HH24:MM:SS不会报错但输出的是「小时:月份:秒」。先跑三段验证把抽象规则落成看得见的结果-- 1) DUMP 看一个值在库里到底以什么类型、什么字节存着 SELECT DUMP(123.45, 1016) AS num_dump, DUMP(123.45, 1016) AS str_dump FROM dual; -- 2) 类型优先级NUMBER 高于 VARCHAR2比较时字符一侧被转成数字 SELECT 1 AS hit FROM dual WHERE 1 1; -- 3) 数字格式模型里 9 和 0 的差别直接影响报表对齐 SELECT TO_CHAR(12.5, 999.99) AS fmt_9, TO_CHAR(12.5, 000.00) AS fmt_0, TO_CHAR(-12.5, 999.99PR) AS fmt_pr FROM dual;第一段里DUMP的第二个参数1016表示「十进制 单字节字符集」输出会带上类型编号NUMBER 和 VARCHAR2 的编号不同一眼就能看出隐式转换发生在哪一侧。第二段的WHERE 1 1能返回一行说明 Oracle 把字符1转成了数字 1而不是反过来这个方向很关键它决定了后面索引会不会失效。第三段里fmt_9输出带前导空格fmt_0输出补零fmt_pr把负号换成尖括号——报表对不齐的时候先查这三个格式元素基本能定位。2.2 第 3 到 6 章伪列、运算符、表达式、条件四层拼出一条 SQL第 3 章伪列只有十几个条目但每一个都能单独写一篇踩坑记录。ROWNUM是在排序之前分配的所以ORDER BY之后再套ROWNUM分页一定乱ROWID是行的物理地址能用来做去重最快的手段但跨表迁移后失效LEVEL只在层次查询里有意义CONNECT_BY_ISLEAF判断当前节点是不是叶子CONNECT_BY_ISCYCLE配合NOCYCLE检测环ORA_ROWSCN拿的是行最后修改的 SCN默认是块级不是行级要行级得建表时开ROWDEPENDENCIES。第 4 章的运算符优先级是很多人忽略的一层。从高到低大致是算术运算、连接符||、比较运算、IS [NOT] NULL/LIKE/IN、[NOT] BETWEEN、NOT、AND、OR。WHERE a 1 OR b 2 AND c 3会先算AND再算OR跟直觉相反加括号不是可选项而是必选项。第 5 章表达式里CASE是标准写法DECODE是 Oracle 特有的简写但只支持等值比较标量子查询表达式在 SELECT 列表里能省一次 JOIN但返回多行就直接报错。第 6 章条件是 SQL 的门禁。除了常规的、、BETWEEN、IN、EXISTS、LIKE19c 还给了REGEXP_LIKE做正则匹配IS JSON、JSON_EXISTS、JSON_TEXTCONTAINS、JSON_EQUAL做 JSON 判断IS ANY、IS PRESENT用在模型查询里。这几种条件跟普通条件混用时优先级按第 6 章开头那张 Condition Precedence 表走记不住就加括号。层次查询最容易一次写对、也最容易在数据有环时卡死给一段可复现的模板-- 展开一棵虚构的组织树LEVEL 给层级CONNECT_BY_ISLEAF 标叶子 -- SYS_CONNECT_BY_PATH 拼出从根到当前节点的路径 SELECT LEVEL AS lvl, LPAD( , (LEVEL - 1) * 2) || emp_name AS tree, CONNECT_BY_ISLEAF AS is_leaf, SYS_CONNECT_BY_PATH(emp_name, /) AS path FROM demo_emp START WITH mgr_id IS NULL CONNECT BY PRIOR emp_id mgr_id ORDER SIBLINGS BY emp_name;START WITH指定根节点的筛选条件CONNECT BY PRIOR 子列 父列指定递归方向ORDER SIBLINGS BY保证同一层内有序而不破坏树形结构。如果数据里存在环这条语句会一直跑下去正确做法是加NOCYCLE再用CONNECT_BY_ISCYCLE把成环的那一行标出来。这套写法我用了很多年比在应用层递归查库快一个数量级但前提是PRIOR的方向不能写反——写反了不会报错只会返回一条链而不是一棵树。2.3 第 7 章函数从 ABS 到 XML 的检索路径第 7 章是整本手册最厚的部分也是「函数大全」这四个字的实体。它先给一张分类索引单行函数数值、返回字符的字符函数、返回数字的字符函数、字符集函数、排序规则函数、日期时间函数、通用比较函数、转换函数、LOB 函数、集合函数、层次函数、数据挖掘函数、XML 函数、JSON 函数、编码解码函数、NULL 相关函数、环境与标识符函数、聚合函数、分析函数、对象引用函数、模型函数、OLAP 函数、Data Cartridge 函数。每个函数条目的结构是固定的语法图、Purpose、Semantics、Examples、See Also。检索路径我一般分三步走。第一步按分类定大类比如要拼接多行字符串先想清楚是聚合还是分析聚合走LISTAGG分析走窗口第二步按字母序翻到条目看语法图里哪些参数是可选的、哪些是关键字必须写第三步看 See Also这一栏经常把一组配套函数串起来比如NVL的 See Also 会指向COALESCE、NULLIF、LNNVL一次能把 NULL 相关的函数全认全。把常用条目落成一张自建速查表比每次翻 PDF 快-- 自建函数速查表把手册里反复查的条目按分类落库字段留够注释位 CREATE TABLE sql_func_ref ( func_name VARCHAR2(64) PRIMARY KEY, category VARCHAR2(32) NOT NULL, since_ver VARCHAR2(8), note VARCHAR2(200) ); INSERT INTO sql_func_ref VALUES (LISTAGG, 聚合/分析, 11.2, WITHIN GROUP 必填19c 支持 DISTINCT); INSERT INTO sql_func_ref VALUES (JSON_TRANSFORM, JSON, 19c, 19c 新增直接改文档内容); INSERT INTO sql_func_ref VALUES (APPROX_PERCENTILE,近似聚合, 12.1, 误差随数据分布变化别用于对账); INSERT INTO sql_func_ref VALUES (VALIDATE_CONVERSION,转换, 12.2, 先校验再转换避免 ORA-01722); COMMIT;表结构里since_ver不是装饰19c 里不少函数是 12c 才进来的跨版本迁移时这一列能直接告诉你哪些语句在旧库上会报「无效标识符」。note一列写的是「不看手册就会错」的那半句比如LISTAGG的WITHIN GROUP是必填而不是可选。2.4 把 PDF 变成可检索的本地索引手册是 PDF直接在阅读器里搜函数名会被页眉页脚干扰尤其函数名后面跟页码的情况。我的做法是先转纯文本再切分命令行两三条就够# -layout 保留列对齐语法图的缩进才不会塌 pdftotext -layout Oracle19c_SQL_Reference.pdf sqlref.txt # 第 7 章的函数名在文本里独立成行用行首行尾锚定筛一遍 grep -n ^[A-Z][A-Z0-9_]*$ sqlref.txt | head -80 # 查单个函数时带上下文语法图和示例通常都在条目后 40 行内 grep -n -A 40 ^ADD_MONTHS$ sqlref.txt-layout是关键参数不加它多列排版会被打散语法图里的方括号和竖线全乱-n输出行号方便回 PDF 对照页码-A 40表示命中行之后再打 40 行正好覆盖一个函数的 Semantics 和 Examples。切完之后我还会按函数名拆成单文件这样grep一次只在一个函数条目里搜噪音小很多。这套流程不依赖任何商业工具pdftotext在常见的 Linux 发行版包管理器里都能装到。3. 函数大全怎么用四类高频函数的参数与边界第 7 章有几百个函数真正常用的不到六十个但每一个都有「手册写了、你不看就会错」的那一面。这一章按字符与数值、日期时间、转换与 NULL、聚合与分析四类拆每类给一段能直接跑的验证代码重点不在语法而在参数边界。3.1 字符与数值函数字节语义与舍入方向字符函数最容易混的是「字符」和「字节」两套语义。LENGTH返回字符数LENGTHB返回字节数SUBSTR按字符切SUBSTRB按字节切INSTR按字符找位置INSTRB按字节找。在 AL32UTF8 字符集下一个中文字符占三字节这两个数字能差三倍。做字段长度校验时如果用的是LENGTHB而建表用的是VARCHAR2(50 CHAR)就会出现「应用说超长、库说没超」的扯皮反过来更常见LENGTH校验通过、插入时报 ORA-12899。数值函数看似简单ROUND和TRUNC的第二个参数在负数时的行为很多人没验证过ROUND(1234.5, -2)是 1200TRUNC(1234.5, -2)也是 1200但ROUND(1250, -2)是 1300 而TRUNC(1250, -2)是 1200。金额计算里这两个函数用错方向对账时会差出分位。-- 同一份数据在字符语义和字节语义下的差异 SELECT LENGTH(数据库) AS char_len, -- 3 LENGTHB(数据库) AS byte_len, -- AL32UTF8 下为 9 SUBSTR(数据库, 2, 1) AS sub_char, -- 据 SUBSTRB(数据库, 4, 3) AS sub_byte -- 据第 4 字节起取 3 字节 FROM dual; -- 负数精度的舍入方向对账前必须确认 SELECT ROUND(1250, -2) AS rnd, -- 1300 TRUNC(1250, -2) AS trc, -- 1200 MOD(17, 5) AS md, -- 2 BITAND(13, 7) AS band -- 5按位与 FROM dual;SUBSTRB那段要特别注意起始字节位置必须落在字符边界上否则取出来的半个字符会变成乱码而且不报错。真正安全的做法是业务层统一用字符语义只有处理 BLOB 或固定字节协议时才动SUBSTRB。BITAND常用于权限位运算参数必须是非负整数传负数会得到跟直觉完全不同的结果。3.2 日期时间函数ADD_MONTHS、MONTHS_BETWEEN 与格式模型ADD_MONTHS的月末行为是经典考点1 月 31 日加一个月返回的是 2 月最后一天平年 28 号、闰年 29 号不会返回 3 月 3 日。这意味着「按月加」不是严格的 30 天做订阅到期计算时必须明确业务要的是「自然月」还是「固定天数」。MONTHS_BETWEEN返回的是带小数的数字两个日期同月同日时是整数否则按 31 天一月折算结果可能为负直接拿来取整会丢方向。TRUNC和ROUND用在日期上是截断到指定单位TRUNC(SYSDATE,MM)是本月一号TRUNC(SYSDATE,IW)是本周一ISO 周TRUNC(SYSDATE,Q)是本季度首日。NEXT_DAY的第二个参数是星期几的字符串受会话的NLS_DATE_LANGUAGE影响英文会话写MONDAY中文会话可能要写星期一这是跨环境脚本失效的常见原因。EXTRACT能从日期或时间戳里抠出年、月、日、时、分、秒和时区字段但它不能抠「周」要周数得用TO_CHAR(d,IW)。-- 月末滚动1 月 31 日加一个月落在 2 月最后一天 SELECT ADD_MONTHS(DATE 2024-01-31, 1) AS next_month, -- 2024-02-29 ADD_MONTHS(DATE 2023-01-31, 1) AS non_leap, -- 2023-02-28 LAST_DAY(DATE 2024-02-10) AS month_end, -- 2024-02-29 TRUNC(DATE 2024-05-17, MM) AS month_start, -- 2024-05-01 TRUNC(DATE 2024-05-17, IW) AS week_start -- 2024-05-13 FROM dual; -- 月份差值带小数取整前先确认业务方向 SELECT MONTHS_BETWEEN(DATE 2024-03-15, DATE 2024-01-15) AS m_int, -- 2 MONTHS_BETWEEN(DATE 2024-03-14, DATE 2024-01-15) AS m_frac -- 1.96... FROM dual;DATE 2024-01-31这种字面量写法不受会话日期格式影响比TO_DATE(2024-01-31,YYYY-MM-DD)更稳能写就写。MONTHS_BETWEEN的结果如果直接ROUND到整数2 月 28 日到 3 月 31 日会被算成 1 个月还是 1 个月多一点取决于具体日期账期计算必须先把规则写死在文档里再写 SQL。3.3 转换与 NULL 相关函数CAST、TO_CHAR、NVL、COALESCE 的取舍转换函数分显式和隐式两类。显式转换里CAST是标准语法TO_CHAR/TO_DATE/TO_NUMBER是 Oracle 特有但支持格式模型能控制输出形状。TO_CHAR的第三个参数NLS_DATE_LANGUAGE能临时改语言避免会话设置影响月份名和星期名。12.2 之后还有VALIDATE_CONVERSION先判断能不能转再转能省掉大量ORA-01722: 无效数字的异常捕获。NULL 相关函数有四个容易混NVL(a,b)两参数a为 NULL 时返回bNVL2(a,b,c)三参数a非 NULL 返回b、为 NULL 返回cNULLIF(a,b)相等时返回 NULLCOALESCE(a,b,c,...)返回第一个非 NULL 值。选型上COALESCE是标准 SQL 且支持多参数跨库迁移优先用它NVL在 Oracle 里对参数类型做隐式转换更宽松两边类型不一致时可能悄悄转换反而埋雷。LNNVL是给条件取反用的专门处理NULL参与比较返回 UNKNOWN 的情况写在WHERE里能把「不等于某值但包含 NULL」的行捞出来。-- 显式转换带语言参数输出不受会话 NLS 影响 SELECT TO_CHAR(DATE 2024-05-17, DD-MON-YYYY, NLS_DATE_LANGUAGEAMERICAN) AS en_fmt, TO_CHAR(DATE 2024-05-17, YYYY年MM月DD日) AS cn_fmt FROM dual; -- 先校验再转换避免异常流 SELECT VALIDATE_CONVERSION(123.45 AS NUMBER) AS ok_num, -- 1 VALIDATE_CONVERSION(12a.45 AS NUMBER) AS bad_num -- 0 FROM dual; -- 四种 NULL 处理的语义差异 SELECT NVL(NULL, 0) AS nvl_r, -- 0 NVL2(x, has, none) AS nvl2_r, -- has NULLIF(5, 5) AS nullif_r, -- NULL COALESCE(NULL, NULL, 3) AS coal_r -- 3 FROM dual;TO_CHAR那段里的中文格式串用了双引号把汉字包起来因为格式模型里汉字不是格式元素不包会被当元素解析。VALIDATE_CONVERSION返回 1 或 0可以直接放进CASE做分流比EXCEPTION WHEN OTHERS干净得多。NULLIF(5,5)返回 NULL 这一点常被用来把「0 当除数」转成 NULL再套NVL兜底避免ORA-01476。3.4 聚合与分析函数LISTAGG、RANK、LAG 的窗口定义聚合函数把多行压成一行分析函数在不减少行数的前提下加一列计算结果。LISTAGG是聚合里最常用的字符串拼接语法是LISTAGG(expr, delimiter) WITHIN GROUP (ORDER BY ...)WITHIN GROUP不是可选装饰漏写直接语法错误。19c 支持LISTAGG(DISTINCT expr, delimiter)去重拼接但去重后排序规则要自己保证稳定。拼接结果超过 4000 字节会报 ORA-01489需要换成XMLAGG或者先截断。分析函数的关键在窗口定义。ROW_NUMBER给唯一序号遇到并列也强行排 1、2、3RANK并列时跳号1、1、3DENSE_RANK并列不跳号1、1、2。取「每组最新一条」用ROW_NUMBER() OVER (PARTITION BY 组键 ORDER BY 时间 DESC)再外套一层筛rn 1这是最稳的写法。LAG/LEAD取前后行第三个参数是越界时的默认值不写默认值就返回 NULL做同比环比时这一列很容易全是 NULL 而没人发现。-- 按部门拼接姓名同时给出组内排名和上一行值 SELECT dept_id, LISTAGG(emp_name, ,) WITHIN GROUP (ORDER BY emp_name) AS name_list, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk, LAG(salary, 1, 0) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_sal FROM demo_emp GROUP BY dept_id, emp_name, salary;这段 SQL 有个陷阱LISTAGG是聚合RANK和LAG是分析两类函数写在同一个 SELECT 里必须先GROUP BY到能唯一确定分析函数输入的程度否则结果不可预期。生产里我一般拆成两层内层用分析函数算序号外层用聚合拼字符串可读性和执行计划都更稳。窗口帧默认是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW做累计求和时必须显式写ROWS BETWEEN ...因为RANGE在遇到重复排序键时会把并列行一起算进去累计值会跳。4. 19c 里值得单独翻手册的部分JSON 函数与近似聚合19c 手册里有两块内容跟早几个版本比明显加厚了一块是 JSON 函数族一块是近似聚合。这两块在业务里用得越来越多但网上的二手资料普遍停留在「能跑通」的层面误差和边界几乎没人讲而这恰恰是手册写得最清楚的地方。4.1 JSON 函数族JSON_VALUE、JSON_QUERY、JSON_TABLE 的分工三个函数的定位完全不同。JSON_VALUE取标量返回 VARCHAR2 或指定类型路径必须指向单个值指向对象或数组会返回 NULL 而不报错JSON_QUERY取对象或数组返回带引号的 JSON 片段JSON_TABLE把 JSON 数组展开成关系行是「JSON 转表」的正解。选型判断很简单要一个字段值用JSON_VALUE要一段结构用JSON_QUERY要把数组变成多行用JSON_TABLE。-- 一份虚构订单 JSON三种取法各取所需 WITH doc AS ( SELECT {orderId:A1001,amount:1280.50, items:[{sku:S1,qty:2},{sku:S2,qty:1}]} AS j FROM dual ) SELECT JSON_VALUE(j, $.orderId) AS order_id, -- 标量 JSON_QUERY(j, $.items) AS items_json, -- 数组片段 JSON_VALUE(j, $.amount RETURNING NUMBER) AS amt -- 显式返回类型 FROM doc; -- JSON_TABLE 把 items 数组展开成行做明细统计 SELECT t.sku, t.qty FROM demo_order o, JSON_TABLE(o.doc, $.items[*] COLUMNS (sku VARCHAR2(16) PATH $.sku, qty NUMBER PATH $.qty)) t;JSON_VALUE的RETURNING NUMBER很重要不写就返回字符串拿去求和会隐式转换遇到12a这种脏数据直接抛异常。JSON_TABLE里的$.items[*]是行路径COLUMNS里每一项的PATH是列路径行路径写错会返回 0 行且不报错调试时先用JSON_QUERY确认数组存在再展开。另外JSON_VALUE默认对超长返回值做截断需要TRUNCATE或ERROR ON ERROR明确行为这一点手册 Semantics 段写得很细。4.2 JSON_TRANSFORM 与 JSON_MERGEPATCH改文档而不是改行19c 新增的JSON_TRANSFORM允许直接在 JSON 文档上做SET、REMOVE、INSERT、APPEND等操作返回修改后的文档不用把整个 JSON 取到应用层解析再写回。JSON_MERGEPATCH走的是 RFC 7396 的合并补丁语义把补丁文档合并到目标文档上同名字段覆盖、null值删除字段。这两个函数解决的是同一个问题的两个方向前者是「精确改某几个路径」后者是「按补丁批量合并」。-- 精确改把 amount 改成 999并删掉 items 数组 SELECT JSON_TRANSFORM({orderId:A1001,amount:1280.5,items:[1,2]}, SET $.amount 999, REMOVE $.items) AS after_transform FROM dual; -- 补丁合并同名字段覆盖null 值删除字段 SELECT JSON_MERGEPATCH({a:1,b:2,c:3}, {b:20,c:null,d:4}) AS merged FROM dual;JSON_TRANSFORM的SET后面跟的是路径和值路径不存在时是新增还是报错取决于有没有加CREATE ON MISSING之类的修饰手册里JSON_TRANSFORM条目的 Semantics 段落列了一整张修饰符表用之前必须对着看一遍。JSON_MERGEPATCH的坑在于数组是整体替换而不是逐元素合并很多人以为是深合并结果补丁里少写一个元素就把原数组覆盖没了。这两个函数都必须保证输入是合法 JSON否则直接报错脏数据场景要先IS JSON过滤。4.3 近似聚合APPROX_COUNT_DISTINCT 与 APPROX_PERCENTILE 的误差边界APPROX_COUNT_DISTINCT基于 HyperLogLog手册给的典型误差在 1% 量级实际误差跟数据基数分布有关基数极小时反而可能完全准确基数极大时误差趋近上界。它的价值在海量数据的去重计数上亿行去重精确算要几十秒近似算通常在一秒内。APPROX_PERCENTILE和APPROX_MEDIAN走的是近似分位数返回的是「大概率落在这个位置」的值不保证是原始数据里真实存在的某个值这一点跟精确MEDIAN有本质区别。-- 精确 vs 近似同一份数据两种算法对比误差 SELECT COUNT(DISTINCT user_id) AS exact_cnt, APPROX_COUNT_DISTINCT(user_id) AS approx_cnt, APPROX_MEDIAN(amount) AS approx_med, APPROX_PERCENTILE(amount, 0.95) AS approx_p95 FROM demo_orders; -- 需要跨天合并近似结果时用 DETAIL 存中间态再 AGG SELECT APPROX_COUNT_DISTINCT_AGG(detail) AS merged_cnt FROM demo_hll_detail;对比时要在同一份数据、同一时刻跑否则误差里混进了数据漂移。APPROX_PERCENTILE的第二个参数是 0 到 1 之间的小数写95不会报错但语义完全错。DETAIL系列函数APPROX_COUNT_DISTINCT_DETAIL、APPROX_PERCENTILE_DETAIL用来把中间结果物化成 BLOB 存表后续用_AGG合并这是做增量统计的正确姿势直接对每天的结果再做APPROX_COUNT_DISTINCT会重复计数很多人在这里翻车。5. 避坑手册里写得清楚、现场却天天翻车的五个点手册的 Semantics 段落把规则写得很明白但规则是抽象的现场是具体的。下面五条是我这些年被问得最多、也是日志里出现频率最高的每条按现象、原因、解决三步走解决步骤都能直接照做。5.1 隐式转换1和1在索引上的差别现象一张几百万行的表字符型工号列上明明建了索引WHERE emp_no 1001却走全表扫执行计划里出现TO_NUMBER(EMP_NO)。原因字符列与数字比较时Oracle 按数据类型优先级把字符一侧转成数字等价于对索引列套了函数普通 B-Tree 索引直接失效。反过来写WHERE num_col 1001是字符转数字、数字列不动索引仍然可用。解决参数类型对齐字符列就传字符串如果业务上确实要按数字比较建函数索引CREATE INDEX idx_emp_no_num ON demo_emp(TO_NUMBER(emp_no))或者加虚拟列再建普通索引。上线前用EXPLAIN PLAN看一眼有没有TO_NUMBER包在列上比事后查慢 SQL 省事。5.2 日期格式模型RR与YY、NLS_DATE_FORMAT的会话级陷阱现象同一句TO_DATE(01-02-03,DD-MM-YY)在测试库跑出 2003 年在生产库跑出 1903 年换个客户端工具结果又变了。原因YY只取两位年世纪由数据库当前世纪补RR是滑动窗口按当前年份后两位决定归到本世纪还是上/下世纪NLS_DATE_FORMAT是会话级参数不同客户端连接的会话设置不同隐式转换TO_DATE(2024-05-17)这种不写格式串的写法完全依赖它。解决年份一律写四位YYYYTO_DATE/TO_CHAR一律显式给格式串跨语言环境再补NLS_DATE_LANGUAGEAMERICAN。应用连接池初始化时统一ALTER SESSION SET NLS_DATE_FORMATYYYY-MM-DD HH24:MI:SS别指望每个客户端都配对。5.3ROWNUM分页为什么ORDER BY之后行号是乱的现象SELECT * FROM (SELECT ... ORDER BY create_time DESC) WHERE ROWNUM 10取第一页正常取第二页时数据重复或者漏行。原因ROWNUM是在行被选出、但ORDER BY排序之前分配的套在排序外层时行号已经按未排序的顺序定死翻页条件ROWNUM BETWEEN 11 AND 20永远返回空因为ROWNUM从 1 开始逐行判定第 11 行到达时前面的行已经被过滤掉了。解决三层结构——最内层排序中间层生成ROWNUM rn最外层按rn过滤或者直接用 12c 以后的OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY语法更短也没有行号陷阱。分页 SQL 我现在的习惯是只写OFFSET/FETCH除非要兼容老版本。5.4 NULL 参与比较与聚合COUNT(*)与COUNT(col)不是一回事现象对账时两边行数差了几百条COUNT(*)和COUNT(status)结果不一样WHERE status NOT IN (SELECT status FROM ...)返回空集。原因COUNT(*)数所有行COUNT(col)跳过col为 NULL 的行NULL 参与任何比较返回 UNKNOWNNOT IN的子查询结果里只要有一个 NULL整个条件就恒为 UNKNOWN返回空集。解决统计口径先明确是「行数」还是「非空值数」NOT IN一律换成NOT EXISTS或者在子查询里加WHERE col IS NOT NULLGROUP BY里 NULL 会被归成一组报表要展示成「未知」就在SELECT里套NVL。5.5 函数索引与 Hints手册给了语法不保证走计划现象按手册语法建了函数索引查询里也写了/* INDEX(t idx_name) */执行计划还是全表扫。原因函数索引要求查询里的表达式跟索引定义逐字一致TO_CHAR(hire_date,YYYY)和TO_CHAR(hire_date,yyyy)在优化器看来是两个表达式Hint 只是建议索引不可用、查询被改写、统计信息过期、绑定变量窥探导致计划翻转任何一种都能让 Hint 失效。解决先用DBMS_XPLAN.DISPLAY_CURSOR看真实计划确认索引有没有被考虑表达式不一致就改写查询或重建索引统计信息用DBMS_STATS.GATHER_TABLE_STATS定期收集。Hint 我的用法是只做临时兜底验证完就删长期依赖 Hint 的 SQL 迟早会在版本升级后翻车。6. 把手册变成验证闭环三个我每次上线前都会跑的自检手册的价值不在于读而在于「查完之后能验证」。我现在的习惯是凡是新写的 SQL 里出现格式模型、隐式转换、分析函数窗口、JSON 路径这四类元素上线前强制跑一遍自检把手册里的 Semantics 段落变成可执行断言。自检项验证语句通过标准日期格式是否显式grep -nE TO_DATE\([^]*\) *.sql无命中所有TO_DATE都带格式串字符列是否被隐式转数字EXPLAIN PLAN后看TO_NUMBER是否包在列上列上无函数包裹分析函数窗口是否显式检查SUM() OVER是否写了ROWS BETWEEN累计类一律显式帧分页是否用行号grep -n ROWNUM *.sql仅允许在去重场景出现配套的验证 SQL 我只留一条最常用的判断隐式转换有没有发生在索引列上-- 跑完 EXPLAIN PLAN 后看谓词段重点找列名外面有没有函数 EXPLAIN PLAN FOR SELECT emp_name FROM demo_emp WHERE emp_no 1001; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, BASIC PREDICATE));PREDICATE这个 format 参数是关键BASIC只给操作类型看不到谓词细节加上之后Predicate Information段落会把TO_NUMBER(EMP_NO)1001原样打出来有没有隐式转换一眼便知。这一步花不到十秒比上线后半夜被慢查询叫醒划算太多。血泪经验是有一年做数据迁移图省事在WHERE里直接拿字符型订单号和数字比较测试环境数据量小没暴露生产上线后核心查询从 200 毫秒涨到 40 秒回滚加建函数索引折腾了四个小时。从那以后我每次改带类型比较的 SQL都强制先跑一遍EXPLAIN PLAN看谓词段确认列名外面干干净净才提交。手册能告诉你规则但规则要落成动作才有意义。希望帮到你。本文还有配套的精品资源点击获取