Mysql数据库：存储过程

目录

前言

一、存储过程概述

1、存储过程的概念

2、存储过程的优点

二、存储过程基本操作

1、创建存储过程

1.1 基本格式

1.2 案例操作

2、调用存储过程

3、查看存储过程信息

4、修改存储过程

5、删除存储过程

三、存储过程参数

1、输入参数的存储过程

1.1 创建输入参数的存储过程

​1.2 调用输入参数的存储过程

2、输出参数的存储过程

2.1 创建输出参数的存储过程

2.2 调用输出参数的存储过程

 3、输入输出参数的存储过程

3.1 创建输入输出参数的存储过程

3.2 调用输入输出参数的存储过程

四、存储过程控制语句

1、if条件判断语句

2、while循环条件语句

五、总结

1、 定义和创建

2、参数类型

3、调用存储过程

4、控制流程语句

5、变量

---

前言

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数

一、存储过程概述

1、存储过程的概念

数据库存储过程（Stored Procedure）是一种预编译的数据库对象，它包含了一系列用于执行特定任务的SQL语句集合。存储过程不仅包含查询语句，还可以包括流程控制语句（如if...then...else、which、loop等）、变量声明、条件判断、循环结构以及其他数据库系统提供的编程元素

存储过程在数据库内部被编译和优化，存放在数据库服务器中，用户不需要每次都发送整个SQL脚本到数据库服务器执行，只需调用存储过程的名称，并传入相应的参数（如果存储过程带有参数的话），就可以执行存储过程内定义的复杂操作

2、存储过程的优点

• 代码复用：存储过程可以被多个应用程序或用户反复调用，提高了代码的重用性和模块化程度
• 性能优化：由于存储过程在数据库内部执行，减少了网络传输成本，尤其对于大批量数据处理和复杂的业务逻辑，可以显著提高执行效率
• 安全性增强：通过权限管理和角色分配，可以控制谁有权执行存储过程，间接实现了对数据的安全保护，无需向用户暴露底层表结构
• 事务控制：存储过程可以包含事务处理，确保一组操作要么全部成功，要么全部回滚，保证数据完整性
• 参数化：存储过程可以定义输入参数、输出参数和输入输出参数，使其更加灵活，能够适应不同的应用场景
• 减少网络流量：只需发送存储过程名称和参数，降低了网络传输的数据量
• 模块化与封装：存储过程可以封装复杂的业务逻辑，便于维护和升级

存储过程在许多大型数据库系统中都有广泛应用，如Oracle、SQL Server、MySQL、PostgreSQL等。开发者可以根据需求编写适合特定场景的存储过程，以提高数据库操作的效率和安全性。 

二、存储过程基本操作

1、创建存储过程

1.1 基本格式

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

1.2 案例操作

delimiter $$       #将语句的结束符号从分号;临时改为两个$$(可以自定义)
create procedure proc()       #创建存储过程，过程名为proc，不带参数
begin                         #过程体以关键字 begin 开始
create table dianzi(id int(3) not null primary key,name varchar(10) not null,score decimal(5,2));
insert into dianzi values(1,'dxz',88),(2,'cli',77),(3,'yqs',66);
select * from dianzi;         #过程体语句
end $$                        #过程体以关键字 end 结束
delimiter ;                   #将语句的结束符号恢复为分号

2、调用存储过程

格式：call 过程名();

call proc();

3、查看存储过程信息

格式：
#查看所有的存储过程信息
show procedure status;

#查看指定存储过程信息
show create procedure [数据库名].过程名\G;
show procedure status like '%过程名%'\G;

#查看所有的存储过程信息
show procedure status;

#查看指定存储过程proc的信息
show create procedure proc\G;
show procedure status like '%proc%'\G;

4、修改存储过程

ALTER PROCEDURE <过程名>[<特征>... ]
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行

 注：

存储过程内容的最好的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程

5、删除存储过程

格式：drop procedure [if exists] 过程名;

