1. 存储过程基础入门存储过程是数据库编程中最实用的功能之一它就像数据库里的小程序把常用的SQL操作封装起来。我第一次接触存储过程时感觉它特别像编程语言中的函数——可以传参数、有变量、能写逻辑判断还能返回结果。存储过程的核心优势在于它能将业务逻辑保留在数据库层。举个例子当我们需要频繁统计工程项目的数量时与其在应用程序里反复写相同的SQL不如创建一个存储过程。比如实验中的第一个案例PROC_COUNTDELIMITER $ CREATE PROCEDURE PROC_COUNT() BEGIN SELECT COUNT(*) FROM J WHERE JNAME LIKE %厂; END;$ DELIMITER ;这个简单的存储过程统计了工程项目名包含厂字的记录数。调用时只需要执行CALL PROC_COUNT()比直接写完整SQL简洁多了。我在实际项目中就遇到过类似需求财务系统需要按月统计各类票据数量用存储过程后代码量减少了40%。存储过程的语法结构可以分为几个关键部分声明部分定义参数和变量BEGIN-END块包含实际执行的SQL语句异常处理可选处理运行时错误初学者常犯的错误是忘记修改DELIMITER。因为存储过程体内有分号所以需要先用DELIMITER $临时改变结束符写完后再改回来。这个细节我当初就栽过跟头调试了半天才发现问题。2. 参数化存储过程实战当存储过程需要动态条件时参数就派上用场了。实验中的PROC_JNAME就是个典型例子DELIMITER $ CREATE PROCEDURE PROC_JNAME(IN city_param CHAR(10)) BEGIN SELECT JNAME FROM J WHERE CITY city_param; END;$ DELIMITER ;这里IN city_param CHAR(10)定义了一个输入参数。调用时传入具体城市名如CALL PROC_JNAME(天津)就能查询该城市的工程项目。参数模式除了IN还有OUT和INOUTIN默认输入参数存储过程内可读不可改OUT输出参数存储过程内可改并返回INOUT输入输出参数我在学生成绩管理系统开发时就用OUT参数返回统计结果。比如计算班级平均分CREATE PROCEDURE PROC_AVG_SCORE( IN class_id INT, OUT avg_score DECIMAL(5,2) ) BEGIN SELECT AVG(score) INTO avg_score FROM scores WHERE class class_id; END调用时先声明变量result再CALL PROC_AVG_SCORE(101, result)最后SELECT result查看结果。这种用法在需要返回多个值时特别有用。3. 多表关联与复杂查询实际业务中单表查询往往不够用。PROC_JINFO存储过程展示了多表关联的典型应用CREATE PROCEDURE PROC_JINFO(IN city VARCHAR(10)) BEGIN SELECT DISTINCT JNAME, PNAME, SUM(QTY) AS SUM_QTY FROM SPJ RIGHT JOIN J ON J.JNOSPJ.JNO LEFT JOIN P ON P.PNOSPJ.PNO WHERE CITYcity GROUP BY SPJ.JNO, SPJ.PNO ORDER BY JNAME, PNAME; END这个存储过程涉及三张表的关联查询工程项目表J零件表P供应情况表SPJ通过RIGHT JOIN确保显示所有符合条件的工程LEFT JOIN保留没有对应零件的记录。我在电商系统开发时订单查询就需要关联用户表、商品表和支付表类似的关联逻辑每天要执行上万次。性能优化技巧为关联字段建立索引避免SELECT *只查询必要字段复杂查询可以拆分为多个临时表操作使用EXPLAIN分析执行计划4. 游标编程详解游标是处理结果集的利器特别适合需要逐行处理的场景。实验第11关的PROC_JGRADE就是个经典案例CREATE PROCEDURE PROC_JGRADE() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE jno CHAR(10); DECLARE qty_sum INT; DECLARE cur CURSOR FOR SELECT JNO, SUM(QTY) FROM SPJ GROUP BY JNO; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; CREATE TEMPORARY TABLE result( jno CHAR(10), total INT, grade CHAR(1) ); OPEN cur; read_loop: LOOP FETCH cur INTO jno, qty_sum; IF done THEN LEAVE read_loop; END IF; INSERT INTO result VALUES( jno, qty_sum, CASE WHEN qty_sum 1000 THEN S WHEN qty_sum BETWEEN 1000 AND 2000 THEN M ELSE L END ); END LOOP; CLOSE cur; SELECT * FROM result; END游标使用四部曲声明游标定义结果集来源打开游标OPEN cur获取数据FETCH cur INTO 变量关闭游标CLOSE cur我在批量处理用户数据时游标帮了大忙。比如需要给连续三个月未登录的用户发送提醒邮件用游标就能逐条检查用户最后登录时间并触发相应操作。5. 事务处理与错误控制存储过程中经常需要保证操作的原子性。PROC_UPDATEGRADE案例展示了事务的典型用法CREATE PROCEDURE PROC_UPDATEGRADE() BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT Error occurred, transaction rolled back AS message; END; START TRANSACTION; UPDATE SC SET rankA WHERE Grade BETWEEN 90 AND 100; UPDATE SC SET rankB WHERE Grade BETWEEN 80 AND 89; -- 更多更新语句... COMMIT; SELECT Grades updated successfully AS message; END关键点START TRANSACTION标记事务开始COMMIT提交事务ROLLBACK回滚事务错误处理通过DECLARE HANDLER实现在银行转账系统中这种事务处理至关重要。我遇到过因网络问题导致扣款成功但存款失败的情况后来加入完整的事务控制才解决问题。6. 性能优化技巧经过多次项目实践我总结出这些存储过程优化经验避免过度使用游标游标是行级操作性能较差。能用集合操作解决的问题就不要用游标。曾经优化过一个报表系统把游标改为批量更新后执行时间从2小时降到15分钟。参数化查询总有人喜欢用字符串拼接SQL这是SQL注入的温床。正确的做法是-- 错误示范 SET sql CONCAT(SELECT * FROM users WHERE id, user_id); PREPARE stmt FROM sql; -- 正确做法 CREATE PROCEDURE safe_query(IN user_id INT) BEGIN SELECT * FROM users WHERE id user_id; END合理使用临时表复杂查询可以分步进行中间结果存入临时表。但要注意会话结束自动删除适当添加索引避免创建过大临时表执行计划分析用EXPLAIN查看SQL执行计划重点关注是否使用了合适索引是否有全表扫描连接顺序是否合理7. 真实项目案例解析去年开发的库存管理系统大量使用了存储过程这里分享一个采购入库的典型案例CREATE PROCEDURE proc_purchase( IN supplier_id VARCHAR(20), IN item_list JSON, OUT result_code INT ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET result_code -1; END; START TRANSACTION; -- 1. 记录主采购单 INSERT INTO purchase_orders(supplier_id, total_amount) SELECT supplier_id, SUM(item-price * item-quantity) FROM JSON_TABLE(item_list, $[*] COLUMNS( item JSON PATH $ )) AS items; SET order_id LAST_INSERT_ID(); -- 2. 记录采购明细 INSERT INTO purchase_items(order_id, product_id, quantity, unit_price) SELECT order_id, item-product_id, item-quantity, item-price FROM JSON_TABLE(item_list, $[*] COLUMNS( item JSON PATH $ )) AS items; -- 3. 更新库存 UPDATE products p JOIN ( SELECT item-product_id AS product_id, SUM(item-quantity) AS quantity FROM JSON_TABLE(item_list, $[*] COLUMNS( item JSON PATH $ )) AS items GROUP BY item-product_id ) AS t ON p.product_id t.product_id SET p.stock p.stock t.quantity; COMMIT; SET result_code 0; END这个存储过程展示了几个实用技巧使用JSON类型传递复杂参数事务确保数据一致性通过LAST_INSERT_ID()获取自增ID多步骤操作保持原子性在开发过程中我发现存储过程的版本管理很重要。建议使用CREATE OR REPLACE PROCEDURE更新存储过程在注释中记录修改历史和作者重要的存储过程要有单元测试配合版本控制系统管理SQL脚本