存储函数与存储过程：三大数据库语法对比与实战陷阱

先说一个我常在代码评审里遇到的场景：有人花了几个星期把存储过程（Stored Procedure）吃得很透，结果第一次上手写存储函数（Stored Function）就被报错教育了一下午。要么是 Oracle 的 ORA-06553，要么是 MySQL 那句经典的 “You have an error in your SQL syntax; check the manual...”。原因基本都一样——把存储过程的写法直接套在了函数上。

存储函数和存储过程这对“兄弟”表面上长得像，实际上从设计定位到调用方式都有清晰的分界。这篇文章就顺着存储过程的进阶路线，把存储函数一次聊透：它到底解决什么问题、Oracle/MySQL/openGauss 三种主流库的写法有什么不同、一个“统计当前库各表数据总量”的真实案例怎么做，以及我这些年踩过的坑。适合已经会写存储过程、想在函数上快速上手的同学，也适合做数据库开发的老手回炉一遍基础认知。

1. 存储函数到底是什么：先别急着写代码

1.1 存储过程与存储函数的本质区别

很多教程喜欢把存储函数定义为“有返回值的存储过程”。这个说法不能算错，但特别容易让人做出错误的设计。真正的本质区别在于调用方式：存储过程是一个独立的调用单元，它通过 CALL（MySQL）或 EXEC（Oracle）执行，你关注的是“这件事做完了没有”；存储函数则是一个可嵌套的表达式单元，它必须返回一个值，而且这个值可以直接当成普通列或常量，放进 SELECT、WHERE、ORDER BY 里。

举个例子。你有一张订单表，需要计算订单金额乘以税率再减去折扣后的最终应付金额。这个逻辑如果写成存储过程就很别扭——你得定义一个 OUT 参数，先 CALL，再把 OUT 参数取出来。换成存储函数，逻辑就顺了：SELECT calc_amount(amount, tax_rate, discount) FROM orders;，calc_amount就像一个你自己封装的普通列，既能在结果集里出现，也能参与后续运算。

用生活化的类比：存储过程像你去饭店点了一桌菜，服务员端上来之后你关心的是“这顿饭做完了”；存储函数像你按了一下计算器的等于键，屏幕上立刻给出一个可复用的数值。动作和值，是区分这对兄弟的第一个分水岭。

1.2 返回值：唯一且必须

存储函数在声明阶段就必须写清楚返回类型。Oracle 是RETURN NUMBER、RETURN VARCHAR2，MySQL 是RETURNS INT、RETURNS VARCHAR(100)，openGauss 也是RETURNS。函数体里必须有一条RETURN语句，这叫“唯一且必须”。

很多第一次写函数的人会有一个误解，以为函数里的RETURN和过程里的RETURN一样，只是提前结束程序用的。过程里RETURN后面可以不带任何值，纯退出；函数里的RETURN后面必须跟一个表达式，它既是退出点，也是交付结果的唯一通道。如果函数体里有多个分支，每个IF/ELSE分支里都要有对应的RETURN，否则编译时就会报缺失返回值的错误。

还有一个容易忽略的点：函数只能返回一个值。你没法像存储过程那样通过多个 OUT 参数带回多个结果。如果你的需求是“把每张表的行数都输出成一个列表”，那就不该用普通函数，应该用存储过程，或者用 Oracle 的管道函数（PIPELINED）、openGauss 的RETURNS SETOF这类“返回集合”的函数设计。知道什么时候不选函数，同样是进阶的一部分。

1.3 确定性（DETERMINISTIC）为何重要

我见过不少开发者在 MySQL 里建函数不写DETERMINISTIC，在 Oracle 里不声明DETERMINISTIC，在 openGauss 里也无所谓IMMUTABLE和VOLATILE。短时间看不出问题，直到某天你发现报表跑了通宵都没出来，定位一看，全卡在一个函数调用上。

“确定性”描述的是：给定相同的入参，是否必然得到相同的结果。比如计算税额，税率和金额固定，税额就固定，这是确定性的；而NOW()、随机数、读一张动态配置表，都是非确定性的。

MySQL 建函数时写不写DETERMINISTIC，直接影响主从复制。如果不写，你在开启了log_bin_trust_function_creators之外的环境里创建函数，会撞上ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration ...，直接拒绝创建。Oracle 的DETERMINISTIC声明则允许优化器把函数调用当作常量折叠，甚至可以利用它创建基于函数的索引；不声明的后果不会立刻报错，但优化器会把它当作易变函数，每次执行都重新调用，性能白浪费。

openGauss 的分类更细：IMMUTABLE（完全不变）、STABLE（同一查询内不变）、VOLATILE（每次调用都可能变）。默认是VOLATILE。如果你建的是一个纯计算函数，却忘了标注IMMUTABLE，查询优化器会拒绝把它下推或预计算，同样一段 SQL 可能慢一个数量级。所以建函数的正确习惯是：写完逻辑先想一下，这个函数是幂等的纯计算，还是会读到数据库状态？能标成纯计算的尽量标出来，让优化器帮你干活。

