MySQL高级7-存储过程

过程,MySQL,高级 · 浏览次数 : 378

小编点评

生成内容时需要带简单的排版，以下是生成内容时需要带的排版内容： 1. 声明游标，存储查询结果集 2. 准备创建表结构 3. 开开启游标 4. 获取右边中的记录 5. 插入数据到新表 6. 关闭游标 7. 存储函数的参数是in类型的，所以可以省略不写 8. 存储函数本身有返回值，所以可以直接使用select进行查看

正文

一、介绍

　　存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库sql语言层面的代码封装与重用。

　　例如：我们在实际开发中经常会遇到先查询数据，然后根据查询结果，进行更新数据，这时候至少就需要多次操作数据库，就需要多次数据请求，而这时我们可以将这里的查询和更新封装到一个sql集合中，这样就减少了一次网络请求。

二、存储过程的特点

封装、复用
可以接收参数，也可以返回数据
减少网络交互，效率提升

三、存储过程语法

　　3.1 创建语法

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

begin

　　--sql语句

end;

mysql> delimiter &
mysql> create procedure p1()
    -> begin 
    ->     select count(*) from student;
    -> end;&
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

　　　　说明1：delimiter & 意思是指定SQL语句的结束符，默认是“;”,这里改成“&”，因为创建存储过程中会包含完整的sql语句，而完整的sql语句后面就会有“;”出现，如果不修改这个结束符，命令行默认遇到“;”就会终止，所以就会执行失败

　　　　说明2：当我们执行完了之后，需要再次使用delimiter ; 将终止符由“&” 改为 “;”

　　3.2 调用语法

call 执行过程名称 ([参数])

mysql> call p1();
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

　　　　说明1：就像函数调用一样，直接call 存储过程名称

　　3.3 查看存储过程语法　　

select * from information_schema.routines where routine_schema="xxx"

　　　　说明1：查询指定数据库的所有存储过程即状态信息，注意这里的xxx是数据库的名称

mysql> select * from information_schema.routines where routine_schema="mysql_test";
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                        | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                           | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | mysql_test     | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin 
    select count(*) from student;
end |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2023-07-31 19:51:26 | 2023-07-31 19:51:26 | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+-------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

　　　　说明2：因为数据列比较多，所以这里有点错乱，大家可以自己查询看一下自己的记录。

　　3.4 查看存储过程定义的语句

mysql> show create procedure p1;
+-----------+----------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                           | Create Procedure                                                                             | character_set_client | collation_connection | Database Collation |
+-----------+----------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p1        | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin 
    select count(*) from student;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+----------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

　　3.5 删除存储过程的语法

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

mysql> drop procedure if exists p1;
Query OK, 0 rows affected (0.01 sec)

四、系统变量

　　4.1 系统变量

　　　　系统变量：是Mysql服务器提供，不是用户定义的，属于服务器层面，分为全局变量(global)、会话变量（session）

　　4.2 查看系统变量　　　　

show [session|global] variables;  -- 查看所有系统变量
show [session|global] variables like "xxx";  --可以通过like 模糊匹配方式查找变量
show @@[session|global] 系统变量名;  --查看指定变量的值

　　4.3 设置系统变量

set [session|global] 系统变量名=值
set @@[session|global] 系统变量名=值

　　　　说明1：没有指定session/global，默认是session会话

　　　　说明2：mysql服务重启之后，所有设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置

五、自定义变量

　　5.1 用户自定变量

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

　　5.2 赋值语法

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

select @var_name := expr [, @var_name := expr]...;
select 字段名 into @var_name from 表名;

　　说明1：赋值的时候可以使用"="也可以使用":="

　　说明2：上面两种方式都可以

　　5.3 使用语法

select @var_name;

六、局部变量

　　6.1 局部变量

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

　　6.2 声明语法

declare 变量名 变量类型 [defailt ...];

　　说明：变量类型就是数据库字段类型：int,bigint,char,varchar,date,time等

　　6.3 赋值语法

　　set 变量名 = 值；
　　set 变量名 := 值；
　　select  字段名 into 变量名 from 表名 ...；

七、if语句

　　7.1 语法

if 条件1 then

elseif 条件2 then

　　...  --可选

else

　　... --可选

end if;

八、参数

　　IN : 该类参数作为输入，也就是需要调用时传入值，默认类型

　　OUT : 该类参数作为输出，也就是该参数可以作为返回值

　　INOUT : 既可以作为输入参数，也可以作为输出参数

create procedure 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型])
begin

　　--SQL语句;

end;

