Oracle函数及方法

博主：情天
发布时间：2024 年 06 月 02 日
702 次浏览
暂无评论
20216字数
分类：笔记

# Oracle常用函数及方法汇总

# 自整理操作

## 日期时间相关

### 时间和字符串转换

#### 字符串转换时间

```sql
函数: to_date(时间字符串, 转换格式)
  
# 24小时制:
to_date('2024-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
  
# 其他自定义格式化:
to_date(v.CHECK_TIME, 'yyyy')
to_date(v.CHECK_TIME, 'yyyy-mm')
to_date(v.CHECK_TIME, 'yyyy-mm-dd')
```

#### 时间转字符串

```sql
函数: to_char(日期, 转换格式)
to_char(v.CHECK_TIME, 'yyyy-mm-dd hh24:mi:ss')
```

### 当前时间

```sql
sysdate  --返回当前系统日期和时间，精确到秒
systimestamp  --返回当前系统日期和时间，精确到毫秒
select sysdate from dual;
select systimestamp from dual;
```

### 日期运算

#### 使用函数进行运算

```sql
# 查询当前时间减去5分钟sql：
SELECT sysdate - INTERVAL '5' MINUTE FROM DUAL;

# 查询当前时间加上5小时sql：
SELECT sysdate + INTERVAL '5' HOUR FROM DUAL;

# 查询当前时间减去5天sql：
SELECT sysdate - INTERVAL '5' DAY FROM DUAL;
```

#### 两个日期进行运算

##### 相差天数

oracle中两个日期直接进行相减, 结果的单位就是天

```sql
-- 相差天数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss'))
from dual;
```

##### 相差小时, 分钟, 秒数

由于结果单位是填, 所以需要结果为小时或者其他单位时 可进行转换, 比如: 小时数 = 结果的天数 * 24

```sql
-- 相差小时数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24
from dual;

-- 相差分钟数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24 * 60 
from dual;

-- 相差秒数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24 * 60 * 60
from dual;
```

##### 相差月份

相差月数使用 months_between() 函数进行计算

```sql
--oracle两个日期的相差月数--
--1）月份都是最后一天，A日期 > B日期 ,返回整数 ---
select months_between(TO_DATE('2024-11-01 00:00:01','yyyy-mm-dd hh24:mi:ss'), TO_DATE('2024-10-01 00:00:01','yyyy-mm-dd hh24:mi:ss')) As 相差月份 from dual;
 
--2）月份都是最后一天，B日期 > A日期 ,返回负数 ---
select months_between(TO_DATE('2024-09-01 00:00:01','yyyy-mm-dd hh24:mi:ss'), TO_DATE('2024-10-01 00:00:01','yyyy-mm-dd hh24:mi:ss')) As 相差月份 from dual;
 
--3）月份天数不一样，A日期 > B日期 ,返回带小数的数字---
select months_between(TO_DATE('2024-10-15 00:00:01','yyyy-mm-dd hh24:mi:ss'), TO_DATE('2024-10-01 00:00:01','yyyy-mm-dd hh24:mi:ss')) As 相差月份 from dual;
```

##### 相差年份

原先月数/12

```sql
--oracle两个日期的相差年份--
select months_between(TO_DATE('2026-11-02 00:00:01','yyyy-mm-dd hh24:mi:ss'), TO_DATE('2024-11-02 00:00:01','yyyy-mm-dd hh24:mi:ss')) /12 As 相差年份 from dual;
```

### TRUNC 函数截断时间

Oracle 数据库中的 TRUNC 函数主要用于截断日期或数字。它可以将日期值截断到指定的日期部分，也可以将数字截断到指定的小数位数

```sql
函数: TRUNC(时间, 截取格式[包含])
TRUNC(CHECK_TIME, 'HH24')	-- 只保留日期的年月日部分和小时
TRUNC(CHECK_TIME, 'dd')		-- 只保留日期的年月日
TRUNC(CHECK_TIME, 'mm')
TRUNC(CHECK_TIME, 'yyyy')
TRUNC(CHECK_TIME, 'Q')		-- 截取为当前季度的开始时间
```

#### TRUNC 用于日期的截断

`TRUNC` 函数可以将日期值截断到指定的日期部分。以下是各种截断方式及示例。

##### 按年截断

将日期截断到当前年的第一天。

```sql
SELECT TRUNC(SYSDATE, 'YYYY') AS start_of_year FROM dual;
```

##### 按月截断

将日期截断到当前月的第一天。

```sql
SELECT TRUNC(SYSDATE, 'MM') AS start_of_month FROM dual;
```

##### 按季度截断

将日期截断到当前季度的第一天。

```sql
-- 当前季度的开始时间
SELECT TRUNC(SYSDATE, 'Q') AS start_of_quarter FROM dual;

-- 当前季度的结束时间
SELECT TRUNC(SYSDATE, 'Q') + interval '3' month  - interval '1' second AS end_of_quarter FROM dual;

-- 获取当前季度的开始和结束时间
SELECT 
    TRUNC(SYSDATE, 'Q') AS start_of_quarter,
    TRUNC(SYSDATE, 'Q') + interval '3' month  - interval '1' second AS end_of_quarter
FROM 
    dual;
```

##### 解释

* **`TRUNC(SYSDATE, 'Q')`** : 截断当前日期到当前季度的第一天。
* **`TRUNC(SYSDATE, 'Q') + interval '3' month  - interval '1' second`**: 通过将当前季度的第一天加上三个月得到下一个季度的第一天，然后减去一秒得到当前季度的最后一天的时间。

这种方法可以帮助我们准确地获取当前季度的开始和结束时间。

‍

##### 按日截断

不指定单位默认就是将日期截断到当天的开始，即去除时间部分。

```sql
SELECT TRUNC(SYSDATE) AS start_of_day FROM dual;
```

##### 按小时截断

将日期截断到当前小时的开始。

```sql
SELECT TRUNC(SYSDATE, 'HH') AS start_of_hour FROM dual;
```

##### 按分钟截断

将日期截断到当前分钟的开始。

```sql
SELECT TRUNC(SYSDATE, 'MI') AS start_of_minute FROM dual;
```

##### 按秒截断

将日期截断到当前秒的开始（实际不会改变，因为秒级别已经是最小单位）。

```sql
SELECT TRUNC(SYSDATE, 'SS') AS start_of_second FROM dual;
```

#### TRUNC 用于数字的截断

`TRUNC` 函数也可以用于截断数字，去掉小数部分或截断到指定的小数位数。

##### 截断到整数部分

将数字截断到整数部分。

```sql
SELECT TRUNC(123.456) AS truncated_number FROM dual;
```

##### 截断到小数位数

将数字截断到指定的小数位数。

```sql
SELECT TRUNC(123.456, 1) AS truncated_number FROM dual;
SELECT TRUNC(123.456, 2) AS truncated_number FROM dual;
```

#### 使用场景

##### 按季度统计数据

按季度统计销售数据。

```sql
SELECT
    TRUNC(sale_date, 'Q') AS quarter,
    SUM(amount) AS total_sales
FROM
    sales
GROUP BY
    TRUNC(sale_date, 'Q');
```

##### 按月份统计数据

按月份统计访问量。

```sql
SELECT
    TRUNC(access_time, 'MM') AS month,
    COUNT(*) AS total_visits
FROM
    web_access_log
GROUP BY
    TRUNC(access_time, 'MM');
```

##### 舍弃小数部分

在财务计算中舍弃小数部分。

```sql
SELECT
    employee_id,
    TRUNC(salary * 0.1) AS bonus
FROM
    employees;
```

#### 总结

`TRUNC` 函数在 Oracle 中是一个非常有用的函数，可以方便地截断日期或数字。在日期处理方面，可以截断到年、月、季度、日、小时、分钟和秒。在数字处理方面，可以截断到整数或指定的小数位。掌握 `TRUNC` 函数的使用方法，可以大大提高数据库查询和数据处理的效率。

## 前后xx分钟去重

在oracle中要对一批数据按照某些字段进行去重, 但是要控制前后15分钟内重复出现的才视为重复, 也就是对这批数据进行前后15分钟去重

主要使用窗口函数 `ROW_NUMBER()` + `LAG()` 和 `LEAD()` 来完成

### 1. 问题抽象

前后时间去重的需求通常出现在以下场景：

* 某些事件在短时间内频繁发生，但这些事件本质上可以归为一个（例如设备重复触发、传感器异常等）。
* 需要根据时间窗口去重，确保在某个时间范围内，只保留最有代表性的记录。

这种需求可以抽象为：对于某个分组（如设备、用户、地点等），检查当前记录与前后的记录是否在特定时间间隔内，去掉时间间隔小于XX分钟的重复记录。

### 2. 核心思路

实现这一逻辑的核心是：

* 分组：按某个或多个字段（如设备ID、车牌号、站点等）将记录分组。
* 排序：按时间字段进行排序，以确保我们可以准确找到前后记录。
* 时间差计算：通过获取当前记录的前后相邻记录的时间，计算与它们的时间差。

在SQL中，借助 `窗口函数` 可以非常方便地完成以上操作。

### 3. 使用窗口函数的技巧

为了获取前后记录，SQL 提供了 `LAG` 和 `LEAD` 窗口函数：

* LAG：获取当前记录前面的记录值（在同一分组内按某个字段排序）。
* LEAD：获取当前记录后面的记录值（同样在同一分组内按某个字段排序）。

窗口函数的语法形式如下：

```sql
LAG(<字段>, <偏移量>, <默认值>) OVER (PARTITION BY <分组字段> ORDER BY <排序字段>)
```

* `<字段>` 是你想获取的前后记录中的值（通常是时间字段）。
* `<偏移量>` 表示获取前几条或后几条记录，通常是1表示前一条或后一条记录。
* `<默认值>` 如果没有前后记录，窗口函数会返回默认值（如 `NULL`）。
* `PARTITION BY` 用来定义分组规则，保证窗口函数只在分组内有效。
* `ORDER BY` 用来按照时间顺序排序。

### 4. 技术实现步骤

1. 数据分组与排序： 使用 `PARTITION BY` 进行分组，按时间排序。这样可以确保在同一组内，前后记录是按时间先后排列的。
2. 计算前后时间差： 利用 `LAG` 和 `LEAD` 获取当前记录的前后记录的时间。通过对比前后记录的时间差，判断是否需要去重。例如：

```sql
    (当前记录时间 - 前一条记录时间) > 指定时间间隔
    ```
3. 过滤重复记录： 在计算出前后时间差后，使用 `WHERE` 条件过滤掉那些时间差小于指定间隔的记录。即保留时间间隔较大的记录。
4. 优化注意事项：

* 大数据集的性能优化：窗口函数在处理大规模数据时，可能会产生性能瓶颈，适当增加索引，特别是在 `PARTITION BY` 和 `ORDER BY` 字段上，能显著提高查询性能。
    * 时间精度：确保时间字段的精度足够（如精确到秒或毫秒），以避免不必要的时间差误判。

### 5. 经验与技巧

* 窗口函数的灵活性：窗口函数是强大且灵活的工具，不仅仅局限于计算前后时间差。在分组统计、排名等场景中也十分有用，熟练掌握 `LAG` 和 `LEAD` 是解决去重问题的关键。
* 条件过滤的控制：为了避免意外删除过多记录，尤其在处理较大的时间窗口时，务必保证过滤逻辑准确无误，并进行充分测试。
* 简化多层过滤逻辑：为了减少多次扫描表，窗口函数通常比子查询或自连接更高效，尤其当涉及多个分组字段时，可以显著提高可读性和维护性。

### 前后xx分钟去重的SQL逻辑总结

```sql
-- 今年按月汇总电警数据推送交警数据统计（交警)  同站点前后十五分钟同车牌需要去重
-- 总量统计
WITH ranked_data AS (
    SELECT
        w.*,
        ROW_NUMBER() OVER (PARTITION BY w.hphm, w.dmdm ORDER BY w.jgsj) AS rn
    FROM
        wf_cp w
    WHERE
        w.jgsj >= DATE '2024-01-01'
      AND w.jgsj < DATE '2024-10-01' and (w.wflx = 11 or  (w.wflx = 5 and w.xs = 2)) and w.bz = 1 and w.SCJG = 1 and w.SC_TIME is not null
),
deduped_data AS (
    SELECT
        r.*,
        LAG(r.jgsj, 1) OVER (PARTITION BY r.hphm, r.dmdm ORDER BY r.jgsj) AS prev_jgsj,
        LEAD(r.jgsj, 1) OVER (PARTITION BY r.hphm, r.dmdm ORDER BY r.jgsj) AS next_jgsj
    FROM
        ranked_data r
),
filtered_data AS (
SELECT
    d.*
FROM
    deduped_data d
WHERE
    (d.prev_jgsj IS NULL OR (d.jgsj - d.prev_jgsj) * 24 * 60 > 15)
  AND (d.next_jgsj IS NULL OR (d.next_jgsj - d.jgsj) * 24 * 60 > 15)
)
-- 按月统计压中间线数量 前后15分钟去重
select f.month as 时间, f.wflx as 违法类型, count(*)
from (
         SELECT
             trunc(f.jgsj, 'mm') AS month,
             case when f.wfdm = 11160 then '闯禁令' else '压中间线' end as wflx
         FROM
             filtered_data f
     ) f
GROUP BY
    month,
    wflx
ORDER BY
    month,
    wflx;
```

在电警数据统计中，为了去重某些同站点、同车牌且发生在前后15分钟内的记录，可以使用窗口函数配合`LAG`和`LEAD`函数来实现。这段SQL的逻辑如下：

### 步骤解析：

