MySQL存储过程

• 定义
  
  • 存储在数据库目录中的一段声明性SQL语句
• 优点
  
  • 有助于提高应用程序的性能。当创建存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询
  • 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数
  • 存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
  • 存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
• 缺点
  
  • 如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。
  • 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
  • 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
  • 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。
• 创建存储过程
  
  • CREATE PROCEDURE

DELIMITER // CREATE PROCEDURE GetAllProducts()   BEGIN   SELECT *  FROM products;   END //DELIMITER ;

• 调用存储过程
  
  • CALL STORED_PROCEDURE_NAME();
    若调用以上创建的存储过程，即 CALL GetAllProducts();
• 存储过程的变量
  
  • 变量声明
    
    • DECLARE
    • 声明一个名为total_sale的变量，数据类型为INT，默认值为0
    • DECLARE total_sale INT DEFAULT 0;
    • 也可声明共享相同数据类型的两个或多个变量
    • DECLARE x, y INT DEFAULT 0;
  • 变量赋值
    
    • 使用SET语句
    • DECLARE total_count INT DEFAULT 0;SET total_count = 10;
    • SELECT INTO语句将查询结果分配给变量
    • SELECT COUNT(*) INTO total_products FROM products
  • 变量范围（作用域）
    
    • 如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，在其它代码块中就无法访问
    • 以@符号开头的变量是会话变量。直到会话结束前它可用和可访问
• 存储过程参数
  
  • 三种模式
    
    • IN - 是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
    • OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。请注意，存储过程在启动时无法访问OUT参数的初始值。
    • INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。
  • 参数定义
    
    • MODE param_name param_type(param_size)
    • MODE可以是IN、OUT、INOUT
  • 示例

DELIMITER $$CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))BEGIN SET count = count + inc;END$$DELIMITER ;SET @counter = 1;CALL set_counter(@counter,1); -- 2CALL set_counter(@counter,1); -- 3CALL set_counter(@counter,5); -- 8SELECT @counter; -- 8