尧图网站建设 
凌晨一点多盯着一个跑了三个月的对账存储过程输出结果总是比实际少一条。翻来覆去改 SQL、换索引、重跑数据最后发现问题既不在 SQL 也不在数据——我在过程里声明了一个叫status的局部变量而表里正好也有一列叫status。语句里那个标识符被 MySQL 当成了列名我的变量从头到尾没参与过比较。这类事故在存储过程开发里非常典型而它唯一的根因就是对 DECLARE 定义局部变量的规则理解得太浅。这一篇专门聊 MySQL 存储过程里的DECLARE也就是局部变量怎么声明、声明在哪里、怎么赋值、作用域多大、什么时候会翻车。它是存储过程系列的第三篇前面聊过整体结构和参数这一篇只聚焦一件事过程内部那些用来暂存中间结果、当计数器、当标志位的变量到底该怎么用。写这篇文章的初衷很简单我在带人的时候发现新手写存储过程最常犯的错不是逻辑写错而是三个地方声明位置放错、赋值方式选错、变量名和列名撞车。这三个坑单独看都不致命凑在一起就是查半天的疑难杂症。所以下面我把语法规则、赋值取舍、作用域边界、调试手法全部拆开讲配上能直接复制跑的完整例子同时把 Oracle 和 SQL Server 的写法放在一起做对照方便你在不同数据库之间切换时不至于手忙脚乱。1. 从一次结果少一条的排查说起DECLARE 到底解决什么问题1.1 事故复盘变量名和列名撞车之后会发生什么那次排查的过程值得完整讲一遍因为它是理解局部变量优先级的活教材。我的过程简化后大致是这样CREATE PROCEDURE sp_check(IN p_id BIGINT) BEGIN DECLARE status INT DEFAULT 1; -- 想用它表示已支付 SELECT COUNT(*) FROM order_detail WHERE user_id p_id AND status status; -- 本意是 status 1 END;status status这个写法在人的直觉里是列等于我的变量但在 MySQL 的解析器眼里WHERE子句里的标识符在很多场景下会优先绑定到列名于是这个条件变成了恒等式等于没过滤COUNT 出来的是全部订单数。后来我改成筛status 1又发现数字对不上因为我的逻辑里其实需要区分已支付和未支付兜了一圈才回到变量本身。这件事给我的教训只有一条但非常硬局部变量名必须和 SQL 语句里出现的列名彻底隔离。MySQL 官方手册也专门提醒过命名冲突的问题因为它的解析优先级跟你想要的往往不一致。行业里最省心的做法是加前缀参数用p_局部变量用v_游标用cur_标志位用done_。加完之后v_status status这种写法一眼就能看出谁是谁撞名的概率直接归零。提示如果你的库里已经有大量不带前缀的老过程别急着改。先做一次全局搜索把所有DECLARE出来的变量名和过程里出现的列名做交叉比对只改真正撞名的那几个改动面能小一个数量级。1.2 局部变量、参数、用户会话变量三个特别容易装错东西的口袋刚接触存储过程的人最容易混的是这三种存东西的地方。它们看起来都能装值但作用域、生命周期、能否跨过程访问完全不同混用会写出很难维护的代码。维度局部变量DECLARE存储过程参数IN/OUT/INOUT用户会话变量x声明位置BEGIN...END块的开头过程定义的参数列表随用随建无需声明命名要求无前缀建议v_无前缀建议p_必须带作用域所在块及其嵌套块整个过程体当前数据库连接跨过程可见生命周期块执行结束即销毁过程结束即销毁连接断开才消失事务回滚的影响不受影响值不回退不受影响不受影响能否对外输出不能必须赋给 OUT 参数可以OUT/INOUT可以调用方直接读这张表里最值得记住的是最后两行。第一变量不是表数据ROLLBACK不会把它恢复到旧值所以事务回滚之后如果还要继续走逻辑一定要手动重置变量或者直接RETURN。第二局部变量天生就是过程内部的事想让调用方拿到结果必须显式赋值给OUT参数。我见过有人在过程里用result一路传值出去功能是能跑但两个过程同时用result就会互相污染这类 bug 在并发场景下极难复现。1.3 先跑一个二十行的最小例子在钻语法细节之前先把最小可运行的骨架跑通后面的规则才有地方落。下面这段可以直接在 MySQL 8.0 或者 5.7 上执行DROP PROCEDURE IF EXISTS sp_min_demo; DELIMITER $$ CREATE PROCEDURE sp_min_demo(IN p_n INT, OUT p_msg VARCHAR(64)) BEGIN DECLARE v_total INT DEFAULT 0; DECLARE v_text VARCHAR(64) DEFAULT ; SET v_total p_n * 2; SET v_text CONCAT(输入 , p_n, 翻倍后 , v_total); SET p_msg v_text; END$$ DELIMITER ; CALL sp_min_demo(21, msg); SELECT msg; -- 输入 21翻倍后 42这里有三件事值得注意DECLARE出现在BEGIN之后的第一段任何可执行语句之前每个变量的类型必须写全VARCHAR后面的长度不能省SET v_total p_n * 2里可以直接引用参数p_n因为参数的作用域覆盖整个过程体。把这个骨架记住后面所有规则都是它的展开。2. DECLARE 的语法骨架与位置不对就报 1064的硬规矩2.1 完整语法形式与 DEFAULT 的正确用法MySQL 里声明局部变量的语法是DECLARE var_name [, var_name] ... type [DEFAULT value]方括号里的DEFAULT是可选的但我强烈建议你每次都写。原因很实际一个没有DEFAULT的变量初始值是NULL不是0也不是空字符串。很多人下意识觉得DECLARE v_cnt INT;之后v_cnt从 0 开始然后写SET v_cnt v_cnt 1结果得到的是NULL而且NULL会沿着整个表达式链条传染下去最后CONCAT出来的字符串是NULL写进表里也是一片NULL排查起来相当费劲。DEFAULT后面跟字面量最稳妥。想用表达式或者查询结果做初始值我的建议是拆成两步先DECLARE ... DEFAULT NULL紧接着用SET或者SELECT ... INTO赋值。这样语义清晰也不依赖具体版本对DEFAULT表达式的支持程度。注意局部变量的声明里写NOT NULL是行不通的。想要保证非空只能靠DEFAULT给一个兜底值再在业务逻辑里自己做校验别指望数据库帮你拦。2.2 声明顺序变量、条件、游标、处理器不能乱这是DECLARE最容易踩的语法坑也是报 1064 的高频原因。在一个BEGIN...END块里所有DECLARE语句必须集中在块的开头而且不同类型之间还有先后顺序变量声明DECLARE var_name type和条件声明DECLARE condition_name CONDITION FOR ...游标声明DECLARE cur_name CURSOR FOR ...处理器声明DECLARE ... HANDLER FOR ...顺序错了、或者中间插了一条SET、SELECT这样的可执行语句MySQL 直接抛语法错误。这个规则的现实影响是你不能用到哪声明到哪。写长过程的时候习惯上要先把所有变量在开头列清楚再往下写逻辑。刚开始会觉得别扭写习惯了反而有好处——打开一个过程先看声明区就能大致知道这个过程的状态变量有哪些代码可读性明显提升。-- 正确顺序示例 BEGIN DECLARE v_done TINYINT DEFAULT 0; -- 1. 变量 DECLARE v_uid BIGINT DEFAULT NULL; -- 1. 变量 DECLARE cur_u CURSOR FOR SELECT id FROM t_u; -- 2. 游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; -- 3. 处理器 -- 下面是可执行语句 END;2.3 类型选择的坑VARCHAR 必须带长度金额别用 INT局部变量的类型可以是 MySQL 支持的大部分标量类型INT、BIGINT、TINYINT、DECIMAL(p,s)、CHAR(n)、VARCHAR(n)、DATE、DATETIME、TIMESTAMP等。选类型的时候有几个坑值得单独说。VARCHAR和CHAR在声明时必须带长度写成DECLARE v_s VARCHAR;会直接报语法错误。长度怎么定我的经验是往宽了给一点比如拼接日志用VARCHAR(512)比抠着算VARCHAR(64)省心因为一旦内容超长MySQL 在非严格模式下会截断你会得到一条看起来正常但少了尾巴的日志比直接报错更难查。金额类变量绝对不要用INT或者BIGINT。用整数存金额意味着你自己要维护乘以 100的小数位约定稍不留神就少乘或多乘一次。直接用DECIMAL(12,2)类型自带精度SUM出来也不会出现浮点误差。同理时间类结果用DATETIME接收别用VARCHAR装字符串不然想比大小还得先转换。TINYINT是标志位的标准选择DECLARE v_done TINYINT DEFAULT 0;这一行几乎会出现在每一个游标循环里后面第 5 节会展开。2.4 多个变量写一条还是一条一个官方语法上DECLARE a, b INT DEFAULT 0;这种写法是可以的多个变量共用同一个类型和一个默认值。但我在实际项目里从来不用这种写法理由有三个。第一默认值不同就得拆开。一个要DEFAULT 0、一个要DEFAULT NULL、一个要DEFAULT 写不到一条语句里还不如一开始就养成一条一个的习惯。第二类型不同更得拆开。第三也是最实际的一点一行一个变量出问题的时候git diff看得清清楚楚多人协作时也不会因为一个人改了默认值影响到同一行里的其他变量。-- 我推荐的写法一行一个声明区对齐 DECLARE v_cnt INT DEFAULT 0; DECLARE v_amount DECIMAL(12,2) DEFAULT 0.00; DECLARE v_last_at DATETIME DEFAULT NULL; DECLARE v_msg VARCHAR(256) DEFAULT ;提示如果你手里的版本对多变量声明直接报语法错误不用惊讶拆成多行就行这对业务逻辑没有任何影响。语法上的便利不值得为了省几行去冒险。3. 赋值两条路SET 与 SELECT ... INTO 的分工3.1 SET 的确定性表达式、多变量、不带 FROM 的 SELECTSET是最直白也最安全的赋值方式右侧可以是一个完整的表达式可以引用参数、其他局部变量也可以调用内置函数。它支持一次给多个变量赋值SET v_a 1, v_b hello, v_c NOW();SET的特点是一定成功只要表达式本身合法不会因为数据情况抛异常。所以对于计算类、拼接类、计数器类的赋值我一律用SET。另外有个小技巧很多人不知道MySQL 允许不带FROM的SELECT直接赋值比如SELECT 1 1 INTO v_total;或者SELECT NOW() INTO v_time;。这在你需要调用函数又不想写SET的时候挺好用。但要注意这种写法和SELECT ... INTO是同一套机制所以下一小节讲的坑它同样适用——虽然不带FROM的查询必然返回一行风险很低。3.2 SELECT ... INTO 的三个经典坑多行、零行、撞名从表里取值赋给变量最自然的写法就是SELECT加INTOSELECT COUNT(*), IFNULL(SUM(amount), 0.00) INTO v_cnt, v_amount FROM order_detail WHERE user_id p_user_id AND status 1;这段代码看起来没问题但它藏着三个必须提前知道的坑。坑一命中多行直接报错。如果查询返回两行以上MySQL 抛 1172Result consisted of more than one row。这在按主键查单行的时候不会发生但一旦WHERE条件不够精确就会炸。解决办法有两个确认唯一性或者在末尾加LIMIT 1。加LIMIT 1能止住报错但你要清楚自己在随便取一行语义上是否可接受。坑二零行只报警告不报错。查询没命中任何行时MySQL 只抛一个 warning1329No data变量保持原来的值。这个特性非常危险因为程序会继续往下跑用的是变量里上一次的值或者默认值。所以我前面反复强调DEFAULT要给DECLARE v_cnt INT DEFAULT 0;至少能保证兜底是 0 而不是NULL。更稳的做法是尽量用聚合函数取值而不是取单行。SELECT COUNT(*), SUM(amount) INTO ...这种写法因为聚合函数的存在永远返回一行天然绕开了零行问题。SUM在没有匹配行时返回NULL所以外面套一层IFNULL(SUM(amount), 0.00)就彻底安全了。这个模式我在实际项目里用得最多几乎可以当成标准模板。坑三变量名和列名撞车。这是第 1 节复盘的那个问题。SELECT的目标列位置也就是INTO前面的那些不会歧义因为INTO后面明确是变量。但在WHERE、ORDER BY、HAVING里引用标识符时同名的列会优先被匹配。所以变量名加前缀这件事不是风格问题是正确性问题。3.3 在 IF、WHILE、REPEAT 里当计数器、累加器和标志位局部变量在流程控制语句里才真正体现出价值。三种最典型的用法累加器用于把多行数据汇总成一个值DECLARE v_sum DECIMAL(12,2) DEFAULT 0.00; -- 循环内部 SET v_sum v_sum v_row_amount;计数器用于控制循环次数DECLARE v_i INT DEFAULT 0; WHILE v_i 10 DO SET v_i v_i 1; -- 业务逻辑 END WHILE;标志位用于游标循环里判断是否读完这是存储过程里最经典的模式第 5 节会给完整代码。这里有个必须提的注意事项WHILE循环里给计数器加 1 这行千万别漏一旦漏掉就是死循环而存储过程的死循环会一直占着数据库连接不放直到你手动KILL掉那个会话。我在测试环境被这个坑坑过一次一个写错的循环把连接池占满整个应用开始报连接超时看起来像数据库挂了实际是一个SET v_i v_i 1写在了IF分支里没走到。3.4 NULL 传染CONCAT 和算术里的隐形炸弹NULL在所有表达式里都有传染性这点在存储过程里尤其容易出问题因为变量很多都是有值就用、没值就空着的状态。-- 只要 v_addr 是 NULL整个结果就是 NULL SET v_msg CONCAT(用户:, v_name, 地址:, v_addr);修正方式是用IFNULL或者COALESCE把可能的NULL兜住SET v_msg CONCAT(用户:, IFNULL(v_name, ), 地址:, IFNULL(v_addr, 未填写));算术同理v_total NULL还是NULL。所以我在过程里给所有可能为空的业务变量都套了IFNULL宁可多写几个字符也不要事后去追一个NULL是怎么传出来的。提示如果你发现写进日志表的字段莫名其妙变成NULL第一反应就去找CONCAT的参数里有没有未初始化或者查询未命中的变量。4. 作用域这只手画出来的圈嵌套块与变量生命周期4.1 BEGIN...END 是作用域的边界不是装饰在 MySQL 里BEGIN...END不只是把语句包起来它同时定义了一个作用域。你在某个块开头DECLARE的变量只在这个块以及它内部的嵌套块里可见。块执行结束变量就销毁了。BEGIN DECLARE v_outer INT DEFAULT 1; BEGIN DECLARE v_inner INT DEFAULT 2; SELECT v_outer v_inner; -- 合法内层能看见外层 END; SELECT v_inner; -- 报错 1327外层看不见内层的变量 END;这个规则本身很好理解但它有个实际影响嵌套块越多你要追的变量作用域就越复杂。所以我在写过程时有个习惯——尽量把块拍平除非确实需要一块独立的异常处理逻辑否则不轻易嵌套。拍平之后所有DECLARE都在最外层一眼能看全。4.2 嵌套块里的同名变量遮蔽这件事尽量别碰如果内层块声明了和外层同名的变量MySQL 会把它们当成两个独立的变量内层那个在块内生效外层的不受影响。这跟很多编程语言里的变量遮蔽行为是一致的。BEGIN DECLARE v_x INT DEFAULT 1; BEGIN DECLARE v_x INT DEFAULT 2; -- 内层新变量遮蔽外层 SELECT v_x; -- 得到 2 END; SELECT v_x; -- 得到 1 END;功能上没问题但可读性上是灾难。同一个名字在同一段代码里指两个东西半年后你自己回头看都要愣一下。更麻烦的是不同数据库对同名遮蔽的处理并不完全一致Oracle 和 SQL Server 的表现就跟 MySQL 有差别跨库迁移时这类代码最先出问题。所以我的立场很明确同名遮蔽在存储过程里属于应当禁止的写法无论是自己写还是 code review看到就改。4.3 过程结束变量就没了调试为什么要靠会话变量和临时表局部变量最大的缺点是它只活在过程执行期间。CALL一旦返回变量全部销毁你没有任何办法在外部查看它们的值。这在调试时非常不友好——过程算出来的中间结果对不对全靠猜。解决办法有三个按侵入性从低到高排列第一用SELECT把中间值直接输出。在开发阶段在过程里插一句SELECT v_cnt AS debug_cnt, v_msg AS debug_msg;CALL的时候会直接把结果集返回到客户端。简单粗暴但要注意在正式环境里这会给调用方多返回一个结果集很多 ORM 框架会因此报错所以上线前必须删干净。第二用用户会话变量把中间值带出来。在过程里写SET dbg_cnt v_cnt;过程执行完之后在同一个连接里SELECT dbg_cnt;就能看到。它不影响过程的结果集是比较干净的调试手段。第三写临时表或者日志表。需要观察循环每一轮的值时这个最管用。把每一轮的关键变量插进临时表执行完再查。临时表还有个好处是会话隔离不会污染正式数据。CREATE TEMPORARY TABLE IF NOT EXISTS tmp_debug( step_no INT, step_desc VARCHAR(128), step_val VARCHAR(256) ); -- 循环内部 INSERT INTO tmp_debug VALUES (v_i, loop, CONCAT(uid, v_uid, , amt, v_amt));注意过程里执行 DDL比如CREATE TEMPORARY TABLE会触发隐式提交如果外部有事务事务边界会被打断。用在调试上问题不大用在生产逻辑里要非常谨慎。4.4 顺带看一眼别的语言局部变量这个概念是相通的理解存储过程的变量作用域最快的办法是拿你熟悉的语言对照一下因为它们背后的模型其实一样作用域由代码块划定生命周期跟着块走。C 里的局部变量被{}圈住出了大括号就析构TypeScript 里的declare global走的是完全相反的方向它是声明合并、给全局命名空间打补丁跟局部正好是两个极端Python 没有块级作用域函数即作用域所以for循环里定义的变量在函数内还能用。MySQL 存储过程更接近 C 那种块级作用域的模型只不过它不允许你在块的中间声明所有声明必须挤在块的开头。这个类比的实用价值在于当你要把一个过程拆成嵌套块的时候先想想如果是 C 你会不会在这个位置开一对大括号如果不会存储过程里也别开。5. 实战用局部变量串起一个用户订单汇总过程理论说完了下面给一个能直接跑通的完整例子。这个例子覆盖了DECLARE、DEFAULT、SET、SELECT ... INTO、IF、游标循环、HANDLER是日常开发里出现频率最高的组合。5.1 建表和造数据DROP TABLE IF EXISTS order_detail; CREATE TABLE order_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, -- 0 未支付 1 已支付 2 已取消 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_status (user_id, status) ); INSERT INTO order_detail (user_id, amount, status) VALUES (1001, 199.00, 1), (1001, 88.50, 1), (1001, 12.00, 0), (1002, 500.00, 1), (1002, 60.00, 2), (1003, 30.00, 0), (1003, 45.00, 1);5.2 汇总过程逐段拆解DROP PROCEDURE IF EXISTS sp_user_order_summary; DELIMITER $$ CREATE PROCEDURE sp_user_order_summary( IN p_user_id BIGINT, OUT p_paid_cnt INT, OUT p_paid_amt DECIMAL(12,2) ) BEGIN -- 声明区全部变量集中在最前面一行一个全部带默认值 DECLARE v_paid_cnt INT DEFAULT 0; DECLARE v_paid_amt DECIMAL(12,2) DEFAULT 0.00; DECLARE v_last_at DATETIME DEFAULT NULL; DECLARE v_msg VARCHAR(256) DEFAULT ; -- 取值用聚合函数永远返回一行天然规避零行问题 SELECT COUNT(*), IFNULL(SUM(amount), 0.00), MAX(created_at) INTO v_paid_cnt, v_paid_amt, v_last_at FROM order_detail WHERE user_id p_user_id AND status 1; -- 注意这里是列名我的变量叫 v_xxx不会撞 -- 分支处理 IF v_paid_cnt 0 THEN SET v_msg CONCAT(用户 , p_user_id, 暂无已支付订单); ELSE SET v_msg CONCAT(用户 , p_user_id, 已支付 , v_paid_cnt, 笔合计 , v_paid_amt, 最近一笔 , IFNULL(DATE_FORMAT(v_last_at, %Y-%m-%d), 无)); END IF; -- 出口把局部变量的值交给 OUT 参数 SET p_paid_cnt v_paid_cnt; SET p_paid_amt v_paid_amt; SELECT v_msg AS summary; END$$ DELIMITER ;这段代码里有几个刻意的设计选择值得逐条解释。为什么用聚合而不是取单行如果写成SELECT amount INTO v_amt FROM order_detail WHERE user_id p_user_id AND status 1一旦这个用户有两笔已支付订单立刻报 1172。用COUNT和SUM这种聚合写法无论有几行都只返回一行代码的健壮性直接上一个台阶。为什么变量全部带DEFAULT这是防止零行警告带来脏值的最后一道防线。即使某个分支没走到变量也是确定的初始值不会出现NULL满屏飞。为什么最后要SET p_paid_cnt v_paid_cnt因为局部变量不能直接对外输出必须显式赋给OUT参数。这一步看着啰嗦但它让哪些值是对外承诺的变得非常明确。IFNULL(DATE_FORMAT(v_last_at, %Y-%m-%d), 无)这层包裹是干什么的DATE_FORMAT传入NULL返回NULLCONCAT遇到NULL整个串就变NULL。所以必须先把NULL兜成一个可读的字符串。这个细节如果不注意你会发现日志里的 summary 字段整条都是空的。5.3 游标循环里最标准的标志位写法需要逐行处理的时候游标加标志位是标准配置。这里的关键是DECLARE CONTINUE HANDLER FOR NOT FOUND必须放在变量和游标之后DROP PROCEDURE IF EXISTS sp_calc_user_totals; DELIMITER $$ CREATE PROCEDURE sp_calc_user_totals() BEGIN DECLARE v_done TINYINT DEFAULT 0; -- 标志位 DECLARE v_uid BIGINT DEFAULT NULL; DECLARE v_amt DECIMAL(12,2) DEFAULT 0.00; DECLARE cur_user CURSOR FOR SELECT user_id, SUM(amount) FROM order_detail WHERE status 1 GROUP BY user_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; DROP TEMPORARY TABLE IF EXISTS tmp_user_totals; CREATE TEMPORARY TABLE tmp_user_totals( user_id BIGINT, total_amount DECIMAL(12,2) ); OPEN cur_user; read_loop: LOOP FETCH cur_user INTO v_uid, v_amt; IF v_done 1 THEN LEAVE read_loop; END IF; INSERT INTO tmp_user_totals VALUES (v_uid, v_amt); END LOOP; CLOSE cur_user; SELECT * FROM tmp_user_totals ORDER BY total_amount DESC; END$$ DELIMITER ;这里有三个非常容易翻车的细节我一个个说。第一FETCH必须紧跟在LOOP开头IF v_done判断必须在FETCH之后。顺序反了的话第一次FETCH拿到的数据会因为标志位还是 0 而被正常处理最后一次FETCH没数据时标志位变 1但你已经把上一轮的数据又处理了一遍——结果是最后一行被重复写入。这个 bug 在数据量小的时候根本看不出来我当年是在一次批量跑几万行的场景下才发现重了一条。第二v_done这个标志位名字不能叫done。倒不是说done一定是保留字而是这类通用词最容易和表里的列名撞。加前缀是零成本的保险。第三LEAVE read_loop里的标签必须和LOOP前面写的标签一致。标签是可以自定义的read_loop、cur_loop都行但你写了什么就要LEAVE什么否则报LEAVE with no matching label。5.4 调用、验证与异常路径测试CALL sp_user_order_summary(1001, cnt, amt); SELECT cnt, amt; -- 2, 287.50 CALL sp_user_order_summary(9999, cnt, amt); SELECT cnt, amt; -- 0, 0.00 CALL sp_calc_user_totals();异常路径一定要专门测这是很多人的盲区。至少要覆盖三种情况用户不存在验证零行处理、用户只有未支付订单验证分支逻辑、金额为小数验证DECIMAL精度没在中间环节被截断。我踩过一次DECIMAL(12,2)的坑中间变量声明成了DECIMAL(12,0)导致小数点被四舍五入最后汇总金额和明细对不上查了很久才定位到是变量类型的问题。提示声明变量的时候类型直接照抄源字段的定义。源字段是DECIMAL(10,2)你的汇总变量给DECIMAL(12,2)宽一点防溢出就够了别凭感觉给类型。6. 换个数据库怎么写Oracle 与 SQL Server 的声明对照同一个需求在不同数据库里的写法差异不小尤其是声明放在哪里这条规则几乎每家都不一样。搞混了会写出看似正确实际报错的代码。6.1 Oracle声明区在 BEGIN 之前还能锚定表类型Oracle 的存储过程里变量声明区位于IS或者AS之后、BEGIN之前语法上更接近先声明后执行的直觉CREATE OR REPLACE PROCEDURE sp_demo(p_id IN NUMBER, p_msg OUT VARCHAR2) IS v_cnt NUMBER(10) : 0; v_amt NUMBER(12,2) DEFAULT 0; v_name VARCHAR2(100) : 未知; v_tmp order_detail.amount%TYPE; -- 锚定列类型 v_row order_detail%ROWTYPE; -- 锚定整行 c_limit CONSTANT NUMBER : 100; -- 常量 BEGIN SELECT COUNT(*) INTO v_cnt FROM order_detail WHERE user_id p_id; p_msg : 共 || v_cnt || 条; END;Oracle 有两个 MySQL 里没有的能力特别值得用。第一是%TYPE直接把变量的类型锚定到某个表的某个字段上表结构改了、字段类型变了变量类型自动跟着变不用手工同步。第二是%ROWTYPE可以声明一个装整行记录的变量。这两个特性在处理宽表的时候能省掉大量重复的类型声明工作。另外注意赋值符号Oracle 用:MySQL 用或者SET。这个差异在两边来回切的时候特别容易写混。6.2 SQL Server开头作用域是整个批处理SQL ServerT-SQL的局部变量必须以开头这一点和 MySQL 的用户会话变量长得一样但含义完全不同——在 SQL Server 里x就是名副其实的局部变量。DECLARE cnt INT 0; DECLARE amt DECIMAL(12,2) 0.00; SELECT cnt COUNT(*), amt ISNULL(SUM(amount), 0) FROM order_detail WHERE user_id 1001 AND status 1; SELECT cnt AS cnt, amt AS amt; IF cnt 0 PRINT 没有已支付订单;最大的差异在作用域模型T-SQL 里BEGIN...END只是一组语句的打包不构成变量作用域。也就是说在BEGIN...END内部声明的变量块结束之后仍然可以使用。这跟 MySQL 完全不同直接从 MySQL 迁过来的人最容易在这里判断失误以为出块就失效了。另外 T-SQL 还支持表变量DECLARE t TABLE(...)把临时结果集装在变量里这点也挺好用。6.3 三家放在一张表里对照对比项MySQLOracleSQL Server声明位置BEGIN之后的第一段IS/AS之后、BEGIN之前批处理内任意位置先声明后用声明关键字DECLARE v_name type DEFAULT ...v_name type : ...DECLARE name type ...作用域边界BEGIN...END块BEGIN...END块整个批处理或过程块不隔离类型锚定不支持%TYPE/%ROWTYPE不支持常量声明不支持显式常量CONSTANT不支持赋值方式SET/SELECT ... INTO:/SELECT ... INTOSET/SELECT v ...这张表我建议存下来做跨库迁移的时候对着看一眼比翻文档快。尤其是作用域边界这一行是三家差异最大、也最容易写错的地方。7. 报错对照表与我在用的调试三板斧7.1 常见报错速查写存储过程时遇到的错误八成集中在这几个。我按报错码、现象、根因、处理整理成表出错的时候先查表再动手比盲目改代码高效得多。报错码关键信息最可能的根因处理方式1064Syntax errorDECLARE写在可执行语句之后或声明顺序错了把所有声明挪到块开头按变量、游标、处理器排序1327Undeclared variable变量没声明就使用或拼写、大小写不一致检查声明区变量名统一小写加前缀1172Result consisted of more than one rowSELECT ... INTO命中多行改用聚合函数或确认唯一性后加LIMIT 11329No data - zero rows fetchedSELECT ... INTO零行只报警告变量必须给DEFAULT优先用聚合1054Unknown column变量撞名被当列解析或表名写错变量统一加v_前缀1305PROCEDURE does not exist库选错或名字拼错USE正确的库检查DELIMITER是否配对1265Data truncated变量长度不够内容被截断加大VARCHAR长度或提前LEFT()截取处理器声明顺序错误HANDLER写在CURSOR前面也会报 1064但它和普通语法错误的提示一模一样看不出区别所以很容易被忽略。遇到 1064 又确认语法没问题的时候先去看声明顺序。7.2 调试三板斧从快到慢各有适用场景第一板斧SELECT直接输出。开发阶段最快的手段在关键位置插一句把变量选出来CALL一下立刻看到值。缺点是会多返回结果集上线前必须清理所以我习惯在调试语句前面加个-- DEBUG注释方便全局搜索清理。第二板斧用户会话变量接力。在过程里写SET dbg_x v_x;过程跑完之后在同一个连接里查dbg_x。好处是不影响结果集坏处是必须保证在同一个连接里查用连接池的话换个连接就查不到了。第三板斧临时表记录全过程。需要看循环每一轮的中间值时用这个。把所有关键变量按轮次插进临时表跑完一次性查出来能非常直观地看到数据是怎么一步步变化的。这个手段在排查某一行数据被重复处理这类问题时几乎无可替代。另外别忘了SHOW WARNINGS;。SELECT ... INTO零行这类问题不会报错只写进警告里CALL之后顺手执行一次很多隐性数据问题当场就能发现。7.3 命名规范与几条我坚持的团队约定踩了这么多坑之后我这边固定下来几条约定新项目一律照做老项目逐步改造参数加p_局部变量加v_游标加cur_标志位加done_临时表加tmp_调试变量加dbg_变量名全部小写因为 MySQL 的局部变量名不区分大小写v_Name和v_name是同一个东西混着写只会让人困惑声明区集中在块开头一行一个变量类型和DEFAULT对齐写方便扫读所有变量都给DEFAULT不给NULL留下解释空间从表里取值优先用聚合函数加IFNULL尽量不用取单行的写法变量类型照抄源字段汇总类往上放宽一档最后再分享一个小技巧。写复杂过程之前我会先单独写一小段声明区把这次要用到的所有变量列出来包括每个变量的用途和初始值然后再动手写逻辑。这个过程花不了五分钟但能让你在写的时候一直清楚我手里有哪些状态大大减少漏声明、拼错名、类型不匹配这类低级错误。存储过程这种调试成本高的东西前期多想两分钟后期少查两小时这笔账怎么算都划算。 MySQL存储过程DECLARE局部变量：声明、作用域、赋值与避坑
凌晨一点多盯着一个跑了三个月的对账存储过程输出结果总是比实际少一条。翻来覆去改 SQL、换索引、重跑数据最后发现问题既不在 SQL 也不在数据——我在过程里声明了一个叫status的局部变量而表里正好也有一列叫status。语句里那个标识符被 MySQL 当成了列名我的变量从头到尾没参与过比较。这类事故在存储过程开发里非常典型而它唯一的根因就是对 DECLARE 定义局部变量的规则理解得太浅。这一篇专门聊 MySQL 存储过程里的DECLARE也就是局部变量怎么声明、声明在哪里、怎么赋值、作用域多大、什么时候会翻车。它是存储过程系列的第三篇前面聊过整体结构和参数这一篇只聚焦一件事过程内部那些用来暂存中间结果、当计数器、当标志位的变量到底该怎么用。写这篇文章的初衷很简单我在带人的时候发现新手写存储过程最常犯的错不是逻辑写错而是三个地方声明位置放错、赋值方式选错、变量名和列名撞车。这三个坑单独看都不致命凑在一起就是查半天的疑难杂症。所以下面我把语法规则、赋值取舍、作用域边界、调试手法全部拆开讲配上能直接复制跑的完整例子同时把 Oracle 和 SQL Server 的写法放在一起做对照方便你在不同数据库之间切换时不至于手忙脚乱。1. 从一次结果少一条的排查说起DECLARE 到底解决什么问题1.1 事故复盘变量名和列名撞车之后会发生什么那次排查的过程值得完整讲一遍因为它是理解局部变量优先级的活教材。我的过程简化后大致是这样CREATE PROCEDURE sp_check(IN p_id BIGINT) BEGIN DECLARE status INT DEFAULT 1; -- 想用它表示已支付 SELECT COUNT(*) FROM order_detail WHERE user_id p_id AND status status; -- 本意是 status 1 END;status status这个写法在人的直觉里是列等于我的变量但在 MySQL 的解析器眼里WHERE子句里的标识符在很多场景下会优先绑定到列名于是这个条件变成了恒等式等于没过滤COUNT 出来的是全部订单数。后来我改成筛status 1又发现数字对不上因为我的逻辑里其实需要区分已支付和未支付兜了一圈才回到变量本身。这件事给我的教训只有一条但非常硬局部变量名必须和 SQL 语句里出现的列名彻底隔离。MySQL 官方手册也专门提醒过命名冲突的问题因为它的解析优先级跟你想要的往往不一致。行业里最省心的做法是加前缀参数用p_局部变量用v_游标用cur_标志位用done_。加完之后v_status status这种写法一眼就能看出谁是谁撞名的概率直接归零。提示如果你的库里已经有大量不带前缀的老过程别急着改。先做一次全局搜索把所有DECLARE出来的变量名和过程里出现的列名做交叉比对只改真正撞名的那几个改动面能小一个数量级。1.2 局部变量、参数、用户会话变量三个特别容易装错东西的口袋刚接触存储过程的人最容易混的是这三种存东西的地方。它们看起来都能装值但作用域、生命周期、能否跨过程访问完全不同混用会写出很难维护的代码。维度局部变量DECLARE存储过程参数IN/OUT/INOUT用户会话变量x声明位置BEGIN...END块的开头过程定义的参数列表随用随建无需声明命名要求无前缀建议v_无前缀建议p_必须带作用域所在块及其嵌套块整个过程体当前数据库连接跨过程可见生命周期块执行结束即销毁过程结束即销毁连接断开才消失事务回滚的影响不受影响值不回退不受影响不受影响能否对外输出不能必须赋给 OUT 参数可以OUT/INOUT可以调用方直接读这张表里最值得记住的是最后两行。第一变量不是表数据ROLLBACK不会把它恢复到旧值所以事务回滚之后如果还要继续走逻辑一定要手动重置变量或者直接RETURN。第二局部变量天生就是过程内部的事想让调用方拿到结果必须显式赋值给OUT参数。我见过有人在过程里用result一路传值出去功能是能跑但两个过程同时用result就会互相污染这类 bug 在并发场景下极难复现。1.3 先跑一个二十行的最小例子在钻语法细节之前先把最小可运行的骨架跑通后面的规则才有地方落。下面这段可以直接在 MySQL 8.0 或者 5.7 上执行DROP PROCEDURE IF EXISTS sp_min_demo; DELIMITER $$ CREATE PROCEDURE sp_min_demo(IN p_n INT, OUT p_msg VARCHAR(64)) BEGIN DECLARE v_total INT DEFAULT 0; DECLARE v_text VARCHAR(64) DEFAULT ; SET v_total p_n * 2; SET v_text CONCAT(输入 , p_n, 翻倍后 , v_total); SET p_msg v_text; END$$ DELIMITER ; CALL sp_min_demo(21, msg); SELECT msg; -- 输入 21翻倍后 42这里有三件事值得注意DECLARE出现在BEGIN之后的第一段任何可执行语句之前每个变量的类型必须写全VARCHAR后面的长度不能省SET v_total p_n * 2里可以直接引用参数p_n因为参数的作用域覆盖整个过程体。把这个骨架记住后面所有规则都是它的展开。2. DECLARE 的语法骨架与位置不对就报 1064的硬规矩2.1 完整语法形式与 DEFAULT 的正确用法MySQL 里声明局部变量的语法是DECLARE var_name [, var_name] ... type [DEFAULT value]方括号里的DEFAULT是可选的但我强烈建议你每次都写。原因很实际一个没有DEFAULT的变量初始值是NULL不是0也不是空字符串。很多人下意识觉得DECLARE v_cnt INT;之后v_cnt从 0 开始然后写SET v_cnt v_cnt 1结果得到的是NULL而且NULL会沿着整个表达式链条传染下去最后CONCAT出来的字符串是NULL写进表里也是一片NULL排查起来相当费劲。DEFAULT后面跟字面量最稳妥。想用表达式或者查询结果做初始值我的建议是拆成两步先DECLARE ... DEFAULT NULL紧接着用SET或者SELECT ... INTO赋值。这样语义清晰也不依赖具体版本对DEFAULT表达式的支持程度。注意局部变量的声明里写NOT NULL是行不通的。想要保证非空只能靠DEFAULT给一个兜底值再在业务逻辑里自己做校验别指望数据库帮你拦。2.2 声明顺序变量、条件、游标、处理器不能乱这是DECLARE最容易踩的语法坑也是报 1064 的高频原因。在一个BEGIN...END块里所有DECLARE语句必须集中在块的开头而且不同类型之间还有先后顺序变量声明DECLARE var_name type和条件声明DECLARE condition_name CONDITION FOR ...游标声明DECLARE cur_name CURSOR FOR ...处理器声明DECLARE ... HANDLER FOR ...顺序错了、或者中间插了一条SET、SELECT这样的可执行语句MySQL 直接抛语法错误。这个规则的现实影响是你不能用到哪声明到哪。写长过程的时候习惯上要先把所有变量在开头列清楚再往下写逻辑。刚开始会觉得别扭写习惯了反而有好处——打开一个过程先看声明区就能大致知道这个过程的状态变量有哪些代码可读性明显提升。-- 正确顺序示例 BEGIN DECLARE v_done TINYINT DEFAULT 0; -- 1. 变量 DECLARE v_uid BIGINT DEFAULT NULL; -- 1. 变量 DECLARE cur_u CURSOR FOR SELECT id FROM t_u; -- 2. 游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; -- 3. 处理器 -- 下面是可执行语句 END;2.3 类型选择的坑VARCHAR 必须带长度金额别用 INT局部变量的类型可以是 MySQL 支持的大部分标量类型INT、BIGINT、TINYINT、DECIMAL(p,s)、CHAR(n)、VARCHAR(n)、DATE、DATETIME、TIMESTAMP等。选类型的时候有几个坑值得单独说。VARCHAR和CHAR在声明时必须带长度写成DECLARE v_s VARCHAR;会直接报语法错误。长度怎么定我的经验是往宽了给一点比如拼接日志用VARCHAR(512)比抠着算VARCHAR(64)省心因为一旦内容超长MySQL 在非严格模式下会截断你会得到一条看起来正常但少了尾巴的日志比直接报错更难查。金额类变量绝对不要用INT或者BIGINT。用整数存金额意味着你自己要维护乘以 100的小数位约定稍不留神就少乘或多乘一次。直接用DECIMAL(12,2)类型自带精度SUM出来也不会出现浮点误差。同理时间类结果用DATETIME接收别用VARCHAR装字符串不然想比大小还得先转换。TINYINT是标志位的标准选择DECLARE v_done TINYINT DEFAULT 0;这一行几乎会出现在每一个游标循环里后面第 5 节会展开。2.4 多个变量写一条还是一条一个官方语法上DECLARE a, b INT DEFAULT 0;这种写法是可以的多个变量共用同一个类型和一个默认值。但我在实际项目里从来不用这种写法理由有三个。第一默认值不同就得拆开。一个要DEFAULT 0、一个要DEFAULT NULL、一个要DEFAULT 写不到一条语句里还不如一开始就养成一条一个的习惯。第二类型不同更得拆开。第三也是最实际的一点一行一个变量出问题的时候git diff看得清清楚楚多人协作时也不会因为一个人改了默认值影响到同一行里的其他变量。-- 我推荐的写法一行一个声明区对齐 DECLARE v_cnt INT DEFAULT 0; DECLARE v_amount DECIMAL(12,2) DEFAULT 0.00; DECLARE v_last_at DATETIME DEFAULT NULL; DECLARE v_msg VARCHAR(256) DEFAULT ;提示如果你手里的版本对多变量声明直接报语法错误不用惊讶拆成多行就行这对业务逻辑没有任何影响。语法上的便利不值得为了省几行去冒险。3. 赋值两条路SET 与 SELECT ... INTO 的分工3.1 SET 的确定性表达式、多变量、不带 FROM 的 SELECTSET是最直白也最安全的赋值方式右侧可以是一个完整的表达式可以引用参数、其他局部变量也可以调用内置函数。它支持一次给多个变量赋值SET v_a 1, v_b hello, v_c NOW();SET的特点是一定成功只要表达式本身合法不会因为数据情况抛异常。所以对于计算类、拼接类、计数器类的赋值我一律用SET。另外有个小技巧很多人不知道MySQL 允许不带FROM的SELECT直接赋值比如SELECT 1 1 INTO v_total;或者SELECT NOW() INTO v_time;。这在你需要调用函数又不想写SET的时候挺好用。但要注意这种写法和SELECT ... INTO是同一套机制所以下一小节讲的坑它同样适用——虽然不带FROM的查询必然返回一行风险很低。3.2 SELECT ... INTO 的三个经典坑多行、零行、撞名从表里取值赋给变量最自然的写法就是SELECT加INTOSELECT COUNT(*), IFNULL(SUM(amount), 0.00) INTO v_cnt, v_amount FROM order_detail WHERE user_id p_user_id AND status 1;这段代码看起来没问题但它藏着三个必须提前知道的坑。坑一命中多行直接报错。如果查询返回两行以上MySQL 抛 1172Result consisted of more than one row。这在按主键查单行的时候不会发生但一旦WHERE条件不够精确就会炸。解决办法有两个确认唯一性或者在末尾加LIMIT 1。加LIMIT 1能止住报错但你要清楚自己在随便取一行语义上是否可接受。坑二零行只报警告不报错。查询没命中任何行时MySQL 只抛一个 warning1329No data变量保持原来的值。这个特性非常危险因为程序会继续往下跑用的是变量里上一次的值或者默认值。所以我前面反复强调DEFAULT要给DECLARE v_cnt INT DEFAULT 0;至少能保证兜底是 0 而不是NULL。更稳的做法是尽量用聚合函数取值而不是取单行。SELECT COUNT(*), SUM(amount) INTO ...这种写法因为聚合函数的存在永远返回一行天然绕开了零行问题。SUM在没有匹配行时返回NULL所以外面套一层IFNULL(SUM(amount), 0.00)就彻底安全了。这个模式我在实际项目里用得最多几乎可以当成标准模板。坑三变量名和列名撞车。这是第 1 节复盘的那个问题。SELECT的目标列位置也就是INTO前面的那些不会歧义因为INTO后面明确是变量。但在WHERE、ORDER BY、HAVING里引用标识符时同名的列会优先被匹配。所以变量名加前缀这件事不是风格问题是正确性问题。3.3 在 IF、WHILE、REPEAT 里当计数器、累加器和标志位局部变量在流程控制语句里才真正体现出价值。三种最典型的用法累加器用于把多行数据汇总成一个值DECLARE v_sum DECIMAL(12,2) DEFAULT 0.00; -- 循环内部 SET v_sum v_sum v_row_amount;计数器用于控制循环次数DECLARE v_i INT DEFAULT 0; WHILE v_i 10 DO SET v_i v_i 1; -- 业务逻辑 END WHILE;标志位用于游标循环里判断是否读完这是存储过程里最经典的模式第 5 节会给完整代码。这里有个必须提的注意事项WHILE循环里给计数器加 1 这行千万别漏一旦漏掉就是死循环而存储过程的死循环会一直占着数据库连接不放直到你手动KILL掉那个会话。我在测试环境被这个坑坑过一次一个写错的循环把连接池占满整个应用开始报连接超时看起来像数据库挂了实际是一个SET v_i v_i 1写在了IF分支里没走到。3.4 NULL 传染CONCAT 和算术里的隐形炸弹NULL在所有表达式里都有传染性这点在存储过程里尤其容易出问题因为变量很多都是有值就用、没值就空着的状态。-- 只要 v_addr 是 NULL整个结果就是 NULL SET v_msg CONCAT(用户:, v_name, 地址:, v_addr);修正方式是用IFNULL或者COALESCE把可能的NULL兜住SET v_msg CONCAT(用户:, IFNULL(v_name, ), 地址:, IFNULL(v_addr, 未填写));算术同理v_total NULL还是NULL。所以我在过程里给所有可能为空的业务变量都套了IFNULL宁可多写几个字符也不要事后去追一个NULL是怎么传出来的。提示如果你发现写进日志表的字段莫名其妙变成NULL第一反应就去找CONCAT的参数里有没有未初始化或者查询未命中的变量。4. 作用域这只手画出来的圈嵌套块与变量生命周期4.1 BEGIN...END 是作用域的边界不是装饰在 MySQL 里BEGIN...END不只是把语句包起来它同时定义了一个作用域。你在某个块开头DECLARE的变量只在这个块以及它内部的嵌套块里可见。块执行结束变量就销毁了。BEGIN DECLARE v_outer INT DEFAULT 1; BEGIN DECLARE v_inner INT DEFAULT 2; SELECT v_outer v_inner; -- 合法内层能看见外层 END; SELECT v_inner; -- 报错 1327外层看不见内层的变量 END;这个规则本身很好理解但它有个实际影响嵌套块越多你要追的变量作用域就越复杂。所以我在写过程时有个习惯——尽量把块拍平除非确实需要一块独立的异常处理逻辑否则不轻易嵌套。拍平之后所有DECLARE都在最外层一眼能看全。4.2 嵌套块里的同名变量遮蔽这件事尽量别碰如果内层块声明了和外层同名的变量MySQL 会把它们当成两个独立的变量内层那个在块内生效外层的不受影响。这跟很多编程语言里的变量遮蔽行为是一致的。BEGIN DECLARE v_x INT DEFAULT 1; BEGIN DECLARE v_x INT DEFAULT 2; -- 内层新变量遮蔽外层 SELECT v_x; -- 得到 2 END; SELECT v_x; -- 得到 1 END;功能上没问题但可读性上是灾难。同一个名字在同一段代码里指两个东西半年后你自己回头看都要愣一下。更麻烦的是不同数据库对同名遮蔽的处理并不完全一致Oracle 和 SQL Server 的表现就跟 MySQL 有差别跨库迁移时这类代码最先出问题。所以我的立场很明确同名遮蔽在存储过程里属于应当禁止的写法无论是自己写还是 code review看到就改。4.3 过程结束变量就没了调试为什么要靠会话变量和临时表局部变量最大的缺点是它只活在过程执行期间。CALL一旦返回变量全部销毁你没有任何办法在外部查看它们的值。这在调试时非常不友好——过程算出来的中间结果对不对全靠猜。解决办法有三个按侵入性从低到高排列第一用SELECT把中间值直接输出。在开发阶段在过程里插一句SELECT v_cnt AS debug_cnt, v_msg AS debug_msg;CALL的时候会直接把结果集返回到客户端。简单粗暴但要注意在正式环境里这会给调用方多返回一个结果集很多 ORM 框架会因此报错所以上线前必须删干净。第二用用户会话变量把中间值带出来。在过程里写SET dbg_cnt v_cnt;过程执行完之后在同一个连接里SELECT dbg_cnt;就能看到。它不影响过程的结果集是比较干净的调试手段。第三写临时表或者日志表。需要观察循环每一轮的值时这个最管用。把每一轮的关键变量插进临时表执行完再查。临时表还有个好处是会话隔离不会污染正式数据。CREATE TEMPORARY TABLE IF NOT EXISTS tmp_debug( step_no INT, step_desc VARCHAR(128), step_val VARCHAR(256) ); -- 循环内部 INSERT INTO tmp_debug VALUES (v_i, loop, CONCAT(uid, v_uid, , amt, v_amt));注意过程里执行 DDL比如CREATE TEMPORARY TABLE会触发隐式提交如果外部有事务事务边界会被打断。用在调试上问题不大用在生产逻辑里要非常谨慎。4.4 顺带看一眼别的语言局部变量这个概念是相通的理解存储过程的变量作用域最快的办法是拿你熟悉的语言对照一下因为它们背后的模型其实一样作用域由代码块划定生命周期跟着块走。C 里的局部变量被{}圈住出了大括号就析构TypeScript 里的declare global走的是完全相反的方向它是声明合并、给全局命名空间打补丁跟局部正好是两个极端Python 没有块级作用域函数即作用域所以for循环里定义的变量在函数内还能用。MySQL 存储过程更接近 C 那种块级作用域的模型只不过它不允许你在块的中间声明所有声明必须挤在块的开头。这个类比的实用价值在于当你要把一个过程拆成嵌套块的时候先想想如果是 C 你会不会在这个位置开一对大括号如果不会存储过程里也别开。5. 实战用局部变量串起一个用户订单汇总过程理论说完了下面给一个能直接跑通的完整例子。这个例子覆盖了DECLARE、DEFAULT、SET、SELECT ... INTO、IF、游标循环、HANDLER是日常开发里出现频率最高的组合。5.1 建表和造数据DROP TABLE IF EXISTS order_detail; CREATE TABLE order_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, -- 0 未支付 1 已支付 2 已取消 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_status (user_id, status) ); INSERT INTO order_detail (user_id, amount, status) VALUES (1001, 199.00, 1), (1001, 88.50, 1), (1001, 12.00, 0), (1002, 500.00, 1), (1002, 60.00, 2), (1003, 30.00, 0), (1003, 45.00, 1);5.2 汇总过程逐段拆解DROP PROCEDURE IF EXISTS sp_user_order_summary; DELIMITER $$ CREATE PROCEDURE sp_user_order_summary( IN p_user_id BIGINT, OUT p_paid_cnt INT, OUT p_paid_amt DECIMAL(12,2) ) BEGIN -- 声明区全部变量集中在最前面一行一个全部带默认值 DECLARE v_paid_cnt INT DEFAULT 0; DECLARE v_paid_amt DECIMAL(12,2) DEFAULT 0.00; DECLARE v_last_at DATETIME DEFAULT NULL; DECLARE v_msg VARCHAR(256) DEFAULT ; -- 取值用聚合函数永远返回一行天然规避零行问题 SELECT COUNT(*), IFNULL(SUM(amount), 0.00), MAX(created_at) INTO v_paid_cnt, v_paid_amt, v_last_at FROM order_detail WHERE user_id p_user_id AND status 1; -- 注意这里是列名我的变量叫 v_xxx不会撞 -- 分支处理 IF v_paid_cnt 0 THEN SET v_msg CONCAT(用户 , p_user_id, 暂无已支付订单); ELSE SET v_msg CONCAT(用户 , p_user_id, 已支付 , v_paid_cnt, 笔合计 , v_paid_amt, 最近一笔 , IFNULL(DATE_FORMAT(v_last_at, %Y-%m-%d), 无)); END IF; -- 出口把局部变量的值交给 OUT 参数 SET p_paid_cnt v_paid_cnt; SET p_paid_amt v_paid_amt; SELECT v_msg AS summary; END$$ DELIMITER ;这段代码里有几个刻意的设计选择值得逐条解释。为什么用聚合而不是取单行如果写成SELECT amount INTO v_amt FROM order_detail WHERE user_id p_user_id AND status 1一旦这个用户有两笔已支付订单立刻报 1172。用COUNT和SUM这种聚合写法无论有几行都只返回一行代码的健壮性直接上一个台阶。为什么变量全部带DEFAULT这是防止零行警告带来脏值的最后一道防线。即使某个分支没走到变量也是确定的初始值不会出现NULL满屏飞。为什么最后要SET p_paid_cnt v_paid_cnt因为局部变量不能直接对外输出必须显式赋给OUT参数。这一步看着啰嗦但它让哪些值是对外承诺的变得非常明确。IFNULL(DATE_FORMAT(v_last_at, %Y-%m-%d), 无)这层包裹是干什么的DATE_FORMAT传入NULL返回NULLCONCAT遇到NULL整个串就变NULL。所以必须先把NULL兜成一个可读的字符串。这个细节如果不注意你会发现日志里的 summary 字段整条都是空的。5.3 游标循环里最标准的标志位写法需要逐行处理的时候游标加标志位是标准配置。这里的关键是DECLARE CONTINUE HANDLER FOR NOT FOUND必须放在变量和游标之后DROP PROCEDURE IF EXISTS sp_calc_user_totals; DELIMITER $$ CREATE PROCEDURE sp_calc_user_totals() BEGIN DECLARE v_done TINYINT DEFAULT 0; -- 标志位 DECLARE v_uid BIGINT DEFAULT NULL; DECLARE v_amt DECIMAL(12,2) DEFAULT 0.00; DECLARE cur_user CURSOR FOR SELECT user_id, SUM(amount) FROM order_detail WHERE status 1 GROUP BY user_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; DROP TEMPORARY TABLE IF EXISTS tmp_user_totals; CREATE TEMPORARY TABLE tmp_user_totals( user_id BIGINT, total_amount DECIMAL(12,2) ); OPEN cur_user; read_loop: LOOP FETCH cur_user INTO v_uid, v_amt; IF v_done 1 THEN LEAVE read_loop; END IF; INSERT INTO tmp_user_totals VALUES (v_uid, v_amt); END LOOP; CLOSE cur_user; SELECT * FROM tmp_user_totals ORDER BY total_amount DESC; END$$ DELIMITER ;这里有三个非常容易翻车的细节我一个个说。第一FETCH必须紧跟在LOOP开头IF v_done判断必须在FETCH之后。顺序反了的话第一次FETCH拿到的数据会因为标志位还是 0 而被正常处理最后一次FETCH没数据时标志位变 1但你已经把上一轮的数据又处理了一遍——结果是最后一行被重复写入。这个 bug 在数据量小的时候根本看不出来我当年是在一次批量跑几万行的场景下才发现重了一条。第二v_done这个标志位名字不能叫done。倒不是说done一定是保留字而是这类通用词最容易和表里的列名撞。加前缀是零成本的保险。第三LEAVE read_loop里的标签必须和LOOP前面写的标签一致。标签是可以自定义的read_loop、cur_loop都行但你写了什么就要LEAVE什么否则报LEAVE with no matching label。5.4 调用、验证与异常路径测试CALL sp_user_order_summary(1001, cnt, amt); SELECT cnt, amt; -- 2, 287.50 CALL sp_user_order_summary(9999, cnt, amt); SELECT cnt, amt; -- 0, 0.00 CALL sp_calc_user_totals();异常路径一定要专门测这是很多人的盲区。至少要覆盖三种情况用户不存在验证零行处理、用户只有未支付订单验证分支逻辑、金额为小数验证DECIMAL精度没在中间环节被截断。我踩过一次DECIMAL(12,2)的坑中间变量声明成了DECIMAL(12,0)导致小数点被四舍五入最后汇总金额和明细对不上查了很久才定位到是变量类型的问题。提示声明变量的时候类型直接照抄源字段的定义。源字段是DECIMAL(10,2)你的汇总变量给DECIMAL(12,2)宽一点防溢出就够了别凭感觉给类型。6. 换个数据库怎么写Oracle 与 SQL Server 的声明对照同一个需求在不同数据库里的写法差异不小尤其是声明放在哪里这条规则几乎每家都不一样。搞混了会写出看似正确实际报错的代码。6.1 Oracle声明区在 BEGIN 之前还能锚定表类型Oracle 的存储过程里变量声明区位于IS或者AS之后、BEGIN之前语法上更接近先声明后执行的直觉CREATE OR REPLACE PROCEDURE sp_demo(p_id IN NUMBER, p_msg OUT VARCHAR2) IS v_cnt NUMBER(10) : 0; v_amt NUMBER(12,2) DEFAULT 0; v_name VARCHAR2(100) : 未知; v_tmp order_detail.amount%TYPE; -- 锚定列类型 v_row order_detail%ROWTYPE; -- 锚定整行 c_limit CONSTANT NUMBER : 100; -- 常量 BEGIN SELECT COUNT(*) INTO v_cnt FROM order_detail WHERE user_id p_id; p_msg : 共 || v_cnt || 条; END;Oracle 有两个 MySQL 里没有的能力特别值得用。第一是%TYPE直接把变量的类型锚定到某个表的某个字段上表结构改了、字段类型变了变量类型自动跟着变不用手工同步。第二是%ROWTYPE可以声明一个装整行记录的变量。这两个特性在处理宽表的时候能省掉大量重复的类型声明工作。另外注意赋值符号Oracle 用:MySQL 用或者SET。这个差异在两边来回切的时候特别容易写混。6.2 SQL Server开头作用域是整个批处理SQL ServerT-SQL的局部变量必须以开头这一点和 MySQL 的用户会话变量长得一样但含义完全不同——在 SQL Server 里x就是名副其实的局部变量。DECLARE cnt INT 0; DECLARE amt DECIMAL(12,2) 0.00; SELECT cnt COUNT(*), amt ISNULL(SUM(amount), 0) FROM order_detail WHERE user_id 1001 AND status 1; SELECT cnt AS cnt, amt AS amt; IF cnt 0 PRINT 没有已支付订单;最大的差异在作用域模型T-SQL 里BEGIN...END只是一组语句的打包不构成变量作用域。也就是说在BEGIN...END内部声明的变量块结束之后仍然可以使用。这跟 MySQL 完全不同直接从 MySQL 迁过来的人最容易在这里判断失误以为出块就失效了。另外 T-SQL 还支持表变量DECLARE t TABLE(...)把临时结果集装在变量里这点也挺好用。6.3 三家放在一张表里对照对比项MySQLOracleSQL Server声明位置BEGIN之后的第一段IS/AS之后、BEGIN之前批处理内任意位置先声明后用声明关键字DECLARE v_name type DEFAULT ...v_name type : ...DECLARE name type ...作用域边界BEGIN...END块BEGIN...END块整个批处理或过程块不隔离类型锚定不支持%TYPE/%ROWTYPE不支持常量声明不支持显式常量CONSTANT不支持赋值方式SET/SELECT ... INTO:/SELECT ... INTOSET/SELECT v ...这张表我建议存下来做跨库迁移的时候对着看一眼比翻文档快。尤其是作用域边界这一行是三家差异最大、也最容易写错的地方。7. 报错对照表与我在用的调试三板斧7.1 常见报错速查写存储过程时遇到的错误八成集中在这几个。我按报错码、现象、根因、处理整理成表出错的时候先查表再动手比盲目改代码高效得多。报错码关键信息最可能的根因处理方式1064Syntax errorDECLARE写在可执行语句之后或声明顺序错了把所有声明挪到块开头按变量、游标、处理器排序1327Undeclared variable变量没声明就使用或拼写、大小写不一致检查声明区变量名统一小写加前缀1172Result consisted of more than one rowSELECT ... INTO命中多行改用聚合函数或确认唯一性后加LIMIT 11329No data - zero rows fetchedSELECT ... INTO零行只报警告变量必须给DEFAULT优先用聚合1054Unknown column变量撞名被当列解析或表名写错变量统一加v_前缀1305PROCEDURE does not exist库选错或名字拼错USE正确的库检查DELIMITER是否配对1265Data truncated变量长度不够内容被截断加大VARCHAR长度或提前LEFT()截取处理器声明顺序错误HANDLER写在CURSOR前面也会报 1064但它和普通语法错误的提示一模一样看不出区别所以很容易被忽略。遇到 1064 又确认语法没问题的时候先去看声明顺序。7.2 调试三板斧从快到慢各有适用场景第一板斧SELECT直接输出。开发阶段最快的手段在关键位置插一句把变量选出来CALL一下立刻看到值。缺点是会多返回结果集上线前必须清理所以我习惯在调试语句前面加个-- DEBUG注释方便全局搜索清理。第二板斧用户会话变量接力。在过程里写SET dbg_x v_x;过程跑完之后在同一个连接里查dbg_x。好处是不影响结果集坏处是必须保证在同一个连接里查用连接池的话换个连接就查不到了。第三板斧临时表记录全过程。需要看循环每一轮的中间值时用这个。把所有关键变量按轮次插进临时表跑完一次性查出来能非常直观地看到数据是怎么一步步变化的。这个手段在排查某一行数据被重复处理这类问题时几乎无可替代。另外别忘了SHOW WARNINGS;。SELECT ... INTO零行这类问题不会报错只写进警告里CALL之后顺手执行一次很多隐性数据问题当场就能发现。7.3 命名规范与几条我坚持的团队约定踩了这么多坑之后我这边固定下来几条约定新项目一律照做老项目逐步改造参数加p_局部变量加v_游标加cur_标志位加done_临时表加tmp_调试变量加dbg_变量名全部小写因为 MySQL 的局部变量名不区分大小写v_Name和v_name是同一个东西混着写只会让人困惑声明区集中在块开头一行一个变量类型和DEFAULT对齐写方便扫读所有变量都给DEFAULT不给NULL留下解释空间从表里取值优先用聚合函数加IFNULL尽量不用取单行的写法变量类型照抄源字段汇总类往上放宽一档最后再分享一个小技巧。写复杂过程之前我会先单独写一小段声明区把这次要用到的所有变量列出来包括每个变量的用途和初始值然后再动手写逻辑。这个过程花不了五分钟但能让你在写的时候一直清楚我手里有哪些状态大大减少漏声明、拼错名、类型不匹配这类低级错误。存储过程这种调试成本高的东西前期多想两分钟后期少查两小时这笔账怎么算都划算。