mysql存储过程的作用是什么

mysql存储过程的作用：1、通过把处理封装在容易使用的单元中，简化复杂的操作；2、简化对变动的管理；3、有助于提高应用程序的性能；4、有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只用发送存储过程的名称和参数；5、可增强SQL语句的功能和灵活性，使mysql能完成复杂的判断和较复杂的运算；6、可提高数据库的安全性和数据的完整性等等。

存储过程简介

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

常用操作数据库的 SQL 语句在执行的时候需要先编译，然后执行。存储过程则采用另一种方式来执行 SQL 语句。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

MySQL 5.0 版本以前并不支持存储过程，这使 MySQL 在应用上大打折扣。MySQL 从 5.0 版本开始支持存储过程，既提高了数据库的处理速度，同时也提高了数据库编程的灵活性

存储过程是数据库中的一个重要功能，存储过程可以用来转换数据、数据迁移、制作报表，它类似于编程语言，一次执行成功，就可以随时被调用，完成指定的功能操作。

使用存储过程不仅可以提高数据库的访问效率，同时也可以提高数据库使用的安全性。

对于调用者来说，存储过程封装了 SQL 语句，调用者无需考虑逻辑功能的具体实现过程。只是简单调用即可，它可以被 Java 和 C# 等编程语言调用。

存储过程的作用（优点）

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

• 简化对变动的管理。如果表名、列名或业务逻辑有变化。只需要更改存储过程的代码，使用它的人员不会改自己的代码；

• 通常存储过程有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。 但是，MySQL 实现的存储过程略有不同。MySQL 存储过程按需编译。在编译存储过程之后，MySQL 将其放入缓存中。MySQL 为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询；

• 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的 SQL 语句，而只用发送存储过程的名称和参数；

• 可增强 SQL 语句的功能和灵活性
  

  存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

• 提高数据库的安全性和数据的完整性

  存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

• 使数据独立

  数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

存储过程的缺点

• 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则 CPU 使用率也会增加，因为 MySQL 数据库最初的设计侧重于高效的查询，不利于逻辑运算；

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

• 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL 不提供调试存储过程的功能；

• 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

MySQL 中的存储过程

创建与调用过程

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

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

运行结果如下

也可以在 Navicat 客户端“函数”节点下查看过程，如下图所示：

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

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

运行结果

存储过程语法解析

• create procedure 用来创建过程；

• mypro 用来定义过程名称；

• (in a int,in b int,out sum int)表示过程的参数，其中 in 表示输入参数，out 表示输出参数。类似于 Java 定义方法时的形参和返回值；

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

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

存储过程的参数

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

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

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

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

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

1).没有参数的过程；

2).只有输入参数的过程；

3).只有输出参数的过程；

4).包含输入和输出参数的过程。

变量

MySQL 中的存储过程类似 java 中的方法。

既然如此，在存储过程中也同样可以使用变量。java 中的局部变量作用域是变量所在的方法，而 MySQL 中的局部变量作用域是所在的存储过程。

变量定义

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

• declare用于声明变量；

• variable_name表示变量名称；

• datatype为 MySQL 的数据类型；

• default用于声明默认值;

• 例如：

  
  

  declare name varchar(20) default ‘jack’。
  

  
  

变量赋值

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();

运行结果

流程控制语句

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);

运行结果

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 语法都可以实现条件判断，但第一种适合范围值判断，而第二种适合确定值判断。

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;

运行结果

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;

运行结果

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 客户端工具进行管理，鼠标点击操作即可，如下图所示：

显示存储过程

SHOW PROCEDURE STATUS;

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

SHOW PROCEDURE status where db = 'schooldb';

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

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

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

SHOW CREATE PROCEDURE mypro1;

删除存储过程“mypro1”

drop PROCEDURE mypro1;