1. 初步筛选数据: 首先，从数据表 `wf_cp` 中筛选出符合条件的记录，具体条件包括：

* 违法类型 (`w.wflx`) 为11或5并且行驶速度 (`w.xs`) 为2。
    * 标识 (`w.bz`) 为1，且数据已经上传 (`w.SCJG = 1`)。
    * 上传时间 (`w.SC_TIME`) 不为空。
    * 违法时间 (`w.jgsj`) 在指定的时间范围内。

这一部分数据存入 `ranked_data` 临时表中，同时利用 `ROW_NUMBER()` 进行分组排序，按车牌 (`w.hphm`) 和站点 (`w.dmdm`) 分别进行编号。
2. 计算前后记录的时间差: 使用窗口函数 `LAG` 和 `LEAD` 计算每条记录在同一站点、同一车牌的前后记录的时间，生成 `prev_jgsj` 和 `next_jgsj`。

* `LAG` 返回当前记录的上一条记录的时间（即前一条记录）。
    * `LEAD` 返回当前记录的下一条记录的时间（即后一条记录）。

这部分数据存入 `deduped_data` 临时表中。
3. 去重逻辑: 去重的条件是在当前记录与前后记录的时间差超过15分钟。通过判断：

* 如果没有前一条记录（即 `prev_jgsj IS NULL`）或者当前记录与前一条记录的时间差大于15分钟。
    * 如果没有后一条记录（即 `next_jgsj IS NULL`）或者后一条记录与当前记录的时间差大于15分钟。

满足上述条件的记录被保留，存入 `filtered_data` 临时表中。
4. 按月汇总统计: 处理完去重逻辑后，按月份 (`trunc(f.jgsj, 'mm')`) 对记录进行分组，并根据违法代码 (`wfdm`) 进行分类统计，生成最终的统计结果。

### 实用场景：

* 这种前后XX分钟去重的逻辑常用于交通违法、设备监控等领域，用于避免短时间内的重复记录对数据统计造成干扰。
* `LAG` 和 `LEAD` 窗口函数是实现这种去重逻辑的关键工具，通过它们可以轻松获得前后记录的时间，从而计算时间差。

通过这种方式处理数据，可以更精确地统计某一时段内的唯一违法行为，避免重复记录的影响。这种逻辑可以灵活应用于其他类似的场景中。

## JSON 字段值处理

### 理解 JSON 数据结构

首先，了解 JSON 数据的结构和层次。例如，以下是你提供的 JSON 示例：

```json
{
    "auditNoeNo": 4,
    "auditNoeNoMap": [
        {"count": 1, "reason": "跨道行驶", "reasonNumber": "110"},
        {"count": 3, "reason": "特殊车辆", "reasonNumber": "121"}
    ],
    "auditNoeOk": 2,
    "auditThreeNo": 0,
    "auditThreeNoMap": [],
    "auditThreeOk": 2,
    "auditTotal": 6,
    "auditTwo1No": 0,
    "auditTwo1NoMap": [],
    "auditTwo1Ok": 2,
    "auditTwo2No": 0,
    "auditTwo2NoMap": [],
    "auditTwo2Ok": 2,
    "checkTotal": 2434,
    "compareTotal": 0,
    "nodeId": "G00153100200501",
    "nodeName": "G15北青公路收费站北广场",
    "overTotal": 0,
    "punishTotal": 0,
    "submitTotal": 0
}
```

### 使用 JSON_VALUE 提取简单字段

对于 JSON 中的简单值，可以使用 `JSON_VALUE` 提取。例如：

```sql
SELECT JSON_VALUE(o.json_data, '$.auditNoeNo') AS auditNoeNo
FROM OW_STAT_ANALYSIS_PROCESS o
WHERE o.DATA_DAY >= DATE '2024-04-01' 
  AND o.DATA_DAY < DATE '2024-05-01' 
  AND o.node_id = 'G00153100200501';
```

### 使用 JSON_TABLE 处理数组和嵌套数据

对于 JSON 数组和嵌套数据，可以使用 `JSON_TABLE` 函数将 JSON 数据转换为关系表格格式。

#### 提取数组中的数据

假设你要提取 `auditNoeNoMap` 数组中的 `count` 字段并计算其总和：

```sql
SELECT 
    SUM(t.count) AS sum_a1
FROM 
    OW_STAT_ANALYSIS_PROCESS o,
    JSON_TABLE(
        o.json_data,
        '$.auditNoeNoMap[*]'
        COLUMNS (
            count NUMBER PATH '$.count'
        )
    ) t
WHERE 
    o.DATA_DAY >= DATE '2024-04-01' 
    AND o.DATA_DAY < DATE '2024-05-01' 
    AND o.node_id = 'G00153100200501';
```

### 提取多个字段

如果需要提取多个字段，例如 `count` 和 `reason`，可以在 `JSON_TABLE` 中定义多个列：

```sql
SELECT 
    t.count,
    t.reason
FROM 
    OW_STAT_ANALYSIS_PROCESS o,
    JSON_TABLE(
        o.json_data,
        '$.auditNoeNoMap[*]'
        COLUMNS (
            count NUMBER PATH '$.count',
            reason VARCHAR2(50) PATH '$.reason'
        )
    ) t
WHERE 
    o.DATA_DAY >= DATE '2024-04-01' 
    AND o.DATA_DAY < DATE '2024-05-01' 
    AND o.node_id = 'G00153100200501';
```

### 将结果集与其他表连接

在处理复杂的 JSON 数据后，可以将其与其他表进行连接，类似于普通 SQL 查询。例如：

```sql
WITH json_data AS (
    SELECT 
        t2.UNIQUE_ID,
        t2.check_time,
        t2.VEHICLE_NO,
        JSON_TABLE(
            t2.json_data,
            '$.auditNoeNoMap[*]'
            COLUMNS (
                count NUMBER PATH '$.count',
                reason VARCHAR2(50) PATH '$.reason'
            )
        ) AS t
    FROM OW_CHECK_VEHICLE t2
    WHERE t2.DATA_DAY >= DATE '2024-04-01'
      AND t2.DATA_DAY < DATE '2024-05-01'
      AND t2.node_id = 'G00153100200501'
)
SELECT 
    n.f_nodeid AS nodeId,
    COUNT(1)
FROM 
    json_data jd
LEFT JOIN 
    OW_CHECK_VEHICLE_RECORD t ON jd.UNIQUE_ID = t.UNIQUE_ID
LEFT JOIN 
    nodecode n ON n.nodeid = jd.EQUIP_CODE
WHERE 
    t.check_time >= DATE '2024-04-01'
    AND t.check_time < DATE '2024-05-01'
    AND t.audit_type = 1
GROUP BY 
    n.f_nodeid;
```

### 创建视图（可选）

如果经常需要处理相同的 JSON 数据结构，可以考虑创建一个视图，以简化查询操作：

```sql
CREATE VIEW vw_audit_data AS
SELECT 
    o.DATA_DAY,
    o.node_id,
    jt.count,
    jt.reason
FROM 
    OW_STAT_ANALYSIS_PROCESS o,
    JSON_TABLE(
        o.json_data,
        '$.auditNoeNoMap[*]'
        COLUMNS (
            count NUMBER PATH '$.count',
            reason VARCHAR2(50) PATH '$.reason'
        )
    ) jt;
```

使用视图之后，查询变得更加简单：

```sql
SELECT 
    node_id,
    SUM(count) AS sum_a1
FROM 
    vw_audit_data
WHERE 
    DATA_DAY >= DATE '2024-04-01' 
    AND DATA_DAY < DATE '2024-05-01'
GROUP BY 
    node_id;
```

通过这些步骤，可以系统地处理存储在数据库字段中的复杂 JSON 数据，使得处理 JSON 数据像处理普通 SQL 查询一样简单。

‍

## 窗口函数（Oracle 12c+）

### ROW_NUMBER() 函数

`ROW_NUMBER()` 是 Oracle 数据库中的一个窗口函数，用于为查询结果集中的每一行分配一个唯一的行号。它按指定的顺序对记录进行编号，常用于需要对结果集进行排序和分页的场景。

#### 语法

```sql
ROW_NUMBER() OVER (PARTITION BY column_name1, column_name2, ... ORDER BY column_name3, column_name4, ...)
```

* `PARTITION BY`：可选子句，用于将数据分组。在每个分区内，行号从1开始。如果没有指定 `PARTITION BY`，则对整个结果集进行编号。
* `ORDER BY`：必需子句，指定行号分配的顺序。

#### 查询示例

查询 `OW_CHECK_VEHICLE_RECORD` 中 `UNIQUE_ID` 相同的记录，按 `AUDIT_TYPE` 倒序排序，并使用 `ROW_NUMBER` 函数对记录编号，最后外层查询中获取 `rn=1` 即 `AUDIT_TYPE` 最大的那条记录：

```sql
SELECT RECORD_ID, UNIQUE_ID, AUDIT_TYPE
FROM (
    SELECT RECORD_ID, UNIQUE_ID, AUDIT_TYPE,
           ROW_NUMBER() OVER (PARTITION BY UNIQUE_ID ORDER BY AUDIT_TYPE DESC) AS rn
    FROM OW_CHECK_VEHICLE_RECORD
    WHERE CHECK_TIME >= TO_DATE('2021-01-01', 'YYYY-MM-DD') AND CHECK_TIME < TO_DATE('2022-01-01', 'YYYY-MM-DD')
)
WHERE rn = 1;
```

#### 更新操作示例

结合 `ROW_NUMBER()` 函数做 `UPDATE` 操作：

```sql
MERGE INTO OW_CHECK_VEHICLE o1
USING (
    SELECT o2.RECORD_ID, o2.UNIQUE_ID,
           ROW_NUMBER() OVER (PARTITION BY o2.UNIQUE_ID ORDER BY o2.AUDIT_TYPE DESC) AS rn
    FROM OW_CHECK_VEHICLE_RECORD o2
    WHERE o2.CHECK_TIME >= TO_DATE('2021-01-01', 'YYYY-MM-DD') AND o2.CHECK_TIME < TO_DATE('2022-01-01', 'YYYY-MM-DD')
) o
ON (o1.UNIQUE_ID = o.UNIQUE_ID AND o.rn = 1)
WHEN MATCHED THEN
    UPDATE SET o1.RECORD_ID = o.RECORD_ID;
```

### OVER (PARTITION BY)

`OVER (PARTITION BY)` 是Oracle数据库中的一个窗口函数，用于在结果集的每个分区上执行计算。它通常与聚合函数（如SUM、AVG、COUNT等）一起使用，以便在每个分区上计算聚合值。

`PARTITION BY` 子句用于将结果集分成多个分区，然后对每个分区应用窗口函数。

示例: 适用于在语句不进行group by 分组时, 单独进行分组聚合计算

```sql
SUM(r1.compareTotal) OVER (PARTITION BY r1.dataDay,r1.nodeId) AS compareTotal
```

‍

## MERGE INTO 函数

`MERGE INTO` 语句是 Oracle 数据库中的一种数据操作语言 (DML) 语句，用于将数据从一个表合并到另一个表中。它可以在一个操作中同时执行插入和更新操作，这在需要同步两个表的数据时非常有用。

### 语法

```sql
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET t.column1 = s.column1, t.column2 = s.column2, ...
WHEN NOT MATCHED THEN
    INSERT (t.id, t.column1, t.column2, ...)
    VALUES (s.id, s.column1, s.column2, ...);
```

* `target_table`：目标表，即要更新或插入数据的表。
* `source_table`：源表，即提供数据的表。
* `ON`：指定合并的条件。
* `WHEN MATCHED THEN`：当合并条件匹配时执行的操作，通常是 `UPDATE`。
* `WHEN NOT MATCHED THEN`：当合并条件不匹配时执行的操作，通常是 `INSERT`。

### 查询并批量更新

使用 `MERGE INTO` 语句加 `USING` 合并查询结果表，然后更新目标表字段：

```sql
MERGE INTO OW_CHECK_VEHICLE_RECORD o
USING (
    SELECT f.UNIQUE_ID, f.FIRST_TRIAL_NAME, f.ABANDON_REASON, f.SCREE_TIME
    FROM FLOW_VEHICLE_SORT f
    WHERE f.CHECK_TIME >= TO_DATE('2023-01-01', 'YYYY-MM-DD') AND f.CHECK_TIME < TO_DATE('2023-11-01', 'YYYY-MM-DD')
) f
ON (o.UNIQUE_ID = f.UNIQUE_ID AND o.AUDIT_TYPE = 1)
WHEN MATCHED THEN
UPDATE SET o.TRIAL_NAME = f.FIRST_TRIAL_NAME, o.REASON = f.ABANDON_REASON, o.TRIAL_TIME = f.SCREE_TIME;
```

### 存在就更新不存在就新增

使用 `MERGE INTO` 语句实现存在即更新，不存在即新增：

