Oracle函数立大功

ApeAssistant
0 阅读6分钟

Oracle函数立大功

在项目开发中，数据库设计往往能让人“眼前一黑”。最近接手的一个项目，数据库里存储日期的字段居然全是字符串格式！

问了项目经理，得到的回答是：“这是公共表，供多个业务系统调用，字符串格式兼容性好，方便操作。”

我内心OS：啊？

下载.jpg下载.jpg下载.jpg下载.jpg

更离谱的是，字段设计时压根没统一日期格式，20240108、2024/1/8 22:28:37、20201103153314……五花八门。现在需求来了，要对这些记录进行统计，兼容各种日期格式。面对这堆“历史遗留问题”，主角登场——Oracle自定义函数！

1. Oracle函数是什么？

Oracle函数是数据库中用于封装特定逻辑的PL/SQL程序块，接受输入参数，执行处理后返回一个值。简单来说，它就像一个“黑盒”，你丢进去一些参数，它吐出一个结果。Oracle内置了很多函数，比如TO_DATE、REGEXP_LIKE，但有时候需求复杂，内置函数满足不了，就需要自定义函数。

自定义函数的好处是：

  • 逻辑复用：写一次，处处调用，减少重复代码。
  • 模块化：把复杂逻辑封装起来，调用时只关心输入输出。
  • 灵活性：可以处理特定业务场景，比如我们这次的日期格式兼容问题。

2. Oracle函数怎么创建？

创建Oracle函数的语法很简单，像写一个小型程序。基本结构如下：

CREATE OR REPLACE FUNCTION function_name (
    parameter1 IN datatype,
    parameter2 IN datatype DEFAULT default_value
) RETURN return_datatype IS
    -- 变量声明
    variable_name datatype;
BEGIN
    -- 逻辑处理
    RETURN result;
EXCEPTION
    -- 异常处理
    WHEN OTHERS THEN
        RETURN default_result;
END;
/
  • CREATE OR REPLACE：如果函数已存在，覆盖它。
  • 参数：用IN指定输入参数，可以设置默认值。
  • RETURN：指定返回类型，比如VARCHAR2、DATE。
  • EXCEPTION：处理异常，确保函数健壮性。

比如，创建一个简单函数，计算两个数的和：

CREATE OR REPLACE FUNCTION add_numbers (
    p_num1 IN NUMBER,
    p_num2 IN NUMBER DEFAULT 0
) RETURN NUMBER IS
    v_result NUMBER;
BEGIN
    v_result := p_num1 + p_num2;
    RETURN v_result;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
/

调用时：SELECT add_numbers(5, 3) FROM dual;，输出8。

3. 用REGEXP_LIKE举例子

REGEXP_LIKE是Oracle的正则表达式函数，常用于字符串模式匹配。在日期兼容场景中，它可以用来判断字符串是否符合某种日期格式。

参数

REGEXP_LIKE(source_string, pattern [, match_parameter])
  • source_string：要检查的字符串。
  • pattern：正则表达式模式，比如^\d{4}/\d{1,2}/\d{1,2}$匹配2024/1/8。
  • match_parameter：可选，控制匹配行为，比如i忽略大小写。

性能

  • 优点：灵活，适合复杂模式匹配。
  • 缺点：正则表达式解析较慢，数据量大时性能不如简单字符串操作（比如SUBSTR）。
  • 优化建议
    • 尽量用具体模式，避免过于宽泛的正则。
    • 结合索引或分区表减少扫描范围。
    • 小数据量用REGEXP_LIKE，大数据量考虑其他方法（如TO_DATE结合异常捕获）。

例子：检查字符串是否为YYYY/MM/DD格式：

SELECT *
FROM your_table
WHERE REGEXP_LIKE(date_column, '^\d{4}/\d{1,2}/\d{1,2}$');

4. 创建兼容日期的函数

面对项目的日期格式乱象（20240108、2024/1/8 22:28:37、20201103153314等），我们需要一个函数把这些字符串转成标准的DATE类型。以下是自定义函数的设计。

函数代码

create FUNCTION convert_to_date(
    p_date_str IN VARCHAR2 DEFAULT NULL,
    p_format IN VARCHAR2 DEFAULT 'YYYY-MM-DD'
)
    RETURN VARCHAR2 IS
    v_date DATE;
