Mysql数据库-存储过程

Mysql数据库-存储过程

5. Mysql数据库-存储过程

1. 存储过程和函数概述

1. 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合 a. 类似于java中的方法: 是一段java代码的集合 b. 存储过程是可以编程的，意味着可以使用变量，表达式，结构控制等语句来完成比较复杂的功能 2. 存储过程和函数的好处 a. 提高代码的复用性 b. 减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率 java : 1. 定义方法 getSum(){这段代码} 多次调用getSum -> 这段代码只需要编译 2. 没有定义方法 这段代码 需要多次调用,需要多次编写 -> 这段代码多次编译 3. 存储过程和函数的区别 存储过程和函数很类似,主要的区别有 a. 函数必须有返回值 b. 存储过程可以没有返回值,也可以有

2. 创建和调用存储过程

2.1 语法:

delimiter $ -- 修改结束分隔符为$-- 创建存储过程create procedure 存储过程名称(参数列表)begin -- SQL语句列表end$delimiter ; -- 修改结束分隔符为;

-- 调用存储过程call 存储过程名称(实际参数) ;

2.2 实例:

2.2.1 准备数据

-- 准备数据create database db2 character set utf8;use db2;create table student( id int primary key auto_increment, name varchar(20), age int, gender varchar(5), score int);-- 添加数据insert into student values(null,'张三',23,'男',95),(null,'李四',24,'男',98),(null,'王五',25,'女',100),(null,'赵六',26,'女',90);

准备好的数据如下：

mysql> select * from student;+----+--------+------+--------+-------+| id | name | age | gender | score |+----+--------+------+--------+-------+| 1 | 张三 | 23 | 男 | 95 || 2 | 李四 | 24 | 男 | 98 || 3 | 王五 | 25 | 女 | 100 || 4 | 赵六 | 26 | 女 | 90 |+----+--------+------+--------+-------+4 rows in set (0.00 sec)mysql>

2.2.2 创建和调用存储过程

# 创建和调用存储过程-- 1. 按照性别进行分组,查询分组学生的总成绩,然后按总成绩进行升序排序select gender,sum(score) getSum from student group by gender order by getSum asc;-- 2. 创建stu_group()存储过程,封装 '1需求' 里的sql语句delimiter $ -- 修改结束分隔符为$-- 创建存储过程create procedure stu_group()begin -- SQL语句列表 select gender,sum(score) getSum from student group by gender order by getSum asc;end$delimiter ; -- 修改结束分隔符为;-- 3. 调用stu_group()存储过程call stu_group(); -- 执行如下：mysql> call stu_group();+--------+--------+| gender | getSum |+--------+--------+| 女 | 190 || 男 | 193 |+--------+--------+2 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)

3. 查看和删除存储过程

3.1 语法:

-- 查询数据库中所有的存储过程select * from mysql.proc where db='数据库名称';

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

3.2 实例:

-- 查询数据库中所有的存储过程select * from mysql.proc where db='db2';-- 删除存储过程drop procedure if exists stu_group;

执行如下：

-- 查询数据库 db2 中所有的存储过程mysql> select * from mysql.proc where db='db2' \G*************************** 1. row *************************** db: db2 name: stu_group type: PROCEDURE specific_name: stu_group language: SQL sql_data_access: CONTAINS_SQL is_deterministic: NO security_type: DEFINER param_list: returns: body: begin -- SQL语句列表 select gender,sum(score) getSum from student group by gender order by getSum asc;end definer: root@localhost created: 2021-02-13 18:47:15 modified: 2021-02-13 18:47:15 sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION comment: character_set_client: utf8collation_connection: utf8_general_ci db_collation: utf8_general_ci body_utf8: begin -- SQL语句列表 select gender,sum(score) getSum from student group by gender order by getSum asc;end1 row in set (0.00 sec)-- 删除存储过程mysql> drop procedure if exists stu_group;Query OK, 0 rows affected (0.00 sec)

4. 局部变量

4.1 语法

-- 1. 定义变量 -- 注意: 这是一个局部变量,作用范围在begin...end之间declare 变量名 数据类型[default 默认值]; -- 2. 变量赋值-- 变量赋值方式一:set 变量名 = 变量值;-- 变量赋值方式二: 将查询到的一个结果赋值给变量select 列名 into 变量 from 表名[where 条件]; -- 3. 使用变量: 这样才能在控制台上看到这个变量select 变量名;

4.2 实例

1.在存储过程中定义一个int类型变量(默认值10),并使用该变量

执行如下：

-- 调用存储过程，获取变量num的值为10mysql> call pro_test1();+------+| num |+------+| 10 |+------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

2.在存储过程中定义一个varchar类型变量,并赋值,最后使用该变量

-- 2. 在存储过程中定义一个varchar类型变量,并赋值,最后使用该变量delimiter $-- 创建存储过程create procedure pro_test2()begin -- 定义变量 declare name varchar(20); -- 赋值 set name = '存储过程中的变量'; -- 使用变量 select name;end$delimiter ;-- 调用存储过程call pro_test2();

执行如下：

mysql> call pro_test2();+--------------------------+| name |+--------------------------+| 存储过程中的变量 |+--------------------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

在存储过程中定义两个int变量,用于男女同学的总分

-- 3. 在存储过程中定义两个int变量,用于男女同学的总分delimiter $-- 创建存储过程create procedure pro_test3()begin -- 定义两个变量(因为类型相同,所以可以写在同一行) declare men,women int; -- 查询男同学的总分,并赋值给变量men select sum(score) into men from student where gender = '男'; -- 查询女同学的总分,并赋值给变量women select sum(score) into women from student where gender = '女'; -- 使用变量 select men,women;end$delimiter ;-- 调用存储过程call pro_test3();