```sql
MERGE INTO OW_REGULATORY_INSPECTION ri
USING (
    SELECT ? as INSPECTION_ID, ? as MANAGE_NUMBER, ? as ENGINEER, ? as INSPECTION_START_DATE, 
           ? as INSPECTION_END_DATE, ? as NODE_ID, ? as INSPECTION_FILE, ? as REPAIR_DESC, 
           ? as FILE_NAME, ? as STATUS, ? as PAVEMENT_DISTRESS, ? as SENSOR, ? as WEIGHT, 
           ? as DELETED, ? as CREATE_TIME, ? as UPDATE_TIME 
    FROM dual
) s 
ON (ri.INSPECTION_ID = s.INSPECTION_ID) 
WHEN MATCHED THEN 
    UPDATE SET ri.MANAGE_NUMBER = s.MANAGE_NUMBER, ri.ENGINEER = s.ENGINEER, 
               ri.INSPECTION_START_DATE = s.INSPECTION_START_DATE, 
               ri.INSPECTION_END_DATE = s.INSPECTION_END_DATE, ri.NODE_ID = s.NODE_ID, 
               ri.INSPECTION_FILE = s.INSPECTION_FILE, ri.REPAIR_DESC = s.REPAIR_DESC, 
               ri.FILE_NAME = s.FILE_NAME, ri.STATUS = s.STATUS, 
               ri.PAVEMENT_DISTRESS = s.PAVEMENT_DISTRESS, ri.SENSOR = s.SENSOR, 
               ri.WEIGHT = s.WEIGHT, ri.DELETED = s.DELETED, 
               ri.CREATE_TIME = s.CREATE_TIME, ri.UPDATE_TIME = s.UPDATE_TIME 
WHEN NOT MATCHED THEN 
    INSERT VALUES (s.INSPECTION_ID, s.MANAGE_NUMBER, s.ENGINEER, s.INSPECTION_START_DATE, 
                   s.INSPECTION_END_DATE, s.NODE_ID, s.INSPECTION_FILE, s.REPAIR_DESC, 
                   s.FILE_NAME, s.STATUS, s.PAVEMENT_DISTRESS, s.SENSOR, s.WEIGHT, 
                   s.DELETED, s.CREATE_TIME, s.UPDATE_TIME);
```

## 根据条件转换内容

### 使用 CASE WHEN 语句

#### 示例 1

```sql
SELECT column1, 
       CASE WHEN column2 = 'xxxxx不通过' THEN 1115 ELSE 0 END AS status
FROM your_table;
```

#### 示例 2

```sql
SELECT column1, 
       CASE 
         WHEN column2 = 'xxxxx不通过' THEN 1115
         WHEN column2 = 'xxxxx通过' THEN 1116
         ELSE 0 
       END AS status
FROM your_table;
```

#### 实际 SQL

```sql
SELECT f.UNIQUE_ID, 
       CASE f.REASON
         WHEN '同一车牌同一时间称重数据非最小值' THEN 1111
         WHEN '超限值小于1吨' THEN 1112
         WHEN '视频超限车辆未经过称重检测设备' THEN 1113
         WHEN '非正常行驶' THEN 1114
         WHEN '其他' THEN 1115
         ELSE NULL 
       END AS REASON
FROM FLOW_VEHICLE_SORT f
WHERE f.CHECK_TIME >= TO_DATE('2023-01-01', 'YYYY-MM-DD') AND f.CHECK_TIME < TO_DATE('2023-11-01', 'YYYY-MM-DD');
```

## Oracle 序列（SEQUENCE）

### 创建序列

类似 MySQL 中的自增：

```sql
-- 
CREATE SEQUENCE name
INCREMENT BY 1 
MAXVALUE number
MINVALUE number
NOCACHE;

-- INCREMENT BY 每次自增的数量, MAXVALUE 自增最大值限制, MINVALUE 起始值
CREATE SEQUENCE SXYJ_SEQUENCE_NAME 
INCREMENT BY 1 
MAXVALUE 9999999999999999999999999999 
MINVALUE 1
NOCACHE;
```

### 示例

```bash
-- 创建表
CREATE TABLE "T_STU" (
  "id" NUMBER NOT NULL PRIMARY KEY,
  "name" VARCHAR2(255)
);
 
-- 创建序列
CREATE SEQUENCE "Seq01" MINVALUE 1 INCREMENT BY 1 NoMaxValue START WITH 1;
 
-- 创建触发器
create TRIGGER "Trigger01" BEFORE INSERT ON "T_STU"
FOR EACH ROW 
BEGIN
  SELECT "Seq01".nextval INTO :NEW."id" FROM dual;
END;
 
-- 插入数据
INSERT INTO "T_STU" ("name") VALUES ('小红');
 
 
-- 查看结果
SELECT * FROM "T_STU";

----------
-- 创建序列
CREATE SEQUENCE "SYS_DICT_TYPE_SEQ" MINVALUE 1 INCREMENT BY 1 NoMaxValue START WITH 20;
 
-- 创建触发器
create TRIGGER "SYS_DICT_TYPE_T1" BEFORE INSERT ON "SYS_DICT_TYPE"
FOR EACH ROW 
BEGIN
  SELECT "SYS_DICT_TYPE_SEQ".nextval INTO :NEW."DICT_ID" FROM dual;
END;
```

### 删除并重新创建序列

1. 删除现有序列：

```sql
DROP SEQUENCE "SYS_DICT_TYPE_SEQ";
```

2. 重新创建序列并设置新的起始值：

```sql
CREATE SEQUENCE "SYS_DICT_TYPE_SEQ"
    MINVALUE 1
    INCREMENT BY 1
    START WITH 100;  -- 新的起始值
```

### 方法二：使用 `RESTART` 子句

如果你的数据库支持 `RESTART` 子句，可以直接使用它来重置序列的起始值：

```sql
ALTER SEQUENCE "SYS_DICT_TYPE_SEQ" RESTART WITH 100;  -- 新的起始值
```

### 示例

假设你希望将序列的起始值设置为 100：

```sql
-- 方法一：删除并重新创建序列
DROP SEQUENCE "SYS_DICT_TYPE_SEQ";

CREATE SEQUENCE "SYS_DICT_TYPE_SEQ"
    MINVALUE 1
    INCREMENT BY 1
    START WITH 100;

-- 方法二：使用 RESTART 子句（如果支持）
ALTER SEQUENCE "SYS_DICT_TYPE_SEQ" RESTART WITH 100;
```

通过以上步骤，你可以成功地将序列的起始值修改为你需要的值。

‍

## Oracle 保留两位小数（不四舍五入）

使用 `TO_CHAR` 函数结合 `TRUNC` 函数来实现保留两位小数、截取不四舍五入：

```sql
SELECT TO_CHAR(TRUNC('21642.2487', 2), '9999999990.99') AS result
FROM dual;
```

### 示例

```sql
SELECT TO_CHAR(a.total / 1000, 'FM999999999990.00')
FROM your_table a;
```

## WITH 子句

在 Oracle 中，`WITH` 子句用于定义一个或多个子查询（也称为公共表表达式，CTE），这些子查询可以在主查询中被多次引用。`WITH` 子句的主要优点是提高查询的可读性和维护性，尤其是在复杂查询中。

以下是使用 `WITH` 子句的语法：

```sql
WITH cte_1 AS (
    -- 这里是子查询
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_2 c2
LEFT JOIN ct1_1 c1 on c1.column1 = c2.column1
WHERE condition;
```

可以定义多个 CTE，每个 CTE 之间用逗号分隔。例如：

```sql
WITH cte1 AS (
    SELECT column1, column2
    FROM table1
    WHERE condition1
),
cte2 AS (
    SELECT column3, column4
    FROM table2
    WHERE condition2
)
SELECT column1, column2, column3, column4
FROM cte1
JOIN cte2 ON cte1.some_column = cte2.some_column;
```

以下是一个实际示例，演示如何使用 `WITH` 子句来简化复杂查询：

```sql
with batch_info as (
  select b.BATCH_NO
  from (
    select b.BATCH_NO
    from OW_GPS_OFF_BATCH b
    where b.MATCH_TIME is null and b.CURRENT_STATUS = 0
    order by b.create_time
  ) b
  where rownum <= 1
)
select t.*
from OW_GPS_OFF_TASK t
where t.batch_no = (SELECT BATCH_NO FROM batch_info)
```

## `FETCH FIRST` 子句

在 Oracle 数据库中，限制查询结果集的行数是一个常见的需求。从 Oracle 12c 版本开始，引入了 `FETCH FIRST` 子句，使得这一操作变得更加简洁和易读。下面详细介绍 `FETCH FIRST` 子句的使用方法、应用场景以及其优点。

### 什么是 `FETCH FIRST` 子句

`FETCH FIRST` 子句用于限制查询结果集中返回的行数。它可以在 `SELECT` 语句的末尾与 `ORDER BY` 子句一起使用，以便在排序后仅返回指定数量的行。

### 基本语法

```sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC]
FETCH FIRST n ROWS ONLY;
```

* `column1, column2, ...`：要查询的列。
* `table_name`：表的名称。
* `ORDER BY column1 [ASC|DESC]`：排序依据的列和排序顺序（升序或降序）。
* `FETCH FIRST n ROWS ONLY`：指定要返回的行数 `n`。

### 示例

#### 示例 1：按薪水降序排序并返回前 5 行

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
```

此查询将按 `salary` 降序排序，并返回前 5 个薪水最高的员工。

#### 示例 2：按雇佣日期升序排序并返回前 10 行

```sql
SELECT employee_id, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date ASC
FETCH FIRST 10 ROWS ONLY;
```

此查询将按 `hire_date` 升序排序，并返回前 10 个最早被雇佣的员工。

### 其他用法

#### 跳过前 n 行并返回接下来的 m 行

使用 `OFFSET` 子句可以实现分页查询，跳过前 n 行并返回接下来的 m 行：

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
```

此查询将按 `salary` 降序排序，跳过前 10 行，并返回接下来的 5 行。

#### 返回相同排序条件下的所有行

使用 `WITH TIES` 子句可以确保返回的行中包含所有具有相同排序条件的行，即使它们超过了指定的行数：

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;
```

此查询将按 `salary` 降序排序，返回前 5 行，并包含所有薪水相同且位于前 5 名中的员工。

### `FETCH FIRST` 的优点

1. **简洁易读**：相比于传统的 `ROWNUM` 方法，`FETCH FIRST` 子句更加简洁明了，代码可读性更高。
2. **性能优化**：`FETCH FIRST` 在执行计划中可以更好地利用数据库的优化机制，提高查询性能。
3. **更好的分页支持**：结合 `OFFSET` 子句，可以轻松实现分页查询，适用于需要分页显示大量数据的应用场景。
4. **灵活的行数控制**：使用 `WITH TIES` 可以确保在排序条件相同的情况下返回所有相关行，提供更灵活的数据展示。

### 总结

`FETCH FIRST` 子句是 Oracle 12c 及更高版本中的一个强大功能，使得限制查询结果行数变得更加简洁和高效。通过结合使用 `ORDER BY` 和 `OFFSET` 子句，可以轻松实现各种复杂的查询需求，包括分页和返回特定数量的行。其简洁的语法和强大的功能使其成为现代数据库查询的一个重要工具。

‍

## ROWNUM 限制返回条数

在 Oracle 数据库中，限制查询结果集的行数是一个常见的需求。在引入 `FETCH FIRST` 子句之前，使用 `ROWNUM` 是一种常见且经典的方式。本文将详细介绍 `ROWNUM` 的使用方法、应用场景以及其优缺点。

### 什么是 `ROWNUM`

`ROWNUM` 是 Oracle 数据库中的一个伪列，用于为查询结果集中的每一行分配一个唯一的序列号，从 1 开始递增。可以利用 `ROWNUM` 来限制查询结果集中的行数。

### 基本语法

```sql
SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= n;
```

* `column1, column2, ...`：要查询的列。
* `table_name`：表的名称。
* `ROWNUM <= n`：限制返回的行数为 `n`。

### 示例

#### 示例 1：返回前 5 行

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE ROWNUM <= 5;
```

此查询将返回表 `employees` 中的前 5 行。

#### 示例 2：按薪水降序排序并返回前 5 行

```sql
SELECT * 
FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
```

此查询将先按 `salary` 降序排序，然后返回前 5 个薪水最高的员工。

### 使用 `ROWNUM` 实现分页查询

`ROWNUM` 可以与子查询结合使用来实现分页查询。假设我们希望返回第 `m` 页的 `n` 行记录：

#### 示例 3：分页查询

```sql
SELECT *
FROM (
    SELECT a.*, ROWNUM rnum
    FROM (
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        ORDER BY salary DESC
    ) a
    WHERE ROWNUM <= :page * :pageSize
)
WHERE rnum > (:page - 1) * :pageSize;
```

此查询将返回第 `page` 页的 `pageSize` 行记录。

### `ROWNUM` 的优点

1. **简单易用**：`ROWNUM` 的语法非常简单，易于理解和使用。
2. **早期版本支持**：适用于早期版本的 Oracle 数据库（例如 11g 及之前的版本）。
3. **灵活性**：可以与各种子查询和排序方式结合使用，灵活实现多种查询需求。

### `ROWNUM` 的局限性

1. **排序问题**：`ROWNUM` 在使用时需要注意排序问题，直接在排序后的结果上应用 `ROWNUM` 会导致结果不正确。解决方法是使用嵌套子查询。
2. **分页复杂性**：实现分页查询时，需要嵌套子查询来生成序号，语法较为复杂。
3. **性能问题**：在大数据量查询时，嵌套子查询可能会影响性能，执行效率不如 `FETCH FIRST` 子句。

### `ROWNUM` 与 `FETCH FIRST` 的对比

* `ROWNUM` 更适用于 Oracle 12c 之前的版本，而 `FETCH FIRST` 是 12c 引入的新功能。
* `FETCH FIRST` 语法更简洁，支持直接分页查询，性能和优化更好。
* `ROWNUM` 在实现复杂查询时，可能需要多层嵌套子查询，语法复杂度较高。

### 总结

`ROWNUM` 是 Oracle 中一种经典的限制返回行数的方法，适用于各种版本的 Oracle 数据库。在使用 `ROWNUM` 时，需要注意排序和分页问题，通过嵌套子查询可以解决这些问题。尽管 `FETCH FIRST` 在 Oracle 12c 中提供了更简洁和高效的解决方案，但 `ROWNUM` 仍然是许多旧系统和特定场景中的重要工具。

