MySQL数据库存储过程和存储函数

MySql数据库存储过程和函数

前言

对于MySql数据库，难道只能遇到一个数据库的需求，然后就写一大堆sql语句吗？MySql的SQL语句能不能像正常的编程一样提供函数这种模式，通过对一定的业务需求进行封装SQL语句，然后下次使用的时候，直接调用该函数即可呢？答案是可以的，数据库的存储过程以及存储函数就是解决上文阐述的问题的，本文将讲解MySql存储过程以及存储函数。

一、存储过程和存储函数的区别

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

二、存储过程如何创建

• 语法结构

delimiter $ --将sql语句结束符号修改为$,这样只有sql遇到$时才开始执行
create procedure 存储过程名(参数列表)
begin
sql语句集合
end$
delimiter ; --将结束符修改为默认的分号 

• 示例
  

三、存储过程如何调用

• 语法

call 存储过程名(参数列表);

• 示例
  

四、存储过程如何查看

• 语法

select * from information_schema.routines where routine_schema='数据名' \G;

• 示例
  

五、存储过程如何删除

• 语法

drop procedure [if exists] 存储过程名;

• 示例

drop procedure if exists pro_t1;

六、存储过程具体语法

存储过程和函数是可以编程的，意味着可以使用变量，表达式，控制结构 等语法来完成比较复杂的功能。

1. 变量

1.1 声明变量语法

通过declare可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

declare 变量名[,...] type [default 默认值]

1.2 为变量赋值语法

1. 直接赋值常量或者赋值表达式

set 变量名=表达式

• 示例：为查询db1数据库中的有哪些存储过程编写存储过程。

create procedure show_pro()
begin
declare intro varchar(100);
set intro = 'db1数据库包含以下存储过程:';
select intro;
select routine_name 存储名, routine_type 类型 from information_schema.routines where routine_schema='db1' ;
end$

2. 通过select … into 方式进行赋值操作

select 筛选字段(或聚合函数) into 变量名 from 表名;

• 示例：查询city中有多少行记录的存储过程

create procedure pro_t2()
begin
declare num int;
select count(*) into num from city;
select num;
end$

2. if条件判断

• 语法结构

if 满足条件 then
执行语句
elseif 满足条件 then
执行语句
else 
执行语句
end if;

• 示例
  根据定义的身高变量，判定当前身高的所属的身材类型；
  180 及以上 ----------> 身材高挑
  170 - 180 ---------> 标准身材
  170 以下 ----------> 一般身材

create procedure pro_t3()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select description ;
end$

3. 传递参数

• 语法结构

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

IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

• in类型传参示例：对于之前创建的查看db1数据库中有哪些存储过程的show_pro，我们可以给show_pro一个输入参数，实现查看任意数据库中有哪些存储过程。

create procedure show_pro(in db_name varchar(20))
begin
declare intro varchar(100);
select routine_name 存储名, routine_type 类型 from information_schema.routines where routine_schema=db_name;
end$

• out类型传参示例：
  根据传入的身高变量，获取当前身高的所属的身材类型

create procedure pro_t4(in height int , out description varchar(100))
begin
if height >= 180 then
set description='高挑';
elseif height >= 170 and height < 180 then
set description='标准';
else
set description='一般';
end if;
end$

调用方式

call pro_t4(180,@res);
select @res

在变量前添加@符号的变量叫做用户会话变量，这种的作用于是整个会话过程。

4. case结构

• 语法结构

case 
when 条件 then
执行sql语句
when 条件 then
执行sql语句
...
else
执行sql语句
end case;

• 示例：根据传入的身高变量，判定当前身高的所属的身材类型

create procedure pro_t5(height int)
begin
declare description varchar(20);
case
when height >= 180 then
set description='高挑';
when height >= 170 and height < 180 then
set description='标准';
else
set description='一般';
end case;
select concat(height,'是',description,'身材') result;
end$

5. while循环

• 语法结构

while 满足的条件 do
执行sql语句
end while;

• 示例：计算从1加到n的值

create procedure pro_t6(n int)
begin
declare total int default 0;
declare num int default 1;
while num<=n do
set total = total + num;
set num = num + 1;
end while;
select total;
end$

6. repeat结构

repeat结构类编程语言中的do while。

• 语法

repeat
执行sql语句
until 不满的条件
end repeat;

• 示例：计算从1加到n的值

create procedure pro_t7(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$

7. loop和leave语句

LOOP 实现简单的循环，通常结合 LEAVE 语句退出循环。

• loop语法结构

[loop标签名:] loop
执行sql语句(sql语句中应该包括，退出循环的命令：leave loop标签名;如果没有将会造成死循环)
end loop [loop标签名];

• 示例：计算从1加到n的值

create procedure pro_t8(n int)
begin
declare total int default 0;
lb:loop
if n<=0 then
leave lb;
end if;
set total=total+n;
set n=n-1;
end loop lb;
select total;
end$

8. 游标（光标）

游标又称光标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。类似高级编程语言中的集合类型。
光标的使用包括光标的声明、open、fetch和 close，其语法分别如下：

• 声明光标

declare 光标名称 cursor for sql语句;

• 打开光标

open 光标名;

• 获取光标(将光标当前数据存储到变量中)

fetch 光标名 into 变量名[,变量名...];

• 关闭光标

close 光标名;

示例：
1.创建一张员工表，并插入数据

create table emp(
id int(11) not null auto_increment ,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key(`id`)
)engine=innodb default charset=utf8 ;

其中comment是为每个字段添加说明信息。

insert into 
emp(id,name,age,salary) 
values
(null,'金毛狮王',55,3800),
(null,'白眉鹰王',60,4000),
(null,'青翼蝠王',38,2800),
(null,'紫衫龙王',42,1800);

2。创建存储过程，通过获取游标中的数据，显示emp表中的信息。

create procedure pro_t9()
begin
declare id int(10);
declare name varchar(50);
declare age int(10);
declare salary int(10);
declare has_data int default 1; -- 终止循环的条件
-- 定义游标
declare emp_cursor cursor for select * from emp;
-- 如果游标数据为空，则执行sql语句，并退出
declare exit handler for not found set has_data=0;
-- 打开游标
open emp_cursor;
-- repeat循环获取游标中的数据
repeat
-- 获取游标中的数据
fetch emp_cursor into id,name,age,salary;
select concat('id: ',id,' name: ',name,' age: ',age,' salary: ',salary);
until has_data=0
end repeat;
-- 关闭游标
close emp_cursor;
end$

七、存储函数

在意义上，存储函数与存储过程唯一区别就是存储函数具有返回值，而存储过程没有返回值。但是，因为存储过程传递的参数有输出参数类型，也用来可以代替返回值，因此，存储过程完全可以完成存储函数的操作。
在语法上，存储函数具体的语法都和存储过程一样。

• 创建存储函数语法结构

delimiter $ --将sql语句结束符号修改为$,这样只有sql遇到$时才开始执行

create function 存储函数名([参数名 类型,...])
returns type --返回值类型
begin
... 
return 变量名;
end$

delimiter ; --将结束符修改为默认的分号 

• 调用存储函数语法结构

select 存储函数名(参数列表);

• 删除存储函数语法结构

drop function 存储函数名

• 查看存储函数和查看存储过程的语法一样
• 示例：定义一个存储函数, 获取满足条件的总记录数

SET GLOBAL log_bin_trust_function_creators = 1;
create function get_count(cid int)
returns int
begin
declare num int;
select count(*) into num from city where country_id=cid;
return num;
end$