MySQL的存储过程

最新推荐文章于 2025-04-30 11:47:01 发布

jin .

最新推荐文章于 2025-04-30 11:47:01 发布

阅读量3.5k

点赞数 1

文章标签： mysql 数据库服务器

本文链接：https://blog.youkuaiyun.com/qq_64333664/article/details/127689807

版权

存储过程是组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

1.1 存储过程的优点

封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码
可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
提高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能
提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。
使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

1.2 创建、调用和查看存储的过程

1.创建存储过程

#语法：

CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>

[过程参数[,…] ] 格式

<过程名>：尽量避免与内置的函数或字段重名

<过程体>：语句

[ IN | OUT | INOUT ] <参数名><类型>

1) 过程名存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2) 过程参数存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

3) 过程体存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $

语法说明如下：

$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。

当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符


成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??

mysql > DELIMITER ??

若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可

mysql > DELIMITER ;

注意：DELIMITER 和分号“;”之间一定要有一个空格

示例：

delimiter $$ #将语句的结束符号从分号;临时改为两个$$ (可以是自定义)

create procedure proc5() #创建存储过程，过程名为proc， 不带参数

-> begin #过程体以关键字BEGIN开始

-> select * from students; #过程体语句（自己根据需求进行编写）

-> end $$ #过程体以关键字END结束

delimiter ; #将语句的结束符号恢复为分号

2.调用存储过程

#语法

call 过程名


#示例：

call proc5

3.查看存储过程

#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）

SHOW CREATE PROCEDURE [数据库.] 存储过程名;

##示例1：

show create procedure hellodb.proc5\G;


##示例2：

show create procedure proc5\G;

4.删除存储过程

#语法

DROP PROCEDURE IF EXISTS 过程名;


#示例：

drop procedure if exists proc5;

1.3 存储过程的参数

IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)
OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

#示例：

delimiter $$

create procedure proc11(in inname char(40))

-> begin

-> select * from students where name = inname;

-> end $$

mysql> delimiter ;

call proc11('Xi Ren');

1.4 存储过程的控制语句

1.4.1 条件控制语句 if-then-else … end if

delimiter $$ #修改默认结束符为$$

create procedure proc12(in pro int) #创建存储过程proc2，参数为pro，类型为int

-> begin #过程体以关键字begin开始

-> declare var int; #定义变量var为int类型

-> set var=pro*3; #设置变量var等于传入参数的3倍

-> if var>=10 then #如果变量var大于10，则执行下面过程体

-> update students set age=age+10; #设置表students中的age加10

-> else #如果变量var不大于10，则执行下面过程体

-> update students set age=age-10; #设置表students中的age减10

-> end if; #结束if语句

-> end $$ #创建存储过程结束

delimiter ; #重新修改默认结束符为原始的;

CALL proc12(4); #调用proc2存储过程，并传入参数4

1.4.2 循环语句 while … end while

#创建表

create table nametest(id int auto_increment primary key,name char(40),age int default 20);


DELIMITER $$ #修改默认结束符为$$

-> create procedure yxp() #创建存储过程yxp

-> begin #过程体以关键字begin开始

-> declare i int; #定义变量i为int类型（长度最大为10）

-> set i = 1; #设置i = 1;

-> while i <= 100 #使用while循环，i要小于100

-> do insert into nametest(name,age) values

#满足条件则进行添加数据，内容为变量i

-> set i=i+1; #变量i每次循环后加1

-> end while; #结束while循环

-> end $$ #创建存储过程结束

delimiter ; #重新修改默认结束符为原始的;

CALL proc6; #调用yxp存储过程