‍

## TRUNCATE 函数

`TRUNCATE` 函数用于清空表数据, 和 `DELETE` 不同的是 `TRUNCATE` 相当于删表重建

****Oracle中的TRUNCATE和DELETE的主要区别在于操作类型、删除范围、事务处理、速度、自增列重置、以及高水位线重置。****

1.  ****操作类型**** ：

*  ****DELETE****  是一个DML（数据操纵语言）操作，它可以通过WHERE子句进行条件删除，并且每次删除一行时，都会在事务日志中为所删除的每行记录一项，支持事务回滚。
    *  ****TRUNCATE****  是一个DDL（数据定义语言）操作，它只能删除整个表的数据，不支持WHERE子句，且操作不会记录单独的删除操作到日志中，因此不能进行回滚。
2.  ****删除范围**** ：

*  ****DELETE****  可以删除表中的部分或所有数据，具体取决于是否使用WHERE子句。
    *  ****TRUNCATE****  只能删除整个表的数据，不能删除部分数据。
3.  ****事务处理**** ：

*  ****DELETE****  操作是事务性的，原数据会被放到rollback segment中，如果需要可以回滚。
    *  ****TRUNCATE****  操作不是事务性的，一旦执行，立即生效，原数据不放到rollback segment中，不能回滚。
4.  ****速度**** ：

* 在数据量较小的情况下，DELETE和TRUNCATE的速度差别不大。但在数据量很大的情况下，TRUNCATE由于不需要支持回滚，使用的系统和事务日志资源少，因此速度更快。
5.  ****自增列重置**** ：

*  ****DELETE****  操作不会重置自增列的初始值。
    *  ****TRUNCATE****  操作会重置自增列为初始值（通常是1）。
6.  ****高水位线重置**** ：

*  ****DELETE****  操作后，表的高水位线（HWM）不会降低，即使表中的数据被删除，HWM仍然保持不变。这可能导致数据库容量只上升不下降。
    *  ****TRUNCATE****  操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度会有提升。

综上所述，TRUNCATE和DELETE在Oracle中的使用取决于具体的需求和场景。如果需要快速清空表并重置自增列，且可以接受操作不可回滚的风险，TRUNCATE是更合适的选择。如果需要条件删除部分数据或支持事务回滚，则应使用DELETE操作。

```sql
TRUNCATE TABLE  OW_GPS_OFF_WEIGHT_MATCH
```

## 使用PL/SQL 块基于sql结果进行条件判断

在实际的数据库操作中，经常需要在执行更新操作之前先进行条件检查，然后根据检查结果执行不同的更新逻辑。使用 PL/SQL 块可以灵活地实现这一需求。下面将介绍如何在 Oracle 数据库中使用 PL/SQL 块在更新前进行条件检查并执行不同的更新操作，并给出具体的代码示例。

### 场景描述

需要在更新 `ow_gps_off_batch` 表的数据之前，先检查 `OW_GPS_OFF_TASK` 表中是否有符合条件的数据。如果没有符合条件的数据，需要更新 `CURRENT_STATUS` 字段，同时更新其他字段。如果有符合条件的数据，我们仅更新部分字段。

### 解决方案

使用 PL/SQL 块，可以在一个事务中完成条件检查和更新操作，确保数据的一致性和操作的原子性。

### PL/SQL 块示例

```sql
DECLARE
    v_value NUMBER := 50;
    v_version NUMBER;
BEGIN
    -- 从表中查询多个值并赋给多个变量
    SELECT 3, 13 INTO v_value, v_version FROM dual;

-- 根据 v_version 的值进行条件判断
    IF v_version < 50 THEN
        DBMS_OUTPUT.PUT_LINE('Value is less than 50');
    ELSIF v_version = 50 THEN
        DBMS_OUTPUT.PUT_LINE('Value is exactly 50');
    ELSE
        update abc set a = 1, b = 2 where id = 111;
    END IF;
END;
```

#### 解释

1. **DECLARE 声明部分**：

* `v_value NUMBER := 50;`：声明并初始化一个名为 `v_value` 的变量，初始值为 50。
    * `v_version NUMBER;`：声明一个名为 `v_version` 的变量，用于存储从 `SELECT INTO` 查询中获取的值。
2. **BEGIN 开始部分**：

* `SELECT 3, 13 INTO v_value, v_version FROM dual;`：从 `dual` 表中查询数据，并将结果赋值给 `v_value, v_version` 变量。
3. **条件判断**：

* `IF v_version < 50 THEN`：如果 `v_version` 小于 50，则执行以下语句块。

* `DBMS_OUTPUT.PUT_LINE('Value is less than 50');`：输出文本信息到控制台。
    * `ELSIF v_version = 50 THEN`：否则，如果 `v_version` 等于 50，则执行以下语句块。

* `DBMS_OUTPUT.PUT_LINE('Value is exactly 50');`：输出文本信息到控制台。
    * `ELSE`：如果以上条件都不满足，则执行以下语句块。

* `update abc set a = 1, b = 2 where id = 111;`：更新abc表
4. **END IF 结束部分**：

* `END IF;`：结束 `IF-ELSIF-ELSE` 结构。
5. **END 结束部分**：

* `END;`：结束 `DECLARE` 块。

#### 注意事项：

* 每个语句结束都需要加上分号 (`;`)。
* 使用 `SELECT INTO` 语句时，确保查询结果只返回一行一列的值。
* `IF-ELSIF-ELSE` 结构可以根据实际业务需求扩展多个条件判断分支。
* 每个条件 (`IF`、`ELSIF`) 都必须以 `THEN` 结尾。
* `IF` 和 `ELSIF` 后面跟随的条件表达式可以是任何返回布尔值的表达式。

通过这种方式，可以在 PL/SQL 中正确定义变量、执行查询，并根据查询结果进行条件判断和相应的处理。

#### 实际案例

```sql
DECLARE
    v_count NUMBER;
BEGIN
    -- 查询符合条件的数据数量
    SELECT COUNT(*)
    INTO v_count
    FROM OW_GPS_OFF_TASK t
    WHERE t.batch_no = #{batchNo}
          AND (t.WEIGHT_MATCH_STATUS = 0 OR t.TRAVEL_MATCH_STATUS = 0);

-- 根据查询结果执行不同的更新操作
    IF v_count = 0 THEN
        UPDATE ow_gps_off_batch
        SET CURRENT_STATUS = 1,
            MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
            MATCH_TIME = SYSDATE,
            UPDATE_TIME = SYSDATE
        WHERE batch_no = #{batchNo};
    ELSE
        UPDATE ow_gps_off_batch
        SET MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
            MATCH_TIME = SYSDATE,
            UPDATE_TIME = SYSDATE
        WHERE batch_no = #{batchNo};
    END IF;
END;
```

### 使用 MyBatis 调用 PL/SQL 块

以下是如何在 MyBatis 中配置和调用上述 PL/SQL 块的示例。

#### MyBatis Mapper XML 配置

```xml
<update id="updateBatchStatus">
    DECLARE
        v_count NUMBER;
    BEGIN
        -- 查询符合条件的数据数量
        SELECT COUNT(*)
        INTO v_count
        FROM OW_GPS_OFF_TASK t
        WHERE t.batch_no = #{batchNo}
              AND (t.WEIGHT_MATCH_STATUS = 0 OR t.TRAVEL_MATCH_STATUS = 0);

-- 根据查询结果执行不同的更新操作
        IF v_count = 0 THEN
            UPDATE ow_gps_off_batch
            SET CURRENT_STATUS = 1,
                MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
                MATCH_TIME = SYSDATE,
                UPDATE_TIME = SYSDATE
            WHERE batch_no = #{batchNo};
        ELSE
            UPDATE ow_gps_off_batch
            SET MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
                MATCH_TIME = SYSDATE,
                UPDATE_TIME = SYSDATE
            WHERE batch_no = #{batchNo};
        END IF;
    END;
</update>
```

#### Java 代码调用

```java
package com.seari.shzc.provider.dao;

import com.seari.shzc.provider.entities.model.gps.GpsOffBatchEntity;
import com.seari.shzc.provider.entities.model.gps.GpsOffTaskEntity;
import com.seari.shzc.provider.entities.request.GpsOffRequest;
import com.seari.shzc.provider.entities.response.GpsOffResponse;
import org.apache.ibatis.annotations.Param;
import java.util.List;

/**
 * @ClassName: GpsOffMapper.java
 * @Author: 情天
 * @Email : ixleo@ixleo.com
 * @Date: 2024/6/19 下午4:04
 * @Version: V1.0
 * @Description: GPS离线监管持久层
 */
public interface GpsOffMapper {

@Autowired
    private SqlSession sqlSession;

/**
     * @author 情天
     * @date 2024/6/26 下午3:14
     * @param batchNo {@link java.lang.String}
     * @return {@link java.lang.Integer}
     * @description 更新批次匹配次数
     */
    Integer updateBatchMatchResult(@Param("batchNo") String batchNo);

}
```

### 使用场景

#### 数据检查与更新

在实际业务场景中，经常需要在执行更新操作之前先进行数据检查。例如，在订单系统中，在更新订单状态之前，需要先检查订单是否已经支付。通过使用 PL/SQL 块，可以在一个事务中完成这些操作，确保数据的一致性。

#### 多条件更新

使用 PL/SQL 块，可以根据不同的条件执行不同的更新操作。这在处理复杂业务逻辑时非常有用。例如，在库存管理系统中，根据库存数量的不同，可以选择不同的补货策略。

### 结论

通过在 PL/SQL 块中使用条件检查并执行不同的更新操作，可以实现复杂的业务逻辑，并确保数据操作的原子性和一致性。结合 MyBatis，可以在 Java 应用中灵活调用这些 PL/SQL 块，实现高效的数据处理。

这种方法提供了极大的灵活性，允许在一个事务中执行多个操作，并根据不同的条件执行不同的更新逻辑。这不仅提高了代码的可读性和可维护性，还增强了系统的健壮性和可靠性。

‍

# Oracle 常用函数

‍

## Null 处理相关函数

### `NVL` 函数

Oracle 的 `NVL` 函数是一个用于处理空值（NULL）的函数。它的主要作用是用指定的值替换 NULL 值。

#### 语法

```sql
NVL(expression1, replacement_value)
```

* `expression1`：要检查是否为 NULL 的表达式。
* `replacement_value`：如果 `expression1` 为 NULL 时返回的值。

#### 用法

如果 `expression1` 是 NULL，则 `NVL` 函数返回 `replacement_value`。如果 `expression1` 不是 NULL，则返回 `expression1`。

#### 示例

1. **基本示例**

```sql
   SELECT NVL(NULL, '替换值') FROM dual;
   -- 结果：'替换值'
   ```
2. **在表中使用**假设有一个名为 `employees` 的表，包含列 `employee_id` 和 `commission`。希望在 `commission` 列为 NULL 时显示 0。

```sql
   SELECT employee_id, NVL(commission, 0) AS commission
   FROM employees;
   ```
3. **计算总和时使用**在计算工资总和时，如果某些员工的奖金为 NULL，希望将这些 NULL 视为 0。

```sql
   SELECT employee_id, salary + NVL(commission, 0) AS total_compensation
   FROM employees;
   ```

通过使用 `NVL` 函数，可以更方便地处理数据表中的 NULL 值，避免因 NULL 引起的错误或数据不完整问题。

‍

### `NVL2` 函数

`NVL2` 函数检查第一个参数，如果第一个参数不为 NULL，则返回第二个参数；如果第一个参数为 NULL，则返回第三个参数。

#### 语法

```sql
NVL2(expression, value_if_not_null, value_if_null)
```

#### 示例

```sql
SELECT NVL2(commission, '有佣金', '无佣金') AS commission_status
FROM employees;
-- 如果 commission 列有值，则返回 '有佣金'，否则返回 '无佣金'
```

‍

### `COALESCE` 函数

`COALESCE` 函数返回其参数列表中的第一个非 NULL 值。它可以接受多个参数。

#### 语法

```sql
COALESCE(expr1, expr2, ..., exprn)
```

#### 示例

```sql
SELECT COALESCE(commission, bonus, 0) AS compensation
FROM employees;
-- 返回 commission 的值，如果 commission 为 NULL，则返回 bonus 的值，如果 bonus 也为 NULL，则返回 0
```

### `NULLIF` 函数

`NULLIF` 函数比较两个表达式，如果它们相等，则返回 NULL；如果它们不相等，则返回第一个表达式的值。

#### 语法

```sql
NULLIF(expr1, expr2)
```

#### 示例

```sql
SELECT NULLIF(salary, 0) AS salary_check
FROM employees;
-- 如果 salary 等于 0，则返回 NULL；否则返回 salary 的值
```

### 综合示例

假设有一个 `employees` 表，包含以下列：`employee_id`, `salary`, `commission`, `bonus`。可以使用上述函数来处理 NULL 值：

```sql
-- 使用 NVL 函数
SELECT employee_id, NVL(commission, 0) AS commission
FROM employees;

-- 使用 NVL2 函数
SELECT employee_id, NVL2(commission, commission, '无佣金') AS commission_status
FROM employees;

-- 使用 COALESCE 函数
SELECT employee_id, COALESCE(commission, bonus, 0) AS total_compensation
FROM employees;

-- 使用 NULLIF 函数
SELECT employee_id, NULLIF(commission, 0) AS commission_check
FROM employees;
```

这些函数提供了处理 NULL 值的灵活工具，可以在不同情况下选择合适的函数来简化查询和数据处理逻辑。

‍

## 字符串函数

### `CONCAT` 函数

#### 语法

