Mysql存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

大多数SQL语句都是针对一个或多个表的单条语句。但是并不是所有的操作都是可以用一条语句来完成的，经常有一些操作是需要多条语句配合才能完成。
我们引入的存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

简单来说，存储过程就是为以后使用而保存的一条或多条SQL语句。可将其视为增强版的批处理文件。

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

使用存储过程的好处：

• 通过把处理封装在一个易用的单元中，可以简化复杂的操作。
• 不需要反复建立一系列的处理步骤，因而保证了数据的一致性。
• 简化了对变动的管理，这一点的延伸就是安全性。
• 存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
• 简化应用开发人员的工作。当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。
• 增强安全性。比如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。
• 减少数据在数据库和应用服务器之间的传输。
• 存储过程增强了SQL语言的功能和灵活性。存储过程可以用 流控制语句 编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
• 存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
• 存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织成 存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
• 存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

MySQL存储过程创建的格式：
CREATE PROCEDURE 过程名 ([过程参数[,…]])
[特性 …] 过程体

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

注意：

• 这里需要注意的是 DELIMITER // 和 DELIMITER ; 两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把 存储过程 当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将 “;” 当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
• 存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
• 过程体的开始与结束使用BEGIN与END进行标识。

关于 存储过程 ，你需要知道

• 存储过程的权限
• 存储过程的创建
• 存储过程的删除
• 存储过程的状态
• 存储过程的变量
• 其他：变量赋值、BEGIN…END复合语句、流程控制、循环语句、迭代（ITERATE）语句、注释语法

DELIMITER $$
 
USE `51fanli_cang`$$
 
DROP PROCEDURE IF EXISTS `Proc_Notify_UserfavDiscount_Mail2`$$
 
CREATE DEFINER=`root`@`%` PROCEDURE `Proc_Notify_UserfavDiscount_Mail2`(IN latestPushTime DATETIME)
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp_Userfav_Discount_Cnt;
CREATE TEMPORARY TABLE tmp_Userfav_Discount_Cnt
    AS
    SELECT
        a.user_id,
        COUNT(a.id) COUNT,
        ROUND(SUM(a.price) - SUM(b.pricenow), 2) diffprice
    FROM fav_userfav a
        INNER JOIN fav_product b
            ON a.product_id = b.id
    WHERE a.is_del = 0
        AND a.price_flg = 2
        AND a.in_buy = 0
        AND b.pricedown_time >= latestPushTime
    GROUP BY a.user_id;
   SELECT user_id,user_name,user_email,topic_title,link_pic,price,link_value,source_title,pricenow,shop_id,source_pic,COUNT,diffprice,rank FROM (
    SELECT user_id,user_name,user_email,topic_title,link_pic,price,link_value,source_title,pricenow,shop_id,source_pic,COUNT,diffprice,@rownum:=@rownum+1,
    IF(@pdept=heyf_tmp.user_id,@rank:=@rank+1,@rank:=1) AS rank, 
    @pdept:=heyf_tmp.user_id FROM (
    SELECT a.user_id ,a.topic_title,a.link_pic,a.price,b.link_value,b.source_title,b.pricenow,b.shop_id,b.source_pic,c.count,c.diffprice,d.user_name,d.user_email FROM fav_userfav a INNER JOIN fav_product b ON  a.product_id=b.id INNER JOIN
    tmp_Userfav_Discount_Cnt c ON a.user_id=c.user_id INNER JOIN fav_user d ON d.user_id = c.user_id
    WHERE  a.is_del = 0 AND a.price_flg = 2 AND a.in_buy = 0 AND d.user_email IS NOT NULL
    ORDER BY a.user_id,b.pricedown_time,a.price - b.pricenow DESC
    ) heyf_tmp ,(SELECT @rownum :=0 , @pdept := NULL ,@rank:=0) a ) result 
    WHERE rank<=5;
END$$
 
DELIMITER ;

迄今为止使用的所有存储过程基本上都是封装MySQL简单的 SELECT语句。虽然它们全都是有效的存储过程例子,但它们所能完成的工作你直接用这些被封装的语句就能完成（如果说它们还能带来更多的东西,那就是使事情更复杂）。
只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来，来使我们的语句执行更加可靠和智能，比如我们可以声明局部变量、添加内部注释、使用循环或判断语句等等。

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

2、降低网络开销
存储过程调用时只需提供存储过程名和必要的参数信息，从而可降低网络的流量。

3、便于进行代码移植
数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。

4、更强的安全性
1）系统管理员可以对执行的某一个存储过程进行权限限制，避免非授权用户对数据的访问
2）在通过网络调用过程时，只有对执行过程的调用是可见的。 因此，恶意用户无法看到表和数据库对象名称、
嵌入自己的 Transact-SQL 语句或搜索关键数据。
3）使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码，所以，
攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
4）可以对过程进行加密，这有助于对源代码进行模糊处理。

劣势：
1、存储过程需要专门的数据库开发人员进行维护，但实际情况是，往往由程序开发员人员兼职
2、设计逻辑变更，修改存储过程没有SQL灵活

为什么在实际应用中，存储过程用到相对较少呢？ 在通常的项目研发中，用存储过程却相对较少，这是为什么呢？ 分析原因如下：
1）没有特定的数据库开发人员，普通程序员兼职进行数据库操作
2）程序员往往只需操作程序，即可完成数据访问，无需再在数据库上进行开发
3）项目需求变动比较频繁，修改SQL语句比较方便，特别是涉及逻辑变更

存储过程与SQL语句如何抉择？ 基于实际应用的经验，给予如下建议： 1、在一些高效率或者规范性要求比较高的项目，建议采用存储过程
2、对于一般项目建议采用参数化命令方式，是存储过程与SQL语句一种折中的方式 3、对于一些算法要求比较高，涉及多条数据逻辑，建议采用存储过程

参考

• https://www.cnblogs.com/Hebe/p/3410863.html
• https://www.cnblogs.com/chenliyang/p/6553068.html
• http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html