create database if not exists bilibili; #创建数据库bilibili use bilibili; #选择bilibili数据库，即可进行下面的代码演示 drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删 show databases; #查看是否删除完成

准备数据

create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null,'黛绮丝','2022567890'),(null,'谢逊','2022567891'),(null,'殷天正','2022567892'),(null,'韦一笑','2022567893');

create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    phone char(11) comment '手机号',
    email varchar(40) comment '邮箱',
    profession varchar(20) comment '专业',
    age int comment '年龄',
    gender int comment '性别',
    status int comment '状态',
    createtime date comment '创建时间'
) comment '用户信息表';
insert into tb_user(id,name,phone,email,profession,age,gender,status,createtime) values
         (null,'吕布',17799990000,'1vbu666@163.com','软件工程',23,1,6,'2001-02-02 00:00:00'),#id字段不用指定，我们设置了自增
         (null,'曹操',17799990001,'caocao666@qq.com','通讯工程',33,1,0,'2001-03-05 00:00:00'),
         (null,'赵云',17799990002,'17799990@139.com','英语',34,1,2,'2001-03-02 00:00:00'),
         (null,'孙悟空',17799990003,'17799990@sina.com','工程造价',54,1,0,'2001-07-02 00:00:00'),
         (null,'花木兰',17799990004,'19980729@sina.com','软件工程',23,2,1,'2001-04-22 00:00:00'),
         (null,'大乔',17799990005,'daqiao666@sina.com','舞蹈',22,2,0,'2001-02-07 00:00:00'),
         (null,'露娜',17799990006,'luna_love@sina.com','应用数学',24,2,0,'2001-08-02 00:00:00'),
         (null,'程咬金',17799990007,'chengyaojin@163.com','化工',38,1,5,'2001-05-23 00:00:00'),
         (null,'项羽',17799990008,'xiaoyu666@qq.com','金属材料',43,1,0,'2001-9-18 00:00:00'),
         (null,'白起',17799990009,'baiqi666@sina.com','机械工程及其自动化',27,1,2,'2001-08-16 00:00:00'),
         (null,'韩信',17799990010,'hanxin520@163.com','无机非金属材料工程',27,1,0,'2001-06-12 00:00:00'),
         (null,'荆轲',17799990011,'jingke123@163.com','会计',29,1,0,'2001-05-11 00:00:00'),
         (null,'兰陵王',17799990012,'lanlinwang666@126.com','工程造价',44,1,1,'2001-04-09 00:00:00'),
         (null,'狂铁',17799990013,'kuangtie@sina.com','应用数学',43,1,2,'2001-04-10 00:00:00'),
         (null,'貂蝉',17799990014,'84958948374@qq.com','软件工程',40,2,3,'2001-02-12 00:00:00'),
         (null,'妲己',17799990015,'2783238293@qq.com','软件工程',31,2,0,'2001-01-30 00:00:00'),
         (null,'芈月',17799990016,'xiaomin2001@sina.com','工业经济',35,2,0,'2000-05-03 00:00:00'),
         (null,'嬴政',17799990017,'8839434342@qq.com','化工',38,1,1,'2001-08-08 00:00:00'),
         (null,'狄仁杰',17799990018,'jujiamlm0166@163.com','国际贸易',30,1,0,'2007-03-12 00:00:00'),
         (null,'安琪拉',17799990019,'jdodmlh@126.com','城市规划',51,2,0,'2001-08-15 00:00:00'),
         (null,'典韦',17799990020,'ycaunanjian@163.com','城市规划',52,1,2,'2000-04-12 00:00:00'),
         (null,'廉颇',17799990021,'lianpo321@126.com','土木工程',19,1,3,'2002-07-18 00:00:00'),
         (null,'后裔',17799990022,'altycj2000@139.com','城市园林',20,1,0,'2002-03-10 00:00:00'),
         (null,'姜子牙',17799990023,'37483844@qq.com','工程造价',29,1,4,'2003-05-26 00:00:00');

存储过程 介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，较少数据在数据库和应用服 务器 之间的传输，对于提高数据处理的效率是有好处的

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用 当我们开发时遇到相同业务的时候，直接调用对应的存储过程即可

存储过程的特点

1、封装、复用

2、可以接收参数，也可以返回数据

3、减少网络交互，效率提升

存储过程 基本语法

存储过程基本语法

创建无参的存储过程