2. 一个函数的三副面孔：Oracle/MySQL/openGauss 语法对照

2.1 Oracle：SQL 与 PL/SQL 的无缝衔接

Oracle 的存储函数是 PL/SQL 体系里的一部分，语法结构相对固定：

CREATE OR REPLACE FUNCTION fn_calc_tax( p_amount IN NUMBER, p_rate IN NUMBER ) RETURN NUMBER DETERMINISTIC IS v_tax NUMBER; BEGIN v_tax := p_amount * p_rate; RETURN v_tax; END; /

创建完成后，直接嵌入 SQL 使用：

SELECT fn_calc_tax(100, 0.06) FROM DUAL;

这段代码里有几个值得注意的细节。一是参数模式IN，Oracle 的函数参数虽然可以写IN、OUT、IN OUT，但函数参数最好只用IN。如果一个函数试图把OUT参数带出去，你就该重新审视设计方向了。二是RETURN NUMBER里的NUMBER可以不写精度，PL/SQL 对数值的隐式转换相当宽容，但宽容也意味着类型错误往往在运行时才暴露，后面踩坑部分再细说。

Oracle 函数的调用场景很灵活，不仅可以出现在 SELECT 列表，还能出现在 WHERE 条件、GROUP BY、ORDER BY 里。只要函数是确定性的，优化器甚至会把它作为常量处理，只计算一次。

2.2 MySQL：与存储过程师出同门

MySQL 的存储函数在语法上和存储过程高度相似，同样有 BEGIN...END 块，同样支持变量、游标、流程控制。但几个关键差异不要搞混：

DELIMITER // CREATE FUNCTION fn_calc_tax( p_amount DECIMAL(10, 2), p_rate DECIMAL(5, 4) ) RETURNS DECIMAL(12, 2) DETERMINISTIC BEGIN RETURN p_amount * p_rate; END // DELIMITER ;

调用方式：

SELECT fn_calc_tax(100.00, 0.06);

MySQL 函数和存储过程最大的边界是：过程用CALL调用，函数用SELECT调用；过程参数支持IN、OUT、INOUT，函数参数只有IN，没有输出参数，你的所有输出只能通过RETURN表达。

还有一个魔鬼细节是DELIMITER。客户端默认把分号当作语句结束符，如果不在创建函数前把分隔符改成//或$$，函数体里的分号会被识别成语句结束，导致一连串语法错误。我曾经看到一个同事把函数直接粘到 Navicat 查询窗口执行，报错报得莫名其妙，其实只是DELIMITER没处理。这个坑几乎人人都会踩一次。

2.3 openGauss：兼容 Oracle 语法下的现代变体

openGauss 建函数的风格兼有 PostgreSQL 和 Oracle 的影子。我常用的是LANGUAGE plpgsql加$$引号的写法：

CREATE OR REPLACE FUNCTION fn_calc_tax( p_amount NUMERIC, p_rate NUMERIC ) RETURNS NUMERIC LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN RETURN p_amount * p_rate; END; $$;

调用方式：

SELECT fn_calc_tax(100.00, 0.06);

openGauss 的RETURNS后面可以接基础类型，也可以接复合类型，需要返回结果集时用RETURNS SETOF。另外它的函数可以声明成SHIPPABLE或NOT SHIPPABLE，这会影响分布式场景下函数是否下发到各个节点执行。关于这一点，普通用户不需要深挖，但你至少要认识到：标注IMMUTABLE之后，优化器才可能把函数调用优化成常量，这是纯计算函数在 openGauss 里不想被“慢待”的关键。

三者的语法要点，我做了一个对照表：

看完这张表，你会发现存储函数在三种数据库里的核心模型是一致的：有输入参数、必须声明返回类型、必须用RETURN返回值、用SELECT调用。差异主要在语法包装和确定性声明的命名上。

3. 实战任务：统计当前库各表数据总量的存储函数

3.1 需求拆解与方案选型

最近有个监控类的需求正好撞上这个题目：想快速知道当前数据库里所有表的数据量总和。应用层去做当然可以，连上数据库，遍历元数据，一张表一张表地查，但代码又长又慢。写成存储函数则很优雅——一条 SQL 直接出结果：

SELECT fn_table_count_total();

那为什么用函数而不是过程？因为最终结果是单个数值，你希望把它嵌入到其他 SQL 里继续使用，或者直接在查询的一开始拿到这个值。如果需要把每张表的行数分摊列出来，那就不是普通函数该管的事，应该用存储过程或者返回集合的函数设计。这里我展示的函数实现是“总行数”，并且在最后给一个衍生方案。