```sql
CONCAT(string1, string2)
```

#### 示例

```sql
SELECT CONCAT(first_name, last_name) AS full_name
FROM employees;
-- 将 first_name 和 last_name 连接成一个字符串
```

### `SUBSTR` 函数

#### 语法

```sql
SUBSTR(string, start_position, length)
```

#### 示例

```sql
SELECT SUBSTR(first_name, 1, 3) AS short_name
FROM employees;
-- 从 first_name 的第一个字符开始，提取长度为 3 的子字符串
```

### `INSTR` 函数

#### 语法

```sql
INSTR(string, substring, [start_position], [nth_appearance])
```

#### 示例

```sql
SELECT INSTR(first_name, 'a') AS position
FROM employees;
-- 查找 first_name 中子字符串 'a' 的位置
```

### `TRIM` 函数

#### 语法

```sql
TRIM([leading | trailing | both] trim_character FROM string)
```

#### 示例

```sql
SELECT TRIM(' ' FROM first_name) AS trimmed_name
FROM employees;
-- 去除 first_name 两端的空格
```

### `REPLACE` 函数

#### 语法

```sql
REPLACE(string, search_string, replacement_string)
```

#### 示例

```sql
SELECT REPLACE(first_name, 'a', 'A') AS replaced_name
FROM employees;
-- 将 first_name 中所有的 'a' 替换为 'A'
```

## 数值函数

### `ROUND` 函数

#### 语法

```sql
ROUND(number, decimal_places)
```

#### 示例

```sql
SELECT ROUND(salary, 2) AS rounded_salary
FROM employees;
-- 将 salary 保留两位小数
```

### `TRUNC` 函数

#### 语法

```sql
TRUNC(number, decimal_places)
```

#### 示例

```sql
SELECT TRUNC(salary, 2) AS truncated_salary
FROM employees;
-- 将 salary 截断为两位小数
```

### `MOD` 函数

#### 语法

```sql
MOD(number, divisor)
```

#### 示例

```sql
SELECT MOD(employee_id, 2) AS remainder
FROM employees;
-- 计算 employee_id 除以 2 的余数
```

### `POWER` 函数

#### 语法

```sql
POWER(base, exponent)
```

#### 示例

```sql
SELECT POWER(salary, 2) AS salary_squared
FROM employees;
-- 计算 salary 的平方
```

## 日期函数

### `SYSDATE` 函数

#### 语法

```sql
SYSDATE
```

#### 示例

```sql
SELECT SYSDATE AS current_date
FROM dual;
-- 返回当前系统日期和时间
```

### `ADD_MONTHS` 函数

#### 语法

```sql
ADD_MONTHS(date, n)
```

#### 示例

```sql
SELECT ADD_MONTHS(hire_date, 6) AS new_date
FROM employees;
-- 返回 hire_date 加上 6 个月后的日期
```

### `MONTHS_BETWEEN` 函数

#### 语法

```sql
MONTHS_BETWEEN(date1, date2)
```

#### 示例

```sql
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS months_difference
FROM employees;
-- 计算当前日期和 hire_date 之间的月数差
```

### `NEXT_DAY` 函数

#### 语法

```sql
NEXT_DAY(date, weekday)
```

#### 示例

```sql
SELECT NEXT_DAY(hire_date, 'FRIDAY') AS next_friday
FROM employees;
-- 返回 hire_date 之后的第一个星期五的日期
```

### `LAST_DAY` 函数

#### 语法

```sql
LAST_DAY(date)
```

#### 示例

```sql
SELECT LAST_DAY(hire_date) AS last_day_of_month
FROM employees;
-- 返回 hire_date 所在月份的最后一天
```

### `EXTRACT` 函数

#### 语法

```sql
EXTRACT(part FROM date)
```

#### 示例

```sql
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;
-- 提取 hire_date 中的年份部分
```

‍

## 聚合函数

### `SUM` 函数

#### 语法

```sql
SUM(expression)
```

#### 示例

```sql
SELECT SUM(salary) AS total_salary
FROM employees;
-- 计算所有员工的工资总和
```

### `AVG` 函数

#### 语法

```sql
AVG(expression)
```

#### 示例

```sql
SELECT AVG(salary) AS average_salary
FROM employees;
-- 计算所有员工的平均工资
```

### `COUNT` 函数

#### 语法

```sql
COUNT(expression)
```

#### 示例

```sql
SELECT COUNT(*) AS total_employees
FROM employees;
-- 计算员工总数
```

### `MAX` 函数

#### 语法

```sql
MAX(expression)
```

#### 示例

```sql
SELECT MAX(salary) AS max_salary
FROM employees;
-- 查找最高工资
```

### `MIN` 函数

#### 语法

```sql
MIN(expression)
```

#### 示例

```sql
SELECT MIN(salary) AS min_salary
FROM employees;
-- 查找最低工资
```

## 转换函数

### `TO_CHAR` 函数

#### 语法

```sql
TO_CHAR(expression, format)
```

#### 示例

```sql
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date_formatted
FROM employees;
-- 将 hire_date 转换为 'YYYY-MM-DD' 格式的字符串
```

### `TO_DATE` 函数

#### 语法

```sql
TO_DATE(string, format)
```

#### 示例

```sql
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') AS new_year
FROM dual;
-- 将字符串 '2023-01-01' 转换为日期
```

### `TO_NUMBER` 函数

#### 语法

```sql
TO_NUMBER(string)
```

#### 示例

```sql
SELECT TO_NUMBER('12345') AS number_value
FROM dual;
-- 将字符串 '12345' 转换为数值 12345
```

### `CAST` 函数

#### 语法

```sql
CAST(expression AS data_type)
```

#### 示例

```sql
SELECT CAST('2023-01-01' AS DATE) AS date_value
FROM dual;
-- 将字符串 '2023-01-01' 转换为日期类型
```

## 条件函数

### `CASE` 表达式

#### 语法

```sql
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END
```

#### 示例

```sql
SELECT employee_id,
    CASE 
        WHEN salary > 5000 THEN '高薪'
        WHEN salary BETWEEN 3000 AND 5000 THEN '中薪'
        ELSE '低薪'
    END AS salary_level
FROM employees;
-- 根据 salary 值分类员工的薪资等级
```

### `DECODE` 函数

#### 语法

```sql
DECODE(expression, search1, result1, search2, result2, ..., default)
```

#### 示例

```sql
SELECT employee_id,
    DECODE(department_id, 
        10, '财务部', 
        20, '人力资源部', 
        30, '研发部', 
        '其他部门') AS department_name
FROM employees;
-- 根据 department_id 返回相应的部门名称
```

## 分析函数

### `RANK` 函数

#### 语法

```sql
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;
-- 根据 salary 对员工进行排名，同部门中工资相同的员工排名相同
```

### `DENSE_RANK` 函数

#### 语法

```sql
DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_dense_rank
FROM employees;
-- 根据 salary 对员工进行密集排名，同部门中工资相同的员工排名相同，且排名无间隔
```

### `ROW_NUMBER` 函数

#### 语法

```sql
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
FROM employees;
-- 根据 salary 对员工进行编号，同部门中每个员工有唯一的编号
```

### `NTILE` 函数

#### 语法

```sql
NTILE(num_buckets) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS ntile_rank
FROM employees;
-- 将员工按 salary 分为 4 组，同部门中每个员工分配一个组编号
```

### `LAG` 函数

#### 语法

```sql
LAG(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       LAG(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary DESC) AS prev_salary
FROM employees;
-- 返回同部门中每个员工的前一个员工的 salary，如果没有前一个员工则返回 0
```

### `LEAD` 函数

#### 语法

```sql
LEAD(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

```sql
SELECT employee_id, salary, 
       LEAD(salary, 1, 0) OVER (PARTITION BY department_id ORDER BY salary DESC) AS next_salary
FROM employees;
-- 返回同部门中每个员工的下一个员工的 salary，如果没有下一个员工则返回 0
```

## 序列生成函数

### `NEXTVAL` 函数

#### 语法

```sql
sequence_name.NEXTVAL
```

#### 示例

```sql
SELECT employees_seq.NEXTVAL AS next_employee_id
FROM dual;
-- 从序列 employees_seq 获取下一个值
```

### `CURRVAL` 函数

#### 语法

```sql
sequence_name.CURRVAL
```

#### 示例

```sql
SELECT employees_seq.CURRVAL AS current_employee_id
FROM dual;
-- 获取序列 employees_seq 当前值
```

## 集合操作函数

### `UNION` 运算符

#### 语法

```sql
SELECT column_list FROM table1
UNION
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
UNION
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中所有不同的 employee_id
```

### `UNION ALL` 运算符

#### 语法

```sql
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
UNION ALL
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中所有的 employee_id，包括重复值
```

### `INTERSECT` 运算符

#### 语法

```sql
SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
INTERSECT
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中都有的 employee_id
```

### `MINUS` 运算符

#### 语法

```sql
SELECT column_list FROM table1
MINUS
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
MINUS
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 中有但 employees_2024 中没有的 employee_id
```

## 位图函数

### `BITAND` 函数

#### 语法

```sql
BITAND(value1, value2)
```

#### 示例

```sql
SELECT BITAND(6, 3) AS bitwise_and
FROM dual;
-- 返回 6 和 3 的位与操作结果，即 2
```

### `BITOR` 函数

#### 语法

```sql
BITOR(value1, value2)
```

#### 示例

```sql
SELECT BITOR(6, 3) AS bitwise_or
FROM dual;
-- 返回 6 和 3 的位或操作结果，即 7
```

### `BITXOR` 函数

#### 语法

```sql
BITXOR(value1, value2)
```

#### 示例

```sql
SELECT BITXOR(6, 3) AS bitwise_xor
FROM dual;
-- 返回 6 和 3 的位异或操作结果，即 5
```

## JSON 函数

### `JSON_OBJECT` 函数

#### 语法

```sql
JSON_OBJECT(key 'value' [, key 'value'] ...)
```

#### 示例

```sql
SELECT JSON_OBJECT('employee_id' VALUE employee_id, 'name' VALUE first_name || ' ' || last_name) AS employee_json
FROM employees;
-- 将 employee_id 和 full_name 转换为 JSON 对象

-- 复制示例
WITH batch_info AS (
    SELECT
        b.batch_no,
        b.current_status,
        b.issue_time,
        b.match_time,
        b.file_url
    FROM OW_GPS_OFF_BATCH b
    WHERE b.batch_no = 'cf61769f63d44aafb1c63d748ea21a61'
),
task_info AS (
    SELECT
        t.task_id,
        t.batch_no,
        t.vehicle_no,
        t.crop_name,
        t.offline_time
    FROM OW_GPS_OFF_TASK t
    WHERE t.batch_no = 'cf61769f63d44aafb1c63d748ea21a61'
),
weight_match_info AS (
    SELECT
        w.task_id,
        w.vehicle_no,
        to_char(w.check_Time, 'yyyy-mm-dd') as check_Time,
        w.axles
    FROM OW_GPS_OFF_WEIGHT_MATCH w
    JOIN task_info t ON t.task_id = w.task_id
    where w.CHECK_STATUS = 1 and w.IS_MAIN = 1
    group by w.task_id,
        w.vehicle_no,
        to_char(w.check_Time, 'yyyy-mm-dd'),
        w.axles
),
travel_match_info AS (
    SELECT
        t.task_id,
        t.vehicle_no,
        t.gate_name,
        t.check_time
    FROM OW_GPS_OFF_TRAVEL_MATCH t
    JOIN task_info tsk ON tsk.task_id = t.task_id
)
SELECT
    JSON_OBJECT(
        'vehicles' VALUE (
            SELECT JSON_ARRAYAGG(
                JSON_OBJECT(
                    'vehicleNo' VALUE t.vehicle_No,
                    'cropName' VALUE t.crop_Name,
                    'offlineTime' VALUE to_char(t.offline_Time, 'yyyy-mm-dd hh24:mi:ss'),
                    'weightInfo' VALUE JSON_OBJECT(
                        'weightDetails' VALUE (
                            SELECT JSON_ARRAYAGG(
                                JSON_OBJECT(
                                    'vehicleNo' VALUE w.vehicle_No,
                                    'checkTime' VALUE w.check_Time,
                                    'axles' VALUE w.axles
                                )
                            )
                            FROM weight_match_info w
                            WHERE w.task_Id = t.task_Id
                        )
                    ),
                    'gateInfo' VALUE JSON_OBJECT(
                        'gateDetails' VALUE (
                            SELECT JSON_ARRAYAGG(
                                JSON_OBJECT(
                                    'vehicleNo' VALUE tr.vehicle_No,
                                    'gateName' VALUE tr.gate_Name,
                                    'checkTime' VALUE to_char(tr.check_Time, 'yyyy-mm-dd hh24:mi:ss')
                                )
                            )
                            FROM travel_match_info tr
                            WHERE tr.task_Id = t.task_Id
                        )
                    )
                )
            )
            FROM task_info t
        )
    ) AS vehicles