create procedure p1()
begin
    select count(*) from student;#例如统计student表的总记录数
end;#创建之后就会在tables的同级目录生成routines目录

调用存储过程

call p1();

查看存储过程。查看bilibili数据库中有几个存储过程，存储过程的名字、存储过程关联的数据库等存储过程的信息

select * from information_schema.routines where routine_schema = 'bilibili';#information_schema.routines是系统表的意思

查看p1存储过程创建建时的语句

show create procedure p1;

删除存储过程

drop procedure if exists p1;

注意:在命令行进行演示时，执行创建存储过程的SQL时，需要通过关键字delimiter来指定SQL语句的结束符

delimiter $$

上面那行表示此时命令的结束不是以默认的分号为准，而是只有当出现$$，才会结束一段完整的命令，就可以在命令行输入如下:

xxxxxxxxxx
create procedure p1()
begin
    select count(*) from student;
end$$

以后我们每个命令的结束符就不是分号了，而是$$。如果要改回来的话，输入如下即可

delimiter ;

存储过程 系统变量

变量。对于MySQL数据库服务器当中的变量分为三类，即系统变量、用户自定义变量、局部变量。这节课我们学习系统变量

系统变量 是MySQL服务器提供的，属于服务器层面。分为全局变量(global)、会话变量(session)

如何查看系统变量 (1)show [session | global] variables;#查看所有系统变量 (2)show [session | global] variables like '......';#通过like模糊匹配查找变量 (3)select @@[session | global] 系统变量名;#查看指定变量的值

如何设置系统变量 (1)set [session | global] 系统变量名 = 值; (2)set @@[session | global]系统变量名 = 值;

一、查看系统变量

show variables;#不加级别参数，那么默认是session会话级别 show session variables;#会话级别 show global variables;#全局级别 show variables like 'auto%';#查看事务自动提交的开关，全称是autocommit select @@autocommit;#查看事务自动提交的开关 select @@global.autocommit;#查看全局的事务自动提交的开关

二、设置系统变量

set session autocommit = 0;#关闭事务自动提交的开关。只对当前会话有效，因为session参数表示会话级别 set session autocommit = 1;#开启，不然会影响下面那些代码演示

注意如下

1、session级别设置的，只有在当前会话有效

2、global级别设置的，在任意会话都有效，但是重启mysql之后，就会失效，并且恢复到原来的设置

3、要想永久有效，就需要在 /etc/my.cnf 中配置

存储过程 用户自定义变量

变量。对于MySQL数据库服务器当中的变量分为三类，即系统变量、用户自定义变量、局部变量。这节课我们学习用户自定义变量

用户自定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前会话

赋值

1、set @var_name = expr [,@var_name = expr] ...;

2、set @var_name := expr [,@var_name := expr] ...;

3、select @var_name := expr [,@var_name := expr] ...;

4、select 字段名 into @var_name from 表名;

使用

select @var_name;

注意: 用户自定义的变量无需对其进行声明或初始化，也就是说这个变量如果没有赋值那么默认值就是null*/

#赋值

set @myname = 'zhangsan';
set @myage := 18;#由于MySQL的=号既可以表示赋值运算符，也可以表示比较运算符，所以为了区分，赋值建议使用:=，比较使用=
set @mygender := '男',@myhobby := 'java';#为多个自定义变量赋值
select @mycolor := 'red';#select关键字也可以像set一样为变量赋值
select count(*) into @mysum from tb_user;#将tb_user表的查询结果赋值给变量
select @myany = '';
select @abc;#不赋值，变量默认为null

#使用

select @myname;
select @myname,@myage,@mygender,@myhobby,@mycolor,@mysum,@myany,@abc;

存储过程 局部变量

局部变量 是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是 在其内声明的begin...end块

声明 declare 变量名 变量类型 [default ...];

变量类型就是数据库字段类型: int、bigint、char、varchar、date、time

赋值

1、set 变量 = 值;

2、set 变量名 = 值;

3、select 字段名 into 变量名 from 表名 ...;*/

create procedure p2()
begin
    declare stu_count int default 0;#声明变量stu_count，指定类型为int，默认值是可选的，例如默认值为0
    set stu_count := 100;#正常赋值
    #select count(*) into @mysum from tb_user;#也是赋值，跟上面那行的效果一样，自定义变量
    #select count(*) into stu_count from tb_user;#也是赋值，跟上面那行的效果一样，局部变量
    select stu_count;#输出
end;

