SQL 函数是可重用的代码块，可以接受参数、执行操作并返回结果。函数可以简化复杂查询，提高代码复用性。以下是 SQL 函数创建的全面指南。
1. 函数基本语法
sql
复制
下载
CREATE [OR REPLACE] FUNCTION function_name ([parameter_list])
RETURNS return_datatype
[LANGUAGE language_name]
AS $$
-- 函数体
DECLARE
-- 变量声明
BEGIN
-- 执行逻辑
RETURN return_value;
END;
$$;
2. 函数类型
2.1 标量函数 (返回单个值)
sql
复制
下载
CREATE FUNCTION get_employee_name(emp_id INT)
RETURNS VARCHAR
AS $$
DECLARE
emp_name VARCHAR(100);
BEGIN
SELECT name INTO emp_name FROM employees WHERE id = emp_id;
RETURN emp_name;
END;
$$ LANGUAGE plpgsql;
2.2 表值函数 (返回表)
sql
复制
下载
CREATE FUNCTION get_department_employees(dept_id INT)
RETURNS TABLE (
emp_id INT,
emp_name VARCHAR(100),
emp_salary DECIMAL(10,2)
)
AS $$
BEGIN
RETURN QUERY
SELECT id, name, salary
FROM employees
WHERE department_id = dept_id;
END;
$$ LANGUAGE plpgsql;
3. 参数类型
3.1 输入参数
sql
复制
下载
CREATE FUNCTION calculate_bonus(salary DECIMAL, performance_rating INT)
RETURNS DECIMAL
AS $$
BEGIN
RETURN salary * 0.1 * performance_rating;
END;
$$ LANGUAGE plpgsql;
3.2 输出参数
sql
复制
下载
CREATE FUNCTION get_employee_stats(
emp_id INT,
OUT total_projects INT,
OUT avg_project_score DECIMAL
)
AS $$
BEGIN
SELECT COUNT(*), AVG(score)
INTO total_projects, avg_project_score
FROM projects
WHERE employee_id = emp_id;
END;
$$ LANGUAGE plpgsql;
4. 函数特性
4.1 确定性函数
sql
复制
下载
CREATE FUNCTION get_tax_rate()
RETURNS DECIMAL
IMMUTABLE -- 表示函数结果不会改变
AS $$
BEGIN
RETURN 0.15; -- 固定税率
END;
$$ LANGUAGE plpgsql;
4.2 安全性定义
sql
复制
下载
CREATE FUNCTION update_salary(emp_id INT, new_salary DECIMAL)
RETURNS VOID
SECURITY DEFINER -- 以函数所有者权限执行
AS $$
BEGIN
UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;
5. 控制结构
5.1 条件语句
sql
复制
下载
CREATE FUNCTION get_employee_level(salary DECIMAL)
RETURNS VARCHAR
AS $$
BEGIN
IF salary > 100000 THEN
RETURN 'Senior';
ELSIF salary > 50000 THEN
RETURN 'Mid';
ELSE
RETURN 'Junior';
END IF;
END;
$$ LANGUAGE plpgsql;
5.2 循环语句
sql
复制
下载
CREATE FUNCTION factorial(n INT)
RETURNS INT
AS $$
DECLARE
result INT := 1;
i INT;
BEGIN
FOR i IN 1..n LOOP
result := result * i;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
6. 异常处理
sql
复制
下载
CREATE FUNCTION safe_divide(a DECIMAL, b DECIMAL)
RETURNS DECIMAL
AS $$
BEGIN
BEGIN
RETURN a / b;
EXCEPTION WHEN division_by_zero THEN
RETURN NULL;
END;
END;
$$ LANGUAGE plpgsql;
7. 不同数据库的语法差异
7.1 MySQL
sql
复制
下载
DELIMITER //
CREATE FUNCTION calculate_age(birth_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN YEAR(CURDATE()) - YEAR(birth_date);
END//
DELIMITER ;
7.2 SQL Server
sql
复制
下载
CREATE FUNCTION dbo.FormatPhoneNumber(@phone VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
RETURN '(' + SUBSTRING(@phone, 1, 3) + ') '
+ SUBSTRING(@phone, 4, 3) + '-'
+ SUBSTRING(@phone, 7, 4);
END;
7.3 Oracle
sql
复制
下载
CREATE OR REPLACE FUNCTION get_employee_count
RETURN NUMBER
IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END;
/
8. 最佳实践
命名规范：使用动词开头，如 calculate_, get_, validate_
参数验证：在函数开始时验证输入参数
错误处理：包含适当的异常处理
注释：说明函数目的、参数和返回值
性能：避免在函数中使用复杂查询或循环
9. 函数管理
9.1 修改函数
sql
复制
下载
CREATE OR REPLACE FUNCTION function_name(...)
-- 其余部分与创建相同
9.2 删除函数
sql
复制
下载
DROP FUNCTION [IF EXISTS] function_name;
9.3 查看函数定义
sql
复制
下载
-- PostgreSQL
\df function_name
-- MySQL
SHOW CREATE FUNCTION function_name;
-- SQL Server
SELECT OBJECT_DEFINITION(OBJECT_ID('schema.function_name'));
10. 实际应用示例
10.1 数据验证函数
sql
复制
下载
CREATE FUNCTION is_valid_email(email VARCHAR)
RETURNS BOOLEAN
AS $$
BEGIN
RETURN email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$';
END;
$$ LANGUAGE plpgsql;
10.2 复杂计算函数
sql
复制
下载
CREATE FUNCTION calculate_compound_interest(
principal DECIMAL,
rate DECIMAL,
years INT,
compounding_per_year INT
)
RETURNS DECIMAL
AS $$
BEGIN
RETURN principal * POWER(1 + rate/compounding_per_year,
compounding_per_year * years);
END;
$$ LANGUAGE plpgsql;
10.3 递归函数
sql
复制
下载
CREATE FUNCTION fibonacci(n INT)
RETURNS INT
AS $$
BEGIN
IF n <= 1 THEN
RETURN n;
ELSE
RETURN fibonacci(n-1) + fibonacci(n-2);
END IF;
END;
$$ LANGUAGE plpgsql;
通过掌握这些SQL函数创建技术，您可以大大提高数据库操作的效率和代码的可维护性。