Oracle 函数（Function）开发文档

原创于 2025-09-17 11:34:55 发布 · 1.2k 阅读

9 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#oracle #数据库 #dba

Oracle数据库技术分享专栏收录该内容

74 篇文章

订阅专栏

1. 概述

Oracle 函数（Function）是一种存储在数据库中的可重用程序单元，用于执行特定的计算或操作并返回单个值。与存储过程（Procedure）相比，函数必须返回一个值，并且可以在 SQL 语句中直接调用，这使得它在数据处理和计算中非常灵活。

1.1 函数的优势

代码重用：一次定义，多处调用
简化复杂计算：将复杂逻辑封装，简化 SQL 语句
提高性能：预编译存储，减少网络传输
维护方便：集中管理，修改一处即可影响所有调用点

2. 函数创建语法

2.1 基本语法

CREATE [OR REPLACE] FUNCTION function_name

    (parameter1 [IN | OUT | IN OUT] data_type [DEFAULT value],

     parameter2 [IN | OUT | IN OUT] data_type [DEFAULT value],

     ...)

RETURN return_data_type

[DETERMINISTIC]

[PARALLEL_ENABLE]

[AGGREGATE | PIPELINED]

IS | AS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_handler_section]

END [function_name];

/

2.2 参数说明

OR REPLACE：如果函数已存在，则替换它
IN：输入参数（默认），只能读取
OUT：输出参数，用于返回值
IN OUT：既作为输入也作为输出
DEFAULT：指定参数默认值
DETERMINISTIC：表明函数对于相同输入始终返回相同结果
PARALLEL_ENABLE：允许在并行查询中使用
AGGREGATE：定义聚合函数
PIPELINED：定义管道函数

3. 函数类型

3.1 标量函数（Scalar Functions）

返回单个值的函数，最常用的函数类型。

示例：

CREATE OR REPLACE FUNCTION calculate_tax(salary IN NUMBER)

RETURN NUMBER

IS

BEGIN

    IF salary <= 5000 THEN

        RETURN salary * 0.1;

    ELSIF salary <= 10000 THEN

        RETURN salary * 0.15;

    ELSE

        RETURN salary * 0.2;

    END IF;

END calculate_tax;

/

3.2 表值函数（Table Functions）

返回一个结果集，可以像表一样被查询。

示例：

CREATE OR REPLACE TYPE emp_type AS OBJECT (

    emp_id NUMBER,

    emp_name VARCHAR2(100),

    salary NUMBER

);

/



CREATE OR REPLACE TYPE emp_table_type AS TABLE OF emp_type;

/



CREATE OR REPLACE FUNCTION get_employees(dept_id IN NUMBER)

RETURN emp_table_type

PIPELINED

IS

BEGIN

    FOR emp IN (SELECT employee_id, last_name, salary

               FROM employees

               WHERE department_id = dept_id)

    LOOP

        PIPE ROW(emp_type(emp.employee_id, emp.last_name, emp.salary));

    END LOOP;

    RETURN;

END get_employees;

/

3.3 聚合函数（Aggregate Functions）

对一组数据进行聚合计算并返回单个结果，如 SUM、AVG 等。可以自定义聚合函数。

4. 开发规范

4.1 命名规范

函数名称使用大写字母，以 F_ 为前缀，如 F_CALCULATE_TAX
参数名称使用小写字母，以 p_ 为前缀，如 p_salary
变量名称使用小写字母，以 v_ 为前缀，如 v_tax_rate

4.2 注释规范

每个函数必须包含头部注释，说明功能、参数、返回值、作者和创建日期
复杂逻辑处必须添加行内注释

示例：

CREATE OR REPLACE FUNCTION F_CALCULATE_TAX(p_salary IN NUMBER)

RETURN NUMBER

IS

    -- 计算员工税费的函数

    -- 参数: p_salary - 员工薪资

    -- 返回值: 计算得出的税费金额

    -- 作者: Leon-Ning Liu

    -- 创建日期: 2025-01-01

    v_tax_rate NUMBER;

BEGIN

    -- 根据薪资范围确定税率

    IF p_salary <= 5000 THEN

        v_tax_rate := 0.1;

    ELSIF p_salary <= 10000 THEN

        v_tax_rate := 0.15;

    ELSE

        v_tax_rate := 0.2;

    END IF;

   

    RETURN p_salary * v_tax_rate;

END F_CALCULATE_TAX;

/

4.3 代码风格

使用缩进提高可读性
每个 SQL 语句单独成行
逻辑块之间空一行分隔
避免过长的函数，单个函数不应超过 300 行

5. 异常处理

函数中必须包含异常处理部分，以捕获和处理可能的错误。

示例：

CREATE OR REPLACE FUNCTION F_DIVIDE(p_num1 IN NUMBER, p_num2 IN NUMBER)

RETURN NUMBER

IS

BEGIN

    IF p_num2 = 0 THEN

        RAISE VALUE_ERROR; -- 主动抛出异常

    END IF;

   

    RETURN p_num1 / p_num2;

EXCEPTION

    WHEN VALUE_ERROR THEN

        DBMS_OUTPUT.PUT_LINE('错误: 除数不能为零');

        RETURN NULL;

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('发生未知错误: ' || SQLERRM);

        RETURN NULL;

END F_DIVIDE;

/

6. 性能优化建议

减少上下文切换：尽量在函数内部完成所有操作，减少 SQL 和 PL/SQL 之间的切换
避免 DML 操作：函数中应尽量避免 INSERT、UPDATE、DELETE 等操作，尤其是在 SQL 语句中调用的函数
使用 DETERMINISTIC 关键字：对于输入相同返回结果也相同的函数，标记为 DETERMINISTIC 可提高性能
限制参数数量：过多的参数会降低函数的可用性和性能
避免过度使用函数：在 WHERE 子句中过度使用函数会导致索引失效，影响查询性能
使用批量操作：处理大量数据时，使用 BULK COLLECT 和 FORALL 提高性能

7. 函数的调用方式

7.1 在 PL/SQL 块中调用

DECLARE

    v_tax NUMBER;

BEGIN

    v_tax := F_CALCULATE_TAX(8000);

    DBMS_OUTPUT.PUT_LINE('税费: ' || v_tax);

END;

/

7.2 在 SQL 语句中调用

SELECT employee_id, last_name, salary, F_CALCULATE_TAX(salary) AS tax

FROM employees

WHERE department_id = 30;

7.3 调用表值函数

SELECT * FROM TABLE(get_employees(30));

8. 函数的管理

8.1 查看函数信息

-- 查看函数定义

SELECT text FROM user_source WHERE name = 'F_CALCULATE_TAX' ORDER BY line;

-- 查看函数状态

SELECT object_name, status FROM user_objects WHERE object_type = 'FUNCTION';