call p2();#如果上面使用正常赋值或局部变量复制，就用这行，输出
select @mysum;#如果上面使用自定义变量赋值的话，这里就用这行，输出
drop procedure if exists p2;#删除存储过程

存储过程 if判断

if判断语法如下

if 条件1 then

......

elseif 条件2 then

......

else

......

end if;

#定义存储过程，完成需求。根据定义的分数score变量，判定当前分数对应的分数等级 #1. score >= 85分，等级为优秀 #2. score >= 60分 且 score < 85分，等级为及格 #3. score < 60分，等级为不及格

create procedure p3()
begin
    declare score int default 58;#定义score变量，default后面可以自己任意写一个要判断的值，例如58
    declare result varchar(10);#定义result变量，用于记录分数等级
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result :='不及格';
    end if;
    select result;#输出
end;

call p3();#调用，调用才会执行
drop procedure if exists p3;#删除存储过程

#存在的两个问题，会在下一节课的参数讲解，我们现在创建的是无参的存储过程，所以就存储下面两个问题 #1. default后面是要判断的值，但是它是被我们写死在存储过程里面，也就是这个值变成了固定的。解决:在p3()里面写个参数 #2. 输出的结果是单纯的展示一下，我们需要的是让这个输出结果返回给p3()存储过程，而不是输出

存储过程 参数

由于默认类型就是in，所以参数类型如果为in，可以省略不写in

用法如下

create procedure 存储过程名称([ in/out/inout 参数名 参数类型])
begin
        .........
end;

#定义存储过程，完成需求

需求1

根据传入的参数score，判定当前分数对应的分数等级，并返回

#1. score >= 85分，等级为优秀 #2. score >= 60分 且 score < 85分，等级为及格 #3. score < 60分，等级为不及格

create procedure p4(in score int, out result varchar(10))#score参数是in类型，接收int类型。result参数是out类型，接收varchar类型
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result :='不及格';
    end if;
end;

call p4(68,@jieshou);#调用。第一个参数是int类型的判断值，第二个参数是自定义变量用于接收返回的值
select @jieshou;#验证。结果是及格
drop procedure if exists p4;#删除存储过程

需求2

将传入的200分制的分数，进行换算，换算成100分制的分数，然后返回

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

set @fenshu = 58;#为自定义变量赋值
call p5(@fenshu);#调用存储过程，把@fenshu变量作为参数传入到p5存储过程，然后p5存储过程会返回新的@fenshu变量值
select @fenshu;#查看，结果为29，也就是58作为200分值，转换成100分值就是29分
drop procedure if exists p5;#删除存储过程

存储过程 case流程控制

这节课的case是流程控制语句的case。之前学的case函数跟节课的语法结构基本一致，如下

语法一

case case_value
         when when_value1 then statement_list1
         [when when_value2 then statement_list2] ...
         [else statement_list]
end case;

语法二

case
         when search_condition1 then statement_list1
         [when search_condition2 then statement_list2] ...
         [else statement_list]
end case;

#定义存储过程，完成需求。根据传入的月份，判断月份所属的季节 #如果是1-3月，为第一季度;4-6月为第二季度;7-9月为第三季度;10-12月份为第四季度