FROM batch_info b;
```

### `JSON_ARRAY` 函数

#### 语法

```sql
JSON_ARRAY(value1 [, value2] ...)
```

#### 示例

```sql
SELECT JSON_ARRAY(employee_id, first_name, last_name) AS employee_array
FROM employees;
-- 将 employee_id、first_name 和 last_name 转换为 JSON 数组
```

### `JSON_VALUE` 函数

#### 语法

```sql
JSON_VALUE(json_string, path)
```

#### 示例

```sql
SELECT JSON_VALUE('{"employee_id":123, "name":"John Doe"}', '$.name') AS employee_name
FROM dual;
-- 从 JSON 字符串中提取 name 值
```

### `JSON_QUERY` 函数

#### 语法

```sql
JSON_QUERY(json_string, path)
```

#### 示例

```sql
SELECT JSON_QUERY('{"employees":[{"id":123, "name":"John Doe"},{"id":124, "name":"Jane Doe"}]}', '$.employees') AS employees
FROM dual;
-- 从 JSON 字符串中提取 employees 数组
```

通过这些函数，可以在 Oracle 数据库中进行更加复杂和多样化的数据处理。这些函数涵盖了数据分析、序列生成、集合操作、位图操作和 JSON 处理等常见需求，提供了强大的工具集来满足不同的应用场景。

‍

## 其他常用函数

### `GREATEST` 函数

#### 语法

```sql
GREATEST(expr1, expr2, ..., exprn)
```

#### 示例

```sql
SELECT GREATEST(salary, bonus) AS max_value
FROM employees;
-- 返回 salary 和 bonus 中的较大值
```

### `LEAST` 函数

#### 语法

```sql
LEAST(expr1, expr2, ..., exprn)
```

#### 示例

```sql
SELECT LEAST(salary, bonus) AS min_value
FROM employees;
-- 返回 salary 和 bonus 中的较小值
```

### `NVARCHAR2` 函数

#### 语法

```sql
NVARCHAR2(length)
```

#### 示例

```sql
CREATE TABLE example (
    name NVARCHAR2(50)
);
-- 创建一个 NVARCHAR2 类型的列，最大长度为 50
```

### `LENGTH` 函数

#### 语法

```sql
LENGTH(string)
```

#### 示例

```sql
SELECT LENGTH(first_name) AS name_length
FROM employees;
-- 返回 first_name 的长度
```

### `UPPER` 函数

#### 语法

```sql
UPPER(string)
```

#### 示例

```sql
SELECT UPPER(first_name) AS upper_name
FROM employees;
-- 将 first_name 转换为大写
```

### `LOWER` 函数

#### 语法

```sql
LOWER(string)
```

#### 示例

```sql
SELECT LOWER(first_name) AS lower_name
FROM employees;
-- 将 first_name 转换为小写
```

### `INITCAP` 函数

#### 语法

```sql
INITCAP(string)
```

#### 示例

```sql
SELECT INITCAP(first_name) AS initcap_name
FROM employees;
-- 将 first_name 的每个单词首字母转换为大写
```

### `LPAD` 函数

#### 语法

```sql
LPAD(string, length, pad_string)
```

#### 示例

```sql
SELECT LPAD(employee_id, 5, '0') AS padded_id
FROM employees;
-- 将 employee_id 左填充到长度 5，用 '0' 作为填充值
```

### `RPAD` 函数

#### 语法

```sql
RPAD(string, length, pad_string)
```

#### 示例

```sql
SELECT RPAD(first_name, 10, ' ') AS padded_name
FROM employees;
-- 将 first_name 右填充到长度 10，用空格作为填充值
```

### `LTRIM` 函数

#### 语法

```sql
LTRIM(string, trim_string)
```

#### 示例

```sql
SELECT LTRIM(first_name, ' ') AS ltrim_name
FROM employees;
-- 去除 first_name 左端的空格
```

### `RTRIM` 函数

#### 语法

```sql
RTRIM(string, trim_string)
```

#### 示例

```sql
SELECT RTRIM(first_name, ' ') AS rtrim_name
FROM employees;
-- 去除 first_name 右端的空格
```

### `ABS` 函数

#### 语法

```sql
ABS(number)
```

#### 示例

```sql
SELECT ABS(-100) AS absolute_value
FROM dual;
-- 返回 -100 的绝对值，即 100
```

### `CEIL` 函数

#### 语法

```sql
CEIL(number)
```

#### 示例

```sql
SELECT CEIL(4.2) AS ceiling_value
FROM dual;
-- 返回 4.2 向上取整后的值，即 5
```

### `FLOOR` 函数

#### 语法

```sql
FLOOR(number)
```

#### 示例

```sql
SELECT FLOOR(4.7) AS floor_value
FROM dual;
-- 返回 4.7 向下取整后的值，即 4
```

### `ROUND` 函数

#### 语法

```sql
ROUND(number, decimal_places)
```

#### 示例

```sql
SELECT ROUND(4.567, 2) AS rounded_value
FROM dual;
-- 将 4.567 保留两位小数，即 4.57
```

### `SIGN` 函数

#### 语法

```sql
SIGN(number)
```

#### 示例

```sql
SELECT SIGN(-100) AS sign_value
FROM dual;
-- 返回 -100 的符号，即 -1
```

### `SQRT` 函数

#### 语法

```sql
SQRT(number)
```

#### 示例

```sql
SELECT SQRT(16) AS square_root
FROM dual;
-- 返回 16 的平方根，即 4
```

‍

最后修改：2024 年 10 月 10 日

如果文章有帮助到你，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

bogyzoqfuv
这是一篇佳作，无论是从内容、语言还是结构上，都堪称完美。
rrmseizkvn
作者的观点新颖且实用，让人在阅读中获得了新的思考和灵感。
tqmjiryxgv
这篇文章如同一幅色彩斑斓的画卷，每一笔都充满了独特的创意。
vjcujybqsu
每一个段落都紧密相连，逻辑清晰，展现了作者高超的写作技巧。
ouikkddqdr
字里行间流露出真挚的情感，让人感同身受，共鸣不已。

Oracle函数及方法

情天 • 2024 年 06 月 02 日

# Oracle常用函数及方法汇总

# 自整理操作

## 日期时间相关

### 时间和字符串转换

#### 字符串转换时间

#### 时间转字符串

```sql
函数: to_char(日期, 转换格式)
to_char(v.CHECK_TIME, 'yyyy-mm-dd hh24:mi:ss')
```

### 当前时间

```sql
sysdate  --返回当前系统日期和时间，精确到秒
systimestamp  --返回当前系统日期和时间，精确到毫秒
select sysdate from dual;
select systimestamp from dual;
```

### 日期运算

#### 使用函数进行运算

```sql
# 查询当前时间减去5分钟sql：
SELECT sysdate - INTERVAL '5' MINUTE FROM DUAL;

# 查询当前时间加上5小时sql：
SELECT sysdate + INTERVAL '5' HOUR FROM DUAL;

# 查询当前时间减去5天sql：
SELECT sysdate - INTERVAL '5' DAY FROM DUAL;
```

#### 两个日期进行运算

##### 相差天数

oracle中两个日期直接进行相减, 结果的单位就是天

```sql
-- 相差天数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss'))
from dual;
```

##### 相差小时, 分钟, 秒数

由于结果单位是填, 所以需要结果为小时或者其他单位时 可进行转换, 比如: 小时数 = 结果的天数 * 24

```sql
-- 相差小时数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24
from dual;

-- 相差分钟数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24 * 60 
from dual;

-- 相差秒数
select to_number(TO_DATE('2024-10-09 00:00:01','yyyy-MM-dd hh24:mi:ss' ) - TO_DATE('2024-10-08 12:00:01','yyyy-MM-dd hh24:mi:ss')) * 24 * 60 * 60
from dual;
```

##### 相差月份

相差月数使用 months_between() 函数进行计算

##### 相差年份

原先月数/12

### TRUNC 函数截断时间

Oracle 数据库中的 TRUNC 函数主要用于截断日期或数字。它可以将日期值截断到指定的日期部分，也可以将数字截断到指定的小数位数

#### TRUNC 用于日期的截断

`TRUNC` 函数可以将日期值截断到指定的日期部分。以下是各种截断方式及示例。

##### 按年截断

将日期截断到当前年的第一天。

```sql
SELECT TRUNC(SYSDATE, 'YYYY') AS start_of_year FROM dual;
```

##### 按月截断

将日期截断到当前月的第一天。

```sql
SELECT TRUNC(SYSDATE, 'MM') AS start_of_month FROM dual;
```

##### 按季度截断

将日期截断到当前季度的第一天。

```sql
-- 当前季度的开始时间
SELECT TRUNC(SYSDATE, 'Q') AS start_of_quarter FROM dual;

-- 当前季度的结束时间
SELECT TRUNC(SYSDATE, 'Q') + interval '3' month  - interval '1' second AS end_of_quarter FROM dual;

##### 解释

这种方法可以帮助我们准确地获取当前季度的开始和结束时间。

‍

##### 按日截断

不指定单位默认就是将日期截断到当天的开始，即去除时间部分。

```sql
SELECT TRUNC(SYSDATE) AS start_of_day FROM dual;
```

##### 按小时截断

将日期截断到当前小时的开始。

```sql
SELECT TRUNC(SYSDATE, 'HH') AS start_of_hour FROM dual;
```

##### 按分钟截断

将日期截断到当前分钟的开始。

```sql
SELECT TRUNC(SYSDATE, 'MI') AS start_of_minute FROM dual;
```

##### 按秒截断

将日期截断到当前秒的开始（实际不会改变，因为秒级别已经是最小单位）。

```sql
SELECT TRUNC(SYSDATE, 'SS') AS start_of_second FROM dual;
```

#### TRUNC 用于数字的截断

`TRUNC` 函数也可以用于截断数字，去掉小数部分或截断到指定的小数位数。

##### 截断到整数部分

将数字截断到整数部分。

```sql
SELECT TRUNC(123.456) AS truncated_number FROM dual;
```

##### 截断到小数位数

将数字截断到指定的小数位数。

```sql
SELECT TRUNC(123.456, 1) AS truncated_number FROM dual;
SELECT TRUNC(123.456, 2) AS truncated_number FROM dual;
```

#### 使用场景

##### 按季度统计数据

按季度统计销售数据。

```sql
SELECT
    TRUNC(sale_date, 'Q') AS quarter,
    SUM(amount) AS total_sales
FROM
    sales
GROUP BY
    TRUNC(sale_date, 'Q');
```

##### 按月份统计数据

按月份统计访问量。

```sql
SELECT
    TRUNC(access_time, 'MM') AS month,
    COUNT(*) AS total_visits
FROM
    web_access_log
GROUP BY
    TRUNC(access_time, 'MM');
```

##### 舍弃小数部分

在财务计算中舍弃小数部分。

```sql
SELECT
    employee_id,
    TRUNC(salary * 0.1) AS bonus
FROM
    employees;
```

#### 总结

## 前后xx分钟去重

在oracle中要对一批数据按照某些字段进行去重, 但是要控制前后15分钟内重复出现的才视为重复, 也就是对这批数据进行前后15分钟去重

主要使用窗口函数 `ROW_NUMBER()` + `LAG()` 和 `LEAD()` 来完成

### 1. 问题抽象

前后时间去重的需求通常出现在以下场景：

### 2. 核心思路

实现这一逻辑的核心是：

在SQL中，借助 `窗口函数` 可以非常方便地完成以上操作。

### 3. 使用窗口函数的技巧

为了获取前后记录，SQL 提供了 `LAG` 和 `LEAD` 窗口函数：

* LAG：获取当前记录前面的记录值（在同一分组内按某个字段排序）。
* LEAD：获取当前记录后面的记录值（同样在同一分组内按某个字段排序）。

窗口函数的语法形式如下：

```sql
LAG(<字段>, <偏移量>, <默认值>) OVER (PARTITION BY <分组字段> ORDER BY <排序字段>)
```

### 4. 技术实现步骤

### 5. 经验与技巧

### 前后xx分钟去重的SQL逻辑总结

在电警数据统计中，为了去重某些同站点、同车牌且发生在前后15分钟内的记录，可以使用窗口函数配合`LAG`和`LEAD`函数来实现。这段SQL的逻辑如下：

### 步骤解析：

1. 初步筛选数据: 首先，从数据表 `wf_cp` 中筛选出符合条件的记录，具体条件包括：

* `LAG` 返回当前记录的上一条记录的时间（即前一条记录）。
    * `LEAD` 返回当前记录的下一条记录的时间（即后一条记录）。

这部分数据存入 `deduped_data` 临时表中。
3. 去重逻辑: 去重的条件是在当前记录与前后记录的时间差超过15分钟。通过判断：

### 实用场景：

通过这种方式处理数据，可以更精确地统计某一时段内的唯一违法行为，避免重复记录的影响。这种逻辑可以灵活应用于其他类似的场景中。

## JSON 字段值处理

### 理解 JSON 数据结构

首先，了解 JSON 数据的结构和层次。例如，以下是你提供的 JSON 示例：

### 使用 JSON_VALUE 提取简单字段

对于 JSON 中的简单值，可以使用 `JSON_VALUE` 提取。例如：

### 使用 JSON_TABLE 处理数组和嵌套数据

对于 JSON 数组和嵌套数据，可以使用 `JSON_TABLE` 函数将 JSON 数据转换为关系表格格式。

#### 提取数组中的数据

假设你要提取 `auditNoeNoMap` 数组中的 `count` 字段并计算其总和：

### 提取多个字段

如果需要提取多个字段，例如 `count` 和 `reason`，可以在 `JSON_TABLE` 中定义多个列：

### 将结果集与其他表连接

在处理复杂的 JSON 数据后，可以将其与其他表进行连接，类似于普通 SQL 查询。例如：

### 创建视图（可选）

如果经常需要处理相同的 JSON 数据结构，可以考虑创建一个视图，以简化查询操作：

使用视图之后，查询变得更加简单：

```sql
SELECT 
    node_id,
    SUM(count) AS sum_a1
FROM 
    vw_audit_data
WHERE 
    DATA_DAY >= DATE '2024-04-01' 
    AND DATA_DAY < DATE '2024-05-01'
GROUP BY 
    node_id;
```

