面试题：MySQL用户自定义函数开发之复杂逻辑实现

以下以MySQL为例，实现该用户自定义函数：

DELIMITER //

CREATE FUNCTION get_avg_salary(department_id INT) RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE avg_sal DECIMAL(10, 2);

    SELECT AVG(salary) INTO avg_sal
    FROM employees
    WHERE department_id = department_id;

    IF avg_sal IS NULL THEN
        SET avg_sal = 0;
    END IF;

    RETURN avg_sal;
END //

DELIMITER ;

说明

定义函数：CREATE FUNCTION get_avg_salary(department_id INT) RETURNS DECIMAL(10, 2) 定义了函数名 get_avg_salary，接受一个整数参数 department_id，返回一个两位小数的十进制数。
声明变量：DECLARE avg_sal DECIMAL(10, 2); 声明了用于存储平均工资的变量 avg_sal。
计算平均工资：SELECT AVG(salary) INTO avg_sal FROM employees WHERE department_id = department_id; 从 employees 表中获取指定部门的平均工资。
处理无员工情况：IF avg_sal IS NULL THEN SET avg_sal = 0; END IF; 如果平均工资为 NULL（即该部门无员工），将其设置为 0。
返回结果：RETURN avg_sal; 返回计算得到的平均工资。

性能优化

索引：在 employees 表的 department_id 字段上创建索引，这样在查询指定部门员工时能加快速度。

CREATE INDEX idx_department_id ON employees(department_id);

避免全表扫描：确保 department_id 字段的查询条件在 WHERE 子句中是最外层条件，避免在复杂子查询或多表连接中因执行计划不佳导致全表扫描。

不同数据库实现略有差异，比如在Oracle中：

CREATE OR REPLACE FUNCTION get_avg_salary(p_department_id IN NUMBER) RETURN NUMBER IS
    v_avg_sal NUMBER;
BEGIN
    SELECT AVG(salary) INTO v_avg_sal
    FROM employees
    WHERE department_id = p_department_id;

    IF v_avg_sal IS NULL THEN
        v_avg_sal := 0;
    END IF;

    RETURN v_avg_sal;
END;

同样，可以在 department_id 字段上创建索引来优化性能：

CREATE INDEX idx_department_id ON employees(department_id);

在SQL Server中：

CREATE FUNCTION get_avg_salary
(
    @department_id INT
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @avg_sal DECIMAL(10, 2);

    SELECT @avg_sal = AVG(salary)
    FROM employees
    WHERE department_id = @department_id;

    IF @avg_sal IS NULL
        SET @avg_sal = 0;

    RETURN @avg_sal;
END;

性能优化同样是在 department_id 字段创建索引：

CREATE INDEX idx_department_id ON employees(department_id);

星途面试题库

面试题：MySQL用户自定义函数开发之复杂逻辑实现

知识考点

面试题答案

说明

性能优化