MySQL-存储过程

再谈存储过程，我们先想想什么是存储过程？存储过程能做什么？怎么使用存储过程？

什么是存储过程及他能做什么

一言以蔽之，由多条SQL语句组成且经过编译存储在数据库中的集合。

那么为什么需要存储过程？理由有很多，当然也有人主张不去使用它，我以为只要是能够方便我们就会有存在意义，而我觉得存储过程的用处彰显明显业务是在如下三点：

1、当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。

2、当我们有业务需要重复执行某些SQL语句时；

3、安全极为重要之时。例如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。

所以它比普通的SQL语句有如下优点：

1、性能的提高：SQL语句在创建过程时进行分析和编译，而存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划，这样，在执行过程时便可节省此开销；

2、降低网络开销：存储过程调用时只需用提供存储过程名和必要的参数信息，减少数据在数据库和应用服务器之间的传输；

3、便于进行代码的移植：由于存储过程是存储在数据库上，与业务所使用的语言没有关系

4、安全性更高；

怎么使用存储过程

创建

当然，在创建时用户必须具有一定权限，所以在创建存储过程时候需要有create routine的权限，移除修改必须有alter routine权限，执行需要有execute权限，我们也可以使用select * from mysql.user where user='#' and host='#';来查看当前自己拥有的权限，而对于一个数据库服务我们可以在mysql数据库的proc表里看到所有的存储过程

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

注意如果我们使用mysql client来定义存储过程要注意结束符，mysql默认结束符是分号，所以我们在这种情况下需要暂时修改结束符标志，使用delimiter来改变结束标识符

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

其中参数有三种模式IN,OUT,INOUT，IN是默认模式，如名字所言：

IN就是把该参数实际值参入到存储过程中，他会被看成只读不可写；

OUT在调用存储过程时会忽略实际参数的值，在存储过程内部

INOUT就是以上两种模式的混合，可读可写

为了解释以上三种模式参数，这里举具体例子：

1、首先创建用于实例的简单表

IN模式与OUT模式：

定义存储过程用于插入一条新记录并且返回总条数，set @total=0定义变量并初始化，通过此变量去接受存储过程处理完成输出的结果。

在上图我们使用自定义变量@age来作为IN的模式参数，并且在存储过程中将count(*)赋值给newage也就是@age，但是发现值并没有改变。

INOUT模式

 

在上面例子里，我们自定义变量username和age并且在存储过程中为其赋id为1的新值，由结果可以看到，INOUT既可以做输入也可以做输出。

其次便是讨论到权限问题了，如果你只是普通用户，那就需要注意了，我们在使用普通用户创建存储过程是需要create routine 权限的，而移除修改必须有alter routine权限，执行需要有execute权限；

同时MySQL本身也会对存储过程语法结构有限制，主要是SQL SECURITY，它有两个选项DEFINER和INVOKER，分别表示存储过程使用创建者的许可执行和使用调用者的许可执行，默认情况下，系统指定为DEFINER 。

可以通过以下例子了解其中意思：

在上面这张图中我们创建一个用户lanco并且只可以登录，接着授予对数据库tes可t创建存储过程权限给该用户，刷新并查看权限；

	左边的存储过程是新建用户lanco@localhost创建，由于没有执行权限，所以调用存储过程会报错
而当我们使用root用户去执行时候依旧会报错， 究其原因是因为我们在创建时候没有去指定definer，所以默认是当前用户lanco@localhost,而SQL SECURITY 的默认也是DEFINER，所以在执行时候需要检查创建者是否有足够去权限

接着我们赋予lanco有执行和修改存储过程的权限，这时候的lanco用户具有创建，执行修改存储过程的权限，但是其实还是无法执行的：

	言外之意很清楚，一名普通用户仅仅具有创建，修改和执行存储过 程权限还是不够，还应该有存储过程内部需要的权限，如本例中存储过程是select一条记录，所以应该还具有select权限。
	root用户也是同样无法存储过程，这也进一步验证了definer的作用，在默认情况下是创建者，并且在执行时检验创建者的权限

这里还有一点需要注意，假如我的存储过程创建者lanco具备了以上需要的权限，而现在是一位新的用户lanco2想要执行该存储过程，他只具有登录和update数据库test的权限，我们去执行call testproc：

而当我们赋予这位新用户lanco2的execute权限之后：

所以总结当SQL SECURITY为DEFINER时候，只需要创建时候验证创建时候指定的用户，而其他用户只需要有登录和execute权限即可以调用存储过程。

上面说的是SQL SECURITY为默认值DEFINER的情况，下面来说一下值为INVOKER的情况，依然举如下例子：

再回收lanco2用户的权限之后，他还具有 UPDATE, CREATE ROUTINE的权限，我们用该用户创建新的存储过程：

所以我们接下来使用root用户调用存储过程：

如图，即使创建者lanco2没有足够的权限，但是依然可以执行，这也证明INVOKER是检查调用者而不是创建者的权限。

讲完上面比较复杂的内容，下面讲一下一些简单的内容：

删除存储过程

****drop  procedure 名称

查看存储过程

***show procedure status like 名称

查看存储过程定义

***show create procedure 名称

通过information_schema.Routines了解存储过程

谢谢阅读，如有意见欢迎指正，共同学习进步^^