通过这些步骤，可以系统地处理存储在数据库字段中的复杂 JSON 数据，使得处理 JSON 数据像处理普通 SQL 查询一样简单。

‍

## 窗口函数（Oracle 12c+）

### ROW_NUMBER() 函数

#### 语法

```sql
ROW_NUMBER() OVER (PARTITION BY column_name1, column_name2, ... ORDER BY column_name3, column_name4, ...)
```

#### 查询示例

#### 更新操作示例

结合 `ROW_NUMBER()` 函数做 `UPDATE` 操作：

### OVER (PARTITION BY)

`PARTITION BY` 子句用于将结果集分成多个分区，然后对每个分区应用窗口函数。

示例: 适用于在语句不进行group by 分组时, 单独进行分组聚合计算

```sql
SUM(r1.compareTotal) OVER (PARTITION BY r1.dataDay,r1.nodeId) AS compareTotal
```

‍

## MERGE INTO 函数

### 语法

### 查询并批量更新

使用 `MERGE INTO` 语句加 `USING` 合并查询结果表，然后更新目标表字段：

### 存在就更新不存在就新增

使用 `MERGE INTO` 语句实现存在即更新，不存在即新增：

## 根据条件转换内容

### 使用 CASE WHEN 语句

#### 示例 1

```sql
SELECT column1, 
       CASE WHEN column2 = 'xxxxx不通过' THEN 1115 ELSE 0 END AS status
FROM your_table;
```

#### 示例 2

```sql
SELECT column1, 
       CASE 
         WHEN column2 = 'xxxxx不通过' THEN 1115
         WHEN column2 = 'xxxxx通过' THEN 1116
         ELSE 0 
       END AS status
FROM your_table;
```

#### 实际 SQL

## Oracle 序列（SEQUENCE）

### 创建序列

类似 MySQL 中的自增：

```sql
-- 
CREATE SEQUENCE name
INCREMENT BY 1 
MAXVALUE number
MINVALUE number
NOCACHE;

### 示例

### 删除并重新创建序列

1. 删除现有序列：

```sql
DROP SEQUENCE "SYS_DICT_TYPE_SEQ";
```

2. 重新创建序列并设置新的起始值：

```sql
CREATE SEQUENCE "SYS_DICT_TYPE_SEQ"
    MINVALUE 1
    INCREMENT BY 1
    START WITH 100;  -- 新的起始值
```

### 方法二：使用 `RESTART` 子句

如果你的数据库支持 `RESTART` 子句，可以直接使用它来重置序列的起始值：

```sql
ALTER SEQUENCE "SYS_DICT_TYPE_SEQ" RESTART WITH 100;  -- 新的起始值
```

### 示例

假设你希望将序列的起始值设置为 100：

```sql
-- 方法一：删除并重新创建序列
DROP SEQUENCE "SYS_DICT_TYPE_SEQ";

CREATE SEQUENCE "SYS_DICT_TYPE_SEQ"
    MINVALUE 1
    INCREMENT BY 1
    START WITH 100;

-- 方法二：使用 RESTART 子句（如果支持）
ALTER SEQUENCE "SYS_DICT_TYPE_SEQ" RESTART WITH 100;
```

通过以上步骤，你可以成功地将序列的起始值修改为你需要的值。

‍

## Oracle 保留两位小数（不四舍五入）

使用 `TO_CHAR` 函数结合 `TRUNC` 函数来实现保留两位小数、截取不四舍五入：

```sql
SELECT TO_CHAR(TRUNC('21642.2487', 2), '9999999990.99') AS result
FROM dual;
```

### 示例

```sql
SELECT TO_CHAR(a.total / 1000, 'FM999999999990.00')
FROM your_table a;
```

## WITH 子句

以下是使用 `WITH` 子句的语法：

可以定义多个 CTE，每个 CTE 之间用逗号分隔。例如：

以下是一个实际示例，演示如何使用 `WITH` 子句来简化复杂查询：

## `FETCH FIRST` 子句

### 什么是 `FETCH FIRST` 子句

`FETCH FIRST` 子句用于限制查询结果集中返回的行数。它可以在 `SELECT` 语句的末尾与 `ORDER BY` 子句一起使用，以便在排序后仅返回指定数量的行。

### 基本语法

```sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC]
FETCH FIRST n ROWS ONLY;
```

### 示例

#### 示例 1：按薪水降序排序并返回前 5 行

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
```

此查询将按 `salary` 降序排序，并返回前 5 个薪水最高的员工。

#### 示例 2：按雇佣日期升序排序并返回前 10 行

```sql
SELECT employee_id, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date ASC
FETCH FIRST 10 ROWS ONLY;
```

此查询将按 `hire_date` 升序排序，并返回前 10 个最早被雇佣的员工。

### 其他用法

#### 跳过前 n 行并返回接下来的 m 行

使用 `OFFSET` 子句可以实现分页查询，跳过前 n 行并返回接下来的 m 行：

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
```

此查询将按 `salary` 降序排序，跳过前 10 行，并返回接下来的 5 行。

#### 返回相同排序条件下的所有行

使用 `WITH TIES` 子句可以确保返回的行中包含所有具有相同排序条件的行，即使它们超过了指定的行数：

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;
```

此查询将按 `salary` 降序排序，返回前 5 行，并包含所有薪水相同且位于前 5 名中的员工。

### `FETCH FIRST` 的优点

### 总结

‍

## ROWNUM 限制返回条数

### 什么是 `ROWNUM`

### 基本语法

```sql
SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= n;
```

* `column1, column2, ...`：要查询的列。
* `table_name`：表的名称。
* `ROWNUM <= n`：限制返回的行数为 `n`。

### 示例

#### 示例 1：返回前 5 行

```sql
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE ROWNUM <= 5;
```

此查询将返回表 `employees` 中的前 5 行。

#### 示例 2：按薪水降序排序并返回前 5 行

```sql
SELECT * 
FROM (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;
```

此查询将先按 `salary` 降序排序，然后返回前 5 个薪水最高的员工。

### 使用 `ROWNUM` 实现分页查询

`ROWNUM` 可以与子查询结合使用来实现分页查询。假设我们希望返回第 `m` 页的 `n` 行记录：

#### 示例 3：分页查询

此查询将返回第 `page` 页的 `pageSize` 行记录。

### `ROWNUM` 的优点

### `ROWNUM` 的局限性

### `ROWNUM` 与 `FETCH FIRST` 的对比

### 总结

‍

## TRUNCATE 函数

`TRUNCATE` 函数用于清空表数据, 和 `DELETE` 不同的是 `TRUNCATE` 相当于删表重建

****Oracle中的TRUNCATE和DELETE的主要区别在于操作类型、删除范围、事务处理、速度、自增列重置、以及高水位线重置。****

1.  ****操作类型**** ：

*  ****DELETE****  操作不会重置自增列的初始值。
    *  ****TRUNCATE****  操作会重置自增列为初始值（通常是1）。
6.  ****高水位线重置**** ：

```sql
TRUNCATE TABLE  OW_GPS_OFF_WEIGHT_MATCH
```

## 使用PL/SQL 块基于sql结果进行条件判断

### 场景描述

### 解决方案

使用 PL/SQL 块，可以在一个事务中完成条件检查和更新操作，确保数据的一致性和操作的原子性。

### PL/SQL 块示例

```sql
DECLARE
    v_value NUMBER := 50;
    v_version NUMBER;
BEGIN
    -- 从表中查询多个值并赋给多个变量
    SELECT 3, 13 INTO v_value, v_version FROM dual;

#### 解释

1. **DECLARE 声明部分**：

* `SELECT 3, 13 INTO v_value, v_version FROM dual;`：从 `dual` 表中查询数据，并将结果赋值给 `v_value, v_version` 变量。
3. **条件判断**：

* `IF v_version < 50 THEN`：如果 `v_version` 小于 50，则执行以下语句块。

* `DBMS_OUTPUT.PUT_LINE('Value is less than 50');`：输出文本信息到控制台。
    * `ELSIF v_version = 50 THEN`：否则，如果 `v_version` 等于 50，则执行以下语句块。

* `DBMS_OUTPUT.PUT_LINE('Value is exactly 50');`：输出文本信息到控制台。
    * `ELSE`：如果以上条件都不满足，则执行以下语句块。

* `update abc set a = 1, b = 2 where id = 111;`：更新abc表
4. **END IF 结束部分**：

* `END IF;`：结束 `IF-ELSIF-ELSE` 结构。
5. **END 结束部分**：

* `END;`：结束 `DECLARE` 块。

#### 注意事项：

通过这种方式，可以在 PL/SQL 中正确定义变量、执行查询，并根据查询结果进行条件判断和相应的处理。

#### 实际案例

### 使用 MyBatis 调用 PL/SQL 块

以下是如何在 MyBatis 中配置和调用上述 PL/SQL 块的示例。

#### MyBatis Mapper XML 配置

-- 根据查询结果执行不同的更新操作
        IF v_count = 0 THEN
            UPDATE ow_gps_off_batch
            SET CURRENT_STATUS = 1,
                MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
                MATCH_TIME = SYSDATE,
                UPDATE_TIME = SYSDATE
            WHERE batch_no = #{batchNo};
        ELSE
            UPDATE ow_gps_off_batch
            SET MATCH_COUNT = NVL(MATCH_COUNT, 0) + 1,
                MATCH_TIME = SYSDATE,
                UPDATE_TIME = SYSDATE
            WHERE batch_no = #{batchNo};
        END IF;
    END;
</update>
```

#### Java 代码调用

```java
package com.seari.shzc.provider.dao;

@Autowired
    private SqlSession sqlSession;

}
```

### 使用场景

#### 数据检查与更新

#### 多条件更新

### 结论

‍

# Oracle 常用函数

‍

## Null 处理相关函数

### `NVL` 函数

Oracle 的 `NVL` 函数是一个用于处理空值（NULL）的函数。它的主要作用是用指定的值替换 NULL 值。

#### 语法

```sql
NVL(expression1, replacement_value)
```

* `expression1`：要检查是否为 NULL 的表达式。
* `replacement_value`：如果 `expression1` 为 NULL 时返回的值。

#### 用法

如果 `expression1` 是 NULL，则 `NVL` 函数返回 `replacement_value`。如果 `expression1` 不是 NULL，则返回 `expression1`。

#### 示例

1. **基本示例**

```sql
   SELECT employee_id, salary + NVL(commission, 0) AS total_compensation
   FROM employees;
   ```

通过使用 `NVL` 函数，可以更方便地处理数据表中的 NULL 值，避免因 NULL 引起的错误或数据不完整问题。

‍

### `NVL2` 函数

`NVL2` 函数检查第一个参数，如果第一个参数不为 NULL，则返回第二个参数；如果第一个参数为 NULL，则返回第三个参数。

#### 语法

```sql
NVL2(expression, value_if_not_null, value_if_null)
```

#### 示例

```sql
SELECT NVL2(commission, '有佣金', '无佣金') AS commission_status
FROM employees;
-- 如果 commission 列有值，则返回 '有佣金'，否则返回 '无佣金'
```

‍

### `COALESCE` 函数

`COALESCE` 函数返回其参数列表中的第一个非 NULL 值。它可以接受多个参数。

#### 语法

```sql
COALESCE(expr1, expr2, ..., exprn)
```

#### 示例

### `NULLIF` 函数

`NULLIF` 函数比较两个表达式，如果它们相等，则返回 NULL；如果它们不相等，则返回第一个表达式的值。

#### 语法

```sql
NULLIF(expr1, expr2)
```

#### 示例

```sql
SELECT NULLIF(salary, 0) AS salary_check
FROM employees;
-- 如果 salary 等于 0，则返回 NULL；否则返回 salary 的值
```

### 综合示例

假设有一个 `employees` 表，包含以下列：`employee_id`, `salary`, `commission`, `bonus`。可以使用上述函数来处理 NULL 值：

```sql
-- 使用 NVL 函数
SELECT employee_id, NVL(commission, 0) AS commission
FROM employees;

-- 使用 NVL2 函数
SELECT employee_id, NVL2(commission, commission, '无佣金') AS commission_status
FROM employees;

-- 使用 COALESCE 函数
SELECT employee_id, COALESCE(commission, bonus, 0) AS total_compensation
FROM employees;

-- 使用 NULLIF 函数
SELECT employee_id, NULLIF(commission, 0) AS commission_check
FROM employees;
```

这些函数提供了处理 NULL 值的灵活工具，可以在不同情况下选择合适的函数来简化查询和数据处理逻辑。

‍

## 字符串函数

### `CONCAT` 函数

#### 语法

```sql
CONCAT(string1, string2)
```

#### 示例

```sql
SELECT CONCAT(first_name, last_name) AS full_name
FROM employees;
-- 将 first_name 和 last_name 连接成一个字符串
```

### `SUBSTR` 函数

#### 语法

```sql
SUBSTR(string, start_position, length)
```

#### 示例

```sql
SELECT SUBSTR(first_name, 1, 3) AS short_name
FROM employees;
-- 从 first_name 的第一个字符开始，提取长度为 3 的子字符串
```

### `INSTR` 函数

#### 语法

```sql
INSTR(string, substring, [start_position], [nth_appearance])
```

#### 示例

```sql
SELECT INSTR(first_name, 'a') AS position
FROM employees;
-- 查找 first_name 中子字符串 'a' 的位置
```

### `TRIM` 函数

#### 语法

```sql
TRIM([leading | trailing | both] trim_character FROM string)
```

#### 示例

```sql
SELECT TRIM(' ' FROM first_name) AS trimmed_name
FROM employees;
-- 去除 first_name 两端的空格
```

### `REPLACE` 函数

#### 语法

```sql
REPLACE(string, search_string, replacement_string)
```

