SQL函数编写指南_SQL自定义FUNCTION示例-SQL-PHP中文网

SQL函数编写指南_SQL自定义FUNCTION示例

尊渡假赌尊渡假赌尊渡假赌

发布： 2025-12-04 20:51:29

原创

667人浏览过

SQL自定义函数分标量函数（返回单值，如手机号脱敏）和表值函数（返回结果集，如生成工作日列表），主流数据库均支持，但须有确定返回值且不可修改数据。

sql函数编写指南_sql自定义function示例

SQL自定义函数（FUNCTION）是提升查询复用性、封装逻辑、简化复杂计算的重要手段。它不是所有数据库都完全支持，但主流系统如 PostgreSQL、SQL Server、Oracle、MySQL（8.0+）均提供不同程度的支持。关键在于：函数必须有确定的返回值，不能修改数据（即不能含 INSERT/UPDATE/DELETE），且调用时像内置函数一样使用（如 SELECT my_func(col) FROM t）。

函数类型与适用场景

不同数据库对函数分类略有差异，但常见两类最实用：

标量函数（Scalar Function）：输入零个或多个参数，返回单个值（如字符串处理、数值转换）。适合封装重复表达式，比如把手机号脱敏为 138****1234。
表值函数（Table-Valued Function）：返回一个结果集（类似虚拟视图），可用于替代复杂子查询或动态 WHERE 条件。PostgreSQL 中用 RETURNS TABLE(...)，SQL Server 支持内联/多语句两种形式。

PostgreSQL 自定义函数示例（标量）

以下是一个安全脱敏手机号的函数，只暴露前3位和后4位：

CREATE OR REPLACE FUNCTION mask_phone(phone TEXT)
RETURNS TEXT AS $$
BEGIN
  IF phone ~ '^[0-9]{11}$' THEN
    RETURN LEFT(phone, 3) || '****' || RIGHT(phone, 4);
  ELSE
    RETURN 'INVALID';
  END IF;
END;
$$ LANGUAGE plpgsql;
<p>-- 使用方式：
-- SELECT name, mask_phone(mobile) FROM users;</p>

登录后复制

注意点：
• 必须声明 RETURNS 类型
• $$ 是字符串定界符，避免内部单引号冲突
• plpgsql 是过程语言，支持 IF/LOOP 等控制结构
• 函数体中不能执行 DML（除非显式声明 VOLATILE 并绕过限制，不推荐）

SQL Server 表值函数示例（内联）

根据起止日期返回指定范围内所有工作日（排除周末）：

雾象

WaytoAGI推出的AI动画生成引擎

1708

查看详情

CREATE FUNCTION dbo.GetWorkdays(@start DATE, @end DATE)
RETURNS TABLE
AS
RETURN
  WITH dates AS (
    SELECT @start AS d
    UNION ALL
    SELECT DATEADD(day, 1, d) FROM dates WHERE d < @end
  )
  SELECT d AS workday
  FROM dates
  WHERE DATEPART(weekday, d) NOT IN (1, 7); -- 周日=1，周六=7（默认设置）

登录后复制

调用方式直接参与 JOIN 或 SELECT：
SELECT * FROM dbo.GetWorkdays('2024-01-01', '2024-01-10');
• 内联表值函数性能好，执行计划可优化
• 不支持变量赋值、循环等过程语句，仅限单个 SELECT
• 若需复杂逻辑，改用多语句表值函数（RETURNS @t TABLE (...) AS BEGIN ... END）

避坑提醒：跨数据库兼容性要点

写函数前先确认当前环境能力边界：

MySQL 5.7 及以前不支持存储函数以外的自定义函数（UDF 需 C 编写，极少用）；MySQL 8.0+ 支持 SQL 函数但语法较简陋，无 PL/SQL 类控制流。
Oracle 函数需在 DECLARE-BEGIN-END 块中，且必须有 RETURN 语句，哪怕返回 NULL。
函数里慎用子查询依赖外部表别名（容易报“列不存在”），尤其嵌套层级深时；建议先抽成 CTE 或临时表。
避免在 WHERE 子句中对字段调用函数（如 WHERE UPPER(name) = 'ABC'），会导致索引失效——应考虑函数索引或提前计算列。

基本上就这些。函数不是万能胶，但用对了能显著提升 SQL 的可读性和可维护性。重点是明确目的、选对类型、控制副作用、留意执行效率。

以上就是SQL函数编写指南_SQL自定义FUNCTION示例的详细内容，更多请关注php中文网其它相关文章！