MySql存储过程

存储过程简介

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

• 大多数 SQL语句都是针对一个或多个表的单条语句。并非所有的操作都那么简单。经常会有一个完整的操作需要多条语句才能完成。存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL 语句的集合。可将其视为批处理文件。虽然他们的作用不仅限于批处理。
• 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

2、存储过程的优点

1. 通过把处理封装在容易使用的单元中，简化复杂的操作；
2. 简化对变动的管理。如果表名、列名或业务逻辑有变化只需要更改存储过程的代码，使用它的人员不会改自己的代码；
3. 通常存储过程有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。 但是，MySQL 实现的存储过程略有不同。MySQL 存储过程按需编译。在编译存储过程之后，MySQL 将其放入缓存中。MySQL 为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询；
4. 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只用发送存储过程的名称和参数；
5. 存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能；
6. 存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

3、存储过程的缺点

1. 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则 CPU 使用率也会增加，因为 MySQL 数据库最初的设计侧重于高效的查询，不利于逻辑运算；
2. 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难；
3. 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能；
4. 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

MySQL 中的存储过程

1、创建与调用过程

创建存储过程，代码如下所示：

-- 创建存储过程
-- 存储过程的创建
create procedure 
mypro(in a int,in b int,out sum int)
BEGIN
	set sum=a+b;
end;
-- procedure存储过程关键词
-- 过程名称mypro
-- begin  end相当于java里的{}
-- in输入 out输出 inout可以输入也可以输出

调用存储过程，代码如下所示：

call mypro(1,2,@s);-- call调用
select @s;-- @s 是用来接收过程输出参数的变量

运行结果如下：

2、存储过程语法解析

1. create procedure 用来创建过程；
2. mypro 用来定义过程名称；
3. (in a int,in b int,out sum int)表示过程的参数，其中 in表示输入参数，out 表示输出参数。类似于Java 定 义方法时的形参和返回值；
4. begin 与 end 表示过程主体的开始和结束，相当于 Java 定义方法的一对大括号；
5. call 用来调用过程，@s 是用来接收过程输出参数的变量。

3、存储过程的参数

有三种参数类型：

1. IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）；
2. OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）；
3. INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

存储过程根据参数可分为四种类别：

1. 没有参数的过程；
2. 只有输入参数的过程；
3. 只有输出参数的过程；
4. 包含输入和输出参数的过程。

4、变量

(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 mypro2()-- 定义存储过程
BEGIN
	declare name varchar(20);-- 定义一个变量
	set name='张';-- 给变量赋值
	select * from mytable where username=name;
end;
call mypro2();-- 调用存储过程

运行结果如下：

5、流程控制语句

（1）、if 条件语句

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE 执行语句

定义存储过程，输入一个整数，使用 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 语句判断是正数还是负数，代码如下所示：

-- 创建过程
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);

运行结果如下：

（3）、while 循环语句

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

定义存储过程，使用 while 循环输出 1 到 10 的累加和，代码如下所示：

-- 创建过程
create procedure mypro5(out sum int)
begin
declare num int default 0;-- num 不可以直接赋值为0，可以默认为0
set sum = 0;
while num<10 do -- 循环开始
set num = num+1;-- 不要忘记使用set赋值
set sum = sum+num;
end while; -- 循环结束
end;
-- 调用过程
call mypro5(@sum);
-- 查询变量值
select @sum;

运行结果如下：

（4）、repeat 循环语句

定义存储过程，使用 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 循环语句

1、 leave 语句效果相当于 java 中的 break，用来终止循环；
2、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;

运行结果如下：

存储过程的管理

1、显示存储过程

显示存储过程，代码如下所示：

SHOW PROCEDURE STATUS;

显示特定数据库的存储过程，代码如下所示：

-- 数据库名schooldb
SHOW PROCEDURE status where db = 'schooldb';

显示特定模式的存储过程，要求显示名称中包含“my”的存储过程，代码如下所示：

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

2、显示存储过程源码

显示存储过程“mypro1”的源码，代码如下所示：

-- mypro1存储过程名称
SHOW CREATE PROCEDURE mypro1

3、删除存储过程

删除存储过程“mypro1”，代码如下所示：

-- mypro1存储过程名称
drop PROCEDURE mypro1;