存储过程

一、存储过程简介

1、为什么要用存储过程？

​ 为以后的使用而保存的一条或多条 MySQL 语句的集合

​ 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、存储过程的优点

• 通过把处理封装在容易使用的单元中，简化复杂的操作；

• 简化对变动的管理。

• 通常存储过程有助于提高应用程序的性能。

• 存储过程有助于减少应用程序和数据库服务器之间的流量

• 存储的程序对任何应用程序都是可重用的和透明的。

• 存储的程序是安全的。

3、存储过程的缺点

• 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。

• 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难；

• 很难调试存储过程。

• 开发和维护存储过程并不容易。

二、MySQL 中的存储过程

1、创建与调用过程

创建存储过程

-- 创建存储过程
create procedure mypro(in a int,in b int,out sum int)
begin
	set sum = a+b;
end;

存储过程语法解析

• create procedure 用来创建过程；

• mypro 用来定义过程名称；

• (in a int,in b int,out sum int)表示过程的参数

  其中 in 表示输入参数，out 表示输出参数。类似于 Java 定义方法时的形参和返回值；

• begin 与 end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；

• call 用来调用过程，@s 是用来接收过程输出参数的变量。 存储过程的参数

存储过程的参数

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型：

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；

• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；

• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

调用存储过程

call mypro(1,2,@s);-- 调用存储过程 
select @s;-- 显示过程输出结果

2、变量

MySQL 中的存储过程类似 java 中的方法。既然如此，在存储过程中也同样可以使用变量。

1. 变量定义

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

• declare 用于声明变量；

• variable_name 表示变量名称；

• datatype 为 MySQL 的数据类型；

• default 用于声明默认值;

• 例如：declare name varchar(20) default ‘jack’。

2. 变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

​ 在存储过程中使用变量

use schooldb;-- 使用 schooldb 数据库
-- 创建过程
create procedure mypro1()
begin
	declare name varchar(20); 
	set name = '丘处机';
    select * from studentinfo where studentname = name;
end;
-- 调用过程
call mypro1();

3、流程控制语句

1. if 条件语句

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE 执行语句，与编程语言中的 if、else if、else 语法类似。

• 定义存储过程，输入一个整数，使用 if 语句判断是正数还是负数

-- 创建过程 
create procedure mypro2(in num int) 
begin
	if num<0 then -- 条件开始 
		select '负数'; 
	elseif num=0 then 
		select '不是正数也不是负数'; 
	else
		select '正数'; 
	end if;-- 条件结束 
end; 
-- 调用过程 
call mypro2(-1);

2. case 条件语句

case 是另一个条件判断的语句，类似于编程语言中的 choose、when 语法。MySQL 中的 case 语句有两种语法 格式。

• 定义存储过程，输入一个整数，使用 case 语句判断是正数还是负数

-- 创建过程
create procedure mypro3(in num int)
begin
	case -- 条件开始
		when num<0 then select '负数';
		when num=0 then select '不是正数也不是负数';
	else select '正数';
	end case; -- 条件结束
end;
-- 调用过程
call mypro3(1);

• 定义存储过程，输入一个整数，使用 case 语句判断是 1 还是 2

-- 创建过程
create procedure mypro4(in num int)
begin
	case num -- 条件开始
		when 1 then select '数值是 1';
		when 2 then select '数值是 2';
		else select '不是 1 也不是 2';
	end case; -- 条件结束
end;
-- 调用过程
call mypro4(3);

**提示：**两种 case 语法都可以实现条件判断，但第一种适合范围值判断，而第二种适合确定值判断。

3. while 循环语句

while 语句的用法和 java 中的 while 循环类似。

• 定义存储过程，使用 while 循环输出 1 到 10 的累加和

-- 创建过程
create procedure mypro5(out sum int)
begin
	declare num int default 0;
	set sum=0;
	while num<10 do -- 循环开始
		set num = num + 1;
		set sum = sum + num;
	end while; -- 循环结束
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;

4. repeat 循环语句

repeat 语句的用法和 java 中的 do…while 语句类似，都是先执行循环操作，再判断条件，

区别是 repeat 表达式值为 false 时才执行循环操作，直到表达式值为 true 停止。

• 定义存储过程，使用 repeat 循环输出 1 到 10 的累加和

-- 创建过程
create procedure mypro6(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	repeat-- 循环开始
		set num = num+1;
		set sum = sum+num;
	until num>=10
	end repeat; -- 循环结束
end; -- 调用过程 
call mypro6(@sum); -- 查询变量值 
select @sum;

5. loop 循环语句

循环语句，用来重复执行某些语句。执行过程中可使用 leave 语句或 iterate 跳出循环，也可以嵌套 IF 等判断语句。

leave 语句效果相当于 java 中的 break，用来终止循环；

iterate 语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

• 定义存储过程，使用 loop 循环输出 1 到 10 的累加和

-- 创建过程
create procedure mypro7(out sum int)
begin
	declare num int default 0; 
	set sum = 0; 
	loop_sum:loop-- 循环开始
		set num = num+1; 
		set sum = sum+num; 
		if num>=10 then
			leave loop_sum;
		end if;
	end loop loop_sum; -- 循环结束
end; -- 调用过程 
call mypro7(@sum); -- 查询变量值 
select @sum;

注意：代码中的 loop_sum 相当于给循环贴个标签，方便多重循环时灵活操作。

三、存储过程的管理

存储过程的管理主要包括：显示过程、显示过程源码、删除过程。

比较简单的方式就是利用 navicat 客户端 工具进行管理，鼠标点击操作即可

1、显示存储过程

• 显示存储过程

SHOW PROCEDURE STATUS;

• 显示特定数据库的存储过程

SHOW PROCEDURE status where db = 'schooldb';

• 显示特定模式的存储过程，要求显示名称中包含“my”的存储过程

SHOW PROCEDURE status where name like '%my%';

2、显示存储过程源码

• 显示存储过程“mypro1”的源码

SHOW CREATE PROCEDURE mypro1;

3、删除存储过程

• 删除存储过程“mypro1”

drop PROCEDURE mypro1;