3.2 Oracle 实现：动态 SQL 扫表

Oracle 版本的核心思路是：通过数据字典ALL_TABLES拿到当前用户下的所有表名，再对每张表执行一次COUNT(*)，累加结果。表名是变量，静态 SQL 拼不出来，必须用EXECUTE IMMEDIATE做动态 SQL。

CREATE OR REPLACE FUNCTION fn_table_count_total( p_owner IN VARCHAR2 DEFAULT USER ) RETURN NUMBER IS v_total NUMBER := 0; v_rows NUMBER; BEGIN FOR rec IN ( SELECT table_name FROM all_tables WHERE owner = UPPER(p_owner) AND table_name NOT LIKE 'BIN$%' ) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM "' || rec.table_name || '"' INTO v_rows; v_total := v_total + v_rows; END LOOP; RETURN v_total; END; /

有两个细节值得强调。

第一，过滤条件里的BIN$%是 Oracle 回收站表名的前缀。如果你刚 DROP 过一张表，又没做PURGE，这些表依然躺在ALL_TABLES里，不去过滤会把已经删除的数据也统计进去。

第二，这里用的是ALL_TABLES加owner条件，而不是直接的USER_TABLES。虽然USER_TABLES更简单，但ALL_TABLES让函数支持传一个其他用户名进去统计对方 schema，适用面更宽。默认参数DEFAULT USER会让调用时保持简单。

3.3 MySQL 实现：游标与预处理语句的配合

MySQL 没有 Oracle 那种数据字典视图，用的是information_schema.TABLES。要遍历所有表，就必须用游标。游标循环里面还要拼接动态 SQL，所以PREPARE/EXECUTE也少不了：

DELIMITER $$ CREATE FUNCTION fn_table_count_total() RETURNS BIGINT DETERMINISTIC READS SQL DATA BEGIN DECLARE v_done INT DEFAULT 0; DECLARE v_table_name VARCHAR(64); DECLARE v_cnt BIGINT DEFAULT 0; DECLARE v_total BIGINT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_table_name; IF v_done = 1 THEN LEAVE read_loop; END IF; SET @dynamic_sql := CONCAT('SELECT COUNT(*) FROM `', v_table_name, '`'); PREPARE stmt FROM @dynamic_sql; EXECUTE stmt INTO v_cnt; DEALLOCATE PREPARE stmt; SET v_total := v_total + v_cnt; END LOOP; CLOSE cur; RETURN v_total; END$$ DELIMITER ;

这里有几个新手容易卡住的点。

游标循环必须有CONTINUE HANDLER FOR NOT FOUND，否则遍历完最后一行后继续FETCH，会直接报No data - zero rows fetched, selected, or processed错误。

TABLE_TYPE = 'BASE TABLE'的作用是过滤掉视图（VIEW）。视图本身没有物理数据行，用COUNT(*)去数视图行数，既慢又语义不对。

拼接表名时为什么要加反引号？因为表名可能和关键字撞车，比如有张表叫order或group，不加反引号会直接语法错误。这个习惯在动态 SQL 里尤其重要。

3.4 openGauss 实现：plpgsql 风格更顺手

openGauss 的实现思路和 MySQL 类似，但元数据来源换成了pg_tables，循环方式用的是 FOR 循环，比手动管理游标更省事：

CREATE OR REPLACE FUNCTION fn_table_count_total() RETURNS BIGINT LANGUAGE plpgsql VOLATILE AS $$ DECLARE v_table_name TEXT; v_total BIGINT := 0; v_cnt BIGINT := 0; BEGIN FOR v_table_name IN SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND tablename NOT LIKE 'sys_%' LOOP EXECUTE 'SELECT count(*) FROM "' || v_table_name || '"' INTO v_cnt; v_total := v_total + v_cnt; END LOOP; RETURN v_total; END; $$;

current_schema()在连接没指定 schema 的时候可能返回空值或者默认值。为了保证统计准确，你可以把WHERE schemaname = current_schema()换成WHERE schemaname = 'public'，或者直接统计所有非系统 schema。openGauss 的系统表前缀通常是sys_或pg_，过滤条件要做两手准备，这一点和生产环境强相关。

如果你需要统计的是某个特定 schema，也可以加一个入参，让调用方自己指定，函数逻辑会更灵活。

3.5 验证与性能观察

函数写完，直接执行：

SELECT fn_table_count_total() AS total_rows;

我在一个 128 张表、总行数约 3800 万的测试库上分别跑了 Oracle 和 MySQL 的版本，耗时都在几秒以内。因为 128 次COUNT(*)在有索引的情况下并不慢，真正的瓶颈只来自一种情况：表没有主键索引，COUNT(*)必须做全表扫描。