　　案例1：根据传入参数score,判定当前分数对应等级，并返回

　　　　1、score >= 85分，等级为优秀

　　　　2、score >= 60分且score < 85分，等级为不及格

　　　　3、score < 60分，等级为不及格

mysql> delimiter &
mysql> create procedure p1(in score int, out result varchar(10))
    -> begin 
    -> if score >= 85 then
    -> set result := "优秀";
    -> elseif score >=60 then
    -> set result := "及格";
    -> else
    -> set result := "不及格";
    -> end if;
    -> end;
    -> &
Query OK, 0 rows affected (2.89 sec)
mysql> delimiter ;

　　　　说明1：delimiter & 是将在终端中默认的SQL结束终止符由“；” 改为“&”,因为存储过程的语句中会包含sql语句，中间有“；”出现。

mysql> call p1(68, @result);
Query OK, 0 rows affected (0.11 sec)

mysql> select @result;
+---------+
| @result |
+---------+
| 及格    |
+---------+

　　　　说明2：在使用存储过程的时候，p1有两个参数，一个in 类型的score ,一个out 类型的result

　　　　说明3：@result 是自定义变量。

　　案例2：将传入的200分制的分数，进行换算，换算成包分之，然后返回分数

mysql> delimiter &
mysql> create procedure p5(inout score double) 
    -> begin
    -> set score := score * 0.5;
    -> end;
    -> &
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ;
mysql> set @score = 198;
Query OK, 0 rows affected (0.02 sec)

mysql> call p5(@score);
Query OK, 0 rows affected (0.20 sec)

mysql> select @score;
+--------+
| @score |
+--------+
|     99 |
+--------+
1 row in set (0.00 sec)

　　　　说明1：delimiter & 是将结束终止符由 “；”, 改变成 “&”

　　　　说明2：inout score double 说明score 是float类型的，同时也是inout类型的

　　　　说明3：set @score = 198 是对变量定义并赋值

九、case语句

　　8.1 语法一

case case_value
　　when when_value1 then statement_list 1
　　[when when_value2 then statement_list 2]...
　　[else statement_list]
end case;

　　8.2 语法二

case
　　when search_condition1 then statement_list 1
　　[when search_condition2 then statement_list 2]...
　　[else statement_llist]
end case;

　　8.3 案例

　　根据传入的月份，判定月份所属的季节

mysql> delimiter &
mysql> 
mysql> create procedure p6(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);
    -> end;
    -> &

mysql> delimiter ;
mysql> call p6(5);
+-----------------------------------------------------------------------------+
| concat("你输入的月份是：",month, ", 所属的季度是：", result)                |
+-----------------------------------------------------------------------------+
| 你输入的月份是：5, 所属的季度是：第二季度                                   |
+-----------------------------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.05 sec)

十、while语句

　　while循环是有条件的循环控制语句，满足条件后，在执行循环体中的sql语句，具体语法为

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

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

mysql> delimiter &
mysql> 
mysql> create procedure p7(in n int)
    -> 　　begin
    -> 　　　　declare totle int default 0;
    -> 　　　　while n>0 do
    -> 　　　　　　set totle := totle + n;
    -> 　　　　　　set n := n - 1;
    -> 　　　　end while;
    -> 　　　　select totle;
    -> 　　end;
    -> &
Query OK, 0 rows affected (1.56 sec)

mysql> delimiter ;
mysql> 
mysql> call p7(10);
+-------+
| totle |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

　　说明1：定义局部变量，记录累计之后的值

　　说明2：每循环一次，就会对n减少1，如果n减到0，则退出循环

十一、repeat

　　repeat是有条件的循环控制语句，当满足条件的时候推出循环，有点类似编程中的do-while语句，但是do-while是满足条件就继续执行，而这里的repeat是满足就退出，这里需要注意，repeat具体语法如下：

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

　　案例：计算从1累加到n的值，n位传入的参数值

