OracleSQL函数全解析：从日期到自定义的深度探索

### Oracle SQL 函数全解析：从日期到自定义的深度探索 在 Oracle SQL 中，函数是强大的工具，能帮助我们更高效地处理数据。本文将详细介绍各种函数，包括正则替换、日期函数、通用函数、转换函数以及存储函数等。 #### 正则替换函数 REGEXP_REPLACE `REGEXP_REPLACE` 函数可用于进行正则表达式替换。例如： ```sql REGEXP_REPLACE(COMMENTS,'F[A-Z]*','?',1,1,'I') ``` 示例结果如下： ```plaintext Hired as the new manager ? the accounting department Founder and ? employee of the company Project (half a month) ? the ACCOUNTING department ``` 需要注意的是，如果要进行不区分大小写的替换，必须为所有函数参数指定值，包括 `pos` 和 `occurrence` 的默认值。`WHERE` 子句可确保查询仅返回匹配的行。同时，“Founder” 一词未被替换，因为其前面没有空格，要留意模式开头的空格。 #### 日期函数 在讨论各种 Oracle 日期函数之前，先回顾一下使用预定义的 ANSI/ISO SQL 标准格式指定日期/时间相关常量（或字面量）的语法。 | 字面量 | 示例 | | --- | --- | | DATE 'yyyy-mm-dd' | DATE '2014-09-25' | | TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff' [AT TIME ZONE '...'] | TIMESTAMP '2014-09-25 23:59:59.99999' AT TIME ZONE 'CET' | | TIMESTAMP 'yyyy-mm-dd hh24:mi:ss.ffffff {+|-}hh:mi' | TIMESTAMP '2014-09-25 23:59:59.99 -5:00' | | INTERVAL 'expr' <qualifier> | INTERVAL '1' YEAR <br> INTERVAL '1 2:3' DAY TO MINUTE | 你可以使用 SQL*Plus 的 `&` 替换方法进行实验，例如： ```sql select &input_date from dual; ``` 如果你直接输入字母数字字符串，如 '21 - JUN - 04'，则必须依赖 Oracle 的隐式转换。此隐式转换的成功与否取决于会话的 `NLS_DATE_FORMAT` 和 `NLS_TIMESTAMP_FORMAT` 参数设置。若要查看会话的所有当前 NLS 参数设置的概述，可使用以下查询： ```sql select * from nls_session_parameters; ``` 在 SQL Developer 中，可通过 `Tools/Preferences/Database/NLS` 在会话级别查看和更改此信息。在 SQL*Plus（当然也包括 SQL Developer）中，可使用以下语句设置首选日期格式： ```sql SQL> alter session set NLS_DATE_FORMAT = 'dd Month yyyy hh24:mi:ss'; SYSDATE -------------------------- 13 February 2014 10:33:42 ``` 常见的 Oracle 日期函数如下： | 函数 | 描述 | | --- | --- | | ADD_MONTHS(d, n) | 日期 d 加上 n 个月 | | MONTHS_BETWEEN(d, e) | 日期 d 和 e 之间的月数 | | LAST_DAY(d) | 包含日期 d 的月份的最后一天 | | NEXT_DAY(d, weekday) | d 之后的第一个工作日（周一、周二等） | | NEW_TIME(d, z1, z2) | 将日期/时间从时区 z1 转换为 z2 | | ROUND(d[, fmt]) | 按 fmt 对 d 进行四舍五入（fmt 的默认值为午夜） | | TRUNC(d[, fmt]) | 按 fmt 对 d 进行截断（fmt 的默认值为午夜） | | EXTRACT(c FROM d) | 从表达式 d 中提取日期/时间组件 c | 下面是这些函数的具体示例： - **EXTRACT 函数**：可使用 ANSI/ISO 标准的 `EXTRACT` 函数提取日期或时间戳表达式的各个组件。 ```sql select bdate , extract(year from bdate) as year_of_birth , extract(month from bdate) as month_of_birth , extract(day from bdate) as day_of_birth from employees where ename = 'KING'; ``` 结果如下： ```plaintext BDATE YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH ----------- ------------- -------------- ------------ 17-NOV-1952 1952 11 17 ``` - **ROUND 和 TRUNC 函数**：`ROUND` 和 `TRUNC` 函数支持的日期格式（fmt）如下表所示。默认格式为 'DD'，即四舍五入或截断到午夜。例如，`TRUNC(SYSDATE,'DD')` 将当前系统日期和时间截断到午夜。 | 格式 | 描述 | | --- | --- | | CC, SCC | 世纪，带或不带负号（公元前） | | [S]YYYY, [S]YEAR, YYY, YY, Y | 年份（多种表示形式） | | IYYY, IYY, IY, I | ISO 年份 | | Q | 季度 | | MONTH, MON, MM, RM | 月份（全名、缩写名、数字、罗马数字） | | IW, WW | （ISO）周数 | | W | 星期几 | | DDD, DD, J | 日（年中的日/月中的日/儒略日） | | DAY, DY, D | 最接近的星期日 | | HH, HH12, HH24 | 小时 | | MI | 分钟 | - **MONTHS_BETWEEN 和 ADD_MONTHS 函数**： ```sql -- MONTHS_BETWEEN 函数示例 select ename, months_between(sysdate,bdate) from employees where deptno = 10; -- ADD_MONTHS 函数示例 select add_months('29-JAN-1996', 1) add_months_1 , add_months('29-JAN-1997', 1) add_months_2 , add_months('11-AUG-1997',-3) add_months_3 from dual; ``` `MONTHS_BETWEEN` 函数结果： ```plaintext ENAME MONTHS_BETWEEN(SYSDATE,BDATE) -------- ----------------------------- CLARK 467.5042 KING 618.2461 MILLER 508.0525 ``` `ADD_MONTHS` 函数结果： ```plaintext ADD_MONTHS_1 ADD_MONTHS_2 ADD_MONTHS_3 ------------ ------------ ------------ 29-FEB-1996 28-FEB-1997 11-MAY-1997 ``` 需要注意非闰年的情况，并且在使用 `ADD_MONTHS` 函数时，最好使用 `DATE` 关键字或 `TO_DATE` 转换函数来指定日期字面量，以避免因隐式转换而出现错误。 - **NEXT_DAY 和 LAST_DAY 函数**： ```sql select sysdate , next_day(sysdate,'SAT') as next_sat , last_day(sysdate) as last_day , round(sysdate,'YY') as round_yy , trunc(sysdate,'CC') as trunc_cc from dual; ``` 结果如下： ```plaintext SYSDATE NEXT_SAT LAST_DAY ROUND_YY TRUNC_CC ----------- ----------- ----------- ----------- ----------- 13-feb-2014 15-feb-2014 28-feb-2014 01-jan-2014 01-jan-2001 ``` #### 通用函数 重要的通用（与数据类型无关）函数如下表所示： | 函数 | 描述 | | --- | --- | | GREATEST(a, b, ...) | 函数参数中的最大值 | | LEAST(a, b, ...) | 函数参数中的最小值 | | NULLIF(a, b) | 如果 a = b，则返回 NULL；否则返回 a | | COALESCE(a, b, ...) | 第一个非 NULL 参数（如果所有参数都为 NULL，则返回 NULL） | | NVL(x, y) | 如果 x 为 NULL，则返回 y；否则返回 x | | NVL2(x, y, z) | 如果 x 不为 NULL，则返回 y；否则返回 z | | CASE x when a1 then b1 when a2 then b2 ... else y end | 根据 x 的值返回相应的结果 | | DECODE(x, a1, b1, a2, b2, ..., an, bn [, y]) | 如果 x = a1，则返回 b1；如果 x = a2，则返回 b2；...；如果 x = an，则返回 bn