1. 存储过程是什么为什么需要它第一次接触数据库开发时我总在Java代码里写满SQL语句。每次统计用户订单都要重复写相同的联表查询直到同事提醒你这代码像复读机啊后来才知道数据库里有种叫存储过程的黑科技能把复杂SQL操作打包成可重复调用的功能模块。简单来说存储过程就像是预装在数据库里的APP。比如电商系统要计算会员积分原本需要在Java/Python里写十几行代码与数据库交互现在只需调用一句CALL calc_points(用户ID)。我做过测试一个包含5表关联的统计查询用存储过程比应用程序处理快3倍因为减少了网络传输和SQL解析开销。2. 创建你的第一个存储过程2.1 基础创建语法先看个供应商管理的例子。假设有供应商表S和供货记录表SPJ现在要给S表增加供应总量统计列ALTER TABLE s ADD sqty INT DEFAULT 0;接下来创建存储过程自动更新这个统计值DELIMITER // CREATE PROCEDURE update_supply_total() BEGIN UPDATE s SET sqty (SELECT SUM(qty) FROM spj WHERE spj.sno s.sno); END // DELIMITER ;这里有个坑要注意MySQL默认用分号结束语句但存储过程体内也有分号。所以先用DELIMITER //临时修改结束符就像告诉数据库看到//再执行这段代码。2.2 执行存储过程调用存储过程比点外卖还简单CALL update_supply_total();执行后查看S表所有供应商的sqty列都会自动更新。我在项目中用这招处理月度报表生成原本需要15分钟的统计任务现在3分钟搞定。3. 带参数的存储过程实战3.1 输入参数(IN)的使用查询特定供应商的供货总量时可以用IN参数动态传入供应商编号CREATE PROCEDURE get_supply_qty(IN supplier_id CHAR(2)) BEGIN SELECT SUM(qty) FROM spj WHERE sno supplier_id; END调用时指定参数值CALL get_supply_qty(S1);3.2 输出参数(OUT)的妙用需要获取计算结果给其他SQL使用时OUT参数就派上用场了CREATE PROCEDURE get_supply_qty_out( IN supplier_id CHAR(2), OUT total_qty INT ) BEGIN SELECT SUM(qty) INTO total_qty FROM spj WHERE sno supplier_id; END调用方式很特别CALL get_supply_qty_out(S1, result); SELECT result; -- 这里显示输出值3.3 输入输出参数(INOUT)最灵活的是INOUT参数既能输入又能输出。比如实现计数器CREATE PROCEDURE counter(INOUT num INT, IN increment INT) BEGIN SET num num increment; END使用时需要先设置变量SET count 1; CALL counter(count, 5); -- 结果count变为64. 存储过程进阶技巧4.1 条件判断与错误处理在新增供应商时加入存在性检查CREATE PROCEDURE add_supplier( IN p_sno CHAR(2), IN p_name VARCHAR(10), IN p_status INT, IN p_city VARCHAR(10) ) BEGIN DECLARE exist_count INT; SELECT COUNT(*) INTO exist_count FROM s WHERE sno p_sno; IF exist_count 0 THEN SELECT 供应商已存在! AS message; ELSE INSERT INTO s VALUES(p_sno, p_name, p_status, p_city); END IF; END4.2 使用游标处理多行数据当需要逐行处理数据时游标是唯一选择。比如批量修改项目信息CREATE PROCEDURE update_projects() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE j_id CHAR(2); DECLARE cur CURSOR FOR SELECT jno FROM j; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO j_id; IF done THEN LEAVE read_loop; END IF; UPDATE j SET city 北京 WHERE jno j_id; END LOOP; CLOSE cur; END5. 存储过程的管理与优化5.1 查看和修改存储过程查看所有存储过程SHOW PROCEDURE STATUS;查看具体定义SHOW CREATE PROCEDURE update_supply_total;修改存储过程需要先删除再重建没有ALTER PROCEDURE语法DROP PROCEDURE IF EXISTS update_supply_total; CREATE PROCEDURE update_supply_total() ...5.2 性能优化建议使用COMMENT添加说明三个月后你肯定记不清这个存储过程是干嘛的CREATE PROCEDURE calc_stats() COMMENT 用于月度统计报表 BEGIN -- 过程体 END避免过度使用游标我曾在处理10万条数据时用游标花了2小时改成批量UPDATE后只要3分钟定期维护存储过程会缓存执行计划当表结构变化后记得重新编译CALL update_supply_total(); -- 首次执行会编译权限控制只给必要用户EXECUTE权限GRANT EXECUTE ON PROCEDURE db.update_supply_total TO report_user;6. 真实项目经验分享去年做物流系统时我设计了一个智能调度存储过程。它接收区域ID和时间范围参数自动完成以下操作检查该区域可用车辆计算每个司机的累计工时根据货物类型匹配合适车型生成最优分配方案原本需要200行Java代码的逻辑现在只需30行SQL存储过程。最惊喜的是执行时间从45秒降到5秒因为所有计算都在数据库内部完成避免了数千次网络往返。有个坑要提醒存储过程调试比较麻烦。我的经验是先用SELECT输出中间结果复杂逻辑拆分成小过程在测试环境充分验证存储过程就像数据库里的瑞士军刀用好了能大幅提升效率。但也要避免滥用对于简单的CRUD操作直接写SQL可能更清晰。