create procedure p6(in month int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
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 p6(4);#调用，输出
drop procedure if exists p6;#删除存储过程

存储过程 while循环

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法如下

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
         ......
end while;

#定义存储过程，完成需求。计算从1累加到n的值，n为传入的参数值

create procedure p7(in n int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
        set n := n - 1;
    end while;
    select total;#输出，被谁调用就在谁那里输出
end;
call p7(100);#调用，传入参数100，输出5050
drop procedure if exists p7;#删除存储过程

存储过程 repeat循环

注意我们上节课学的while循环是满足条件则进行循环，而这节课的repeat是满足条件的时候退出循环。语法如下

#先执行一次逻辑，然后判断逻辑是否满足，如果满足则退出，如果不满足则继续下一次循环
repeat
         ......
         until 条件
end repeat;

#定义存储过程，完成需求。计算从1累加到n的值，n为传入的参数值

create procedure p8(in n int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare total int default 0;
    repeat
        set total := total +n;
        set n := n-1;
    until n<=0 #现在上面的循环走一遍，再执行这里的条件判断，如果条件还不满足，就继续执行上面的循环
    end repeat;
    select total;#输出，被谁调用就在谁那里输出
end;
call p8(50);#调用，传入参数50，输出1275
drop procedure if exists p8;#删除存储过程

存储过程 loop循环

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。loop可以配合一下两个语句使用:

1、leave: 配合循环使用，退出循环，用来指定退出循环的条件

2、iterate: 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环

3、可以使用begin_label标记，用来标识当前循环

[begin_label:] loop
         ......
end loop [end_label];

leave label;#退出指定标记的循环体
iterate label;#直接进入下一次循环

#定义存储过程，完成需求

需求1:

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

create procedure p9(in n int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare total int default 0;
    biaoji:loop #开始循环
        if n<=0 then #循环退出的条件
            leave biaoji; #满足条件之后就退出
        end if;
        #上面是退出的语句。下面是编写累加的语句
        set total := total + n;
        set n := n-1;
    end loop biaoji; #循环结束
    select total;#输出total
end;
call p9(100);#调用，传入参数100，输出5050
drop procedure if exists p9;#删除存储过程

需求2:

计算从1到n之间的偶数累加的值，也就是奇数不参与计算，只计算偶数，n为传入的参数值。难点:如果当前累加的数据是奇数，那么就直接进入下一次循环

create procedure p10(in n int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare total int default 0;
    biaoji:loop #开始循环
        if n<=0 then #循环退出的条件
            leave biaoji; #满足条件之后就退出
        end if;
        #上面是判断是否满足退出条件。下面是判断累加的数据是否是奇数，是就不对该奇数进行累加
        if n%2 = 1 then
            set n := n - 1;#退出之前先把n减1，表示进入下一个数的判断，不然会一直死循环在当前这个数
            iterate biaoji;#从这里直接退出，不执行下面的语句，重新执行biaoji循环
        end if;
        #上面是判断当前累加的数据如果是奇数，就直接进入下面的语句。下面是编写累加的语句，也是我们的核心语句
        set total := total + n;
        set n := n-1;
    end loop biaoji; #循环结束
    select total;#输出total
end;
call p10(10);#调用，传入参数10，输出30
drop procedure if exists p10;#删除存储过程

存储过程 cursor游标

-- 通过变量记录查询结果。在上面的184行讲过一次，p2()存储过程那里就是，可去看一下，不看也行

create procedure p11() #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;
call p11();#调用，输出
drop procedure if exists p11;#删除存储过程

#注意当count()改为之后，再执行call p11()就会报错，原因:stu_count是自定义的整型变量，不可能接收的了student整张表的数据 #也就是stu_count变量只能接收单行单列的整型数据

#所以局部变量是不能存储整张表的数据，那如果我们需要存储一整张表，怎么办呢，就需要使用我们这节课的游标。如下

游标也叫光标，由于存储过程定义的局部变量不能存储整张表的数据，只能存储单行单列的数据，所以为了解决不能存储多行多列的问题， 就会使用到这节课的游标

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。简单来说就是游标是存储数据的一个集合，可以对游标使用遍历 游标的使用包括游标的声明、打开open、获取fetch、关闭close。语法如下

声明游标

xxxxxxxxxx
declare 游标名称 cursor for 查询语句;

打开游标

xxxxxxxxxx
open 游标名称;

获取游标数据

xxxxxxxxxx
fetch 游标名称 into 变量 [,变量];

关闭游标

close 游标名称;

定义存储过程，完成案例

根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名和专业，并将用户的姓名和专业插入到所创建的一张

新表(id,name,profession)中这节课的循环程序会报错，下面可以都不运行，运行了的话，就删除p12存储过程、tb_user_pro表

create procedure p12(in uage int) #创建存储过程。 #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare uname varchar(20);#变量。存储一个值
    declare upro varchar(20);#变量。存储一个值。注意变量的声明要写在游标声明的上面。下面那行就是游标声明
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;#游标。游标名称可以自定义，把查到的数据存储到该游标
    drop table if exists tb_user_pro;#为避免下面创建表失败，我们这里先判断有没有这个表，有的话删除
    create table if not exists tb_user_pro(#创建一张新表tb_user_pro
        id int primary key auto_increment,
        name varchar(20),
        profession varchar(20)
    );
    open u_cursor;#开启游标
    while true do #使用循环来遍历游标。这里写成了死循环。留下了一个问题就是当游标里面的数据被遍历完了但是没有退出循环，就会报错，下节课解决
        fetch u_cursor into uname,upro;#获取游标数据，每循环一次就获取下一条数据，并把数据赋值给变量uname,upro变量
        insert into tb_user_pro values (null,uname,upro);#把变量的数据再插入到我们创建的tb_user_pro表。由于id设置了自增，所以可以直接给null值
    end while;
    close u_cursor;#关闭游标
end;
call p12(40);#调用。查询年龄小于等于40的用户的姓名和专业。游标会把符合条件的数据放到一个新表tb_user_pro
drop procedure if exists p12;#删除存储过程
drop table if exists tb_user_pro;#删除tb_user_pro表

注意:当游标里面的数据被遍历完之后，如果此时不退出循环，继续遍历这个游标，就会报错。所以我们在循环遍历游标的时候，要指定退出循环的条件， 除了写一个退出循环的条件，还有没有其他办法可以结束循环。下节课的事件处理程序handler会学

存储过程 条件处理程序handler

#完善上节课的案例。注意上节课只是没有退出循环的语句，其他代码都是正确的，注释也是得当的，并且虽然报错了但是生成了正确的表数据，唯一的问题就 #是没有写退出循环的条件，出现了报错，但是代码是执行正确的，我们只需要解决让它不报错即可 #我下面就直接复制上节课的案例代码，但是我不复制注释，所以下面出现的注释都是这节课的内容，跟上节课区分开

条件处理程序 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。语法如下

declare handler_action handler for condition_value [,condition_value] ... statement;

上面有两个需要介绍的参数为handler_action类型，condition_value条件。如下

handler_action (1) continue: 继续执行当前程序 (2) exit: 终止执行当前程序

condition_value (1) sqlstate slqstate_value: 状态码, 如02000 (2) sqlwarning: 所有以01开头的sqlstate状态码的简写 (3) not found: 所有以02开头的sqlstate状态码的简写 (4) sqlexception: 所有没有被sqlwarning或not found捕获的sqlstate状态码的简写

简单来说就是condition_value通过不同的状态码，来决定执行handler_action的哪个类型(继续执行或终止执行两种类型) 怎么看状态码: 看报错，报错的红字显示什么状态码就使用什么状态码 官方文档也有相关的状态码表示的含义https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

create procedure p12(in uage int) #由于默认类型就是in，所以参数类型如果为in，可以省略不写in
begin
    declare uname varchar(20);
    declare upro varchar(20);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    declare exit handler for sqlstate '02000' close u_cursor;#如果满足状态码是0200，就执行exit类型，也就是退出程序，然后还要关闭游标
    #当检测到程序报错并且状态码是0200，就会执行上面那行，执行退出程序并关闭游标
    #把sqlstate '02000' 写成 not found 也是对的，因为not found表示02开头的状态码
    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(20),
        profession varchar(20)
    );
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values (null,uname,upro);
    end while;#这里报错，报错的状态码是0200
    close u_cursor;#
end;
call p12(40);#调用。查询年龄小于等于40的用户的姓名和专业。游标会把符合条件的数据放到一个新表tb_user_pro
drop procedure if exists p12;#删除存储过程
drop table if exists tb_user_pro;#删除tb_user_pro表

存储函数 使用

存储函数 是有返回值的存储过程，存储函数的参数只能是in类型。注意in是直接省略的，不要写上去，不然报错 也就是我们在定义存储函数的时候，必须要指定返回值，参数列表必须是输入类型in 语法如下

create function 存储函数名称 ([参数列表])
returns type [characteristic ...]
begin
         ......
         return ...;
end;

对于上述的characteristic说明

1、deterministic: 相同的输入参数总是产生相同的结果

2、no sql: 不包含sql语句

3、reads sql data: 包含读取数据的语句，但不包含写入数据的语句

#定义存储函数，完成需求。计算从1累加到n的值，n为传入的参数值

create function fun1(n int)  #n前面有一个in是必须要省略的，别写上去，不然报错
returns int deterministic #返回值类型int，指定特性为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);#调用fun1函数，由于得到的是返回值，所以我们可以把返回值查询出来。计算1~100的和
drop function if exists fun1;#删除存储过程。注意函数的存储过程参数是使用function。之前学的是程序的存储过程参数才是用procedure

总结

1、这节课用到的存储函数在实际中用的比较少，因为存储函数能做的事，存储过程也能做。当需要存储过程涉及返回值时， 只需要把存储过程的参数类型从in改为out即可

2、存储函数的弊端:使用存储函数必须要有返回值*/