执行如下：

-- 调用存储过程mysql> call pro_test3();+------+-------+| men | women |+------+-------+| 193 | 190 |+------+-------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

5. if 条件判断语句

5.1 语法

# java的if判断if(判断条件1){ 执行语句1}else if(判断条件2){ 执行语句2}else if ...else{ 执行语句n }

if 判断条件1 then 执行的sql语句1;[elseif 判断条件2 then 执行的sql语句2;]...[else 执行的sql语句n;]end if;

5.2 实例

/* 实例: 定义一个int变量 total,用于存储班级总成绩 定义一个varchar变量 info,用于存储分数描述 根据总成绩判断并设置: total>380分, info = 学习优秀 320<=total<=380分, info = 学习不错 total<320分, info = 学习一般 */delimiter $-- 创建存储过程create procedure pro_test4()begin -- 定义变量 declare total int; declare info varchar(10); -- 赋值total,并判断 select sum(score) into total from student; -- 判断total的值，设置info描述 if total > 380 then set info = '学习优秀'; elseif total >= 320 then set info = '学习不错'; else set info = '学习一般'; end if; -- 使用变量 select total,info; end$delimiter ; -- 调用存储过程call pro_test4();

执行如下：

-- 调用存储过程mysql> call pro_test4();+-------+--------------+| total | info |+-------+--------------+| 383 | 学习优秀 |+-------+--------------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

6. 参数传递

6.1 用户变量

在上面我们学习了局部变量，而在学习参数传递之前，我们还应该学习好用户变量。

那么 用户变量 与 局部变量 有什么区别呢？

用户变量：可以在 mysql 登录会话中的任意地方使用局部变量：只能在 存储过程 的 begin...end 之间使用，其他地方使用则会报错。

# 用户变量1. 看成java中的成员变量,当前会话有效: 一次的登录到退出2. 用户变量的定义 set @变量名 = 变量值; -- 用户变量的变量名之前必须加@ -- 用户变量必须给值3. 用户变量的使用 select @变量名;

下面我们来演示操作一下 用户变量 与 局部变量 的区别：

-- 1. 在非存储过程的代码中，无法使用局部变量mysql> declare num01 int;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'declare num01 int' at line 1mysql> mysql> set num01 = 10;ERROR 1193 (HY000): Unknown system variable 'num01'mysql> -- 2. 在变量的前面增加 @ 符号，将其设置为 用户变量，即可使用-- 2.1 用户变量的定义mysql> set @num01 = 10;Query OK, 0 rows affected (0.00 sec)-- 2.2 用户变量的使用mysql> select @num01;+--------+| @num01 |+--------+| 10 |+--------+1 row in set (0.00 sec)mysql>

好了，目前我们已经大概理解了什么是 用户变量，然后我们就可以开始学习使用 用户变量 来进行 参数传递 的使用了。

6.2 语法:

# 存储过程的语法delimiter $-- 创建存储过程create procedure 存储过程名称([in/out/inout] 参数名 参数类型)begin sql语句列表;end$ delimiter ;# 存储过程的参数和返回值IN : 代表输入参数，也就是需要调用方传递实际数据(默认)OUT: 代表输出参数，也就是该参数可以作为返回值INOUT: 既可以作为输入参数，也可以作为输出参数

6.3 实例

执行如下：

# 备注@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做 系统变量

7. while 循环语句

7.1 语法

初始化语句;while 条件判断语句 do 循环体语句; 条件控制语句;end while;

# java的循环-- 实例: 计算1~100之间的偶数和 -- 方案一: for循环int result = 0;for(int i=0;i<=100;i++){ if(i % 2 == 0){ result += i; }}sout: result -- 方案二: while循环int result = 0;int i = 1;while(i <= 100){ if(i % 2 == 0){ result += i; } i++;}sout: result

7.2 实例

-- 实例: 计算1~100之间的偶数和delimiter $create procedure pro_test6()begin -- 定义求和变量 declare result int default 0; -- 定义循环变量 declare i int default 1; -- while循环 while i <= 100 do if i % 2 = 0 then set result = result + i; end if; set i = i + 1; end while; select result; end$delimiter ;-- 调用存储过程call pro_test6();

执行如下：

-- 调用存储过程mysql> call pro_test6();+--------+| result |+--------+| 2550 |+--------+1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>

8. 存储函数

8.1 语法

1. 存储函数和存储过程是非常相似的,区别在于存储函数必须有返回值 2. 创建存储函数 delimiter $ create function 函数名(参数列表) returns 返回值类型 begin sql语句列表; return 结果; end$ delimiter ;3. 调用函数 select 函数名(实际参数);4. 删除存储函数 drop function 函数名;

8.2 实例

-- 案例: 定义存储函数,获取学生表中成绩大于95分的学生数量delimiter $create function fun_test1()returns intbegin -- 定义变量 declare s_count int; -- 查询成绩大于95分的学生数量,并给s_count赋值 select count(*) into s_count from student where score > 95; -- 返回统计结果 return s_count;end$delimiter ;-- 调用函数select fun_test1();-- 删除函数drop function fun_test1;

执行如下：

-- 调用函数mysql> select fun_test1();+-------------+| fun_test1() |+-------------+| 2 |+-------------+1 row in set (0.00 sec)-- 删除函数mysql> drop function fun_test1;Query OK, 0 rows affected (0.00 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。