尧图网页设计 
1. 先理清楚存储函数和存储过程的边界到底在哪存储过程系列写到第三篇这次聊存储函数。很多初学者走到这一步会卡住因为函数FUNCTION和过程PROCEDURE看起来实在太像了——都是数据库里预先编译好的代码块都能接收参数内部都支持变量、游标、条件判断。导致不少人分不清什么时候该用函数、什么时候该用过程最后全写成过程或者踩了“函数里偷偷改数据”的雷而不自知。先说本质区别。存储过程是一段独立的业务逻辑可以通过CALL/EXECUTE来调用重点在“做事情”的过程本身没有强制要求返回结果存储函数则是把一段计算逻辑封装成一个“能求值”的单元它有且必须有一个返回值而且函数的精髓在于可以直接嵌进SQL语句里用像内置函数一样参与表达式运算。举一个生活化的例子。你去餐厅点菜厨师炒菜的过程就是存储过程——你关心的是菜端上来的结果但炒菜过程中他是先放油还是先放盐你不关心也不需要他汇报而存储函数更像是一个“调料量取器”你告诉它“按3人份给我算盐量”它只负责给你一个数字你拿这个数字继续做别的决策。从调用方式上看差异更直观过程用CALL proc_name(...)或EXECUTE语句调用不能出现在SELECT的字段列表里函数用SELECT func_name(...) 或直接嵌入WHERE、SET、VALUES、CHECK约束等位置这是两者从设计层面就被赋予的不同使命。在Oracle、MySQL、openGauss三款数据库里这种差异是共通的但细节上有一些各自的门道。以我日常使用的经验Oracle对函数的约束最严格函数内默认不允许执行DML增删改因为Oracle本身把函数区分为“可嵌套”和“不可嵌套”两类这直接影响函数能不能在SQL里被安全调用MySQL的约束相对松一些函数体内可以做DML操作但这也带来困扰——如果函数里写了更新操作又把这个函数放在一条UPDATE语句的SET子句里就很容易出现意想不到的二次修改openGauss作为国内用得越来越多的开源数据库语法上延续了PostgreSQL的风格函数支持的语句更多甚至可以定义返回结果集的函数和视图、报表类场景结合得相当顺手。这篇博文就围绕“存储函数到底该怎么用”展开从创建语法、参数选择、权限与确定性声明到配合动态SQL做一表统计的实战案例最后整理我在三款库里踩过的坑和排查经验希望能帮在存储过程进阶路上的同学把函数这块补全。2. 定义一个存储函数前的关键选择参数、返回类型与函数体限制2.1 参数模式IN、OUT和IN OUT到底怎么选写过存储过程的人都清楚过程参数有三种模式IN入参、OUT出参、IN OUT出入参。函数的参数也分这三种但绝大多数情况下你应该只用IN。这背后有两个原因。第一函数的设计哲学是“输入确定、输出确定”像一个数学公式给定自变量就能算出因变量。如果你在函数里整了OUT参数调用方除了拿到返回值还得额外准备变量去接收OUT这样的用法放在SQL嵌入场景里是完全行不通的——你不可能在一条SELECT语句里为一个函数传一个变量进去再等它把OUT带出来SQL引擎没有这个机制。第二函数如果过度依赖OUT参数往往说明这个函数承担了“过程化”的职责这是设计上的气味问题。我在评审同事代码时看到FUNCTION里带OUT参数的基本都会建议改写成过程除非是类似Oracle中管道函数这类特殊情况。以MySQL为例一个典型的单参数函数长这样DELIMITER $$ CREATE FUNCTION avg_score(subject_name VARCHAR(50)) RETURNS DECIMAL(5, 2) DETERMINISTIC READS SQL DATA BEGIN DECLARE result_val DECIMAL(5, 2); SELECT AVG(score) INTO result_val FROM exam_scores WHERE subject subject_name; RETURN result_val; END$$ DELIMITER ;注意到RETURNS DECIMAL(5, 2)这一行这是函数和过程最明显的语法差异——过程用OUT参数来“带出”结果函数则必须用RETURNS声明返回类型并且在函数体内一定要有RETURN语句把值送出去。RETURNS和RETURN这两个关键字一个在头、一个在尾缺一不可最容易踩的坑是把RETURNS写漏了或者把类型长度写错导致字段精度在后续计算中被截断。2.2 确定性修饰词DETERMINISTIC与SQL访问修饰符很多第一次写存储函数的人不理解为什么MySQL的建函数语句里非要写DETERMINISTIC或NOT DETERMINISTICOracle里为什么没有这个关键词openGauss又为什么不强制。这个要从MySQL的复制机制说起。MySQL主从复制时从库要重放主库的binlog。如果函数是“不确定的”——比如内部用了NOW()、RAND()或UUID()这类每次执行都产生不同结果的函数——那么主库上的执行结果和从库上重放时计算出的结果就可能不一致导致主从数据漂移。为了规避这个风险MySQL要求创建函数时显式声明自己是确定性的还是不确定性的并且还要求声明SQL访问类型READS SQL DATA、MODIFIES SQL DATA、NO SQL等。在实际操作中我给大家一个务实的指引如果你的函数只读数据、不依赖时间/随机数就写DETERMINISTIC和READS SQL DATA好处是可以在索引表达式和生成列里使用优化器也能做更多的缓存优化。如果函数体里确实用了NOW()之类的非确定函数必须写NOT DETERMINISTIC否则严格模式下建函数会直接报错。如果函数完全不涉及SQL就写NO SQL例如纯数学计算函数。Oracle里没有这些义务性声明它把确定性问题交给了函数调用场景去判断能否在函数索引里使用、能否在物化视图快速刷新中引用默认都以更保守的标准执行。openGauss则因为源自PostgreSQL其函数本质上是“自定义函数”同样不需要强制声明但如果你把函数用在分区裁剪或代价估算场景时标注 IMMUTABLE 或 STABLE 这种稳定性级别能让优化器做出更好的执行计划——这个知识点很多从MySQL转过来的同学会忽略值得留意。2.3 函数体内能不能写DML这是个原则问题在Oracle里一个自定义函数如果在SQL语句中调用而函数体内又包含INSERT/UPDATE/DELETE通常会报ORA-14551: cannot perform a DML operation inside a query。Oracle不允许“查询里夹带写操作”这是为了保证语句的读写语义清晰、避免同一语句内的关联更新混乱。在MySQL里没有这么硬性的禁令你可以在函数里UPDATE一张表然后通过SELECT func()来触发。但我要提醒一句能写不代表应该写。函数一旦具备副作用它就变成了“披着函数外衣的过程”。试想一个场景——你在一条UPDATE语句的SET子句里调用了某个函数而这个函数内部又恰好UPDATE了这张表执行计划稍作调整可能就产生不可预期的连锁更新排查起来非常痛苦。我在生产环境里制定的约定是函数内只负责读和算绝不写所有涉及写操作的逻辑一律收敛到存储过程中。这样代码审查轻松很多排障时的思维负担也小很多。openGauss在这方面其实提供了更灵活的路线它支持返回表结构RETURNS TABLE的函数这种函数可以像视图一样嵌入查询后面配合动态SQL能玩出很多花样。但从设计的角度我仍然建议沿用“函数纯计算”的纪律除非你有特别充分的理由。3. 一个实战案例统计当前库下各表数据总量的存储过程既然相关热词里有“建一个统计当前库下各表数据总量的存储过程”这正好是一个能把存储过程和函数串起来综合运用的完善案例我把它放在这里一点点讲清楚。3.1 需求拆解与技术选型需求非常明确在一个库里扫描所有用户表统计每个表有多少行数据然后输出“表名 | 总行数”这样的清单。日常运维、数据迁移前评估、归档策略制定都会用到。这个需求如果用静态SQL做你得先手工查出所有表然后为每张表写一条SELECT COUNT(*)当表数量一多就极其低效。正确的思路是利用数据字典加动态SQL第一步查出所有用户表第二步循环拼接并执行COUNT语句第三步把结果汇总。这时就引出一个关键选择用过程还是用函数如果你只是想“执行一下然后看清单”可以写成过程——因为它本质是一个批处理动作没有谁会把“统计所有表的行数”这个动作塞进一条SELECT语句里当字段用。但如果我们把需求调整成“传入一个用户名返回他名下某张表的总行数”这就天然是一个函数职责——因为它是一个有输入、有单值输出的计算逻辑。为了把过程与函数的协作关系说透我在这个案例里会同时实现两者并演示标准的三部曲用过程做批处理用函数做单点计算再用一个巧妙的方式把函数的适用范围扩大。3.2 环境准备示例库与三张测试表假设我现在有一个业务库里面至少有三张表需要统计先造一点基础数据CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARACTER SET utf8mb4; USE demo_db; CREATE TABLE customer ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, level VARCHAR(10) DEFAULT normal ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, amount DECIMAL(10, 2) DEFAULT 0, order_date DATE ); CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, sku VARCHAR(30), quantity INT DEFAULT 1 ); INSERT INTO customer (name, level) VALUES (张三, vip), (李四, normal), (王五, vip); INSERT INTO orders (customer_id, amount, order_date) VALUES (1, 199.00, CURDATE()), (2, 88.50, CURDATE()), (3, 399.00, CURDATE()); INSERT INTO order_items (order_id, sku, quantity) VALUES (1, SKU-1001, 2), (1, SKU-1002, 1), (2, SKU-2001, 3);这样就有三张表customer有3行orders有3行order_items有3行。当然真实环境中表数量远远不止这里只是为了验证逻辑正确性。3.3 完整实现可动态统计任意库表数据量的存储过程实施思路是从information_schema.tables中查询当前库下所有基础表BASE TABLE逐一拼接动态SQL利用游标遍历把每张表的COUNT(*)结果插入临时表最后统一取出。我先说一个细节为什么查动态SQL时用“拼接”而不是参数绑定因为表名和字段名这类标识符是无法通过占位符绑定的只能直接拼接进SQL语句。这是动态SQL的边界问题——绑定变量只能传“值”不能传“名字”。Oracle和MySQL在这个问题上的处理一致openGauss也一样。下面是MySQL版本的存储过程我做了比较完整的注释DELIMITER $$ CREATE PROCEDURE sp_stats_table_counts(IN db_name VARCHAR(64)) BEGIN DECLARE v_table_name VARCHAR(64); DECLARE v_count BIGINT DEFAULT 0; DECLARE v_sql VARCHAR(500); DECLARE done INT DEFAULT 0; -- 游标当前库下所有 BASE TABLE DECLARE cur_tables CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema db_name AND table_type BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; -- 用一个临时表暂存统计结果 DROP TEMPORARY TABLE IF EXISTS temp_table_stats; CREATE TEMPORARY TABLE temp_table_stats ( table_name VARCHAR(64), row_count BIGINT ) ENGINE MEMORY; OPEN cur_tables; read_loop: LOOP FETCH cur_tables INTO v_table_name; IF done 1 THEN LEAVE read_loop; END IF; SET v_sql CONCAT(SELECT COUNT(*) INTO cnt FROM , db_name, ., v_table_name, ); SET exec_sql v_sql; PREPARE stmt FROM exec_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_count cnt; INSERT INTO temp_table_stats(table_name, row_count) VALUES (v_table_name, v_count); END LOOP; CLOSE cur_tables; SELECT table_name AS 表名, row_count AS 总行数 FROM temp_table_stats ORDER BY row_count DESC; END$$ DELIMITER ;执行方式就是CALL sp_stats_table_counts(demo_db)。这过程里有几个容易出错的地方我提醒一下MEMORY引擎临时表在数据量大的统计场景下速度很快但如果中途MySQL重启临时表生命周期极短它本身就是会话级的这点没问题。用cnt这个用户变量承接动态SQL查出的结果是因为PREPARE的EXECUTE语句无法直接把结果INTO到一个普通存储过程中的局部变量里但可以写入用户变量。这是MySQL动态SQL的一个经典约束不了解的话会在这里卡壳。游标的declare必须在变量声明之后、且在handlers之前这个顺序是语法强制的不少新手报错都是因为变量声明顺序不对。如果这条需求放在Oracle里思路完全一致但细节有变化Oracle中用的不是information_schema而是user_tables或all_tables动态SQL执行用EXECUTE IMMEDIATE配合INTO也能把单行统计结果取到变量中。下面是Oracle的对应版本关键逻辑同样完整CREATE OR REPLACE PROCEDURE sp_stats_table_counts( v_owner VARCHAR2 DEFAULT USER ) IS v_table_name VARCHAR2(128); v_count NUMBER; BEGIN EXECUTE IMMEDIATE TRUNCATE TABLE temp_table_stats; FOR rec IN (SELECT table_name FROM all_tables WHERE owner v_owner) LOOP v_table_name : rec.table_name; EXECUTE IMMEDIATE SELECT COUNT(*) FROM || v_owner || . || v_table_name INTO v_count; INSERT INTO temp_table_stats(table_name, row_count) VALUES (v_table_name, v_count); END LOOP; FOR r IN (SELECT * FROM temp_table_stats ORDER BY row_count DESC) LOOP DBMS_OUTPUT.PUT_LINE(r.table_name || : || r.row_count); END LOOP; END;Oracle里FOR循环隐式游标的写法比MySQL显式声明游标要省事很多这也是两个数据库在编程体验上一个比较明显的差异。openGauss的写法其实跟Oracle很像但需要把变量和游标定义放在BEGIN前的DECLARE区并且动态SQL的字符串拼接不能直接在内部引用变量——这一点和PostgreSQL一致。openGauss完整的对应实现我放到后面的常见问题章节里展开因为这块坑也不少。3.4 用存储函数包装统计逻辑从单表计数到“一函多表”过程做完了现在切换到函数视角。如果需求变成“给定一个表名返回这张表的数据总量”那本质就是一个典型的存储函数。MySQL版本如下DELIMITER $$ CREATE FUNCTION fn_table_count(table_name_in VARCHAR(64)) RETURNS BIGINT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_count BIGINT DEFAULT 0; SET tbl_name : table_name_in; SET sql_text : CONCAT(SELECT COUNT(*) FROM , tbl_name, INTO cnt); PREPARE s FROM sql_text; EXECUTE s; DEALLOCATE PREPARE s; SET v_count : cnt; RETURN v_count; END$$ DELIMITER ;用法SELECT fn_table_count(customer)结果直接返回数字。这样一个函数有任何实际意义吗单独看好像作用有限但注意函数最大的价值是“可以嵌套使用”。假设你已经有了存储过程sp_stats_table_counts它把每张表的统计结果存到了temp_table_stats临时表你想在结果集基础上增加一列“占比”你就可以写SELECT table_name, row_count, ROUND(row_count / SUM(row_count) OVER (), 4) AS ratio FROM temp_table_stats;如果换成“要求统计每张表最近30天新增的数据量”你就可以在函数上做文章传两个参数返回周期内的行数DELIMITER $$ CREATE FUNCTION fn_table_count_by_period( table_name_in VARCHAR(64), start_date DATE, end_date DATE ) RETURNS BIGINT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_count BIGINT DEFAULT 0; SET tbl_name : table_name_in; SET sql_text : CONCAT( SELECT COUNT(*) FROM , tbl_name, WHERE order_date BETWEEN , DATE_FORMAT(start_date, %Y-%m-%d), AND , DATE_FORMAT(end_date, %Y-%m-%d), INTO cnt ); PREPARE s FROM sql_text; EXECUTE s; DEALLOCATE PREPARE s; SET v_count : cnt; RETURN v_count; END$$ DELIMITER ;调用示例SELECT fn_table_count_by_period(orders, DATE_SUB(CURDATE(), INTERVAL 7 DAY), CURDATE())这段查询直接返回最近一周的订单量。这种带动态SQL的函数就是报表开发里的“瑞士军刀”用得好的话非常省事。但我必须补充一个关键限制如果表结构里没有order_date字段这段函数执行时会报错。所以动态表名的函数天然有一个弱点——它无法在编译期验证字段合法性只能在运行期暴露问题。初学时可能觉得这是鸡肋但实际生产场景里能够动态传入表名的函数往往被用在运维巡检、元数据管理、数据质量稽核这类“高度模式化”的任务中这类任务对表结构的齐整度有基础约束所以动态SQL的灵活性反而更加珍贵。3.5 一个容易被忽略的点临时表的生命周期与函数共存注意到我在存储过程中用临时表temp_table_stats存结果。临时表有几个特性跟函数搭配时容易踩坑临时表只在“当前会话”存活如果CALL这个过程的连接关闭再重开临时表会消失。这意味着你无法在另一个连接池里的会话中直接访问刚才函数的执行结果。所以实际生产中如果统计结果需要跨会话持久化应该改为物理表或者导出为报表。如果把临时表的创建逻辑放到函数里会出现一个更要命的问题——函数内不能直接访问另一个会话中定义的同名临时表甚至同一个存储过程里如果先建临时表、再调用另一个函数去访问它函数所在的作用域是否能看见这个临时表在不同数据库下表现还不太一样。这就是为什么我建议把临时表的创建放在过程里而不是函数里过程结束后再展示结果的做法最稳妥既避免临时表可见性争议也给后续扩展留出余地。4. 使用存储函数时的高频报错与排查技巧实录这一章专门整理我在实际项目里遇到过的、以及帮同事排查过的典型问题。很多问题是文档里不会直接写清楚“为什么”的我尽量把原因和排查路径都列出来当作一份速查手册。4.1 Oracle下ORA-14551查询里不允许DML操作这条报错出现时很多同事会一脸懵我的函数明明没有提交事务为什么在SELECT里调用就报这个错根本原因在于Oracle把“SQL语句执行过程中的函数调用”语境视作查询上下文在这种上下文中任何事务型操作——INSERT、UPDATE、DELETE——都会被禁止哪怕是写入临时表也不行临时表也算DML。排查思路很简单先把函数体里面所有写语句移除确认报错消失再把写入操作转移到调用函数的外层上下文。比如你本来想在函数里记录一条日志那就在存储过程里调用函数之后再单独写INSERT日志的语句。如果确实需要函数内部写数据一个折中办法是把函数声明为自治事务PRAGMA AUTONOMOUS_TRANSACTION但这会带来事务割裂的风险我不建议在生产环境里滥用。4.2 MySQL创建函数时报1418二进制的日志问题MySQL报错消息往往很直白This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)。展开说就是当binlog开启且log_bin_trust_function_creators参数为OFF时MySQL不允许创建没有明确安全标识的函数目的是防止从库数据不一致。大部分开发库默认不开binlog因此很多人没遇过这条报错一旦到了生产环境镜像库或者开启GTID复制的实例上这种报错就非常常见。三种解决办法按优先级排序最推荐在创建函数的DDL中老老实实加上DETERMINISTIC或READS SQL DATA或NO SQL。如果你确认这个函数只用于开发环境、不参与复制可以临时把log_bin_trust_function_creators设为ON。但生产环境千万别乱开否则会绕过函数安全标识检查。如果业务上确实需要写一个“不确定”的函数而且生产环境必须开启那就用CREATE FUNCTION前先SET GLOBAL log_bin_trust_function_creators ON然后立刻创建创建完毕后马上恢复OFF。这种方式治标不治本只能应急。4.3 openGauss里函数插入临时表总是报错openGauss中如果你想在函数里创建临时表并把统计结果存进去需要注意临时表的可见性。openGauss临时表分两种会话级临时表ON COMMIT PRESERVE ROWS和事务级临时表ON COMMIT DELETE ROWS。默认情况如果不写ON COMMIT子句行为可能与你预期不同。另外如果函数里创建临时表函数的并发调用会让临时表名字唯一性出现冲突——两个会话同时执行同名函数时后在执行的会话可能报“relation already exists”。解决方式是使用PG_TEMP_SCHEMA下的临时对象或者在函数内部将临时表名设置一个随机后缀比如用当前会话PID拼接。这个坑在PostgreSQL阵营里非常经典openGauss完整继承了它。所以如果你要在openGauss函数里做统计中间存储优先考虑全局临时表GLOBAL TEMPORARY或者普通内存表能省很多事。4.4 动态SQL中变量拼接的引号哲学单引号、双引号、反引号这个是每一门数据库动态SQL教程都会讲、但每个新手都会犯错的知识点。MySQL里字符串常量用单引号标识符用反引号Oracle里字符串常量用单引号别名和标识符可以不用引号或用双引号openGauss同样是单引号字符串、双引号标识符。动态SQL最大的痛点在于“拼接出来的语句必须是你手工执行也不会报错的语句”。我有个很笨但很有效的方法熟练之后仍然会先在Navicat或SQLcl里把最终要执行的SQL完整打印出来然后拿这段打印结果直接执行一遍。如果有问题肯定是拼接逻辑的问题而不是数据库的问题。MySQL里常见的引号错误是这句话SET sql_text CONCAT(SELECT COUNT(*) FROM , tbl_name, WHERE name 张三);这里为了在字符串里面嵌入“张三”的单引号必须用两个连续的单引号来转义。很多新人在第一次尝试时会漏掉一个拼接出来的SQL就成了WHERE name 张三直接执行就会语法报错。我习惯的做法是先拼一个格式化的SQL模板出来再代入值。比如SET sql_template SELECT COUNT(*) FROM %s WHERE order_date %s; SET sql_text CONCAT( REPLACE(REPLACE(sql_template, %s, tbl_name), %s, date_str) );虽然REPLACE嵌套看起来繁琐但比直接CONCAT一堆片段要好排查得多。4.5 MySQL与openGauss在函数上的权限差异MySQL里创建函数需要CREATE ROUTINE权限执行函数需要EXECUTE权限同时因为函数是全局对象不附属于某个schema的虽然你可以用schema名限定所以还要考虑库级权限。一个比较隐蔽的点是如果你通过存储过程调用函数该存储过程的DEFINER和调用者权限集合会共同影响函数内部动态SQL的权限判断。我用一句话归纳MySQL权限检查发生时会综合当前会话用户的权限和存储对象DEFINER的权限两者交集决定最终能访问什么。openGauss则采用和PostgreSQL相似的ACL体系权限控制比MySQL要细很多。函数默认只对创建者可见要对其他用户开放得单独GRANT EXECUTE。如果函数内部访问了某些表函数调用者需要对这些表具备SELECT权限否则即使是函数定义者也不能替调用者“越权”查数据。这一点跟Oracle的“定义者权限”与“调用者权限”取舍异曲同工。所以权限问题真的不是“写完函数就算了”的事。建议生产环境统一用一个业务账号创建函数并显式授予必要用户的EXECUTE权限避免每个会话用root或超级账号跑函数防止权限过宽留下风险。4.6 最常见的“函数返回NULL”问题有人写函数时发现SELECT fn_xxx()返回NULL百思不得其解。这通常是函数体内某次查询没有匹配到数据INTO变量没有被赋值而变量初始化又没有给默认值。MySQL里如果SELECT ... INTO一个变量查不到数据这个变量就保持原来的值但你声明变量时若没写DEFAULT它的初始值就是NULL。所以我的习惯是所有接收SQL结果集的变量声明时就给默认值。声明成DECLARE v_count BIGINT DEFAULT 0然后查询后如果没匹配到函数返回0而不是NULL这样在报表展示时观感好很多也不会因为NULL参与算术运算时把结果变成NULL。Oracle在这一点上更严格SELECT INTO如果查不到数据会直接抛NO_DATA_FOUND异常你得用EXCEPTION块捕获并给默认值。相比MySQL的“静默NULL”Oracle这种设计反而更能暴露潜在逻辑问题开发时要专门处理。5. 存储函数进阶的四个实用技巧基础语法和坑都讲完之后再分享几个我用下来觉得提升效率明显的技巧。这些技巧不一定适合所有场景但恰当地用会让你写出来的函数更稳健、更好维护。5.1 用函数做报表层的数据清洗实际报表开发里直接暴露原始字段给业务方是偷懒的表现。我经常把清洗逻辑封装成函数比如把手机号中间四位打码、把身份证号里的出生日期提取出来、把金额统一转成万元并保留两位小数之类的操作全部收拢到SQL层。这样业务方在除了报表工具时——比如FineReport或Tableau——只需SELECT biz_func(phone)即可前端不需要依赖后端脚本或JS。做得比较理想的一个函数就是手机号打码CREATE FUNCTION mask_phone(phone VARCHAR(11)) RETURNS VARCHAR(11) DETERMINISTIC NO SQL BEGIN RETURN CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)); END;这种函数在Oracle和openGauss中同样可以建只是语法上RETURNS前面要加RETURN关键字或者没有RETURNS这么直接但核心思路一致。报表组的同事都挺喜欢这种方式的因为他们不用关心数据脱敏规则写在哪个服务里数据库层搞定就能直接用。5.2 用函数做复杂校验的标准化入口除了脱敏函数还特别适合做“数据走查”类型的任务。比如一个订单表业务上要求amount必须大于0超过5000需要人工标记复核。你可以建一个校验函数返回结果为‘OK’或‘NEED_REVIEW’CREATE FUNCTION check_order_amount(amount_in DECIMAL(10, 2)) RETURNS VARCHAR(20) DETERMINISTIC NO SQL BEGIN IF amount_in 0 THEN RETURN INVALID; ELSEIF amount_in 5000 THEN RETURN NEED_REVIEW; ELSE RETURN OK; END IF; END;然后你可以在数据集成同步前执行一条检查SQLSELECT order_id, amount, check_order_amount(amount) AS check_result FROM orders WHERE check_order_amount(amount) OK;这样校验规则集中在数据库层多个系统接入时都走同一个口径也避免了不同后端语言各自实现一遍规则带来的“校验结果对不上”问题。在我经手的几个数据中台项目里这种模式让团队少吵了很多架——大家终于对“什么算异常数据”达成了一致。5.3 用函数做流水号或编码生成另一个我经常用的场景是编码生成。比如业务上需要一个“日期 序列号”的流水号单纯靠应用层的Redis计数器或数据库自增ID不容易保证“当日全局连续”。这时写一个函数读取一张计数表并且用UPDATE的原子性保证并发不重复CREATE FUNCTION next_serial_no(prefix_in VARCHAR(10)) RETURNS VARCHAR(20) MODIFIES SQL DATA BEGIN DECLARE v_seq INT; DECLARE v_result VARCHAR(20); UPDATE serial_counter SET current_value current_value 1 WHERE prefix prefix_in; SELECT current_value INTO v_seq FROM serial_counter WHERE prefix prefix_in; SET v_result CONCAT(prefix_in, DATE_FORMAT(NOW(), %Y%m%d), LPAD(v_seq, 6, 0)); RETURN v_result; END;这个函数用了UPDATE加SELECT利用行锁保证并发时不会拿到相同的序列号。注意其中用到了MODIFIES SQL DATA这在MySQL里是允许的在Oracle里面临ORA-14551限制所以同样的逻辑在Oracle里应该封装成过程或者用序列SEQUENCE实现。选择哪种取决于你的数据库平台我在这里列出来是为了展示函数的“计算包装”能力实际生产时要根据库的特性做调整。5.4 函数与存储过程的分工箴言能函数就不过程能SQL就不函数最后这个技巧更像是一条设计原则。很多开发者容易把函数当成“一个可以在SQL里调用的存储过程”这种认知偏差会导致两种不健康的代码风格一是函数里塞了大段业务逻辑函数体膨胀到几百行这违背了“函数应当轻量”的初衷二是把所有数据处理都试图用函数去包装结果代码里充满了难以维护的动态SQL。我个人的分工原则非常简单数据计算、数据转换、单值结果、需要嵌入SQL表达式 —— 用存储函数。批量操作、多步流程、需要事务控制的复杂业务 —— 用存储过程。能用一条普通SQL完成的聚合/关联 —— 就别写函数或过程。这条原则帮我减少了很多自己忽悠自己的“额外抽象”。实际开发中我见过有人为了“方便复用”把一个简单的两表LEFT JOIN都封装成函数结果调用时为了在WHERE里用函数还不得不写成SELECT * FROM t WHERE fn_xxx(t.id) 0这种写法性能比直接JOIN差了不止一个量级。这是典型的过度封装希望引以为戒。6. 基于实际项目的调优经验函数与存储过程性能剖析存储函数和存储过程在性能上的表现直接影响线上稳定性。这批经验来自我为几个业务系统做过的慢SQL治理未必放之四海皆准但普适性很高。6.1 函数内嵌查询与JOIN谁更快很多人问我写函数在SELECT语句里调用会不会导致无法走索引这得分两种情形。如果函数参数是从表字段传入的例如WHERE fn_table_count(t.table_name) 100那数据库对这个函数的求值次数等于符合条件的数据行数。也就是说5000张表就要调用5000次函数每次函数内部还有一条COUNT动态SQL这样效率一定很低——光SQL解析和权限校验就够吃CPU的。反过来如果你用纯SQL做JOIN聚合一次全表扫描加一次GROUP BY就出结果开销完全不同。所以我的建议是能用JOIN和聚合解决的问题永远不要靠循环调用函数来解决。函数适合做“单点计算”不适合做“批量计算”。批量计算就应该交给SQL引擎因为SQL是声明式的优化器知道如何并行、如何走索引而函数则把执行计划切成了一片片黑盒。6.2 函数返回大结果集怎么办存储函数返回单值但如果用openGauss的RETURNS TABLE或者Oracle的PIPELINED管道函数返回结果集性能问题就又不一样了。Oracle的管道函数比普通函数强在可以“边产出边消费”适合做数据转换流水线但管道函数在并行查询和排序场景中可能导致临时表空间暴涨。openGauss支持返回SETOF record或TABLE但返回结果集大小受work_mem影响如果结果集太大溢出到临时文件时性能会陡降。我在一次大批量报表生成时用过这种函数为了处理几十万行结果不得不把work_mem调大到256MB甚至512MB才算压住磁盘交换。这类函数确实灵活但在大数据量生产环境中要谨慎评估它对内存的影响。6.3 确定性声明对优化器的重要意义在MySQL里如果你把纯计算函数声明为DETERMINISTIC优化器在某些情况下可以优化掉对相同参数的重复调用同样在Oracle里如果函数被标记为DETERMINISTIC可以用于基于函数的索引比如对字符串做大小写不敏感的索引CREATE INDEX idx_name_upper ON customer (UPPER(name));但如果你把带副作用的函数标记成DETERMINISTIC就会制造巨大隐患优化器一旦认为函数结果稳定就可能把两次调用的求值结果缓存起来最终拿到过期数据。所以DETERMINISTIC这个声明是个双刃剑它帮优化器做条件判断也逼你把函数真正做到“确定”。6.4 动态SQL函数并发执行时的锁竞争之前提到的函数里内嵌动态SQL如果在并发场景下频繁执行内部会反复PREPARE和EXECUTE这比直接调用静态SQL要昂贵。更重要的是如果函数内部更新了一个全局的计数表比如编码生成函数所有并发请求都会串行等待这一行的行锁。我压测过单机并发200个请求同时调用next_serial_no的时候吞吐量从每秒几百直接掉到几十QPS指数级下滑。解决策略有两个方向一是减少锁粒度比如把“单条记录”改成“一批预取序列号”每次拼接多个号出来二是尽量把生成逻辑放到应用层数据库只做兜底。具体选哪个取决于你们的并发量级。我处理过的一个订单编号场景日订单几十万最终是靠应用层生成编号加数据库唯一索引兜底的方式扛下来的数据库函数只作为备用方案存在。7. 最后再分享一点个人使用心得存储函数这个主题我越用越觉得它本质是“把SQL思维成果沉淀为可复用资产”的工具。你每一次把一份长达十几行的CASE WHEN封装成一个函数其实是把业务口径固化成了数据库对象而口径这个东西在跨系统协作中恰恰是最容易失控的地方。函数不只是一个编程技巧它更是团队数据规范的一部分。我个人在实际项目中的体会是不要把存储函数当作“数据库里的程序”要当作“SQL表达式的一个扩展装置”。凡是你会在SQL里反复书写的计算逻辑就值得用函数包装凡是带状态的批处理流程就交给存储过程凡是能在应用层用简单表达式解决的问题不要盲目下放到数据库层。分清这几种边界你的数据库代码质量会明显上一个台阶。另外想提醒一个很多人容易忽略的心态问题别为了炫技而大量使用函数。数据库函数最大的维护成本是“隐藏逻辑”——如果业务方不知道某个字段已经经过函数加工他们很可能在报表里再用一套自己的口径再做一次转换导致最终数字对不上。所以每创建一个函数都要同时维护一份文档说明它的输入、输出、用途和版本变更。只有把数据库对象当资产来管理才能让存储函数成为团队效率的放大器而不是下一场故障的导火索。这一篇先把函数的基础和进阶用法讲到这里。下一篇我会继续沿着存储过程系列往下走把触发器、物化视图和批量任务调度串起来做一套完整的数据库自动化运维方案。如果你在实际用函数时遇到什么奇葩报错或者对参数写法和权限坑有疑问欢迎在评论区聊聊我看到了都会逐个回复。 存储函数与存储过程：从边界到动态SQL实战与避坑指南
1. 先理清楚存储函数和存储过程的边界到底在哪存储过程系列写到第三篇这次聊存储函数。很多初学者走到这一步会卡住因为函数FUNCTION和过程PROCEDURE看起来实在太像了——都是数据库里预先编译好的代码块都能接收参数内部都支持变量、游标、条件判断。导致不少人分不清什么时候该用函数、什么时候该用过程最后全写成过程或者踩了“函数里偷偷改数据”的雷而不自知。先说本质区别。存储过程是一段独立的业务逻辑可以通过CALL/EXECUTE来调用重点在“做事情”的过程本身没有强制要求返回结果存储函数则是把一段计算逻辑封装成一个“能求值”的单元它有且必须有一个返回值而且函数的精髓在于可以直接嵌进SQL语句里用像内置函数一样参与表达式运算。举一个生活化的例子。你去餐厅点菜厨师炒菜的过程就是存储过程——你关心的是菜端上来的结果但炒菜过程中他是先放油还是先放盐你不关心也不需要他汇报而存储函数更像是一个“调料量取器”你告诉它“按3人份给我算盐量”它只负责给你一个数字你拿这个数字继续做别的决策。从调用方式上看差异更直观过程用CALL proc_name(...)或EXECUTE语句调用不能出现在SELECT的字段列表里函数用SELECT func_name(...) 或直接嵌入WHERE、SET、VALUES、CHECK约束等位置这是两者从设计层面就被赋予的不同使命。在Oracle、MySQL、openGauss三款数据库里这种差异是共通的但细节上有一些各自的门道。以我日常使用的经验Oracle对函数的约束最严格函数内默认不允许执行DML增删改因为Oracle本身把函数区分为“可嵌套”和“不可嵌套”两类这直接影响函数能不能在SQL里被安全调用MySQL的约束相对松一些函数体内可以做DML操作但这也带来困扰——如果函数里写了更新操作又把这个函数放在一条UPDATE语句的SET子句里就很容易出现意想不到的二次修改openGauss作为国内用得越来越多的开源数据库语法上延续了PostgreSQL的风格函数支持的语句更多甚至可以定义返回结果集的函数和视图、报表类场景结合得相当顺手。这篇博文就围绕“存储函数到底该怎么用”展开从创建语法、参数选择、权限与确定性声明到配合动态SQL做一表统计的实战案例最后整理我在三款库里踩过的坑和排查经验希望能帮在存储过程进阶路上的同学把函数这块补全。2. 定义一个存储函数前的关键选择参数、返回类型与函数体限制2.1 参数模式IN、OUT和IN OUT到底怎么选写过存储过程的人都清楚过程参数有三种模式IN入参、OUT出参、IN OUT出入参。函数的参数也分这三种但绝大多数情况下你应该只用IN。这背后有两个原因。第一函数的设计哲学是“输入确定、输出确定”像一个数学公式给定自变量就能算出因变量。如果你在函数里整了OUT参数调用方除了拿到返回值还得额外准备变量去接收OUT这样的用法放在SQL嵌入场景里是完全行不通的——你不可能在一条SELECT语句里为一个函数传一个变量进去再等它把OUT带出来SQL引擎没有这个机制。第二函数如果过度依赖OUT参数往往说明这个函数承担了“过程化”的职责这是设计上的气味问题。我在评审同事代码时看到FUNCTION里带OUT参数的基本都会建议改写成过程除非是类似Oracle中管道函数这类特殊情况。以MySQL为例一个典型的单参数函数长这样DELIMITER $$ CREATE FUNCTION avg_score(subject_name VARCHAR(50)) RETURNS DECIMAL(5, 2) DETERMINISTIC READS SQL DATA BEGIN DECLARE result_val DECIMAL(5, 2); SELECT AVG(score) INTO result_val FROM exam_scores WHERE subject subject_name; RETURN result_val; END$$ DELIMITER ;注意到RETURNS DECIMAL(5, 2)这一行这是函数和过程最明显的语法差异——过程用OUT参数来“带出”结果函数则必须用RETURNS声明返回类型并且在函数体内一定要有RETURN语句把值送出去。RETURNS和RETURN这两个关键字一个在头、一个在尾缺一不可最容易踩的坑是把RETURNS写漏了或者把类型长度写错导致字段精度在后续计算中被截断。2.2 确定性修饰词DETERMINISTIC与SQL访问修饰符很多第一次写存储函数的人不理解为什么MySQL的建函数语句里非要写DETERMINISTIC或NOT DETERMINISTICOracle里为什么没有这个关键词openGauss又为什么不强制。这个要从MySQL的复制机制说起。MySQL主从复制时从库要重放主库的binlog。如果函数是“不确定的”——比如内部用了NOW()、RAND()或UUID()这类每次执行都产生不同结果的函数——那么主库上的执行结果和从库上重放时计算出的结果就可能不一致导致主从数据漂移。为了规避这个风险MySQL要求创建函数时显式声明自己是确定性的还是不确定性的并且还要求声明SQL访问类型READS SQL DATA、MODIFIES SQL DATA、NO SQL等。在实际操作中我给大家一个务实的指引如果你的函数只读数据、不依赖时间/随机数就写DETERMINISTIC和READS SQL DATA好处是可以在索引表达式和生成列里使用优化器也能做更多的缓存优化。如果函数体里确实用了NOW()之类的非确定函数必须写NOT DETERMINISTIC否则严格模式下建函数会直接报错。如果函数完全不涉及SQL就写NO SQL例如纯数学计算函数。Oracle里没有这些义务性声明它把确定性问题交给了函数调用场景去判断能否在函数索引里使用、能否在物化视图快速刷新中引用默认都以更保守的标准执行。openGauss则因为源自PostgreSQL其函数本质上是“自定义函数”同样不需要强制声明但如果你把函数用在分区裁剪或代价估算场景时标注 IMMUTABLE 或 STABLE 这种稳定性级别能让优化器做出更好的执行计划——这个知识点很多从MySQL转过来的同学会忽略值得留意。2.3 函数体内能不能写DML这是个原则问题在Oracle里一个自定义函数如果在SQL语句中调用而函数体内又包含INSERT/UPDATE/DELETE通常会报ORA-14551: cannot perform a DML operation inside a query。Oracle不允许“查询里夹带写操作”这是为了保证语句的读写语义清晰、避免同一语句内的关联更新混乱。在MySQL里没有这么硬性的禁令你可以在函数里UPDATE一张表然后通过SELECT func()来触发。但我要提醒一句能写不代表应该写。函数一旦具备副作用它就变成了“披着函数外衣的过程”。试想一个场景——你在一条UPDATE语句的SET子句里调用了某个函数而这个函数内部又恰好UPDATE了这张表执行计划稍作调整可能就产生不可预期的连锁更新排查起来非常痛苦。我在生产环境里制定的约定是函数内只负责读和算绝不写所有涉及写操作的逻辑一律收敛到存储过程中。这样代码审查轻松很多排障时的思维负担也小很多。openGauss在这方面其实提供了更灵活的路线它支持返回表结构RETURNS TABLE的函数这种函数可以像视图一样嵌入查询后面配合动态SQL能玩出很多花样。但从设计的角度我仍然建议沿用“函数纯计算”的纪律除非你有特别充分的理由。3. 一个实战案例统计当前库下各表数据总量的存储过程既然相关热词里有“建一个统计当前库下各表数据总量的存储过程”这正好是一个能把存储过程和函数串起来综合运用的完善案例我把它放在这里一点点讲清楚。3.1 需求拆解与技术选型需求非常明确在一个库里扫描所有用户表统计每个表有多少行数据然后输出“表名 | 总行数”这样的清单。日常运维、数据迁移前评估、归档策略制定都会用到。这个需求如果用静态SQL做你得先手工查出所有表然后为每张表写一条SELECT COUNT(*)当表数量一多就极其低效。正确的思路是利用数据字典加动态SQL第一步查出所有用户表第二步循环拼接并执行COUNT语句第三步把结果汇总。这时就引出一个关键选择用过程还是用函数如果你只是想“执行一下然后看清单”可以写成过程——因为它本质是一个批处理动作没有谁会把“统计所有表的行数”这个动作塞进一条SELECT语句里当字段用。但如果我们把需求调整成“传入一个用户名返回他名下某张表的总行数”这就天然是一个函数职责——因为它是一个有输入、有单值输出的计算逻辑。为了把过程与函数的协作关系说透我在这个案例里会同时实现两者并演示标准的三部曲用过程做批处理用函数做单点计算再用一个巧妙的方式把函数的适用范围扩大。3.2 环境准备示例库与三张测试表假设我现在有一个业务库里面至少有三张表需要统计先造一点基础数据CREATE DATABASE IF NOT EXISTS demo_db DEFAULT CHARACTER SET utf8mb4; USE demo_db; CREATE TABLE customer ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, level VARCHAR(10) DEFAULT normal ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT NOT NULL, amount DECIMAL(10, 2) DEFAULT 0, order_date DATE ); CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, sku VARCHAR(30), quantity INT DEFAULT 1 ); INSERT INTO customer (name, level) VALUES (张三, vip), (李四, normal), (王五, vip); INSERT INTO orders (customer_id, amount, order_date) VALUES (1, 199.00, CURDATE()), (2, 88.50, CURDATE()), (3, 399.00, CURDATE()); INSERT INTO order_items (order_id, sku, quantity) VALUES (1, SKU-1001, 2), (1, SKU-1002, 1), (2, SKU-2001, 3);这样就有三张表customer有3行orders有3行order_items有3行。当然真实环境中表数量远远不止这里只是为了验证逻辑正确性。3.3 完整实现可动态统计任意库表数据量的存储过程实施思路是从information_schema.tables中查询当前库下所有基础表BASE TABLE逐一拼接动态SQL利用游标遍历把每张表的COUNT(*)结果插入临时表最后统一取出。我先说一个细节为什么查动态SQL时用“拼接”而不是参数绑定因为表名和字段名这类标识符是无法通过占位符绑定的只能直接拼接进SQL语句。这是动态SQL的边界问题——绑定变量只能传“值”不能传“名字”。Oracle和MySQL在这个问题上的处理一致openGauss也一样。下面是MySQL版本的存储过程我做了比较完整的注释DELIMITER $$ CREATE PROCEDURE sp_stats_table_counts(IN db_name VARCHAR(64)) BEGIN DECLARE v_table_name VARCHAR(64); DECLARE v_count BIGINT DEFAULT 0; DECLARE v_sql VARCHAR(500); DECLARE done INT DEFAULT 0; -- 游标当前库下所有 BASE TABLE DECLARE cur_tables CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema db_name AND table_type BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; -- 用一个临时表暂存统计结果 DROP TEMPORARY TABLE IF EXISTS temp_table_stats; CREATE TEMPORARY TABLE temp_table_stats ( table_name VARCHAR(64), row_count BIGINT ) ENGINE MEMORY; OPEN cur_tables; read_loop: LOOP FETCH cur_tables INTO v_table_name; IF done 1 THEN LEAVE read_loop; END IF; SET v_sql CONCAT(SELECT COUNT(*) INTO cnt FROM , db_name, ., v_table_name, ); SET exec_sql v_sql; PREPARE stmt FROM exec_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_count cnt; INSERT INTO temp_table_stats(table_name, row_count) VALUES (v_table_name, v_count); END LOOP; CLOSE cur_tables; SELECT table_name AS 表名, row_count AS 总行数 FROM temp_table_stats ORDER BY row_count DESC; END$$ DELIMITER ;执行方式就是CALL sp_stats_table_counts(demo_db)。这过程里有几个容易出错的地方我提醒一下MEMORY引擎临时表在数据量大的统计场景下速度很快但如果中途MySQL重启临时表生命周期极短它本身就是会话级的这点没问题。用cnt这个用户变量承接动态SQL查出的结果是因为PREPARE的EXECUTE语句无法直接把结果INTO到一个普通存储过程中的局部变量里但可以写入用户变量。这是MySQL动态SQL的一个经典约束不了解的话会在这里卡壳。游标的declare必须在变量声明之后、且在handlers之前这个顺序是语法强制的不少新手报错都是因为变量声明顺序不对。如果这条需求放在Oracle里思路完全一致但细节有变化Oracle中用的不是information_schema而是user_tables或all_tables动态SQL执行用EXECUTE IMMEDIATE配合INTO也能把单行统计结果取到变量中。下面是Oracle的对应版本关键逻辑同样完整CREATE OR REPLACE PROCEDURE sp_stats_table_counts( v_owner VARCHAR2 DEFAULT USER ) IS v_table_name VARCHAR2(128); v_count NUMBER; BEGIN EXECUTE IMMEDIATE TRUNCATE TABLE temp_table_stats; FOR rec IN (SELECT table_name FROM all_tables WHERE owner v_owner) LOOP v_table_name : rec.table_name; EXECUTE IMMEDIATE SELECT COUNT(*) FROM || v_owner || . || v_table_name INTO v_count; INSERT INTO temp_table_stats(table_name, row_count) VALUES (v_table_name, v_count); END LOOP; FOR r IN (SELECT * FROM temp_table_stats ORDER BY row_count DESC) LOOP DBMS_OUTPUT.PUT_LINE(r.table_name || : || r.row_count); END LOOP; END;Oracle里FOR循环隐式游标的写法比MySQL显式声明游标要省事很多这也是两个数据库在编程体验上一个比较明显的差异。openGauss的写法其实跟Oracle很像但需要把变量和游标定义放在BEGIN前的DECLARE区并且动态SQL的字符串拼接不能直接在内部引用变量——这一点和PostgreSQL一致。openGauss完整的对应实现我放到后面的常见问题章节里展开因为这块坑也不少。3.4 用存储函数包装统计逻辑从单表计数到“一函多表”过程做完了现在切换到函数视角。如果需求变成“给定一个表名返回这张表的数据总量”那本质就是一个典型的存储函数。MySQL版本如下DELIMITER $$ CREATE FUNCTION fn_table_count(table_name_in VARCHAR(64)) RETURNS BIGINT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_count BIGINT DEFAULT 0; SET tbl_name : table_name_in; SET sql_text : CONCAT(SELECT COUNT(*) FROM , tbl_name, INTO cnt); PREPARE s FROM sql_text; EXECUTE s; DEALLOCATE PREPARE s; SET v_count : cnt; RETURN v_count; END$$ DELIMITER ;用法SELECT fn_table_count(customer)结果直接返回数字。这样一个函数有任何实际意义吗单独看好像作用有限但注意函数最大的价值是“可以嵌套使用”。假设你已经有了存储过程sp_stats_table_counts它把每张表的统计结果存到了temp_table_stats临时表你想在结果集基础上增加一列“占比”你就可以写SELECT table_name, row_count, ROUND(row_count / SUM(row_count) OVER (), 4) AS ratio FROM temp_table_stats;如果换成“要求统计每张表最近30天新增的数据量”你就可以在函数上做文章传两个参数返回周期内的行数DELIMITER $$ CREATE FUNCTION fn_table_count_by_period( table_name_in VARCHAR(64), start_date DATE, end_date DATE ) RETURNS BIGINT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_count BIGINT DEFAULT 0; SET tbl_name : table_name_in; SET sql_text : CONCAT( SELECT COUNT(*) FROM , tbl_name, WHERE order_date BETWEEN , DATE_FORMAT(start_date, %Y-%m-%d), AND , DATE_FORMAT(end_date, %Y-%m-%d), INTO cnt ); PREPARE s FROM sql_text; EXECUTE s; DEALLOCATE PREPARE s; SET v_count : cnt; RETURN v_count; END$$ DELIMITER ;调用示例SELECT fn_table_count_by_period(orders, DATE_SUB(CURDATE(), INTERVAL 7 DAY), CURDATE())这段查询直接返回最近一周的订单量。这种带动态SQL的函数就是报表开发里的“瑞士军刀”用得好的话非常省事。但我必须补充一个关键限制如果表结构里没有order_date字段这段函数执行时会报错。所以动态表名的函数天然有一个弱点——它无法在编译期验证字段合法性只能在运行期暴露问题。初学时可能觉得这是鸡肋但实际生产场景里能够动态传入表名的函数往往被用在运维巡检、元数据管理、数据质量稽核这类“高度模式化”的任务中这类任务对表结构的齐整度有基础约束所以动态SQL的灵活性反而更加珍贵。3.5 一个容易被忽略的点临时表的生命周期与函数共存注意到我在存储过程中用临时表temp_table_stats存结果。临时表有几个特性跟函数搭配时容易踩坑临时表只在“当前会话”存活如果CALL这个过程的连接关闭再重开临时表会消失。这意味着你无法在另一个连接池里的会话中直接访问刚才函数的执行结果。所以实际生产中如果统计结果需要跨会话持久化应该改为物理表或者导出为报表。如果把临时表的创建逻辑放到函数里会出现一个更要命的问题——函数内不能直接访问另一个会话中定义的同名临时表甚至同一个存储过程里如果先建临时表、再调用另一个函数去访问它函数所在的作用域是否能看见这个临时表在不同数据库下表现还不太一样。这就是为什么我建议把临时表的创建放在过程里而不是函数里过程结束后再展示结果的做法最稳妥既避免临时表可见性争议也给后续扩展留出余地。4. 使用存储函数时的高频报错与排查技巧实录这一章专门整理我在实际项目里遇到过的、以及帮同事排查过的典型问题。很多问题是文档里不会直接写清楚“为什么”的我尽量把原因和排查路径都列出来当作一份速查手册。4.1 Oracle下ORA-14551查询里不允许DML操作这条报错出现时很多同事会一脸懵我的函数明明没有提交事务为什么在SELECT里调用就报这个错根本原因在于Oracle把“SQL语句执行过程中的函数调用”语境视作查询上下文在这种上下文中任何事务型操作——INSERT、UPDATE、DELETE——都会被禁止哪怕是写入临时表也不行临时表也算DML。排查思路很简单先把函数体里面所有写语句移除确认报错消失再把写入操作转移到调用函数的外层上下文。比如你本来想在函数里记录一条日志那就在存储过程里调用函数之后再单独写INSERT日志的语句。如果确实需要函数内部写数据一个折中办法是把函数声明为自治事务PRAGMA AUTONOMOUS_TRANSACTION但这会带来事务割裂的风险我不建议在生产环境里滥用。4.2 MySQL创建函数时报1418二进制的日志问题MySQL报错消息往往很直白This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)。展开说就是当binlog开启且log_bin_trust_function_creators参数为OFF时MySQL不允许创建没有明确安全标识的函数目的是防止从库数据不一致。大部分开发库默认不开binlog因此很多人没遇过这条报错一旦到了生产环境镜像库或者开启GTID复制的实例上这种报错就非常常见。三种解决办法按优先级排序最推荐在创建函数的DDL中老老实实加上DETERMINISTIC或READS SQL DATA或NO SQL。如果你确认这个函数只用于开发环境、不参与复制可以临时把log_bin_trust_function_creators设为ON。但生产环境千万别乱开否则会绕过函数安全标识检查。如果业务上确实需要写一个“不确定”的函数而且生产环境必须开启那就用CREATE FUNCTION前先SET GLOBAL log_bin_trust_function_creators ON然后立刻创建创建完毕后马上恢复OFF。这种方式治标不治本只能应急。4.3 openGauss里函数插入临时表总是报错openGauss中如果你想在函数里创建临时表并把统计结果存进去需要注意临时表的可见性。openGauss临时表分两种会话级临时表ON COMMIT PRESERVE ROWS和事务级临时表ON COMMIT DELETE ROWS。默认情况如果不写ON COMMIT子句行为可能与你预期不同。另外如果函数里创建临时表函数的并发调用会让临时表名字唯一性出现冲突——两个会话同时执行同名函数时后在执行的会话可能报“relation already exists”。解决方式是使用PG_TEMP_SCHEMA下的临时对象或者在函数内部将临时表名设置一个随机后缀比如用当前会话PID拼接。这个坑在PostgreSQL阵营里非常经典openGauss完整继承了它。所以如果你要在openGauss函数里做统计中间存储优先考虑全局临时表GLOBAL TEMPORARY或者普通内存表能省很多事。4.4 动态SQL中变量拼接的引号哲学单引号、双引号、反引号这个是每一门数据库动态SQL教程都会讲、但每个新手都会犯错的知识点。MySQL里字符串常量用单引号标识符用反引号Oracle里字符串常量用单引号别名和标识符可以不用引号或用双引号openGauss同样是单引号字符串、双引号标识符。动态SQL最大的痛点在于“拼接出来的语句必须是你手工执行也不会报错的语句”。我有个很笨但很有效的方法熟练之后仍然会先在Navicat或SQLcl里把最终要执行的SQL完整打印出来然后拿这段打印结果直接执行一遍。如果有问题肯定是拼接逻辑的问题而不是数据库的问题。MySQL里常见的引号错误是这句话SET sql_text CONCAT(SELECT COUNT(*) FROM , tbl_name, WHERE name 张三);这里为了在字符串里面嵌入“张三”的单引号必须用两个连续的单引号来转义。很多新人在第一次尝试时会漏掉一个拼接出来的SQL就成了WHERE name 张三直接执行就会语法报错。我习惯的做法是先拼一个格式化的SQL模板出来再代入值。比如SET sql_template SELECT COUNT(*) FROM %s WHERE order_date %s; SET sql_text CONCAT( REPLACE(REPLACE(sql_template, %s, tbl_name), %s, date_str) );虽然REPLACE嵌套看起来繁琐但比直接CONCAT一堆片段要好排查得多。4.5 MySQL与openGauss在函数上的权限差异MySQL里创建函数需要CREATE ROUTINE权限执行函数需要EXECUTE权限同时因为函数是全局对象不附属于某个schema的虽然你可以用schema名限定所以还要考虑库级权限。一个比较隐蔽的点是如果你通过存储过程调用函数该存储过程的DEFINER和调用者权限集合会共同影响函数内部动态SQL的权限判断。我用一句话归纳MySQL权限检查发生时会综合当前会话用户的权限和存储对象DEFINER的权限两者交集决定最终能访问什么。openGauss则采用和PostgreSQL相似的ACL体系权限控制比MySQL要细很多。函数默认只对创建者可见要对其他用户开放得单独GRANT EXECUTE。如果函数内部访问了某些表函数调用者需要对这些表具备SELECT权限否则即使是函数定义者也不能替调用者“越权”查数据。这一点跟Oracle的“定义者权限”与“调用者权限”取舍异曲同工。所以权限问题真的不是“写完函数就算了”的事。建议生产环境统一用一个业务账号创建函数并显式授予必要用户的EXECUTE权限避免每个会话用root或超级账号跑函数防止权限过宽留下风险。4.6 最常见的“函数返回NULL”问题有人写函数时发现SELECT fn_xxx()返回NULL百思不得其解。这通常是函数体内某次查询没有匹配到数据INTO变量没有被赋值而变量初始化又没有给默认值。MySQL里如果SELECT ... INTO一个变量查不到数据这个变量就保持原来的值但你声明变量时若没写DEFAULT它的初始值就是NULL。所以我的习惯是所有接收SQL结果集的变量声明时就给默认值。声明成DECLARE v_count BIGINT DEFAULT 0然后查询后如果没匹配到函数返回0而不是NULL这样在报表展示时观感好很多也不会因为NULL参与算术运算时把结果变成NULL。Oracle在这一点上更严格SELECT INTO如果查不到数据会直接抛NO_DATA_FOUND异常你得用EXCEPTION块捕获并给默认值。相比MySQL的“静默NULL”Oracle这种设计反而更能暴露潜在逻辑问题开发时要专门处理。5. 存储函数进阶的四个实用技巧基础语法和坑都讲完之后再分享几个我用下来觉得提升效率明显的技巧。这些技巧不一定适合所有场景但恰当地用会让你写出来的函数更稳健、更好维护。5.1 用函数做报表层的数据清洗实际报表开发里直接暴露原始字段给业务方是偷懒的表现。我经常把清洗逻辑封装成函数比如把手机号中间四位打码、把身份证号里的出生日期提取出来、把金额统一转成万元并保留两位小数之类的操作全部收拢到SQL层。这样业务方在除了报表工具时——比如FineReport或Tableau——只需SELECT biz_func(phone)即可前端不需要依赖后端脚本或JS。做得比较理想的一个函数就是手机号打码CREATE FUNCTION mask_phone(phone VARCHAR(11)) RETURNS VARCHAR(11) DETERMINISTIC NO SQL BEGIN RETURN CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)); END;这种函数在Oracle和openGauss中同样可以建只是语法上RETURNS前面要加RETURN关键字或者没有RETURNS这么直接但核心思路一致。报表组的同事都挺喜欢这种方式的因为他们不用关心数据脱敏规则写在哪个服务里数据库层搞定就能直接用。5.2 用函数做复杂校验的标准化入口除了脱敏函数还特别适合做“数据走查”类型的任务。比如一个订单表业务上要求amount必须大于0超过5000需要人工标记复核。你可以建一个校验函数返回结果为‘OK’或‘NEED_REVIEW’CREATE FUNCTION check_order_amount(amount_in DECIMAL(10, 2)) RETURNS VARCHAR(20) DETERMINISTIC NO SQL BEGIN IF amount_in 0 THEN RETURN INVALID; ELSEIF amount_in 5000 THEN RETURN NEED_REVIEW; ELSE RETURN OK; END IF; END;然后你可以在数据集成同步前执行一条检查SQLSELECT order_id, amount, check_order_amount(amount) AS check_result FROM orders WHERE check_order_amount(amount) OK;这样校验规则集中在数据库层多个系统接入时都走同一个口径也避免了不同后端语言各自实现一遍规则带来的“校验结果对不上”问题。在我经手的几个数据中台项目里这种模式让团队少吵了很多架——大家终于对“什么算异常数据”达成了一致。5.3 用函数做流水号或编码生成另一个我经常用的场景是编码生成。比如业务上需要一个“日期 序列号”的流水号单纯靠应用层的Redis计数器或数据库自增ID不容易保证“当日全局连续”。这时写一个函数读取一张计数表并且用UPDATE的原子性保证并发不重复CREATE FUNCTION next_serial_no(prefix_in VARCHAR(10)) RETURNS VARCHAR(20) MODIFIES SQL DATA BEGIN DECLARE v_seq INT; DECLARE v_result VARCHAR(20); UPDATE serial_counter SET current_value current_value 1 WHERE prefix prefix_in; SELECT current_value INTO v_seq FROM serial_counter WHERE prefix prefix_in; SET v_result CONCAT(prefix_in, DATE_FORMAT(NOW(), %Y%m%d), LPAD(v_seq, 6, 0)); RETURN v_result; END;这个函数用了UPDATE加SELECT利用行锁保证并发时不会拿到相同的序列号。注意其中用到了MODIFIES SQL DATA这在MySQL里是允许的在Oracle里面临ORA-14551限制所以同样的逻辑在Oracle里应该封装成过程或者用序列SEQUENCE实现。选择哪种取决于你的数据库平台我在这里列出来是为了展示函数的“计算包装”能力实际生产时要根据库的特性做调整。5.4 函数与存储过程的分工箴言能函数就不过程能SQL就不函数最后这个技巧更像是一条设计原则。很多开发者容易把函数当成“一个可以在SQL里调用的存储过程”这种认知偏差会导致两种不健康的代码风格一是函数里塞了大段业务逻辑函数体膨胀到几百行这违背了“函数应当轻量”的初衷二是把所有数据处理都试图用函数去包装结果代码里充满了难以维护的动态SQL。我个人的分工原则非常简单数据计算、数据转换、单值结果、需要嵌入SQL表达式 —— 用存储函数。批量操作、多步流程、需要事务控制的复杂业务 —— 用存储过程。能用一条普通SQL完成的聚合/关联 —— 就别写函数或过程。这条原则帮我减少了很多自己忽悠自己的“额外抽象”。实际开发中我见过有人为了“方便复用”把一个简单的两表LEFT JOIN都封装成函数结果调用时为了在WHERE里用函数还不得不写成SELECT * FROM t WHERE fn_xxx(t.id) 0这种写法性能比直接JOIN差了不止一个量级。这是典型的过度封装希望引以为戒。6. 基于实际项目的调优经验函数与存储过程性能剖析存储函数和存储过程在性能上的表现直接影响线上稳定性。这批经验来自我为几个业务系统做过的慢SQL治理未必放之四海皆准但普适性很高。6.1 函数内嵌查询与JOIN谁更快很多人问我写函数在SELECT语句里调用会不会导致无法走索引这得分两种情形。如果函数参数是从表字段传入的例如WHERE fn_table_count(t.table_name) 100那数据库对这个函数的求值次数等于符合条件的数据行数。也就是说5000张表就要调用5000次函数每次函数内部还有一条COUNT动态SQL这样效率一定很低——光SQL解析和权限校验就够吃CPU的。反过来如果你用纯SQL做JOIN聚合一次全表扫描加一次GROUP BY就出结果开销完全不同。所以我的建议是能用JOIN和聚合解决的问题永远不要靠循环调用函数来解决。函数适合做“单点计算”不适合做“批量计算”。批量计算就应该交给SQL引擎因为SQL是声明式的优化器知道如何并行、如何走索引而函数则把执行计划切成了一片片黑盒。6.2 函数返回大结果集怎么办存储函数返回单值但如果用openGauss的RETURNS TABLE或者Oracle的PIPELINED管道函数返回结果集性能问题就又不一样了。Oracle的管道函数比普通函数强在可以“边产出边消费”适合做数据转换流水线但管道函数在并行查询和排序场景中可能导致临时表空间暴涨。openGauss支持返回SETOF record或TABLE但返回结果集大小受work_mem影响如果结果集太大溢出到临时文件时性能会陡降。我在一次大批量报表生成时用过这种函数为了处理几十万行结果不得不把work_mem调大到256MB甚至512MB才算压住磁盘交换。这类函数确实灵活但在大数据量生产环境中要谨慎评估它对内存的影响。6.3 确定性声明对优化器的重要意义在MySQL里如果你把纯计算函数声明为DETERMINISTIC优化器在某些情况下可以优化掉对相同参数的重复调用同样在Oracle里如果函数被标记为DETERMINISTIC可以用于基于函数的索引比如对字符串做大小写不敏感的索引CREATE INDEX idx_name_upper ON customer (UPPER(name));但如果你把带副作用的函数标记成DETERMINISTIC就会制造巨大隐患优化器一旦认为函数结果稳定就可能把两次调用的求值结果缓存起来最终拿到过期数据。所以DETERMINISTIC这个声明是个双刃剑它帮优化器做条件判断也逼你把函数真正做到“确定”。6.4 动态SQL函数并发执行时的锁竞争之前提到的函数里内嵌动态SQL如果在并发场景下频繁执行内部会反复PREPARE和EXECUTE这比直接调用静态SQL要昂贵。更重要的是如果函数内部更新了一个全局的计数表比如编码生成函数所有并发请求都会串行等待这一行的行锁。我压测过单机并发200个请求同时调用next_serial_no的时候吞吐量从每秒几百直接掉到几十QPS指数级下滑。解决策略有两个方向一是减少锁粒度比如把“单条记录”改成“一批预取序列号”每次拼接多个号出来二是尽量把生成逻辑放到应用层数据库只做兜底。具体选哪个取决于你们的并发量级。我处理过的一个订单编号场景日订单几十万最终是靠应用层生成编号加数据库唯一索引兜底的方式扛下来的数据库函数只作为备用方案存在。7. 最后再分享一点个人使用心得存储函数这个主题我越用越觉得它本质是“把SQL思维成果沉淀为可复用资产”的工具。你每一次把一份长达十几行的CASE WHEN封装成一个函数其实是把业务口径固化成了数据库对象而口径这个东西在跨系统协作中恰恰是最容易失控的地方。函数不只是一个编程技巧它更是团队数据规范的一部分。我个人在实际项目中的体会是不要把存储函数当作“数据库里的程序”要当作“SQL表达式的一个扩展装置”。凡是你会在SQL里反复书写的计算逻辑就值得用函数包装凡是带状态的批处理流程就交给存储过程凡是能在应用层用简单表达式解决的问题不要盲目下放到数据库层。分清这几种边界你的数据库代码质量会明显上一个台阶。另外想提醒一个很多人容易忽略的心态问题别为了炫技而大量使用函数。数据库函数最大的维护成本是“隐藏逻辑”——如果业务方不知道某个字段已经经过函数加工他们很可能在报表里再用一套自己的口径再做一次转换导致最终数字对不上。所以每创建一个函数都要同时维护一份文档说明它的输入、输出、用途和版本变更。只有把数据库对象当资产来管理才能让存储函数成为团队效率的放大器而不是下一场故障的导火索。这一篇先把函数的基础和进阶用法讲到这里。下一篇我会继续沿着存储过程系列往下走把触发器、物化视图和批量任务调度串起来做一套完整的数据库自动化运维方案。如果你在实际用函数时遇到什么奇葩报错或者对参数写法和权限坑有疑问欢迎在评论区聊聊我看到了都会逐个回复。