mysql存储过程和函数

存储过程和函数

即实现经过编译并存储在数据库中的一段SQL语句的集合。可以处理简单的业务逻辑
好处：类似于java中定义方法的好处。

1. 提高了代码的复用性
2. 减少了数据在数据库和应用服务器直接按的传输，提高了效率
3. 较少了代码层面的业务处理
   两者区别：
4. 存储函数必须有返回值
5. 存储过程可以没有返回值

存储过程语法

• 创建相关

1. 创建存储过程

--1.创建存储过程
--修改结束分隔符：原sql语句是;结束，而存储过程中可能有多条sql语句
delimiter$
--创建存储过程
create procedure 存储过程名称(参数列表)
begin
	SQL语句列表;
end$
--修改结束分隔符
delimiter;

2. 调用

--2. 调用存储过程

call 存储过程(参数);

3. 查看

select * from mysql.proc where  db='数据库名称';

4. 删除

drop procedure if exists 存储过程名称;

• 定义变量
  定义位置在begin和end之间。

1. 定义变量给定默认值.。可选

declare 变量名 数据类型 [default 默认值];

2. 变量赋值一：

set 变量名=变量值;

3. 变量赋值二:

--将查询出的字段值赋值给变量
select 列名 into 变量名 from 表名 [where条件]

• if语句的使用

if 判断条件 then sql语句;
[elseif 判断条件2 then 执行的sql;]
--此处elseif没有空格，可以有多个，和java一致
...
[else 执行的sql;]
end if;

• 参数传递的使用

create procedure 存储过程名称([in|out|inout] 参数名 数据类型)
begin
	sql语句列表；
end$
--in代表输入参数。不写默认为in
--out代表输出参数
--inout代表既可以作为出入参数，也可以作为出入参数 

• while循环

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

存储函数

1. 创建存储函数

--1.创建存储函数
--修改结束分隔符：原sql语句是;结束，而存储过程中可能有多条sql语句
delimiter$
--创建存储函数
create funcation 函数名称(参数列表)
returns 返回值类型
begin
	SQL语句列表;
	return 结果;
end$
--修改结束分隔符
delimiter;

2. 调用存储函数

select 函数名称(实际参数);

3. 删除存储函数

drop function 函数名称;

• 案例

-- 1. 按照性别分类，计算成绩总和，按照升序
SELECT sex,sum(grade) grades from student GROUP BY sex ORDER BY sex

-- 2创建存储过程对上述需求
delimiter$
create procedure sumGrade()
begin
	SELECT sex,sum(grade) grades from student GROUP BY sex ORDER BY sex;
end$
delimiter;
-- 3 调用该存储过程

call sumGrade();
-- 4 查看所有存储过程
select * from mysql.proc where db='db1';
-- 该方法在mysql8.X已被弃用

-- 5删除2中存储过程、

drop PROCEDURE if EXISTS sumGrade;
-- 6 创建存储过程，定义一个默认值为100的int类型变量
-- 7 创建存储过程，定义一个字符串类型变量，并给其set赋值
delimiter$
create procedure sumGrade()
begin
	declare age int default 100;
	declare name VARCHAR(20);
	set name='yh';
	select age,name;
end$
delimiter;

-- 8创建存储，定义两个int类型变量，记录男女总成绩，并给其使用select赋值
delimiter$
create procedure sumGrade()
begin
	declare mGrade double;
	declare wGrade double;
	select sum(grade) into mGrade from student where sex='男';
	select sum(grade) into wGrade from student where sex='女';
	select mGrade,wGrade;
end$
delimiter;
-- 9  if使用：int变量存储学生总成绩，vachar变量保存总成绩级别，根据总成绩的划分为级别变量赋值
delimiter$
create procedure sumGrade()
begin
	declare sumGrades double;
	declare descs VARCHAR(100);
	select sum(grade) into sumGrades from student;
	if sumGrades > 100.0 then set descs='优秀成绩';
	elseif sumGrades<=100.0 then set descs='成绩合格';
	end if;
	select sumGrades,descs;
end$
delimiter;

-- 10参数列表使用。输入总成绩变量，输出成绩级别变量，用if完成级别判定
delimiter$
create procedure sumGrade(in sumGrades double,out descs VARCHAR(100))
begin
	if sumGrades > 100.0 then set descs='优秀成绩';
	elseif sumGrades<=100.0 then set descs='成绩合格';
	end if;
	select sumGrades,descs;
end$
delimiter;
-- 完成调用
call sumGrade(200,@descs);
-- 11while循环的使用：求1-100的偶数和。判断偶数时一个等号
delimiter$
create procedure sumGrade()
begin
	declare i int default 1;
	declare sums int default 0;
	while i<=100 do 
		if i%2=0 then set sums=sums+i;
		end if;
		set i=i+1;
	end while;
	select sums;
end$
delimiter;

call sumGrade();
-- 12 定义存储函数，获取学生表中成绩>20的学生数量
delimiter$
create function countS(g int)
returns int
begin
	declare s_count int;
	select count(*) into s_count from student where grade>g;
	return s_count;
end$
delimiter;

--调用函数
select countS(10);
-- 删除函数
drop function countS;