#### 示例

```sql
SELECT REPLACE(first_name, 'a', 'A') AS replaced_name
FROM employees;
-- 将 first_name 中所有的 'a' 替换为 'A'
```

## 数值函数

### `ROUND` 函数

#### 语法

```sql
ROUND(number, decimal_places)
```

#### 示例

```sql
SELECT ROUND(salary, 2) AS rounded_salary
FROM employees;
-- 将 salary 保留两位小数
```

### `TRUNC` 函数

#### 语法

```sql
TRUNC(number, decimal_places)
```

#### 示例

```sql
SELECT TRUNC(salary, 2) AS truncated_salary
FROM employees;
-- 将 salary 截断为两位小数
```

### `MOD` 函数

#### 语法

```sql
MOD(number, divisor)
```

#### 示例

```sql
SELECT MOD(employee_id, 2) AS remainder
FROM employees;
-- 计算 employee_id 除以 2 的余数
```

### `POWER` 函数

#### 语法

```sql
POWER(base, exponent)
```

#### 示例

```sql
SELECT POWER(salary, 2) AS salary_squared
FROM employees;
-- 计算 salary 的平方
```

## 日期函数

### `SYSDATE` 函数

#### 语法

```sql
SYSDATE
```

#### 示例

```sql
SELECT SYSDATE AS current_date
FROM dual;
-- 返回当前系统日期和时间
```

### `ADD_MONTHS` 函数

#### 语法

```sql
ADD_MONTHS(date, n)
```

#### 示例

```sql
SELECT ADD_MONTHS(hire_date, 6) AS new_date
FROM employees;
-- 返回 hire_date 加上 6 个月后的日期
```

### `MONTHS_BETWEEN` 函数

#### 语法

```sql
MONTHS_BETWEEN(date1, date2)
```

#### 示例

```sql
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS months_difference
FROM employees;
-- 计算当前日期和 hire_date 之间的月数差
```

### `NEXT_DAY` 函数

#### 语法

```sql
NEXT_DAY(date, weekday)
```

#### 示例

```sql
SELECT NEXT_DAY(hire_date, 'FRIDAY') AS next_friday
FROM employees;
-- 返回 hire_date 之后的第一个星期五的日期
```

### `LAST_DAY` 函数

#### 语法

```sql
LAST_DAY(date)
```

#### 示例

```sql
SELECT LAST_DAY(hire_date) AS last_day_of_month
FROM employees;
-- 返回 hire_date 所在月份的最后一天
```

### `EXTRACT` 函数

#### 语法

```sql
EXTRACT(part FROM date)
```

#### 示例

```sql
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;
-- 提取 hire_date 中的年份部分
```

‍

## 聚合函数

### `SUM` 函数

#### 语法

```sql
SUM(expression)
```

#### 示例

```sql
SELECT SUM(salary) AS total_salary
FROM employees;
-- 计算所有员工的工资总和
```

### `AVG` 函数

#### 语法

```sql
AVG(expression)
```

#### 示例

```sql
SELECT AVG(salary) AS average_salary
FROM employees;
-- 计算所有员工的平均工资
```

### `COUNT` 函数

#### 语法

```sql
COUNT(expression)
```

#### 示例

```sql
SELECT COUNT(*) AS total_employees
FROM employees;
-- 计算员工总数
```

### `MAX` 函数

#### 语法

```sql
MAX(expression)
```

#### 示例

```sql
SELECT MAX(salary) AS max_salary
FROM employees;
-- 查找最高工资
```

### `MIN` 函数

#### 语法

```sql
MIN(expression)
```

#### 示例

```sql
SELECT MIN(salary) AS min_salary
FROM employees;
-- 查找最低工资
```

## 转换函数

### `TO_CHAR` 函数

#### 语法

```sql
TO_CHAR(expression, format)
```

#### 示例

```sql
SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') AS hire_date_formatted
FROM employees;
-- 将 hire_date 转换为 'YYYY-MM-DD' 格式的字符串
```

### `TO_DATE` 函数

#### 语法

```sql
TO_DATE(string, format)
```

#### 示例

```sql
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') AS new_year
FROM dual;
-- 将字符串 '2023-01-01' 转换为日期
```

### `TO_NUMBER` 函数

#### 语法

```sql
TO_NUMBER(string)
```

#### 示例

```sql
SELECT TO_NUMBER('12345') AS number_value
FROM dual;
-- 将字符串 '12345' 转换为数值 12345
```

### `CAST` 函数

#### 语法

```sql
CAST(expression AS data_type)
```

#### 示例

```sql
SELECT CAST('2023-01-01' AS DATE) AS date_value
FROM dual;
-- 将字符串 '2023-01-01' 转换为日期类型
```

## 条件函数

### `CASE` 表达式

#### 语法

```sql
CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END
```

#### 示例

### `DECODE` 函数

#### 语法

```sql
DECODE(expression, search1, result1, search2, result2, ..., default)
```

#### 示例

## 分析函数

### `RANK` 函数

#### 语法

```sql
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

### `DENSE_RANK` 函数

#### 语法

```sql
DENSE_RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

### `ROW_NUMBER` 函数

#### 语法

```sql
ROW_NUMBER() OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

### `NTILE` 函数

#### 语法

```sql
NTILE(num_buckets) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

### `LAG` 函数

#### 语法

```sql
LAG(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

### `LEAD` 函数

#### 语法

```sql
LEAD(expression, offset, default) OVER (PARTITION BY partition_expression ORDER BY order_expression)
```

#### 示例

## 序列生成函数

### `NEXTVAL` 函数

#### 语法

```sql
sequence_name.NEXTVAL
```

#### 示例

```sql
SELECT employees_seq.NEXTVAL AS next_employee_id
FROM dual;
-- 从序列 employees_seq 获取下一个值
```

### `CURRVAL` 函数

#### 语法

```sql
sequence_name.CURRVAL
```

#### 示例

```sql
SELECT employees_seq.CURRVAL AS current_employee_id
FROM dual;
-- 获取序列 employees_seq 当前值
```

## 集合操作函数

### `UNION` 运算符

#### 语法

```sql
SELECT column_list FROM table1
UNION
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
UNION
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中所有不同的 employee_id
```

### `UNION ALL` 运算符

#### 语法

```sql
SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
UNION ALL
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中所有的 employee_id，包括重复值
```

### `INTERSECT` 运算符

#### 语法

```sql
SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
INTERSECT
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 和 employees_2024 中都有的 employee_id
```

### `MINUS` 运算符

#### 语法

```sql
SELECT column_list FROM table1
MINUS
SELECT column_list FROM table2
```

#### 示例

```sql
SELECT employee_id FROM employees_2023
MINUS
SELECT employee_id FROM employees_2024;
-- 返回 employees_2023 中有但 employees_2024 中没有的 employee_id
```

## 位图函数

### `BITAND` 函数

#### 语法

```sql
BITAND(value1, value2)
```

#### 示例

```sql
SELECT BITAND(6, 3) AS bitwise_and
FROM dual;
-- 返回 6 和 3 的位与操作结果，即 2
```

### `BITOR` 函数

#### 语法

```sql
BITOR(value1, value2)
```

#### 示例

```sql
SELECT BITOR(6, 3) AS bitwise_or
FROM dual;
-- 返回 6 和 3 的位或操作结果，即 7
```

### `BITXOR` 函数

#### 语法

```sql
BITXOR(value1, value2)
```

#### 示例

```sql
SELECT BITXOR(6, 3) AS bitwise_xor
FROM dual;
-- 返回 6 和 3 的位异或操作结果，即 5
```

## JSON 函数

### `JSON_OBJECT` 函数

#### 语法

```sql
JSON_OBJECT(key 'value' [, key 'value'] ...)
```

#### 示例

```sql
SELECT JSON_OBJECT('employee_id' VALUE employee_id, 'name' VALUE first_name || ' ' || last_name) AS employee_json
FROM employees;
-- 将 employee_id 和 full_name 转换为 JSON 对象

### `JSON_ARRAY` 函数

#### 语法

```sql
JSON_ARRAY(value1 [, value2] ...)
```

#### 示例

```sql
SELECT JSON_ARRAY(employee_id, first_name, last_name) AS employee_array
FROM employees;
-- 将 employee_id、first_name 和 last_name 转换为 JSON 数组
```

### `JSON_VALUE` 函数

#### 语法

```sql
JSON_VALUE(json_string, path)
```

#### 示例

```sql
SELECT JSON_VALUE('{"employee_id":123, "name":"John Doe"}', '$.name') AS employee_name
FROM dual;
-- 从 JSON 字符串中提取 name 值
```

### `JSON_QUERY` 函数

#### 语法

```sql
JSON_QUERY(json_string, path)
```

#### 示例

```sql
SELECT JSON_QUERY('{"employees":[{"id":123, "name":"John Doe"},{"id":124, "name":"Jane Doe"}]}', '$.employees') AS employees
FROM dual;
-- 从 JSON 字符串中提取 employees 数组
```

‍

## 其他常用函数

### `GREATEST` 函数

#### 语法

```sql
GREATEST(expr1, expr2, ..., exprn)
```

#### 示例

```sql
SELECT GREATEST(salary, bonus) AS max_value
FROM employees;
-- 返回 salary 和 bonus 中的较大值
```

### `LEAST` 函数

#### 语法

```sql
LEAST(expr1, expr2, ..., exprn)
```

#### 示例

```sql
SELECT LEAST(salary, bonus) AS min_value
FROM employees;
-- 返回 salary 和 bonus 中的较小值
```

### `NVARCHAR2` 函数

#### 语法

```sql
NVARCHAR2(length)
```

#### 示例

```sql
CREATE TABLE example (
    name NVARCHAR2(50)
);
-- 创建一个 NVARCHAR2 类型的列，最大长度为 50
```

### `LENGTH` 函数

#### 语法

```sql
LENGTH(string)
```

#### 示例

```sql
SELECT LENGTH(first_name) AS name_length
FROM employees;
-- 返回 first_name 的长度
```

### `UPPER` 函数

#### 语法

```sql
UPPER(string)
```

#### 示例

```sql
SELECT UPPER(first_name) AS upper_name
FROM employees;
-- 将 first_name 转换为大写
```

### `LOWER` 函数

#### 语法

```sql
LOWER(string)
```

#### 示例

```sql
SELECT LOWER(first_name) AS lower_name
FROM employees;
-- 将 first_name 转换为小写
```

### `INITCAP` 函数

#### 语法

```sql
INITCAP(string)
```

#### 示例

```sql
SELECT INITCAP(first_name) AS initcap_name
FROM employees;
-- 将 first_name 的每个单词首字母转换为大写
```

### `LPAD` 函数

#### 语法

```sql
LPAD(string, length, pad_string)
```

#### 示例

```sql
SELECT LPAD(employee_id, 5, '0') AS padded_id
FROM employees;
-- 将 employee_id 左填充到长度 5，用 '0' 作为填充值
```

### `RPAD` 函数

#### 语法

```sql
RPAD(string, length, pad_string)
```

#### 示例

```sql
SELECT RPAD(first_name, 10, ' ') AS padded_name
FROM employees;
-- 将 first_name 右填充到长度 10，用空格作为填充值
```

### `LTRIM` 函数

#### 语法

```sql
LTRIM(string, trim_string)
```

#### 示例

```sql
SELECT LTRIM(first_name, ' ') AS ltrim_name
FROM employees;
-- 去除 first_name 左端的空格
```

### `RTRIM` 函数

#### 语法

```sql
RTRIM(string, trim_string)
```

#### 示例

```sql
SELECT RTRIM(first_name, ' ') AS rtrim_name
FROM employees;
-- 去除 first_name 右端的空格
```

### `ABS` 函数

#### 语法

```sql
ABS(number)
```

#### 示例

```sql
SELECT ABS(-100) AS absolute_value
FROM dual;
-- 返回 -100 的绝对值，即 100
```

### `CEIL` 函数

#### 语法

```sql
CEIL(number)
```

#### 示例

```sql
SELECT CEIL(4.2) AS ceiling_value
FROM dual;
-- 返回 4.2 向上取整后的值，即 5
```

### `FLOOR` 函数

#### 语法

```sql
FLOOR(number)
```

#### 示例

```sql
SELECT FLOOR(4.7) AS floor_value
FROM dual;
-- 返回 4.7 向下取整后的值，即 4
```

### `ROUND` 函数

#### 语法

```sql
ROUND(number, decimal_places)
```

#### 示例

```sql
SELECT ROUND(4.567, 2) AS rounded_value
FROM dual;
-- 将 4.567 保留两位小数，即 4.57
```

### `SIGN` 函数

#### 语法

```sql
SIGN(number)
```

#### 示例

```sql
SELECT SIGN(-100) AS sign_value
FROM dual;
-- 返回 -100 的符号，即 -1
```

### `SQRT` 函数

#### 语法

```sql
SQRT(number)
```

#### 示例

```sql
SELECT SQRT(16) AS square_root
FROM dual;
-- 返回 16 的平方根，即 4
```

‍

Oracle函数及方法

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

使用 Cloudflare 配置 URL 重定向规则

快速排序（Quick Sort）详解

CentOS 中常用存储占用统计以及文件查询

IDEA 文件方法注释和版权配置指南

Linux 中查看端口占用情况

将指定 Jar 包推送到 Maven 仓库中

使用 Cloudflare 配置 URL 重定向规则

网站悼念黑白灰 CSS 样式

解决Fastdfs依赖包问题：Dependency 'org.csourcefastdfs-client-java1.27-SNAPSHOT' not

Oracle函数及方法

Oracle函数及方法

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Oracle函数及方法

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款