MySQL存储过程
目录
一、存储过程概述
存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的就是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与定义好的存储过程的功能相同的服务时，只需要调用“CALL 存储过程名称”即可使用相应的功能！
一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由SQL语句和一些特殊的控制结构组成！
当希望在不同的应用程序或平台上执行相应的特定功能时，存储过程就变得尤为合适！
二、存储过程的优点
- 封装性：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，并且DBA可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码；
- 可增强：SQL语句的功能和灵活性、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和复杂的运算；
- 可减少网络流量：由于存储过程时在服务端运行的，且执行速度快，因此当客户计算上调用该存储过程时，网络中传送的只是调用该语句，从而降低网络负载；
- 高性能：存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区执行二进制代码即可，从而提供了系统的效率和性能；
- 提高数据库的安全性和数据的完整性：使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限；
三、自定义存储过程示例
mysql> select * from fruits; #查询一条SQL语句 +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 2.60 | +------+------+------------+---------+ mysql> delimiter // #更改其默认的分隔符为“//”，也可以是其他任意符号，只要不是默认的“;”就行 mysql> create procedure test() #定义存储过程test -> begin -> select * from fruits; #将sql语句写入存储过程，可以写多条 -> end// mysql> delimiter ; #更改为默认的分割符 mysql> call test(); #调用刚才定义的test存储过程 +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 2.60 | +------+------+------------+---------+
通过上面的例子可以看出，存储过程是类似于一个shell脚本的，存放的是一些sql语句的集合，当然，它同样有一些判断、循环等语句。
四、带while循环的存储过程
下面的例子是存储过程借助while循环来计算1+2+3...+100的结果是多少。
mysql> delimiter // #改变默认的截断符为“//” mysql> create procedure test1() #创建存储过程 -> begin #存储过程开始 -> declare n int; #定义变量名称 -> declare summary int; #定义变量名称 -> set n=0; #设置变量的初始值为0 -> set summary=0; #设置变量的初始值为0 -> while n<=100 #当n小于或等于100时，执行以下操作 -> do -> set summary=summary+n; -> set n=n+1; -> end while; #循环结束 -> select summary; #查询summary的值 -> end // #存储过程结束 mysql> delimiter ; #将截断符改回默认的 mysql> call test1(); #调用存储过程 +---------+ | summary | +---------+ | 5050 | +---------+
五、带if判断的存储过程
以下实现的是如果传参的值大于或等于10，则执行else下面的SQL语句，如果传参的值小于10，则执行then下面的SQL语句。
mysql> delimiter // mysql> create procedure test2(in num int) -> begin -> if num < 10 then -> select * from fruits where f_price<10; -> else -> select * from fruits where f_price>=10; -> end if; -> end // mysql> delimiter ; mysql> call test2(9); #定义传入值为9 +------+------+---------+---------+ | f_id | s_id | f_name | f_price | +------+------+---------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m2 | 105 | xbabay | 2.60 | | o2 | 103 | coconut | 9.20 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 2.60 | +------+------+---------+---------+ mysql> call test2(10); #定义传入值为10 +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | m1 | 106 | mango | 15.70 | | m3 | 105 | xxtt | 11.60 | | t1 | 102 | banana | 10.30 | +------+------+------------+---------+
六、带有case的存储过程
该存储过程实现结果为：当传入的值为偶数时，输出fruits表中s_id列为偶数的行，如果传入的值为奇数，输出s_id列为奇数的行，否则输出空。
mysql> delimiter // mysql> create procedure test3(in num int) -> begin -> case num%2 -> when 0 then -> select * from fruits where s_id%2=0; -> when 1 then -> select * from fruits where s_id%2=1; -> else -> select null; -> end case; -> end // mysql> delimiter ; mysql> call test3(4); #传入偶数测试 +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | b2 | 104 | berry | 7.60 | | bs1 | 102 | orange | 11.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | +------+------+--------+---------+ mysql> call test3(3); #传入奇数测试 +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b5 | 107 | xxxx | 3.60 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t4 | 107 | xbababa | 2.60 | +------+------+------------+---------+
七、将存储过程传出到全局环境变量
mysql> delimiter // mysql> create procedure test4(out num float) -> begin -> select max(f_price) into num from fruits ; -> end // mysql> delimiter ; mysql> call test4(@num); mysql> select @num; #由于最大的值是浮点数，所以会这样 +--------------------+ | @num | +--------------------+ | 15.699999809265137 | +--------------------+
八、其他关于存储过程的操作语句
mysql> help procedure; #查看帮助 topics: ALTER PROCEDURE #修改procedure，一般用不到，需要修改的话，直接删除再创建即可 CREATE PROCEDURE #创建procedure DROP PROCEDURE #删除procedure PROCEDURE ANALYSE SELECT SHOW SHOW CREATE PROCEDURE 存储过程名 #查看某个存储过程的详细信息
九、附加：如何复制表
① 方法一
like方法能一模一样的将一个表的结果复制生成一个新表，包括复制表的备注、索引、主键外键、存储引擎等。但是不包括表数据，如下：
mysql> create table new_fruits like fruits; mysql> desc new_fruits; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | f_id | char(10) | NO | PRI | NULL | | | s_id | int(11) | NO | | NULL | | | f_name | char(255) | NO | | NULL | | | f_price | decimal(8,2) | NO | | NULL | | +---------+--------------+------+-----+---------+-------+
② 方法二
select的方法值复制字段属性，其它的主键、索引、表备注、存储引擎都没有复制。如下：
mysql> create table new_fruits_select select * from fruits; mysql> select * from new_fruits_select; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | c0 | 101 | cherry | 3.20 | | l2 | 104 | lemon | 6.40 | | m1 | 106 | mango | 15.70 | | m2 | 105 | xbabay | 2.60 | | m3 | 105 | xxtt | 11.60 | | o2 | 103 | coconut | 9.20 | | t1 | 102 | banana | 10.30 | | t2 | 102 | grape | 5.30 | | t4 | 107 | xbababa | 2.60 | +------+------+------------+---------+
*************** 当你发现自己的才华撑不起野心时，就请安静下来学习吧！***************
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· 上周热点回顾（3.3-3.9）
· winform 绘制太阳，地球，月球 运作规律