SQL 自定义函数指南

SQL 自定义函数（SQL UDF）允许你用 SQL 表达式封装可复用的计算逻辑，在查询中像内置函数一样调用。

选型建议

什么时候用 SQL 函数：

• 同一个计算逻辑在多个查询中重复出现
• 业务规则需要集中维护（如折扣计算、分类规则）
• 需要封装复杂的 CASE WHEN 或多步计算

涉及的 SQL 命令

CREATE FUNCTION

CREATE OR REPLACE FUNCTION

DROP FUNCTION

DESC FUNCTION

SHOW EXTERNAL FUNCTIONS

前置准备

CREATE TABLE doc_orders ( order_id INT, order_date DATE, amount DOUBLE, category STRING ); INSERT INTO doc_orders VALUES (1, DATE '2024-01-01', 99.9, 'electronics'), (2, DATE '2024-01-03', 299.0, 'clothing'), (3, DATE '2024-01-03', 49.5, 'food'), (4, DATE '2024-01-05', 199.0, 'electronics'), (5, DATE '2024-01-05', 0.0, 'food');

场景一：标量函数 — 封装计算公式

需求：统一管理折扣计算逻辑，不同地方调用时保持一致。

-- 创建折扣价格函数，rate 有默认值 0.9（九折） CREATE OR REPLACE FUNCTION public.discount_price(price DOUBLE, rate DOUBLE DEFAULT 0.9) RETURNS DOUBLE RETURN ROUND(price * rate, 2);

-- 使用默认折扣（九折） SELECT public.discount_price(100.0); -- 90.0 -- 指定折扣（八折） SELECT public.discount_price(100.0, 0.8); -- 80.0 -- 在查询中使用 SELECT order_id, amount, public.discount_price(amount) AS discounted FROM doc_orders WHERE category = 'electronics';

场景二：标量函数 — 封装分类规则

需求：根据金额对订单分级，规则集中维护。

CREATE OR REPLACE FUNCTION public.order_tier(amount DOUBLE) RETURNS STRING RETURN CASE WHEN amount >= 200 THEN 'Premium' WHEN amount >= 50 THEN 'Standard' WHEN amount > 0 THEN 'Basic' ELSE 'Free' END;

SELECT order_id, amount, public.order_tier(amount) AS tier FROM doc_orders ORDER BY amount DESC;

场景三：标量函数 — 数据清洗

需求：清洗手机号，去除非数字字符。

CREATE OR REPLACE FUNCTION public.clean_phone(phone STRING) RETURNS STRING RETURN REGEXP_REPLACE(TRIM(phone), '[0-9]', '');

SELECT public.clean_phone(' 138-1234-5678 '); -- 13812345678 SELECT public.clean_phone('+86 (010) 8888-9999'); -- 8601088889999

场景四：表函数 — 返回多行结果

表函数（Table Function）返回一张虚拟表，可以在

FROM

需求：生成连续日期序列，用于填充日历维度。

CREATE OR REPLACE FUNCTION public.date_range(start_date DATE, end_date DATE) RETURNS TABLE (dt DATE) RETURN SELECT DATE_ADD(start_date, pos) AS dt FROM (SELECT POSEXPLODE(SPLIT(SPACE(DATEDIFF(end_date, start_date)), ' '))) t(pos, v);

-- 直接调用 SELECT * FROM public.date_range(DATE '2024-01-01', DATE '2024-01-05');

-- 与订单表 LEFT JOIN，找出无订单的日期 SELECT d.dt, COALESCE(SUM(o.amount), 0) AS daily_revenue FROM public.date_range(DATE '2024-01-01', DATE '2024-01-05') d LEFT JOIN doc_orders o ON o.order_date = d.dt GROUP BY d.dt ORDER BY d.dt;

函数管理

查看函数定义

DESC FUNCTION public.order_tier;

返回函数名、创建时间、完整 SQL 定义等信息。

更新函数

使用

CREATE OR REPLACE FUNCTION

CREATE OR REPLACE FUNCTION public.order_tier(amount DOUBLE) RETURNS STRING RETURN CASE WHEN amount >= 300 THEN 'VIP' -- 新增 VIP 档 WHEN amount >= 200 THEN 'Premium' WHEN amount >= 50 THEN 'Standard' WHEN amount > 0 THEN 'Basic' ELSE 'Free' END;

CREATE OR REPLACE

删除函数

DROP FUNCTION IF EXISTS public.order_tier;

注意事项

• 必须带 Schema 前缀：调用时必须写

  schema_name.function_name

  schema_name.function_name
  ，否则报"函数找不到"。可通过

  SET cz.sql.remote.udf.lookup.policy = builtin_first

  SET cz.sql.remote.udf.lookup.policy = builtin_first
  改变解析策略，详见 SET（会话参数）
• 默认参数必须在末尾：有默认值的参数必须排在无默认值的参数之后
• 表函数只能用查询：

  RETURNS TABLE

  RETURNS TABLE
  的函数体只能是

  SELECT

  SELECT
  语句，不能是表达式
• 函数体不支持 DML：SQL 函数内不能执行 INSERT/UPDATE/DELETE
• 不支持递归：函数体内不能调用自身，会报"函数找不到"
• 可以调用其他自定义函数：函数体内可以调用同 Schema 下的其他 SQL 函数，需带 Schema 前缀
• 与内置函数同名时：带 Schema 前缀调用自定义函数，不带前缀调用内置函数，两者互不干扰
• NULL 输入：NULL 参数会参与 CASE WHEN 判断，

  NULL >= 200

  NULL >= 200
  为 false，最终走 ELSE 分支

• OR REPLACE

  OR REPLACE
  与

  IF NOT EXISTS

  IF NOT EXISTS
  不能同时使用：会报语法错误

相关文档

• CREATE SQL FUNCTION
• DROP FUNCTION
• DESC FUNCTION
• 开发外部函数
• SET（会话参数）