遇到千万级大表又没主键的时候，你真正该考虑的就不是“函数怎么写”，而是“这个统计是否需要精确值”。Oracle 的ALL_TABLES.NUM_ROWS、MySQL 8.0 的information_schema.TABLES.TABLE_ROWS、openGauss 的pg_class.reltuples都是统计信息，毫秒级返回，绝大多数监控场景完全够用。精确和快，总要选一个，这是做数据统计绕不开的常识。

4. 存储函数实战中的四个大坑

4.1 返回类型不匹配：隐式转换是天使也是魔鬼

几乎每个写函数的人都会被ORA-06502: PL/SQL: numeric or value error或者 MySQL 的Data truncation教育过。最容易被忽视的是边界值问题。

RETURNS INT的函数能返回的最大值是 21 亿多，一旦数据量超过这个数，结果直接溢出，甚至变成负数。我曾经接手一个报表系统，上线三个月后业务量涨到 20 多亿，某天的报表金额突然出现负数，排查半天才发现函数返回类型写的是INT，改成BIGINT才恢复。金额类字段用DECIMAL(20, 2)，数量类用BIGINT，字符串注意长度上限，这是在声明返回类型时就要做的保守估计。

另外，Oracle 的NUMBER类型极其宽容，隐式转换会在你别无察觉时发生。可宽容也意味着问题暴露得晚。同一个函数在开发库跑几个月都没事，到了生产库因为某个字段值变大突然崩掉，这种故事并不新鲜。

4.2 函数中的 DML 副作用：SELECT 里的地雷

存储函数和普通编程语言的函数最大的不同，是它在数据库进程内执行。不少人把UPDATE、DELETE直接写进函数，觉得调用后顺便更新一下数据挺好。这个想法在 MySQL 里尤其危险：函数在 SELECT 中执行时处于查询上下文，此时修改其他表的数据，轻则破坏读一致性，重则直接报错。

Oracle 里对应的报错是ORA-14551: cannot perform a DML operation inside a query。即便你用自治事务绕过限制，这种隐式副作用也会变成排障噩梦。你不清楚哪次查询悄悄改过数据，不知道数据什么时候变的，更不敢随便重构那段 SQL。原则只有一个：函数在 SELECT 场景下只读数据，不做修改。真要变更数据，写存储过程，让动作发生在过程里，而不是函数里。

4.3 权限与安全：函数调用链上的两道裂缝

MySQL 的函数有DEFINER属性，默认是当前创建用户。如果你用一个只读账号去 SELECT 一个 DEFINER 为高权限用户的函数，实际执行逻辑时用的是高权限，这就容易成为攻击者可利用的点。如果函数内部又拼了动态 SQL，而调用方能够影响拼接内容，那就是标准的 SQL 注入入口。

我做统计类函数时，过滤条件至少要包含回收站表名、系统表、历史分区表，并且对入参加UPPER()转大写，配合白名单匹配。Oracle 的ALL_TABLES查询里，owner条件如果直接拼入参，用户传一个'SCOTT' OR '1'='1'这类值，结果会完全失控。函数的动态 SQL 和普通应用的 SQL 注入没有本质区别，只是很多人写存储函数时下意识放松警惕。

4.4 性能：一个函数的行级调用陷阱

最后一个坑，也是性能层面最常见的：把函数用在查询的每一行上。

比如SELECT order_id, fn_calc_amount(order_amount, discount) FROM orders;，当orders有 50 万行，这个函数就被调用 50 万次。哪怕每次只花 0.01 毫秒，整体就是 5 秒，如果你在函数内部又做了一次查询，哪怕是很小的子查询，50 万次 SQL 同时砸向数据库，再好的机器也会被打爆。

这类问题的修复思路一般有三条。第一，把函数声明为DETERMINISTIC/IMMUTABLE，让优化器有机会缓存或折叠，减少重复调用；第二，改写 SQL，把函数逻辑拆分到 JOIN 或生成列里，避免行级调用；第三，干脆把这段查询封装成视图，让优化器从整体角度做计划。

我个人的习惯是：任何要进 SELECT 列表的函数，写完第一版先看执行计划。一旦看到类似 Function Scan 或者大量循环调用，立刻停下来想替代方案。函数用对了是工具，用错了就是拖垮数据库的定时炸弹。

最后分享一个我自己的实操细节。凡是统计类的存储函数，建完之后我从不会直接拿到生产环境，而是先做几组边界测试：建一个空库跑一遍、一个只有视图没有物理表的库跑一遍、一个表名带空格或关键字的库跑一遍。只有这些极端场景全部通过，我才会放心交给业务方。统计函数看起来简单，真正咬人的地方往往就在你最容易忽略的边界条件上。这篇没打算把每个数据库的语法细枝末节做成速查手册，而是希望给已经能熟练使用存储过程、想往存储函数方向进阶的同学一个还算完整的思考框架。至于排序、分页、隔离级别这些更细的题目，等下次有合适案例，我再接着写。