MySQL存储过程

题外话

之前和师兄在聊技术规划时，发现数据库这一块的东西可能是自己涉猎最少的地方，所以想要加深一步地理解。而师兄（一个非典型的DBA）给出的建议就是让我去多看看存储过程这一块的东西，于是我产生了写这篇文章的原始动力。

存储过程

数据库语言SQL语句在执行的时候需要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

和其它编程语言中的构造相似，因为它们都可以：

• 接受输入参数并以输出参数的格式（IN,OUT,INOUT）向调用程序返回多个值。
• 包含用于在数据库中执行操作的编程语句。 这包括调用其他过程。
• 向调用程序返回状态值，以指明成功或失败（以及失败的原因）。

存储过程的优点

减少了服务器/客户端网络流量

过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装，每个单独的代码行都不得不跨网络发送。

更强的安全性

存储过程相当于在客户端与数据库之间多设了一个中间保护层，可利用中间保护层设置安全权限，提高健壮性。

多个用户和客户端程序可以通过过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限。 过程控制执行哪些进程和活动，并且保护基础数据库对象。 这消除在了单独的对象级别授予权限的要求，并且简化了安全层。

可在 CREATE PROCEDURE 语句中指定 EXECUTE AS 子句以便实现对其他用户的模拟，或者使用户或应用程序无需针对基础对象和命令的直接权限，即可执行某些数据库活动。 例如，某些操作（如 TRUNCATE TABLE）没有可授予的权限。 若要执行 TRUNCATE TABLE，用户必须对指定表具有 ALTER 权限。 授予用户对表的 ALTER 权限可能不是最佳方法，因为用户将拥有超出截断表的能力的权限。 通过将 TRUNCATE TABLE 语句纳入模块中并指定该模块作为一个有权修改表的用户执行，您可以将截断表的权限扩展至授予其对模块的 EXECUTE 权限的用户。

在通过网络调用过程时，只有对执行过程的调用是可见的。 因此，恶意用户无法看到表和数据库对象名称、嵌入自己的 Transact-SQL 语句或搜索关键数据。

使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码，所以，攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。

可以对过程进行加密，这有助于对源代码进行模糊处理。

更快的速度

默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。 因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程

存储过程的语法（MySQL）

创建存储过程

MySQL创建存储过程的方式：
CREATE PROCEDURE 过程名([过程参数[…]]) [特性…] 过程体
举例来说（统计个数输出）：

mysql> DELIMITER //  
    mysql> CREATE PROCEDURE proc1(OUT s int)  
        -> BEGIN 
        -> SELECT COUNT(*) INTO s FROM user;  
        -> END 
        -> //  
    mysql> DELIMITER ; 

注意：
1. DELIMITER//和DELIMITER是分隔符的意思，将存储过程的代码和一般的代码分开
2. 存储过程的输入、输出、输入输出参数如果有多个的话，用”,” 分开
3. 过程体用BEGIN、END来做起止标识

存储过程的参数类型

• IN：输入参数。在调用存储过程时指定，默认为此类型
• OUT：输出参数，在存储过程中可以被改变，并且可返回
• INOUT：输入输出参数，IN和OUT结合

存储过程的调用

定义存储过程后可以通过CALL命令调用：

CALL <过程名>

以INOUT类型为例：

//创建过程
    mysql > DELIMITER //   
    mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
    -> BEGIN 
    -> SELECT p_inout;  
    -> SET p_inout=2;  
    -> SELECT p_inout;   
    -> END;  
    -> //   
    mysql > DELIMITER ; 

调用过程
    mysql > SET @p_out=1;  
    mysql > CALL sp_demo_out_parameter(@p_out);  

存储过程的变量

变量定义

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

例如：

DECLARE x,y INT DEFAULT 0

表示定义两个默认值为0的变量x,y

这里说明一下变量的定义，有些人提前了解过的可能知道变量有两种命名方式：@name和name，究竟加不加@的区别在哪呢。其实他们的区别就在于作用范围

如果不加@，那么name的作用范围一般就是在对应的BEGIN和END之间，END过后，此变量失效。

而加了@，那么@name就是会话变量（session variable），在整个会话过程中他都是有作用的，类似于全局变量，这种变量的用途比较广，因为只要在一个会话内（某一个连接过程中），这个变量可以在被调用的存储过程或者代码之间共享数据。

变量赋值

SET 变量名=表达式值

比如

SET @name = 1

还有在存储存储过程中使用用户变量

    mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
    mysql > SET @greeting='Hello';  
    mysql > CALL GreetWorld( );  
    +----------------------------+  
    | CONCAT(@greeting,' World') |  
    +----------------------------+  
    |  Hello World               |  
    +----------------------------+  

存储过程的查看

方法一：（直接查询）

select `specific_name` from mysql.proc where db = 'your_db_name' and `type` = 'procedure'

方法二：（查看数据库里所有存储过程+内容）

show procedure status

方法三：（查看当前数据库里存储过程列表）

select specific_name from mysql.proc

方法四：（查看某一个存储过程的具体内容）

select body from mysql.proc where specific_name = 'your_proc_name'

查看存储过程或函数的创建代码：

show create procedure your_proc_name

存储过程的删除

drop procedure your_proc_name

存储过程的控制语句

存储过程的控件语句主要有if-then-else语句，case语句，循环语句，迭代语句等等

if-then-else语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;  

case语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 

循环语句

while…end while语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ; 

repeat…end repeat语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ; 

loop…end loop语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ; 

ITERATE迭代

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ; 

总结

如果有不懂的地方，还是从文档上出发，去寻找官方给出的解决方案，推荐在这里学
直接在英文文档上面学习