drop procedure if exists proc;   #删除名为proc的存储过程

三、存储过程参数

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

1、输入参数的存储过程

输入参数（IN）允许你向存储过程传递数据。存储过程内部可以使用这些数据，但不能修改传入的参数值

1.1 创建输入参数的存储过程

delimiter $$
create procedure class(in inname varchar(30))
begin
select * from info where name=inname;
end $$
delimiter ;

1.2 调用输入参数的存储过程

call class('lyy');   #调用传入参数为'lyy'

#变量位inname的值可以为任意info表中name字段的值

2、输出参数的存储过程

输出参数（OUT）允许存储过程返回数据给调用者。存储过程可以修改输出参数的值，调用结束后，调用者可以获取这个值

2.1 创建输出参数的存储过程

delimiter $$
create procedure chu(out num int)
begin
set num=30;
end $$
delimiter ;

2.2 调用输出参数的存储过程

call chu(@summum);   #给NULL值的summum变量赋值
select @summum;      #查看summum变量值

 3、输入输出参数的存储过程

输入输出参数（INOUT）既可以让你向存储过程传递数据，又可以从存储过程返回数据。这意味着存储过程可以读取参数的初始值，执行一些操作，然后修改这个参数的值，最后返回给调用者

3.1 创建输入输出参数的存储过程

delimiter $$
create procedure churu(inout a int)
begin
select a;
set a=a+30;
select a;
end $$
delimiter ;

3.2 调用输入输出参数的存储过程

set @word=10;         #设置初始变量值
call churu(@word);    #调用存储过程

四、存储过程控制语句

1、if条件判断语句

在MySQL数据库中，存储过程中的IF判断语句用于根据条件执行不同的代码块

格式：
if condition then
    statement_list
[elseif condition then
    statement_list]
[else
    statement_list]
end if;

#condition 是一个布尔表达式，如果为真，就执行 then 之后的语句，否则就跳过
#THEN 是一个关键字，用于指示 condition 为真时要执行的语句
#statement_list 是一个或多个SQL语句，用于在 condition 为真时执行
#elseif 和 else 是可选的，用于在 condition 为假时执行其他的语句

delimiter $$
create procedure ifgc(in num int)
begin
if num >=10 then
set num=num-10;
else
set num=num*2;
end if;
select num;                                                                                       
end $$                                                                                            
delimiter ;

call ifgc(3);
call ifgc(15);

2、while循环条件语句

在MySQL数据库中，存储过程中的 while 循环语句允许根据给定的条件重复执行一组语句，直到该条件不再为真。这种循环结构特别适用于当你不知道需要执行循环体内代码的确切次数时

格式：
while condition do
    statement_list
end while;

#condition 是一个布尔表达式，如果为真，就执行 do 和 end while 之间的语句，然后再次检查 condition。如果 condition 为假，就跳过 do 和 end while 之间的语句，继续执行后面的代码
#do 和 end while 是关键字，用于指示 condition 为真时要执行的语句

delimiter $$                                                                                                       
create procedure xunh ()
begin
declare i int;
declare j int;
set i=10;
set j=1;
while j<=10 do
set i=i+j;
set j=j+1;
end while;
select i;
end $$
delimiter ;

call xunh;

五、总结

1、 定义和创建

• 存储过程通过create procedure语句定义和创建
• 可以包含输入（IN）、输出（OUT）和输入输出（INOUT）参数
• 使用begin和end关键字包围存储过程的主体

2、参数类型

• 输入参数（IN）：允许调用者向存储过程传递数据
• 输出参数（OUT）：允许存储过程向调用者返回数据
• 输入输出参数（INOUT）：既可以作为输入也可以作为输出，允许存储过程读取和修改传入的参数值

3、调用存储过程

使用CALL语句来执行存储过程，并传递任何必要的参数。

4、控制流程语句

存储过程可以包含复杂的逻辑，包括条件语句（if else）和循环语句（loop、repeat、while）

5、变量

存储过程内可以声明和使用局部变量