存储过程

存储过程

【1】简介

• 存储过程是一种在数据库中保存的sql语句集合，它可以执行一系列的数据库操作。
• 使用存储过程有以下几点好处

1. 性能提升：存储过程在服务器端执行，减少了网络通信量和延迟。
2. 代码重用和标准化：存储过程可以被多次调用，有助于代码重用和保持一致性。
3. 安全性：可以限制对特定数据的直接访问，而是通过存储过程提供控制的接口。
4. 维护：修改存储过程比更改多个应用程序中的SQL语句要容易

【2】创建和调用存储过程

• 在MySQL中，你可以使用CREATE PROCEDURE语句来创建一个存储过程。这里是一个基本示例：

-- 创建存储过程
delimiter $$
create procedure p1()
begin
	select * from dep;
end$$
delimiter ;

-- 调用存储过程
call p1;

delimiter $$
create procedure p1()
begin
	select name,
	if(gender='male','小哥哥','小姐姐') from emp;
end$$
delimiter ;

【3】参数

• 在mysql中，存储过程可以接收三种类型的参数

1. 输入参数(in):这就像是你给这个小程序的原材料。你告诉它需要用到什么，它就会用这些材料来完成任务。比如，你可以告诉它需要查询哪个用户的信息。
2. 输出参数（OUT）：这就像是小程序做好的菜肴。完成任务后，它会把结果给你。比如，小程序可以告诉你某个用户的年龄。
3. 输入输出参数（INOUT）：这就像是你给小程序一个半成品，让它加工后再还给你。你给它一些信息，它处理这些信息，然后把处理后的信息返回给你。比如，你给它一个数字，它把这个数字加倍后还给你。

-- 语法
create procedure <存储过程名称>([in/out/inout <参数名><参数类型>])
begin
	sql语句
end;

-- 例子
drop procedure if exists p1;
delimiter $$
create procedure p1(in age int,out res varchar(15))
begin
	set res=case
		when age < 20 then '小奶狗'
        when age between 20 and 30 then '小年轻'
        when age between 30 and 40 then '小叔叔'
        else '老前辈'
        end;
end$$
delimiter ;

变量补充

• 在MySQL中，有三种主要类型的变量：系统变量、用户定义变量（自定义变量）和局部变量。

【1】系统变量

• 系统变量是mysql服务器提供的，用于控制其操作的变量。

• 它们分为全局变量和会话变量

• 全局变量 影响MySQL服务器的整体操作。例如，innodb_buffer_pool_size用于控制InnoDB存储引擎的缓冲池大小。

• 会话变量 只影响当前连接的行为。例如，sql_mode可以设置不同的SQL模式，以改变SQL语句的解析和执行方式。

• 可以用SHOW VARIABLES来查看系统变量，用SET语句来设置它们的值。

【2】自定义变量

• 用户定义变量是由用户创建的，可以在一次连接中传递数据。他们以@符号开始

• 定义的变量不需要手动删除，它和python一样具有垃圾回收机制

-- set 定义变量
set @myvar = 1;

-- select 定义变量
select @myvar:= 1;

-- 查询变量
select @myvar;

【3】局部变量

• 局部变量是在存储过程或者函数内 定义的，只在定义他们的语句块中有效

-- 定义局部变量语法
declare <变量名> <变量类型> [default <默认值>];

-- 声名（必须紧跟着begin后面写）
delimiter $$
create procedure p1()
begin
	declare dep_count int default 0;
	set dep_count = (select count(id) from dep);
	select dep_count;
end$$
delimiter ;

流程控制函数补充

【1】if函数

-- if函数用于基于条件表达式执行不同的操作
if(<条件表达式>,<条件为真返回的值>,<条件为假返回的值>)

-- 通过把查询结果的性别变成小哥哥或者小姐姐
select name,if(gender='male','小哥哥','小姐姐') as gender from emp where id < 7;

+--------+-----------+
| name   | gender    |
+--------+-----------+
| 刘备   | 小哥哥    |
| 关羽   | 小哥哥    |
| 张飞   | 小哥哥    |
| 赵云   | 小哥哥    |
| 马超   | 小哥哥    |
| 黄忠   | 小姐姐    |
+--------+-----------+

【2】case语句

• case语句在处理多条件判断时更为灵活
• case语句分为简单case和搜索case

-- 简单case
select name,
case gender
	when 'male' then '小哥哥'
	when 'female' then '小姐姐'
	else '保密'
end
from emp;

-- 这样看来简单case就是直接拿查询到的结果做判断

-- 搜索case
select name,
case
	when age < 20 then '小奶狗'
	when age >= 20 then '小年轻'
	when age >= 30 then '小叔叔'
	when age >= 40 then '大叔'
	else '老前辈'
end
from emp;
-- 搜索case就是将查询到的结果再一次进行比较

【3】coalesce函数

• coalesce函数用于从其参数列表中返回第一个非null的值，如果所有参数都是null，则最后返回null

-- 语法 (value是函数的参数，可以是任意数量)
coalesce(value1,value2,value3...)

-- 使用场景
-- coalesce 函数在处理可能返回null值的数据时非常好用，它允许你为可能的null值指定一个备选项。这在数据库中是常见的需求，因为NULL值可能会导致计算或结果出现问题。

-- 示例
-- 有一张员工表emp，其中有的员工并没有领导。这时候就可以使用coalesce函数为其设置一个备选值
select name,
	coalesce(leader_id,'no leader') as leader
from emp;

【4】nullif函数

• nullif 函数用于比较两个表达式，如果他们两个相等，则返回null
• 如果不相等则返回第一个表达式的值
• 这个函数主要用于防止除以0的错误或者处理特定情况下的数据

-- 语法
nullif(<表达式1>,<表达式2>)

-- 使用场景
-- 1.防止除0错误
-- 2.当在特点情况下，希望在两个表达式相等时，将值替换为null

-- 示例
-- 有一张员工表，要计算员工平均薪资。如果有些员工的工资为0，直接除以0会导致错误，这时候就需要nullif函数

循环控制语句补充

• 在MySQL中，循环控制语句主要用于存储过程和函数中，用来重复执行一段代码直到满足特定条件。MySQL提供了三种主要的循环控制结构：LOOP循环、REPEAT循环和WHILE循环。

【1】loop循环

• LOOP提供了最基本的循环功能，它会不断地执行一段代码，直到遇到LEAVE语句。通常与IF语句一起使用来判断何时退出循环。

-- 语法
[标签:]loop
	sql逻辑
leave 标签;
iterate 标签;
end loop [标签];

-- 求和例子
drop procedure if exists p1;
delimiter $$
create procedure p1(in num int)
begin 
	declare res int default 0;
sum:loop
	if num = 5 then
		set num = num -1;
		iterate sum;
	end if;
		set res = res + num;
		set num = num -1;
	if num<=0 then
		leave sum;
	end if;
	end loop;
	select res;
end$$
delimiter ;

-- 调用p1
call p1(100)

+------+
| res  |
+------+
| 5045 |
+------+