BEGIN
    IF p_date_str IS NULL THEN
        RETURN NULL; -- 处理默认输入
    END IF;

    CASE
        WHEN REGEXP_LIKE(p_date_str, '^\d{4}-\d{2}-\d{2}$') THEN
            v_date := TO_DATE(p_date_str, 'YYYY-MM-DD');
        WHEN REGEXP_LIKE(p_date_str, '^\d{2}/\d{2}/\d{4}$') THEN
            v_date := TO_DATE(p_date_str, 'DD/MM/YYYY');
        WHEN REGEXP_LIKE(p_date_str, '^\d{8}$') THEN
            v_date := TO_DATE(p_date_str, 'YYYYMMDD');
        WHEN REGEXP_LIKE(p_date_str, '^\d{4}/\d{1,2}/\d{1,2} \d{2}:\d{2}:\d{2}$') THEN
            v_date := TO_DATE(p_date_str, 'YYYY/MM/DD HH24:MI:SS');
        WHEN REGEXP_LIKE(p_date_str, '^\d{14}$') THEN
            v_date := TO_DATE(p_date_str, 'YYYYMMDDHH24MISS');
        WHEN REGEXP_LIKE(p_date_str, '^\d{4}-\d{2}-\d{2} \d{1,2}:\d{2}:\d{2}$') THEN
            v_date := TO_DATE(p_date_str, 'YYYY-MM-DD HH24:MI:SS');
        ELSE
            v_date := NULL; -- 无法解析的格式
        END CASE;

    IF v_date IS NULL THEN
        RETURN NULL;
    END IF;

    RETURN TO_CHAR(v_date, p_format);
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- 异常处理
END;
/

使用方法

SELECT convert_to_date('20240108') FROM dual; -- 输出 2024-01-08
SELECT convert_to_date('2024/1/8 22:28:37') FROM dual; -- 输出 2024-01-08 22:28:37
SELECT convert_to_date('20201103153314') FROM dual; -- 输出 2020-11-03 15:33:14

优势

  • 兼容性强：通过正则匹配多种格式，适应项目中的“花式”日期。
  • 健壮性：异常捕获确保函数不会因格式错误崩溃。
  • 灵活性：支持默认格式参数，方便扩展。
  • 可维护性：逻辑清晰，容易添加新格式。

注意

  • 函数假设输入字符串是有效的日期格式，实际项目中可能需要更严格的校验（比如验证月份是否在1-12）。
  • 正则表达式性能需关注，数据量大时可考虑优先用SUBSTR提取年月日再校验。

5. CASE WHEN顺序对性能的影响

在处理日期兼容时，有人可能会想到用CASE WHEN代替正则，比如：

SELECT CASE
           WHEN LENGTH(date_column) = 8 THEN TO_DATE(date_column, 'YYYYMMDD')
           WHEN REGEXP_LIKE(date_column, '^\d{4}/\d{1,2}/\d{1,2}\s+\d{2}:\d{2}:\d{2}$') THEN TO_DATE(date_column, 'YYYY/MM/DD HH24:MI:SS')
           ELSE NULL
       END AS converted_date
FROM your_table;

CASE WHEN的顺序对性能有影响：

  • 短路求值：Oracle按顺序执行CASE WHEN，一旦匹配就停止后续检查。所以，高频格式放前面能减少不必要的计算。
  • 正则的代价：如果正则表达式（如REGEXP_LIKE）出现在靠前的条件，而数据大多不匹配，会浪费大量计算资源。
  • 优化建议
    • 统计数据中日期格式的分布，把最常见的格式放在CASE WHEN的最前面。
    • 尽量用简单条件（比如LENGTH）作为前置过滤，减少正则的使用。
    • 如果表数据量大，考虑建立函数索引或物化视图加速查询。

6. 总结Oracle函数

Oracle自定义函数是处理复杂逻辑的利器，尤其在面对“奇葩”需求时，能大大提高代码复用性和可维护性。本文的convert_to_date函数通过正则匹配和TO_DATE，完美解决了字符串日期的兼容问题。它的核心优势在于模块化设计和异常处理，确保在各种“花式”日期格式下都能稳定运行。

关于CASE WHEN的性能，顺序优化是个小技巧，但影响不可忽视。实际项目中，建议结合数据分布和查询频率，合理安排条件顺序，甚至考虑索引优化。

最后，吐槽一下公司的字段设计：把日期存成字符串，还格式不统一，简直是“历史遗留问题”的教科书案例！😭 这种设计不仅增加开发难度，还埋下性能隐患。所以以后项目初期就规范字段类型，用DATE类型存储日期，省得后人“含泪踩坑”。😤

(对您有帮助 && 觉得我总结的还行) -> 受累点个免费的赞👍，谢谢

avatar
文章
阅读
粉丝