mysql> delimiter &
mysql> create procedure p8(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;
    -> &
Query OK, 0 rows affected (0.24 sec)
mysql> delimiter ;

　　调用验证

mysql> call p8(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.05 sec)

Query OK, 0 rows affected (0.05 sec)

十二、loop

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

- leave: 配合循环使用，退出循环。
- itetate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

　　语法：

[begin_label:] loop
　　sql逻辑
end loop [end_label];


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

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

mysql> delimiter &
mysql> create procedure p9(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;
    -> &
Query OK, 0 rows affected (1.34 sec)

mysql> delimiter ;

　　调用验证

mysql> call p9(10);
+-------+
| total |
+-------+
|    55 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

　　案例2：计算从1到n之间的偶数累加的值，n为传入的参数值

mysql> delimiter &
mysql> create procedure p10(in n int)
    -> begin
    -> 　　declare total int default 0;
    -> 　　sum:loop
    -> 　　　　if n<=0 then
    -> 　　　　　　leave sum;
    -> 　　　　end if;
    -> 　　　　if n%2 = 1 then
    -> 　　　　　　set n:=n-1;
    -> 　　　　　　iterate sum;
    -> 　　　　end if;
    -> 　　　　set total := total + n;
    -> 　　　　set n := n - 1;
    -> 　　end loop sum;
    -> 　　select total;
    -> end;
    -> &
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;

　　　调用验证

mysql> call p10(10);
+-------+
| total |
+-------+
|    30 |
+-------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.08 sec)

十三、游标

　　游标（cursor）：是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理，游标的使用包括游标的声明，open、fetch和close，其语法分别如下

　　13.1 游标的声明

declare 游标名称 cursor for 查询语句；

　　13.2 游标的打开

open 游标名称；

　　13.3 获取游标记录

fetch 游标名称 into 变量 [, 变量]；

　　13.4 关闭游标

close 游标名称；

十四、条件处理程序

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

　　14.1 语法：

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

handler_action
　　continue: 继续执行当前程序
　　exit:终止执行当前程序

condition_value
　　sqlstate sqlstate_value：状态码 ，如02000
　　sqlwarning: 所有以01开头的sqlstate代码的简写
　　not fount: 所有以02开头的sqlstate代码的简写
　　sqlexception:所有没有被sqlwarning或者not fount 捕获的sqlstate代码的简写

　　14.2 案例

　　根据传入的参数uage，来查询学生表student中，所有的学生年龄小于等于uage的学生姓名（name）和分数(score),并将学生的name和score字段插入到所创建的一张新表(id，name，score)中

mysql> delimiter &
mysql> create procedure p11(in uage int)
    -> begin
    -> declare uname varchar(100);
    -> declare uscore int;
    -> declare u_cursor cursor for select name,score from student where age <= uage;
    -> declare exit handler for sqlstate '02000' close u_cursor;
    -> 
    -> drop table if exists tb_student;
    -> create table if not exists tb_student(
    -> id int primary key auto_increment,
    -> name varchar(100),
    -> score int
    -> );
    -> 
    -> open u_cursor;
    -> while true do
    -> fetch u_cursor into uname, uscore;
    -> insert into tb_student values (null, uname, uscore);
    -> end while;
    -> close u_cursor;
    -> end;
    -> &
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;

　　调用验证

mysql> call p11(20);
Query OK, 0 rows affected (0.63 sec)

　　查看当前数据中是否多了一个tb_sudent表

mysql> show tables;
+----------------------+
| Tables_in_mysql_test |
+----------------------+
| student              |
| tb_student           |
+----------------------+
2 rows in set (0.00 sec)

　　查看tb_student表中数据

mysql> select * from tb_student;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | 张三   |    88 |
|  2 | 赵六   |    94 |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+--------+------+-------+
| id | name   | age  | score |
+----+--------+------+-------+
|  1 | 张三   |   18 |    88 |
|  2 | 李四   |   21 |    83 |
|  3 | 王五   |   24 |    76 |
|  4 | 赵六   |   19 |    94 |
+----+--------+------+-------+
4 rows in set (0.00 sec)

　　说明1：声明游标，存储查询结果集

　　说明2：准备创建表结构

　　说明3：开启游标

　　说明4：获取右边中的记录

　　说明5：插入数据到新表

　　说明6：关闭游标

十五、存储函数

　　存储函数：是有返回值的存储过程，储存函数的参数只能是 in 类型的，具体语法如下

create function 存储函数名称（[参数列表]）
returns type [characteristic]
begin
　　--SQL语句
　　return ....;
end;

　　characteristic说明：

- deterministic:相同的输入参数总是产生相同的结果
- no sql:不包含sql语句
- reads sql data: 包含读取数据的语句，但不包含写入数据的语句

　　案例实现

mysql>delimiter &
mysql> create function fun1(n int)
        ->returns 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;&    
Query OK, 0 rows affected (0.02 sec)

　　存储函数的调用

mysql> delimiter ;
mysql> select fun1(50);
+----------+
| fun1(50) |
+----------+
|     1275 |
+----------+
1 row in set (0.00 sec)

　　说明1：因为存储函数的参数是in类型的，所以可以省略不写

　　说明2：存储函数本身有返回值，所以可以直接使用select进行查看