做存储过程开发到一定阶段你会发现真正拉开水平差距的不是会不会写 SELECT而是会不会用变量。存储过程里的局部变量靠DECLARE声明看起来就是一行声明语句但它牵涉到作用域、声明顺序、类型精度、命名冲突、异常处理一整套东西。我自己在给几套订单系统写批处理逻辑的时候踩过的坑基本都集中在这几行 DECLARE 上——有人把声明写在 BEGIN 中间导致整段过程建不上有人变量名和列名撞车导致 WHERE 条件永远为真还有人拿 VARCHAR(10) 去接一个身份证号跑了一个月才发现数据被悄悄截断。这篇是存储过程的使用系列的第三篇专门聊 DECLARE 定义局部变量。MySQL 5.7/8.0 为主线同时会把 SQL Server 和 Oracle 的写法横向拉出来对照。不管你是刚写完第一个存储过程的新手还是需要维护一堆历史过程的老手这里面的语法规则、类型推算、报错排查和经验技巧都能直接拿去用。1. 局部变量在存储过程中的角色定位1.1 没有变量存储过程就只是一段写死的脚本把存储过程想象成一条自动化产线SQL 语句是机械臂那局部变量就是传送带上的料框。没有料框机械臂只能抓固定位置的那一个零件抓完就没了。有了料框中间结果才能被暂存、被传递、被反复加工。举个最直白的例子你要统计某个用户在某段时间的订单总额再按总额分档给折扣。总额这个数字从 SUM 算出来到拿去做 IF 判断中间必须有个地方存着——这个地方就是局部变量。你要是硬不用变量就得把 SUM 查询重复写三遍分别塞进三个 IF 条件里代码量翻三倍不说三次查询之间数据还可能被并发写入改掉结果直接对不上。局部变量在存储过程里承担四类职责承接查询结果的中间容器、循环和游标的计数与状态开关、拼装动态 SQL 的字符串缓存、以及给 OUT 参数做最终赋值的出口。理解这四类用途你在设计过程的时候就知道该声明哪几个变量而不是写到哪算到哪。1.2 DECLARE 的作用域是块级不是过程级这是最容易误解的一点。很多从 C 语言或者 TypeScript 转过来的人会下意识觉得变量声明在函数开头作用域就是整个函数但在 MySQL 的存储过程里DECLARE 的作用域是它所在的那个BEGIN ... END块块一结束变量就消失。也就是说如果一个过程里有嵌套块DELIMITER $$ CREATE PROCEDURE p_scope_demo() BEGIN DECLARE v_outer INT DEFAULT 10; BEGIN DECLARE v_inner INT DEFAULT 20; SELECT v_outer v_inner AS inner_sum; -- 合法内层能看见外层 END; SELECT v_inner; -- 报错Unknown column v_inner END$$ DELIMITER ;内层块可以读外层块的变量反过来不行。如果内层块又声明了一个同名变量那内层用的是自己那份外层不受影响——这就是典型的变量遮蔽。这条规则在调试的时候特别有用你在一段长过程里发现某个变量值莫名其妙不对先看它是不是在某个嵌套块里被重新声明了一遍。这里有个实用心得嵌套块不要超过两层。三层以上的嵌套块变量作用域就开始变成心智负担了维护的人得一行行往上翻才能确认这个 v_x 到底是哪一层声明的。真到了这个复杂度拆成两个独立的过程用临时表传数据比硬嵌要清爽得多。1.3 设计阶段先数清楚需要几个变量我在动手写过程之前习惯在草稿纸上列一张变量清单格式很土但很管用变量名类型初值用途v_totalDECIMAL(12,2)0.00承接订单总额v_cntINT0承接订单笔数v_avgDECIMAL(12,2)0.00计算客单价v_discountDECIMAL(4,2)1.00折扣系数这张表有三个好处一是类型和初值在写代码前就定死了避免后面为了迁就一个 IF 分支去改类型二是初值写清楚就不会出现变量没赋初值结果参与运算得到 NULL这种低级问题三是变量超过八个的时候你会本能地警觉——一个过程需要十几个局部变量通常意味着它干了太多事该拆了。1.4 三种变量的区分要先搞明白初学的人经常把三类变量混在一起这里一次性理清局部变量DECLARE v_x INT;没有前缀作用域是声明它的BEGIN ... END块必须用 DECLARE 声明才能用。用户变量会话变量SET x 1;带前缀不需要声明作用范围是整个数据库连接连接断开就没了。因为是连接级共享两个过程之间可以用它传值但也正因如此特别容易被别的地方污染。系统变量global.max_connections、session.autocommit双前缀由数据库本身维护读可以改要谨慎。用一句话记住区别局部变量是过程内部的事用户变量是整个连接的事系统变量是整个数据库实例的事。作用域从小到大风险从低到高。绝大多数业务逻辑用局部变量就够了只有真的需要跨过程传临时值或者拼动态 SQL 的时候才去动用户变量。2. DECLARE 语法细节与类型选择2.1 MySQL 中 DECLARE 的三条硬规矩MySQL 的 DECLARE 语法本身很短DECLARE 变量名 [, 变量名 ...] 数据类型 [DEFAULT 默认值];但它的位置规矩是硬的写错一个字符过程就建不上第一条DECLARE 必须写在BEGIN ... END块的最前面。只要前面出现任何一条可执行语句哪怕是SET后面再写 DECLARE 直接报语法错误。第二条声明之间有固定顺序先是变量和条件CONDITION再是游标CURSOR最后是处理器HANDLER。这个顺序不能乱。原因很好理解——处理器要捕获游标触发的 NOT FOUND游标要引用前面声明的变量它们之间是依赖关系所以只能按依赖方向排。第三条DECLARE 只能出现在BEGIN ... END里不能用在过程体外。匿名块例外但 MySQL 的匿名块也得靠BEGIN ... END包起来才有地方声明。一个位置完全正确的骨架长这样DELIMITER $$ CREATE PROCEDURE p_skeleton() BEGIN -- 1. 变量声明 DECLARE v_done INT DEFAULT 0; DECLARE v_id INT UNSIGNED; DECLARE v_name VARCHAR(64); -- 2. 游标声明 DECLARE cur_user CURSOR FOR SELECT id, name FROM t_user WHERE status 1; -- 3. 处理器声明 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; -- 4. 从这里开始才是可执行语句 OPEN cur_user; read_loop: LOOP FETCH cur_user INTO v_id, v_name; IF v_done 1 THEN LEAVE read_loop; END IF; -- 业务处理 END LOOP; CLOSE cur_user; END$$ DELIMITER ;注意声明顺序错了MySQL 报的是 1064 语法错误错误信息不会告诉你顺序不对只会指在 DECLARE 那一行。遇到 1064 且确认括号分隔符都没问题第一反应就去检查声明顺序。2.2 数据类型怎么选几个容易翻车的地方变量声明和建表选字段类型逻辑是一致的但变量的容错要求更高——表字段写错了至少还有约束报错变量写窄了是静默截断最难查。金额一律用 DECIMAL不用 FLOAT 和 DOUBLE。FLOAT/DOUBLE 是二进制浮点0.1 0.2 不等于 0.3 这种问题在金额场景是致命的。DECIMAL(12,2)的含义是总共 12 位数字其中 2 位小数整数部分最多 10 位能表示的最大值是 9999999999.99也就是九十九亿多对绝大多数订单汇总场景够用。如果你的业务涉及跨境结算或者分账把精度提到DECIMAL(18,4)别省。字符串长度要按最坏情况算。VARCHAR(64)在 utf8mb4 字符集下最多占 64 × 4 256 字节。这里的关键是MySQL 的 VARCHAR 长度单位是字符不是字节所以VARCHAR(64)存 64 个汉字没问题。但如果你拿它去接一个可能超过 64 字符的拼接结果超出的部分会被直接砍掉而且在很多 MySQL 配置下只给一个 Warning不报错。这是静默截断最典型的来源。计数用 INT UNSIGNED。订单笔数、循环下标这类非负整数加 UNSIGNED 能多一倍上限更重要的是语义清晰——看到 UNSIGNED 就知道这个变量不可能为负。日期时间统一用 DATETIME 而不是 TIMESTAMP。TIMESTAMP 有 2038 年的上限并且会跟随时区转换跨时区系统里容易出现存进去和取出来差八小时的怪事。DATETIME 是所见即所得的字面值变量里用最稳妥。实操心得所有承接 SELECT 结果的局部变量类型要么和源字段完全一致要么比源字段更宽。我一般会直接去表结构里抄字段定义抄的时候顺手把 VARCHAR 加宽一档。这个习惯让我少查了很多 Data truncated 的工单。2.3 DEFAULT 与 NULL没赋初值的变量到底是什么一个只写了DECLARE v_x INT;的变量它的初始值是NULL不是 0也不是空字符串。这一点和很多编程语言的直觉相反。NULL 参与运算的传染性非常强DECLARE v_a INT; DECLARE v_b INT DEFAULT 1; SELECT v_a v_b; -- 结果是 NULL只要有一项是 NULL加法结果就是 NULL然后这个 NULL 继续往 IF 条件里传IF NULL 0的结果既不是 TRUE 也不是 FALSE而是 UNKNOWN走的是 ELSE 分支。等你发现分支走错了回头查变量本身看着人畜无害问题就出在它没赋初值。所以我的建议很直接除了明确要用 NULL 表达未取到值的变量其余一律写 DEFAULT。数值给 0字符串给空串计数给 0布尔开关给 0。写的时候多敲几个字符排查的时候省几个小时。关于 DEFAULT 的值还有一个版本差异要注意早年的 MySQL 要求 DEFAULT 后面必须跟常量写函数调用或者子查询会报错。我用过的 5.7 和 8.0 在部分场景下放宽了限制但为了跨版本兼容还是老老实实写常量需要动态计算初值就在块的第一条语句里用 SET 赋。2.4 命名规范v_ 前缀和重名规避局部变量的命名业界没有强制标准但有几个约定俗成值得遵守。最主流的是前缀法局部变量用v_入参用p_出参用p_out_或者直接o_。这样你在一大段代码里扫一眼就知道这个标识符是什么来路。我见过一些规范用l_local和i_input效果一样关键是团队内统一。前缀真正解决的是变量名和列名冲突的问题这个坑在下一节会详细讲先给个结论变量名永远不要和任何一张相关表的列名同名。加了v_前缀之后v_user_id和列user_id天然不会撞车这是白送的一层保险。另一条经验是变量数量控制。我给自己定了一条线单个BEGIN ... END块的局部变量不超过十个。超过这个数说明这段逻辑承担的职责太多我会把它按取数—计算—落库三段拆开前一段的产物用临时表落地后一段再读。这样做还有个额外好处每段都能单独跑调试的时候不用整条链路一起跑。3. 实操一个带局部变量的完整存储过程3.1 场景设定与建表假设要做一个用户订单汇总给定用户 ID 和时间区间算出这个用户在区间内已支付订单的总额和笔数按总额给出阶梯折扣把结果写回 OUT 参数同时往日志表里记一条审计。先建两张表DROP TABLE IF EXISTS t_order; CREATE TABLE t_order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_status_time (user_id, status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; DROP TABLE IF EXISTS t_sp_log; CREATE TABLE t_sp_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, proc_name VARCHAR(64) NOT NULL, log_text VARCHAR(512) NOT NULL, log_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;索引idx_user_status_time的字段顺序是 (user_id, status, created_at)这个顺序有讲究。查询条件是user_id ? AND status 1 AND created_at BETWEEN等值条件在前、范围条件在后索引才能被完整用上。如果把 created_at 放中间后面的 status 就用不上索引了这是很常见的索引设计失误。3.2 过程完整代码DELIMITER $$ DROP PROCEDURE IF EXISTS sp_user_order_summary$$ CREATE PROCEDURE sp_user_order_summary( IN p_user_id INT UNSIGNED, IN p_start DATETIME, IN p_end DATETIME, OUT p_total DECIMAL(12,2), OUT p_cnt INT, OUT p_discount DECIMAL(4,2) ) BEGIN DECLARE v_total DECIMAL(12,2) DEFAULT 0.00; DECLARE v_cnt INT DEFAULT 0; DECLARE v_avg DECIMAL(12,2) DEFAULT 0.00; DECLARE v_discount DECIMAL(4,2) DEFAULT 1.00; DECLARE v_msg VARCHAR(512) DEFAULT ; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_total 0.00; SET p_cnt 0; SET p_discount 1.00; END; SELECT IFNULL(SUM(amount), 0), COUNT(*) INTO v_total, v_cnt FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 THEN SET v_avg v_total / v_cnt; END IF; IF v_total 10000.00 THEN SET v_discount 0.85; ELSEIF v_total 5000.00 THEN SET v_discount 0.92; ELSEIF v_total 1000.00 THEN SET v_discount 0.97; ELSE SET v_discount 1.00; END IF; SET p_total v_total; SET p_cnt v_cnt; SET p_discount v_discount; SET v_msg CONCAT(uid, p_user_id, total, v_total, cnt, v_cnt, avg, v_avg, disc, v_discount); INSERT INTO t_sp_log(proc_name, log_text) VALUES (sp_user_order_summary, v_msg); END$$ DELIMITER ;3.3 逐段解读与关键参数推算声明段。五个局部变量全部带 DEFAULT放在块的最前面。类型上做了刻意的放大v_total用DECIMAL(12,2)比源字段amount的DECIMAL(10,2)宽了两档。为什么要放大因为SUM(amount)的结果可能比单笔金额大得多。假设一个用户一年下了十万笔订单平均每笔 1000 元总和就是一亿DECIMAL(10,2)的整数部分只有 8 位上限 99999999.99一亿直接溢出。放大到 12 位上限提到九十九亿留够了余量。异常处理器。DECLARE EXIT HANDLER FOR SQLEXCEPTION捕获块内所有 SQL 异常触发后执行里面的赋值语句把三个 OUT 参数设成安全默认值然后退出整个块。这个设计保证了无论中间哪一步炸掉调用方拿到的永远是一组可解释的值而不是残留的脏数据或者 NULL。EXIT 和 CONTINUE 的区别要记牢EXIT 处理完直接离开当前块CONTINUE 处理完继续往下执行。做汇总统计这类场景出错之后继续跑没意义所以用 EXIT。取数段。SELECT ... INTO v_total, v_cnt把两个聚合值一次性塞进两个变量。这里有两处细节。第一IFNULL(SUM(amount), 0)是必须的——当 WHERE 条件没有匹配到任何行时SUM返回的是 NULL 而不是 0COUNT(*)则返回 0。如果不包 IFNULLv_total 就是 NULL后面所有比较都变成 UNKNOWN折扣会一路掉到 ELSE 分支。第二created_at p_start AND created_at p_end用的是左闭右开区间终点不含。这样做的好处是把2024-12-31 23:59:59这种边界值彻底排除掉避免最后一秒的订单算不算这个月这种扯皮。计算段。客单价用IF v_cnt 0守护避免除零。MySQL 里整数除以零在部分模式下返回 NULL开了 strict 模式会直接报 1365 错误两种情况都不好受加个判断最省心。折扣阶梯是从高到低排的先判 10000再判 5000。顺序不能反过来写否则一笔 15000 的订单会先在 5000那里被拦下给出 0.92 而不是 0.85。这种从宽到窄或者从高到低的排序原则凡是写多级 IF 都要遵守。输出段。三个SET p_xxx v_xxx把内部变量转成 OUT 参数。中间多这一层转换看着啰嗦但它把计算逻辑和接口契约隔开了。以后要给过程加功能比如再返回一个最高单笔金额只要在内部加变量输出段加一行赋值前面所有逻辑不动。3.4 调用与结果验证-- 先灌点测试数据 INSERT INTO t_order(user_id, amount, status, created_at) VALUES (1001, 300.00, 1, 2024-03-01 10:00:00), (1001, 800.50, 1, 2024-03-05 14:20:00), (1001, 60.00, 0, 2024-03-06 09:00:00), (1001, 4200.00,1, 2024-04-11 16:00:00); -- 调用 CALL sp_user_order_summary( 1001, 2024-01-01 00:00:00, 2025-01-01 00:00:00, total, cnt, disc ); SELECT total, cnt, disc;预期结果total 5300.50cnt 3disc 0.92。注意那笔 60 元的订单 status 是 0待支付不参与统计笔数正好是 3。调用的时候有个细节MySQL 的 OUT 参数必须传变量不能传字面量。CALL sp_user_order_summary(1001, ..., ..., 0, 0, 0)会直接报 1414 错误。传total这种用户变量是最常用的做法也可以在一个外层过程的局部变量里接收。验证完再查一下日志表SELECT * FROM t_sp_log ORDER BY id DESC LIMIT 1;能看到一条uid1001 total5300.50 cnt3 avg1766.83 disc0.92的记录说明内部变量算对了而且和 OUT 输出一致。3.5 同一逻辑改写为 SQL Server 与 Oracle三个数据库的声明语法差异挺大放在一起对照记最快。先看 SQL Server 版本CREATE OR ALTER PROCEDURE dbo.sp_user_order_summary p_user_id INT, p_start DATETIME, p_end DATETIME, p_total DECIMAL(12,2) OUTPUT, p_cnt INT OUTPUT, p_discount DECIMAL(4,2) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE v_total DECIMAL(12,2) 0.00; DECLARE v_cnt INT 0; DECLARE v_avg DECIMAL(12,2) 0.00; DECLARE v_discount DECIMAL(4,2) 1.00; SELECT v_total ISNULL(SUM(amount), 0), v_cnt COUNT(*) FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 SET v_avg v_total / v_cnt; IF v_total 10000.00 SET v_discount 0.85; ELSE IF v_total 5000.00 SET v_discount 0.92; ELSE IF v_total 1000.00 SET v_discount 0.97; ELSE SET v_discount 1.00; SET p_total v_total; SET p_cnt v_cnt; SET p_discount v_discount; END;再看 OracleCREATE OR REPLACE PROCEDURE sp_user_order_summary( p_user_id IN NUMBER, p_start IN DATE, p_end IN DATE, p_total OUT NUMBER, p_cnt OUT NUMBER, p_discount OUT NUMBER ) IS v_total NUMBER(12,2) : 0; v_cnt NUMBER : 0; v_avg NUMBER(12,2) : 0; v_discount NUMBER(4,2) : 1; BEGIN SELECT NVL(SUM(amount), 0), COUNT(*) INTO v_total, v_cnt FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 THEN v_avg : v_total / v_cnt; END IF; IF v_total 10000 THEN v_discount : 0.85; ELSIF v_total 5000 THEN v_discount : 0.92; ELSIF v_total 1000 THEN v_discount : 0.97; ELSE v_discount : 1.00; END IF; p_total : v_total; p_cnt : v_cnt; p_discount : v_discount; END; /把三家的差异整理成一张表写跨库代码的时候直接对着查对比项MySQLSQL ServerOracle声明关键字DECLAREDECLARE声明段直接写匿名块才用 DECLARE变量前缀无必须带 无默认值写法DEFAULT 值 值: 值 或 DEFAULT 值赋值方式SET v_x 值SET x 值v_x : 值注释符号-- 或 #----作用域粒度BEGIN...END 块级批或过程级不随块细分块级嵌套块可重声明类型锚定字段不支持不支持支持 %TYPE / %ROWTYPE语句分隔需要 DELIMITER不需要需要 / 结束 PL/SQL 块ELSE IF 写法ELSEIFELSE IFELSIFOracle 的%TYPE特别值得单独说一句这是它比另外两家强的地方。写成v_total t_order.amount%TYPE;变量的类型会自动跟随表字段走。以后表字段从DECIMAL(10,2)改成DECIMAL(12,2)过程不用重新编译。MySQL 和 SQL Server 没这个能力只能靠人工维护类型一致性——所以在 MySQL 里我更倾向于把类型定义写在注释里注明参照的是哪个字段。4. 常见报错与排查技巧实录4.1 高频报错速查表下面这些错误码我在实际项目里基本都遇到过整理出来给个对照错误码 / 现象根因处理方式1064 语法错误指向 DECLARE 行DECLARE 没放在块首或声明顺序错全部 DECLARE 上移到块开头按变量→游标→处理器排序1305 PROCEDURE does not existDELIMITER 没设或没还原只执行了前半段检查分隔符执行后记得DELIMITER ;1329 No data - zero rows fetchedSELECT ... INTO没查到任何行加CONTINUE HANDLER FOR NOT FOUND或改用聚合加 IFNULL1172 Result consisted of more than one rowSELECT ... INTO返回多行WHERE 条件收窄或加LIMIT 11054 Unknown column v_x变量在作用域外引用或忘了声明检查嵌套块边界确认 DECLARE 存在1292 Truncated incorrect DOUBLE value类型隐式转换失败显式CAST或修正变量类型1265 Data truncated for column变量长度不够写入被截断放大变量类型字符串至少加宽一档1414 OUT 参数必须传变量CALL 时给 OUT 位置传了字面量改用var接收1365 Division by 0除数变量为 0除之前用 IF 判断除数大于 01048 Column cannot be null变量取到 NULL 写进了非空列变量声明给 DEFAULT 兜底4.2 声明位置错了到底会发生什么这个值得展开说因为它是最常见又最费时间的一类问题。看下面这段CREATE PROCEDURE p_bad() BEGIN SET tmp 1; DECLARE v_x INT DEFAULT 0; -- 报错 SELECT v_x; END;执行的时候你会收到 1064错误信息指向DECLARE v_x INT DEFAULT 0那一行。但你可能盯着这行看了十分钟语法明明没问题。原因在它上面那行SET tmp 1;——MySQL 解析到 DECLARE 的时候已经认为这个块的声明区结束了。处理原则很简单块内所有的 DECLARE 全部上移一条可执行语句都不能夹在中间。我写过程的固定动作就是先敲 BEGIN然后一口气把所有 DECLARE 写完再去写业务逻辑。这样结构上就不可能出错。排查的时候如果 1064 怎么都找不到原因还有一个高频嫌疑人是分隔符。客户端默认用分号作为语句结束符过程体内部也有分号不加 DELIMITER 的话客户端会在第一个内部分号处就把语句截断发出去创建出来的过程自然是不完整的后续调用报 1305。所以标准流程永远是三件套DELIMITER $$开头END$$收尾DELIMITER ;还原。4.3 变量名撞上列名最阴的一类 bug这个坑我中过一次排查了两个小时。简化后的代码是这样的DELIMITER $$ CREATE PROCEDURE p_name_conflict() BEGIN DECLARE user_id INT DEFAULT 1001; DECLARE v_cnt INT DEFAULT 0; SELECT COUNT(*) INTO v_cnt FROM t_order WHERE user_id user_id; -- 这里的两个 user_id 都是列 SELECT v_cnt; END$$ DELIMITER ;我的本意是统计 user_id 等于 1001 的订单数结果这个查询返回的是全表行数。原因就在 MySQL 的名字解析规则当 SQL 语句里出现一个名字它既是列名又是局部变量名的时候MySQL 一律按列名处理。所以WHERE user_id user_id变成了列和自身的比较条件永远为真整表扫描。这条规则的官方建议是局部变量不要和表列同名看着像废话但它救过很多人。规避手段有两个层次第一层是命名规范加v_前缀v_user_id和user_id天然不冲突这是性价比最高的方案。第二层是如果真的要写同名变量比如从别的代码直接拷过来的在 SQL 里显式区分比如给表加别名然后写t.user_id v_user_id或者干脆改名。顺带提醒一句存储过程的参数名也有同样的问题。如果入参叫user_id在WHERE user_id user_id里同样会被解析成列。所以参数也要加前缀p_user_id这种写法不是形式主义是实打实的防雷措施。4.4 游标和循环里变量的经典坑游标配合局部变量用的时候有几个坑几乎人人都会踩一次。看这段循环DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id, amount FROM t_order WHERE status 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id, v_amount; IF v_done 1 THEN LEAVE read_loop; END IF; -- 业务处理 END LOOP; CLOSE cur;第一个坑是忘记声明 NOT FOUND 处理器。没有它游标取完最后一行之后再 FETCH会直接抛 1329 异常中断整个过程。很多人第一次写游标循环跑出来的结果是处理了前面的数据然后突然报错就是缺了这个处理器。第二个坑是 v_done 忘了给初值。如果写成DECLARE v_done INT;它是 NULLIF v_done 1永远不成立循环就退不出来变成死循环。反过来如果某个嵌套结构里 v_done 被提前置成 1循环一次都不执行。所以这个标志位必须DEFAULT 0。第三个坑是 FETCH 的变量个数和游标列数不匹配。SELECT id, amount FROM ...两列就必须 FETCH 到两个变量里多一个少一个都报错。第四个坑是处理器的作用范围。CONTINUE HANDLER FOR NOT FOUND注册在哪个块就只在哪个块里生效。如果把游标循环写在嵌套块里处理器却声明在外层行为可能不符合预期。稳妥做法是处理器的声明范围覆盖游标的整个使用范围。第五个坑是空结果集。如果游标的 SELECT 一行都没查到第一次 FETCH 就会触发 NOT FOUNDv_done 立刻变 1循环体一次都不进。这个行为是对的但如果你在循环外面还有依赖循环内变量的逻辑那些变量就保持着初始值。我的习惯是循环结束后统一做一次兜底判断比如IF v_cnt 0 THEN ...把空数据的情况显式处理掉。5. 进阶局部变量在真实项目里的用法5.1 用日志表把变量打印出来MySQL 的存储过程没有 printf调试的时候不能直接在代码里打日志。我在项目里的做法是建一张日志表需要观察哪个变量就插一条记录INSERT INTO t_sp_log(proc_name, log_text) VALUES (sp_debug, CONCAT(step1 v_total, IFNULL(v_total,NULL), v_cnt, IFNULL(v_cnt,NULL)));关键在IFNULL(v_total, NULL)这层包装。如果变量是 NULLCONCAT 的结果会整体变成 NULL你往日志表里插进去的就是一个空值什么信息都看不到。包一层之后至少能确认这个变量此刻确实是 NULL——这本身就是重要线索。调试完记得把 INSERT 清掉或者用一个开关变量控制DECLARE v_debug TINYINT DEFAULT 0;调试期设成 1上线设成 0。这样一份代码两个环境通用不用上线前手忙脚乱删日志语句。5.2 动态 SQL 里局部变量拼不进去怎么办局部变量有个明确的能力边界不能直接当表名、列名或者 LIMIT 的参数用LIMIT 在较新版本里可以用变量但表名绝对不行。想动态指定表名必须走预处理语句DECLARE v_tb VARCHAR(64) DEFAULT t_order; DECLARE v_sql VARCHAR(512) DEFAULT ; SET v_sql CONCAT(SELECT COUNT(*) INTO dyn_cnt FROM , v_tb, WHERE status 1); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_cnt dyn_cnt;这里有一个必须注意的约束PREPARE后面只能跟用户变量或者字符串字面量不能直接跟局部变量。所以流程变成了局部变量拼字符串 → 赋值给用户变量 dyn_cnt → 从用户变量取回局部变量。多绕这一圈有安全隐患——用户变量是连接级共享的如果拼接的内容里混进了外部输入就可能被注入。所以动态 SQL 的表名、列名一定要走白名单校验不能直接拼接前端传进来的字符串IF v_tb NOT IN (t_order, t_order_bak) THEN SET v_msg CONCAT(illegal table name: , v_tb); -- 记录并退出 END IF;注意PREPARE出来的语句名上面的 stmt也要记得DEALLOCATE释放。不释放会一直占着会话资源在连接池环境里长时间跑批量任务可能把预处理语句的数量顶到上限。5.3 可维护性上我踩过的三个教训教训一是变量声明不要图省事集中堆在最后。我见过一个过程声明区里二十多个变量按功能混在一起没有任何注释。后来加需求的人又在末尾追加了五个。三个月后谁都不敢动这个文件。现在的做法是功能相关的变量写在一起中间用注释分隔比如-- 金额相关、-- 循环控制相关。教训二是不要用变量替代参数。有段时间图方便我把原本该做成入参的东西直接在过程里DECLARE v_date DATE DEFAULT CURDATE();。结果测试和生产的日期不一致排查了半天。凡是外部需要控制的输入一律做成 IN 参数让调用方显式传值别用缺省值藏着。教训三是类型定义要和表结构挂钩。MySQL 没有%TYPE所以我会在每个变量的声明行末尾加注释标明它对应哪张表的哪个字段比如DECLARE v_amount DECIMAL(10,2) DEFAULT 0.00; -- ref: t_order.amount。后来表字段从 DECIMAL(10,2) 扩到 DECIMAL(12,2) 的时候这份注释帮我快速定位到了所有需要同步修改的变量省了至少半天。写得多了你会有一个感觉局部变量的声明区其实就是这个存储过程的数据接口清单。声明区长什么样这个过程的设计水平就什么样。声明混乱、类型随意、没注释、和列名打架的过程里面的业务逻辑基本也好不到哪去。反过来一份排布整齐、类型精确、注释到位的声明区后面上百行的业务代码读起来会很顺。下次动手写过程的时候不妨先在声明区多花五分钟。 MySQL存储过程DECLARE局部变量：作用域、类型与报错排查
做存储过程开发到一定阶段你会发现真正拉开水平差距的不是会不会写 SELECT而是会不会用变量。存储过程里的局部变量靠DECLARE声明看起来就是一行声明语句但它牵涉到作用域、声明顺序、类型精度、命名冲突、异常处理一整套东西。我自己在给几套订单系统写批处理逻辑的时候踩过的坑基本都集中在这几行 DECLARE 上——有人把声明写在 BEGIN 中间导致整段过程建不上有人变量名和列名撞车导致 WHERE 条件永远为真还有人拿 VARCHAR(10) 去接一个身份证号跑了一个月才发现数据被悄悄截断。这篇是存储过程的使用系列的第三篇专门聊 DECLARE 定义局部变量。MySQL 5.7/8.0 为主线同时会把 SQL Server 和 Oracle 的写法横向拉出来对照。不管你是刚写完第一个存储过程的新手还是需要维护一堆历史过程的老手这里面的语法规则、类型推算、报错排查和经验技巧都能直接拿去用。1. 局部变量在存储过程中的角色定位1.1 没有变量存储过程就只是一段写死的脚本把存储过程想象成一条自动化产线SQL 语句是机械臂那局部变量就是传送带上的料框。没有料框机械臂只能抓固定位置的那一个零件抓完就没了。有了料框中间结果才能被暂存、被传递、被反复加工。举个最直白的例子你要统计某个用户在某段时间的订单总额再按总额分档给折扣。总额这个数字从 SUM 算出来到拿去做 IF 判断中间必须有个地方存着——这个地方就是局部变量。你要是硬不用变量就得把 SUM 查询重复写三遍分别塞进三个 IF 条件里代码量翻三倍不说三次查询之间数据还可能被并发写入改掉结果直接对不上。局部变量在存储过程里承担四类职责承接查询结果的中间容器、循环和游标的计数与状态开关、拼装动态 SQL 的字符串缓存、以及给 OUT 参数做最终赋值的出口。理解这四类用途你在设计过程的时候就知道该声明哪几个变量而不是写到哪算到哪。1.2 DECLARE 的作用域是块级不是过程级这是最容易误解的一点。很多从 C 语言或者 TypeScript 转过来的人会下意识觉得变量声明在函数开头作用域就是整个函数但在 MySQL 的存储过程里DECLARE 的作用域是它所在的那个BEGIN ... END块块一结束变量就消失。也就是说如果一个过程里有嵌套块DELIMITER $$ CREATE PROCEDURE p_scope_demo() BEGIN DECLARE v_outer INT DEFAULT 10; BEGIN DECLARE v_inner INT DEFAULT 20; SELECT v_outer v_inner AS inner_sum; -- 合法内层能看见外层 END; SELECT v_inner; -- 报错Unknown column v_inner END$$ DELIMITER ;内层块可以读外层块的变量反过来不行。如果内层块又声明了一个同名变量那内层用的是自己那份外层不受影响——这就是典型的变量遮蔽。这条规则在调试的时候特别有用你在一段长过程里发现某个变量值莫名其妙不对先看它是不是在某个嵌套块里被重新声明了一遍。这里有个实用心得嵌套块不要超过两层。三层以上的嵌套块变量作用域就开始变成心智负担了维护的人得一行行往上翻才能确认这个 v_x 到底是哪一层声明的。真到了这个复杂度拆成两个独立的过程用临时表传数据比硬嵌要清爽得多。1.3 设计阶段先数清楚需要几个变量我在动手写过程之前习惯在草稿纸上列一张变量清单格式很土但很管用变量名类型初值用途v_totalDECIMAL(12,2)0.00承接订单总额v_cntINT0承接订单笔数v_avgDECIMAL(12,2)0.00计算客单价v_discountDECIMAL(4,2)1.00折扣系数这张表有三个好处一是类型和初值在写代码前就定死了避免后面为了迁就一个 IF 分支去改类型二是初值写清楚就不会出现变量没赋初值结果参与运算得到 NULL这种低级问题三是变量超过八个的时候你会本能地警觉——一个过程需要十几个局部变量通常意味着它干了太多事该拆了。1.4 三种变量的区分要先搞明白初学的人经常把三类变量混在一起这里一次性理清局部变量DECLARE v_x INT;没有前缀作用域是声明它的BEGIN ... END块必须用 DECLARE 声明才能用。用户变量会话变量SET x 1;带前缀不需要声明作用范围是整个数据库连接连接断开就没了。因为是连接级共享两个过程之间可以用它传值但也正因如此特别容易被别的地方污染。系统变量global.max_connections、session.autocommit双前缀由数据库本身维护读可以改要谨慎。用一句话记住区别局部变量是过程内部的事用户变量是整个连接的事系统变量是整个数据库实例的事。作用域从小到大风险从低到高。绝大多数业务逻辑用局部变量就够了只有真的需要跨过程传临时值或者拼动态 SQL 的时候才去动用户变量。2. DECLARE 语法细节与类型选择2.1 MySQL 中 DECLARE 的三条硬规矩MySQL 的 DECLARE 语法本身很短DECLARE 变量名 [, 变量名 ...] 数据类型 [DEFAULT 默认值];但它的位置规矩是硬的写错一个字符过程就建不上第一条DECLARE 必须写在BEGIN ... END块的最前面。只要前面出现任何一条可执行语句哪怕是SET后面再写 DECLARE 直接报语法错误。第二条声明之间有固定顺序先是变量和条件CONDITION再是游标CURSOR最后是处理器HANDLER。这个顺序不能乱。原因很好理解——处理器要捕获游标触发的 NOT FOUND游标要引用前面声明的变量它们之间是依赖关系所以只能按依赖方向排。第三条DECLARE 只能出现在BEGIN ... END里不能用在过程体外。匿名块例外但 MySQL 的匿名块也得靠BEGIN ... END包起来才有地方声明。一个位置完全正确的骨架长这样DELIMITER $$ CREATE PROCEDURE p_skeleton() BEGIN -- 1. 变量声明 DECLARE v_done INT DEFAULT 0; DECLARE v_id INT UNSIGNED; DECLARE v_name VARCHAR(64); -- 2. 游标声明 DECLARE cur_user CURSOR FOR SELECT id, name FROM t_user WHERE status 1; -- 3. 处理器声明 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; -- 4. 从这里开始才是可执行语句 OPEN cur_user; read_loop: LOOP FETCH cur_user INTO v_id, v_name; IF v_done 1 THEN LEAVE read_loop; END IF; -- 业务处理 END LOOP; CLOSE cur_user; END$$ DELIMITER ;注意声明顺序错了MySQL 报的是 1064 语法错误错误信息不会告诉你顺序不对只会指在 DECLARE 那一行。遇到 1064 且确认括号分隔符都没问题第一反应就去检查声明顺序。2.2 数据类型怎么选几个容易翻车的地方变量声明和建表选字段类型逻辑是一致的但变量的容错要求更高——表字段写错了至少还有约束报错变量写窄了是静默截断最难查。金额一律用 DECIMAL不用 FLOAT 和 DOUBLE。FLOAT/DOUBLE 是二进制浮点0.1 0.2 不等于 0.3 这种问题在金额场景是致命的。DECIMAL(12,2)的含义是总共 12 位数字其中 2 位小数整数部分最多 10 位能表示的最大值是 9999999999.99也就是九十九亿多对绝大多数订单汇总场景够用。如果你的业务涉及跨境结算或者分账把精度提到DECIMAL(18,4)别省。字符串长度要按最坏情况算。VARCHAR(64)在 utf8mb4 字符集下最多占 64 × 4 256 字节。这里的关键是MySQL 的 VARCHAR 长度单位是字符不是字节所以VARCHAR(64)存 64 个汉字没问题。但如果你拿它去接一个可能超过 64 字符的拼接结果超出的部分会被直接砍掉而且在很多 MySQL 配置下只给一个 Warning不报错。这是静默截断最典型的来源。计数用 INT UNSIGNED。订单笔数、循环下标这类非负整数加 UNSIGNED 能多一倍上限更重要的是语义清晰——看到 UNSIGNED 就知道这个变量不可能为负。日期时间统一用 DATETIME 而不是 TIMESTAMP。TIMESTAMP 有 2038 年的上限并且会跟随时区转换跨时区系统里容易出现存进去和取出来差八小时的怪事。DATETIME 是所见即所得的字面值变量里用最稳妥。实操心得所有承接 SELECT 结果的局部变量类型要么和源字段完全一致要么比源字段更宽。我一般会直接去表结构里抄字段定义抄的时候顺手把 VARCHAR 加宽一档。这个习惯让我少查了很多 Data truncated 的工单。2.3 DEFAULT 与 NULL没赋初值的变量到底是什么一个只写了DECLARE v_x INT;的变量它的初始值是NULL不是 0也不是空字符串。这一点和很多编程语言的直觉相反。NULL 参与运算的传染性非常强DECLARE v_a INT; DECLARE v_b INT DEFAULT 1; SELECT v_a v_b; -- 结果是 NULL只要有一项是 NULL加法结果就是 NULL然后这个 NULL 继续往 IF 条件里传IF NULL 0的结果既不是 TRUE 也不是 FALSE而是 UNKNOWN走的是 ELSE 分支。等你发现分支走错了回头查变量本身看着人畜无害问题就出在它没赋初值。所以我的建议很直接除了明确要用 NULL 表达未取到值的变量其余一律写 DEFAULT。数值给 0字符串给空串计数给 0布尔开关给 0。写的时候多敲几个字符排查的时候省几个小时。关于 DEFAULT 的值还有一个版本差异要注意早年的 MySQL 要求 DEFAULT 后面必须跟常量写函数调用或者子查询会报错。我用过的 5.7 和 8.0 在部分场景下放宽了限制但为了跨版本兼容还是老老实实写常量需要动态计算初值就在块的第一条语句里用 SET 赋。2.4 命名规范v_ 前缀和重名规避局部变量的命名业界没有强制标准但有几个约定俗成值得遵守。最主流的是前缀法局部变量用v_入参用p_出参用p_out_或者直接o_。这样你在一大段代码里扫一眼就知道这个标识符是什么来路。我见过一些规范用l_local和i_input效果一样关键是团队内统一。前缀真正解决的是变量名和列名冲突的问题这个坑在下一节会详细讲先给个结论变量名永远不要和任何一张相关表的列名同名。加了v_前缀之后v_user_id和列user_id天然不会撞车这是白送的一层保险。另一条经验是变量数量控制。我给自己定了一条线单个BEGIN ... END块的局部变量不超过十个。超过这个数说明这段逻辑承担的职责太多我会把它按取数—计算—落库三段拆开前一段的产物用临时表落地后一段再读。这样做还有个额外好处每段都能单独跑调试的时候不用整条链路一起跑。3. 实操一个带局部变量的完整存储过程3.1 场景设定与建表假设要做一个用户订单汇总给定用户 ID 和时间区间算出这个用户在区间内已支付订单的总额和笔数按总额给出阶梯折扣把结果写回 OUT 参数同时往日志表里记一条审计。先建两张表DROP TABLE IF EXISTS t_order; CREATE TABLE t_order ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_status_time (user_id, status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; DROP TABLE IF EXISTS t_sp_log; CREATE TABLE t_sp_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, proc_name VARCHAR(64) NOT NULL, log_text VARCHAR(512) NOT NULL, log_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;索引idx_user_status_time的字段顺序是 (user_id, status, created_at)这个顺序有讲究。查询条件是user_id ? AND status 1 AND created_at BETWEEN等值条件在前、范围条件在后索引才能被完整用上。如果把 created_at 放中间后面的 status 就用不上索引了这是很常见的索引设计失误。3.2 过程完整代码DELIMITER $$ DROP PROCEDURE IF EXISTS sp_user_order_summary$$ CREATE PROCEDURE sp_user_order_summary( IN p_user_id INT UNSIGNED, IN p_start DATETIME, IN p_end DATETIME, OUT p_total DECIMAL(12,2), OUT p_cnt INT, OUT p_discount DECIMAL(4,2) ) BEGIN DECLARE v_total DECIMAL(12,2) DEFAULT 0.00; DECLARE v_cnt INT DEFAULT 0; DECLARE v_avg DECIMAL(12,2) DEFAULT 0.00; DECLARE v_discount DECIMAL(4,2) DEFAULT 1.00; DECLARE v_msg VARCHAR(512) DEFAULT ; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_total 0.00; SET p_cnt 0; SET p_discount 1.00; END; SELECT IFNULL(SUM(amount), 0), COUNT(*) INTO v_total, v_cnt FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 THEN SET v_avg v_total / v_cnt; END IF; IF v_total 10000.00 THEN SET v_discount 0.85; ELSEIF v_total 5000.00 THEN SET v_discount 0.92; ELSEIF v_total 1000.00 THEN SET v_discount 0.97; ELSE SET v_discount 1.00; END IF; SET p_total v_total; SET p_cnt v_cnt; SET p_discount v_discount; SET v_msg CONCAT(uid, p_user_id, total, v_total, cnt, v_cnt, avg, v_avg, disc, v_discount); INSERT INTO t_sp_log(proc_name, log_text) VALUES (sp_user_order_summary, v_msg); END$$ DELIMITER ;3.3 逐段解读与关键参数推算声明段。五个局部变量全部带 DEFAULT放在块的最前面。类型上做了刻意的放大v_total用DECIMAL(12,2)比源字段amount的DECIMAL(10,2)宽了两档。为什么要放大因为SUM(amount)的结果可能比单笔金额大得多。假设一个用户一年下了十万笔订单平均每笔 1000 元总和就是一亿DECIMAL(10,2)的整数部分只有 8 位上限 99999999.99一亿直接溢出。放大到 12 位上限提到九十九亿留够了余量。异常处理器。DECLARE EXIT HANDLER FOR SQLEXCEPTION捕获块内所有 SQL 异常触发后执行里面的赋值语句把三个 OUT 参数设成安全默认值然后退出整个块。这个设计保证了无论中间哪一步炸掉调用方拿到的永远是一组可解释的值而不是残留的脏数据或者 NULL。EXIT 和 CONTINUE 的区别要记牢EXIT 处理完直接离开当前块CONTINUE 处理完继续往下执行。做汇总统计这类场景出错之后继续跑没意义所以用 EXIT。取数段。SELECT ... INTO v_total, v_cnt把两个聚合值一次性塞进两个变量。这里有两处细节。第一IFNULL(SUM(amount), 0)是必须的——当 WHERE 条件没有匹配到任何行时SUM返回的是 NULL 而不是 0COUNT(*)则返回 0。如果不包 IFNULLv_total 就是 NULL后面所有比较都变成 UNKNOWN折扣会一路掉到 ELSE 分支。第二created_at p_start AND created_at p_end用的是左闭右开区间终点不含。这样做的好处是把2024-12-31 23:59:59这种边界值彻底排除掉避免最后一秒的订单算不算这个月这种扯皮。计算段。客单价用IF v_cnt 0守护避免除零。MySQL 里整数除以零在部分模式下返回 NULL开了 strict 模式会直接报 1365 错误两种情况都不好受加个判断最省心。折扣阶梯是从高到低排的先判 10000再判 5000。顺序不能反过来写否则一笔 15000 的订单会先在 5000那里被拦下给出 0.92 而不是 0.85。这种从宽到窄或者从高到低的排序原则凡是写多级 IF 都要遵守。输出段。三个SET p_xxx v_xxx把内部变量转成 OUT 参数。中间多这一层转换看着啰嗦但它把计算逻辑和接口契约隔开了。以后要给过程加功能比如再返回一个最高单笔金额只要在内部加变量输出段加一行赋值前面所有逻辑不动。3.4 调用与结果验证-- 先灌点测试数据 INSERT INTO t_order(user_id, amount, status, created_at) VALUES (1001, 300.00, 1, 2024-03-01 10:00:00), (1001, 800.50, 1, 2024-03-05 14:20:00), (1001, 60.00, 0, 2024-03-06 09:00:00), (1001, 4200.00,1, 2024-04-11 16:00:00); -- 调用 CALL sp_user_order_summary( 1001, 2024-01-01 00:00:00, 2025-01-01 00:00:00, total, cnt, disc ); SELECT total, cnt, disc;预期结果total 5300.50cnt 3disc 0.92。注意那笔 60 元的订单 status 是 0待支付不参与统计笔数正好是 3。调用的时候有个细节MySQL 的 OUT 参数必须传变量不能传字面量。CALL sp_user_order_summary(1001, ..., ..., 0, 0, 0)会直接报 1414 错误。传total这种用户变量是最常用的做法也可以在一个外层过程的局部变量里接收。验证完再查一下日志表SELECT * FROM t_sp_log ORDER BY id DESC LIMIT 1;能看到一条uid1001 total5300.50 cnt3 avg1766.83 disc0.92的记录说明内部变量算对了而且和 OUT 输出一致。3.5 同一逻辑改写为 SQL Server 与 Oracle三个数据库的声明语法差异挺大放在一起对照记最快。先看 SQL Server 版本CREATE OR ALTER PROCEDURE dbo.sp_user_order_summary p_user_id INT, p_start DATETIME, p_end DATETIME, p_total DECIMAL(12,2) OUTPUT, p_cnt INT OUTPUT, p_discount DECIMAL(4,2) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE v_total DECIMAL(12,2) 0.00; DECLARE v_cnt INT 0; DECLARE v_avg DECIMAL(12,2) 0.00; DECLARE v_discount DECIMAL(4,2) 1.00; SELECT v_total ISNULL(SUM(amount), 0), v_cnt COUNT(*) FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 SET v_avg v_total / v_cnt; IF v_total 10000.00 SET v_discount 0.85; ELSE IF v_total 5000.00 SET v_discount 0.92; ELSE IF v_total 1000.00 SET v_discount 0.97; ELSE SET v_discount 1.00; SET p_total v_total; SET p_cnt v_cnt; SET p_discount v_discount; END;再看 OracleCREATE OR REPLACE PROCEDURE sp_user_order_summary( p_user_id IN NUMBER, p_start IN DATE, p_end IN DATE, p_total OUT NUMBER, p_cnt OUT NUMBER, p_discount OUT NUMBER ) IS v_total NUMBER(12,2) : 0; v_cnt NUMBER : 0; v_avg NUMBER(12,2) : 0; v_discount NUMBER(4,2) : 1; BEGIN SELECT NVL(SUM(amount), 0), COUNT(*) INTO v_total, v_cnt FROM t_order WHERE user_id p_user_id AND status 1 AND created_at p_start AND created_at p_end; IF v_cnt 0 THEN v_avg : v_total / v_cnt; END IF; IF v_total 10000 THEN v_discount : 0.85; ELSIF v_total 5000 THEN v_discount : 0.92; ELSIF v_total 1000 THEN v_discount : 0.97; ELSE v_discount : 1.00; END IF; p_total : v_total; p_cnt : v_cnt; p_discount : v_discount; END; /把三家的差异整理成一张表写跨库代码的时候直接对着查对比项MySQLSQL ServerOracle声明关键字DECLAREDECLARE声明段直接写匿名块才用 DECLARE变量前缀无必须带 无默认值写法DEFAULT 值 值: 值 或 DEFAULT 值赋值方式SET v_x 值SET x 值v_x : 值注释符号-- 或 #----作用域粒度BEGIN...END 块级批或过程级不随块细分块级嵌套块可重声明类型锚定字段不支持不支持支持 %TYPE / %ROWTYPE语句分隔需要 DELIMITER不需要需要 / 结束 PL/SQL 块ELSE IF 写法ELSEIFELSE IFELSIFOracle 的%TYPE特别值得单独说一句这是它比另外两家强的地方。写成v_total t_order.amount%TYPE;变量的类型会自动跟随表字段走。以后表字段从DECIMAL(10,2)改成DECIMAL(12,2)过程不用重新编译。MySQL 和 SQL Server 没这个能力只能靠人工维护类型一致性——所以在 MySQL 里我更倾向于把类型定义写在注释里注明参照的是哪个字段。4. 常见报错与排查技巧实录4.1 高频报错速查表下面这些错误码我在实际项目里基本都遇到过整理出来给个对照错误码 / 现象根因处理方式1064 语法错误指向 DECLARE 行DECLARE 没放在块首或声明顺序错全部 DECLARE 上移到块开头按变量→游标→处理器排序1305 PROCEDURE does not existDELIMITER 没设或没还原只执行了前半段检查分隔符执行后记得DELIMITER ;1329 No data - zero rows fetchedSELECT ... INTO没查到任何行加CONTINUE HANDLER FOR NOT FOUND或改用聚合加 IFNULL1172 Result consisted of more than one rowSELECT ... INTO返回多行WHERE 条件收窄或加LIMIT 11054 Unknown column v_x变量在作用域外引用或忘了声明检查嵌套块边界确认 DECLARE 存在1292 Truncated incorrect DOUBLE value类型隐式转换失败显式CAST或修正变量类型1265 Data truncated for column变量长度不够写入被截断放大变量类型字符串至少加宽一档1414 OUT 参数必须传变量CALL 时给 OUT 位置传了字面量改用var接收1365 Division by 0除数变量为 0除之前用 IF 判断除数大于 01048 Column cannot be null变量取到 NULL 写进了非空列变量声明给 DEFAULT 兜底4.2 声明位置错了到底会发生什么这个值得展开说因为它是最常见又最费时间的一类问题。看下面这段CREATE PROCEDURE p_bad() BEGIN SET tmp 1; DECLARE v_x INT DEFAULT 0; -- 报错 SELECT v_x; END;执行的时候你会收到 1064错误信息指向DECLARE v_x INT DEFAULT 0那一行。但你可能盯着这行看了十分钟语法明明没问题。原因在它上面那行SET tmp 1;——MySQL 解析到 DECLARE 的时候已经认为这个块的声明区结束了。处理原则很简单块内所有的 DECLARE 全部上移一条可执行语句都不能夹在中间。我写过程的固定动作就是先敲 BEGIN然后一口气把所有 DECLARE 写完再去写业务逻辑。这样结构上就不可能出错。排查的时候如果 1064 怎么都找不到原因还有一个高频嫌疑人是分隔符。客户端默认用分号作为语句结束符过程体内部也有分号不加 DELIMITER 的话客户端会在第一个内部分号处就把语句截断发出去创建出来的过程自然是不完整的后续调用报 1305。所以标准流程永远是三件套DELIMITER $$开头END$$收尾DELIMITER ;还原。4.3 变量名撞上列名最阴的一类 bug这个坑我中过一次排查了两个小时。简化后的代码是这样的DELIMITER $$ CREATE PROCEDURE p_name_conflict() BEGIN DECLARE user_id INT DEFAULT 1001; DECLARE v_cnt INT DEFAULT 0; SELECT COUNT(*) INTO v_cnt FROM t_order WHERE user_id user_id; -- 这里的两个 user_id 都是列 SELECT v_cnt; END$$ DELIMITER ;我的本意是统计 user_id 等于 1001 的订单数结果这个查询返回的是全表行数。原因就在 MySQL 的名字解析规则当 SQL 语句里出现一个名字它既是列名又是局部变量名的时候MySQL 一律按列名处理。所以WHERE user_id user_id变成了列和自身的比较条件永远为真整表扫描。这条规则的官方建议是局部变量不要和表列同名看着像废话但它救过很多人。规避手段有两个层次第一层是命名规范加v_前缀v_user_id和user_id天然不冲突这是性价比最高的方案。第二层是如果真的要写同名变量比如从别的代码直接拷过来的在 SQL 里显式区分比如给表加别名然后写t.user_id v_user_id或者干脆改名。顺带提醒一句存储过程的参数名也有同样的问题。如果入参叫user_id在WHERE user_id user_id里同样会被解析成列。所以参数也要加前缀p_user_id这种写法不是形式主义是实打实的防雷措施。4.4 游标和循环里变量的经典坑游标配合局部变量用的时候有几个坑几乎人人都会踩一次。看这段循环DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT id, amount FROM t_order WHERE status 1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur; read_loop: LOOP FETCH cur INTO v_id, v_amount; IF v_done 1 THEN LEAVE read_loop; END IF; -- 业务处理 END LOOP; CLOSE cur;第一个坑是忘记声明 NOT FOUND 处理器。没有它游标取完最后一行之后再 FETCH会直接抛 1329 异常中断整个过程。很多人第一次写游标循环跑出来的结果是处理了前面的数据然后突然报错就是缺了这个处理器。第二个坑是 v_done 忘了给初值。如果写成DECLARE v_done INT;它是 NULLIF v_done 1永远不成立循环就退不出来变成死循环。反过来如果某个嵌套结构里 v_done 被提前置成 1循环一次都不执行。所以这个标志位必须DEFAULT 0。第三个坑是 FETCH 的变量个数和游标列数不匹配。SELECT id, amount FROM ...两列就必须 FETCH 到两个变量里多一个少一个都报错。第四个坑是处理器的作用范围。CONTINUE HANDLER FOR NOT FOUND注册在哪个块就只在哪个块里生效。如果把游标循环写在嵌套块里处理器却声明在外层行为可能不符合预期。稳妥做法是处理器的声明范围覆盖游标的整个使用范围。第五个坑是空结果集。如果游标的 SELECT 一行都没查到第一次 FETCH 就会触发 NOT FOUNDv_done 立刻变 1循环体一次都不进。这个行为是对的但如果你在循环外面还有依赖循环内变量的逻辑那些变量就保持着初始值。我的习惯是循环结束后统一做一次兜底判断比如IF v_cnt 0 THEN ...把空数据的情况显式处理掉。5. 进阶局部变量在真实项目里的用法5.1 用日志表把变量打印出来MySQL 的存储过程没有 printf调试的时候不能直接在代码里打日志。我在项目里的做法是建一张日志表需要观察哪个变量就插一条记录INSERT INTO t_sp_log(proc_name, log_text) VALUES (sp_debug, CONCAT(step1 v_total, IFNULL(v_total,NULL), v_cnt, IFNULL(v_cnt,NULL)));关键在IFNULL(v_total, NULL)这层包装。如果变量是 NULLCONCAT 的结果会整体变成 NULL你往日志表里插进去的就是一个空值什么信息都看不到。包一层之后至少能确认这个变量此刻确实是 NULL——这本身就是重要线索。调试完记得把 INSERT 清掉或者用一个开关变量控制DECLARE v_debug TINYINT DEFAULT 0;调试期设成 1上线设成 0。这样一份代码两个环境通用不用上线前手忙脚乱删日志语句。5.2 动态 SQL 里局部变量拼不进去怎么办局部变量有个明确的能力边界不能直接当表名、列名或者 LIMIT 的参数用LIMIT 在较新版本里可以用变量但表名绝对不行。想动态指定表名必须走预处理语句DECLARE v_tb VARCHAR(64) DEFAULT t_order; DECLARE v_sql VARCHAR(512) DEFAULT ; SET v_sql CONCAT(SELECT COUNT(*) INTO dyn_cnt FROM , v_tb, WHERE status 1); PREPARE stmt FROM v_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET v_cnt dyn_cnt;这里有一个必须注意的约束PREPARE后面只能跟用户变量或者字符串字面量不能直接跟局部变量。所以流程变成了局部变量拼字符串 → 赋值给用户变量 dyn_cnt → 从用户变量取回局部变量。多绕这一圈有安全隐患——用户变量是连接级共享的如果拼接的内容里混进了外部输入就可能被注入。所以动态 SQL 的表名、列名一定要走白名单校验不能直接拼接前端传进来的字符串IF v_tb NOT IN (t_order, t_order_bak) THEN SET v_msg CONCAT(illegal table name: , v_tb); -- 记录并退出 END IF;注意PREPARE出来的语句名上面的 stmt也要记得DEALLOCATE释放。不释放会一直占着会话资源在连接池环境里长时间跑批量任务可能把预处理语句的数量顶到上限。5.3 可维护性上我踩过的三个教训教训一是变量声明不要图省事集中堆在最后。我见过一个过程声明区里二十多个变量按功能混在一起没有任何注释。后来加需求的人又在末尾追加了五个。三个月后谁都不敢动这个文件。现在的做法是功能相关的变量写在一起中间用注释分隔比如-- 金额相关、-- 循环控制相关。教训二是不要用变量替代参数。有段时间图方便我把原本该做成入参的东西直接在过程里DECLARE v_date DATE DEFAULT CURDATE();。结果测试和生产的日期不一致排查了半天。凡是外部需要控制的输入一律做成 IN 参数让调用方显式传值别用缺省值藏着。教训三是类型定义要和表结构挂钩。MySQL 没有%TYPE所以我会在每个变量的声明行末尾加注释标明它对应哪张表的哪个字段比如DECLARE v_amount DECIMAL(10,2) DEFAULT 0.00; -- ref: t_order.amount。后来表字段从 DECIMAL(10,2) 扩到 DECIMAL(12,2) 的时候这份注释帮我快速定位到了所有需要同步修改的变量省了至少半天。写得多了你会有一个感觉局部变量的声明区其实就是这个存储过程的数据接口清单。声明区长什么样这个过程的设计水平就什么样。声明混乱、类型随意、没注释、和列名打架的过程里面的业务逻辑基本也好不到哪去。反过来一份排布整齐、类型精确、注释到位的声明区后面上百行的业务代码读起来会很顺。下次动手写过程的时候不妨先在声明区多花五分钟。