1. 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合
a. 类似于java中的方法: 是一段java代码的集合
b. 存储过程是可以编程的,意味着可以使用变量,表达式,结构控制等语句来完成比较复杂的功能
2. 存储过程和函数的好处
a. 提高代码的复用性
b. 减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率
java :
1. 定义方法 getSum(){这段代码} 多次调用getSum -> 这段代码只需要编译
2. 没有定义方法 这段代码 需要多次调用,需要多次编写 -> 这段代码多次编译
3. 存储过程和函数的区别
存储过程和函数很类似,主要的区别有
a. 函数必须有返回值
b. 存储过程可以没有返回值,也可以有
delimiter $ -- 修改结束分隔符为$
-- 创建存储过程
create procedure 存储过程名称(参数列表)
begin
-- SQL语句列表
end$
delimiter ; -- 修改结束分隔符为;
-- 调用存储过程
call 存储过程名称(实际参数) ;
-- 准备数据
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>
# 创建和调用存储过程
-- 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)
-- 查询数据库中所有的存储过程
select * from mysql.proc where db='数据库名称';
-- 删除存储过程
drop procedure [if exists] 存储过程名称;
-- 查询数据库中所有的存储过程
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: utf8
collation_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;
end
1 row in set (0.00 sec)
-- 删除存储过程
mysql> drop procedure if exists stu_group;
Query OK, 0 rows affected (0.00 sec)
-- 1. 定义变量
-- 注意: 这是一个局部变量,作用范围在begin...end之间
declare 变量名 数据类型[default 默认值];
-- 2. 变量赋值
-- 变量赋值方式一:
set 变量名 = 变量值;
-- 变量赋值方式二: 将查询到的一个结果赋值给变量
select 列名 into 变量
from 表名[where 条件];
-- 3. 使用变量: 这样才能在控制台上看到这个变量
select 变量名;
1.在存储过程中定义一个int类型变量(默认值10),并使用该变量
-- 1. 在存储过程中定义一个int类型变量(默认值10),并使用该变量
delimiter $ -- 声明结束符 $
-- 创建存储过程
create procedure pro_test1()
begin
-- 定义变量
declare num int default 10;
-- 使用变量
select num;
end$
delimiter ; -- 声明结束符 ;
-- 调用存储过程
call pro_test1();
执行如下:
-- 调用存储过程,获取变量num的值为10
mysql> 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>
-- 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>
# 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;
/*
实例:
定义一个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>
在上面我们学习了局部变量,而在学习参数传递之前,我们还应该学习好用户变量。
那么 用户变量 与 局部变量 有什么区别呢?
# 用户变量
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 1
mysql>
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>
好了,目前我们已经大概理解了什么是 用户变量,然后我们就可以开始学习使用 用户变量 来进行 参数传递 的使用了。
# 存储过程的语法
delimiter $
-- 创建存储过程
create procedure 存储过程名称([in/out/inout] 参数名 参数类型)
begin
sql语句列表;
end$
delimiter ;
# 存储过程的参数和返回值
IN : 代表输入参数,也就是需要调用方传递实际数据(默认)
OUT: 代表输出参数,也就是该参数可以作为返回值
INOUT: 既可以作为输入参数,也可以作为输出参数
/*
实例:
定义一个int变量 total,用于存储班级总成绩
定义一个varchar变量 info,用于存储分数描述
根据总成绩判断并设置:
total>380分, info = 学习优秀
320<=total<=380分, info = 学习不错
total<320分, info = 学习一般
*/
delimiter $
-- 创建存储过程:传入total参数,传出info参数
create procedure pro_test5(in total int,out info varchar(20))
begin
if total > 380 then
set info = '学习优秀';
elseif total >= 320 then
set info = '学习不错';
else
set info = '学习一般';
end if;
end$
delimiter ;
-- 调用
-- 1. 直接给total赋值一个常量
-- 注意: out输出变量需要在调用时声明,以便在存储过程外调用
-- 并且此变量是当前会话变量(可以理解成java中的成员变量),当前会话可访问
-- 语法: set @变量名 = "初始值"
-- @符号必须要加
-- 并且给此变量赋值
set @result = '初始值';
call pro_test5(350,@result);
select @result;
-- 2. 给total赋值一个sql语句的执行结果
set @total = (select sum(score) from student);
set @result = '初始值';
call pro_test5(@total,@result);
select @total,@result;
执行如下:
# 备注
@description : 这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做 系统变量
初始化语句;
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
-- 实例: 计算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>
1. 存储函数和存储过程是非常相似的,区别在于存储函数必须有返回值
2. 创建存储函数
delimiter $
create function 函数名(参数列表)
returns 返回值类型
begin
sql语句列表;
return 结果;
end$
delimiter ;
3. 调用函数
select 函数名(实际参数);
4. 删除存储函数
drop function 函数名;
-- 案例: 定义存储函数,获取学生表中成绩大于95分的学生数量
delimiter $
create function fun_test1()
returns int
begin
-- 定义变量
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)