【从删库到跑路】MySQL数据库 | 存储过程 | 存储函数(使用代码辅助理解)

🎊专栏【MySQL】
🍔喜欢的诗句：更喜岷山千里雪 三军过后尽开颜。
🎆音乐分享【The Right Path】
🥰欢迎并且感谢大家指出小吉的问题

🎄存储过程介绍

MySQL 存储过程（Stored Procedure）是一种预编译的代码块，它可以接受参数、执行特定的操作并返回结果。存储过程是一种将多条 SQL 语句组合在一起形成一个可重用的业务逻辑单元，从而简化应用程序的开发和维护

🎄存储过程特点

可以减少网络流量：数据库服务器上的存储过程可以减少客户端应用程序和数据库之间的网络流量。这是因为存储过程只需要发送参数和执行语句，而不需要每次都发送完整的 SQL 语句。

可以提高数据库性能：存储过程可以避免反复地编译解释 SQL 语句，从而提高数据库的执行效率。

可以实现可重用的代码：存储过程可以将公共的业务逻辑抽象出来，形成一个可重用的代码库，从而简化应用程序的开发和维护。

可以保护数据库安全：存储过程可以通过使用参数验证和权限控制等方式来保护数据库的安全。

🌺存储过程

⭐创建

create procedure 存储过程名称[(参数列表)]
begin

-- sql语句
1

end；

⭐调用

来执行 创建 过程中的sql语句

call 名称({参数});

create procedure p1()
begin
    select count(*) from tb_abc;
end;

call p1();
1
2
3
4
5
6

⭐查看

select * from information_schema.ROUTINES where ROUTINE_SCHEMA=‘数据库名’; --指定数据库的存储过程和状态信息

show create procedure p1; --查询某个存储过程的定义

查看详细内容

select * from information_schema.ROUTINES where ROUTINE_SCHEMA='abc';

show create procedure p1;
1
2
3

⭐删除

drop procedure [if exists] 存储过程名称;

drop procedure if exists p1;
1

🍔全局变量

⭐查看系统变量

show session variables ;
1

如果要查询所有以 auto 开头的系统变量

show session variables like 'auto%';
1

上面查看的是session级别的，是当前会话级别的
如果我们要查看全局级别的
把session改为global即可

show global variables like 'auto%';
1

上面我们使用了like，是模糊匹配
如果我们已经目前找到了系统变量的值，应该怎么办呢
方法如下

select @@autocommit;
1

⭐设置系统变量

0代表关闭自动提交开关，1代表打开自动提交开关

set session autocommit =0;
1

我们再次执行，发现session级别的自动提交开关已经变为0了

🍔用户定义变量

⭐赋值

set @myname = 'itcast';   -- 没有:
set @myage :=10;          -- 有 :  
set @mygender :='男',@myhobby :='java';
1
2
3

⭐使用

select @myname,@myage,@mygender,@myhobby;
1

🍔局部变量

⭐声明

declare
1

⭐赋值

create procedure p2()
begin
    declare tb_count int default 0;    -- 局部变量要使用declare
    select count(*) into tb_count from tb_abc;
    select tb_count;
end;

call p2();
1
2
3
4
5
6
7
8

🎍if判断

根据定义的分数score变量，判定当前分数对应的分数等级
score>=85 优秀
score>=60 && score M< 85 及格
score<60 不及格

create procedure p4()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
        set result:='优秀';
    elseif score>=60 then
        set result:='及格';
    else
        set result:='不及格';
    end if;

    select result; -- 展示结果
end;

call p4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

🎍参数

根据传入(in) 参数score，判定当前分数对应的分数等级，并返回(out)
score>=85 优秀
score>=60 && score M< 85 及格
score<60 不及格

create procedure p5(in score int,out result varchar(10))
begin
#     declare score int default 58;
#     declare result varchar(10);
    if score>=85 then
        set result:='优秀';
    elseif score>=60 then
        set result:='及格';
    else
        set result:='不及格';
    end if;

#     select result;
end;

call p5(68,@result);
select @result;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

将传入的200分制的分数，换算威百分制，然后返回分数 ---->inout

create procedure p6(inout score double)
begin
    set score:=score*0.5;
end;

set @score=198;
call p6(@score);
select @score;
1
2
3
4
5
6
7
8

这段代码中的 select @score; 表示查询的是存储过程中 call p6(@score); 执行后 @score 的值

🎍case

根据传入的月份，判断季度
1-3月 第一季度
4-6月 第二季度
7-9月 第三季度
10-12月 第四季度

create procedure p7(in month int)
begin
    declare result varchar(10);

    case
        when month>=1 and month <=3 then
            set result :='第一季度';
        when month>=4 and month <=6 then
            set result :='第二季度';
        when month>=7 and month <=9 then
            set result :='第三季度';
         when month>=10 and month <=12 then
            set result :='第四季度';
        else
            set result:='非法参数';
    end case ;

    select concat('月份',month,'季度',result); -- 借住concat函数，进行字符串拼接

end;

call p7(4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

🎍while

计算从1累加到n的值，n为传入的参数值

create procedure p8(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total:=total+n;
        set n:=n-1;
    end while ;
    select total;
end;

call p8(10);
1
2
3
4
5
6
7
8
9
10
11

🎍repeat循环

计算从1累加到n的值，n为传入的参数值

create procedure p9(in n int)
begin
    declare total int default 0;
    repeat
        set total:=total+n;
        set n:=n-1;
    until n<=0
    end repeat;

    select total;
end;
call p9(10);
1
2
3
4
5
6
7
8
9
10
11
12

while 满足条件 进行 循环
repeat 满足条件 退出 循环

🎍loop循环

计算从1累加到n的值，n为传入的参数值

create procedure p10(in n int)
begin
    declare total int default 0;

    sum:loop
        if n<=0 then
            leave sum;
        end if;
        set total:=total+n;
        set n:=n-1;
    end loop sum;

    select total;
end;

call p10(100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

🎍游标(光标)

查询id小于uid的员工，并且把它们的信息存入到新表中

我们运行后发现运行失败，报错了
为了解决这个问题，我们要使用下面的方法

🎍handler

在代码中加入这一段代码

declare exit handler for not found close u_course;
1

🎆存储函数

计算从1累加到n的值，n为传入的参数值

create function fun1(n int)
returns int deterministic -- 指定特性deterministic
begin
    declare total int default 0;

    while n>0 do
        set total:=total+n;
        set n:=n-1;
        end while;

    return total;
end;

select fun1(100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14