二，存储过程（1）

1，存储过程概述

存储过程是在数据库目录中声明的SQL语句段。一个存储过程可以被触发器，其他的存储过程和例如Java，Python，PHP等应用程序调用。
自己调用自己的存储过程是递归存储过程。许多的数据库管理系统支持递归存储过程，但是MySQL数据库对该功能支持不是很好。5.0以后的版本支持存储过程。

1.1，存储过程的优点

• 通常情况下存储过程可以提高应用程序的执行性能。存储过程一旦被创建，存储过程就被编译存储在数据库中，MySQL的存储过程有点不同，它的存储过程是按需编译。编译一个存储过程，MySQL将它放进缓存中去。MySQL为每次的连接维护自己的存储过程。
• 存储过程有助于减少应用程序和数据库服务器之间的通信，因为不是发送多长的SQL语句，应用程序只发送姓名和存储过程的参数。
• 存储过程是可以重复使用和对于应用程序而言是透明的。
• 存储过程是安全的。

1.2，存储过程的缺点

• 如果你使用了大量的存储过程，用这些存储过程的每个连接的内存使用量将大幅增加。此外，如果你过度使用存储过程里面大量的逻辑运算，CPU使用率也将增加，因为数据库服务器不是为逻辑运算而精心设计的。
• 存储过程构建体使其更难以开发出具有复杂的业务逻辑的存储过程。
• 它是难以调试存储过程。只有少数的数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
• 开发和维护存储过程是不容易的。开发和维护存储过程需要专业的技能，这不是所有的开发人员都能够掌握的

2，存储过程编写

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

存储过程细节解释：

• DELIMITER // 不是存储过程的相关语法，DELIMITER语句改变其分号分隔符标准（;）到另一个（//），使用这样的方式是因为我们要存储过程作为一个整体传递给服务器，而不是让MySQL的工具，同时解释每条语句。
• CREATE PROCEDURE GetAllProducts()使用CREATE PROCEDURE语法来创建名为GetAllProducts的储存过程。
• 在begin和end之间的被称为储存过程体。将声明的sql语句放在这个存储过程体重处理业务逻辑。

2.1，使用SQLyog创建储存过程

copy paste以上代码

2.2，储存过程调用

CALL GetAllProducts();

3，存储过程变量

变量声明格式：

DECLARE variable_name datatype(size) DEFAULT default_value;

语法详解：

• 在DECLARE后面写上声明变量的具体变量名称，变量名称要符合MySQL的命名规范。
• 指定变量的类型，例如INT，VARCHAR，DATETIME等
• 指定变量 的默认值

为变量赋值：

DECLARE variable_name datatype(size) DEFAULT default_value;SET variable_name = [value];

示例程序：

DECLARE total_products INT DEFAULT 0;SELECT COUNT(*) INTO total_productsFROM products;

3.1，变量作用域

如果在BEGIN END块中声明一个变量，到达END时，它将超出自己的作用域范围之外。因为一个变量只在自己的作用域内有效，所以可以声明两个或更多的相同的变量在不同的作用域内。但是声明多个相同变量名的做法不是一个好的编程方式。
以@开头的变量是会话变量，它会存在直到会话的结束。

4，存储过程参数

大多数的存储过程需要开发人员提供参数，参数的使用使得存储过程更加灵活和有用。存储参数有三种模式：IN，OUT，INOUT。

• IN：是默认的模式。当在存储过程中定义一个IN模式的参数，调用的程序需要传递一个参数给储存过程。另外，IN模式的参数是被保护的。意味着不管在函数体内这个参数值怎么改变，在函数体执行完毕该参数的原来的值是不变的。
• OUT：这个参数的值在函数体中是可以改变的，并且储存过程需要把这个值返回给调用的程序。储存过程在刚开始的时候是没有权限访问初始化的值的。
• INOUT：该参数是上面两个参数的合并，就是该参数需要调用的程序传入到储存过程中，又需要将该参数在函数体中处理后的参数值返回给调用的程序。

语法格式：

MODE param_name param_type(param_size)

IN模式示例程序：

DELIMITER //CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT *  FROM offices WHERE country = countryName; END //DELIMITER ;

调用该储存过程：

CALL GetOfficeByCountry("France");

执行结果：


OUT模式示例程序：

DELIMITER //CREATE PROCEDURE CountOrderByStatus( IN orderStatus VARCHAR(25), OUT total INT)BEGIN SELECT count(orderNumber) INTO total FROM orders WHERE status = orderStatus;END//DELIMITER ;

调用该储存过程：

CALL CountOrderByStatus("Shipped",@total);SELECT @total;

执行结果：


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

4，存储过程返回多个值

DELIMITER //CREATE PROCEDURE get_order_by_cust( IN cust_no INT, OUT shipped INT, OUT canceled INT, OUT resolved INT, OUT disputed INT)BEGIN -- shipped SELECT            COUNT(*) INTO shipped        FROM            orders        WHERE            customerNumber = cust_no                AND STATUS = 'Shipped'; -- canceled SELECT            COUNT(*) INTO canceled        FROM            orders        WHERE            customerNumber = cust_no                AND STATUS = 'Canceled'; -- resolved SELECT            COUNT(*) INTO resolved        FROM            orders        WHERE            customerNumber = cust_no                AND STATUS = 'Resolved'; -- disputed SELECT            COUNT(*) INTO disputed        FROM            orders        WHERE            customerNumber = cust_no                AND STATUS = 'Disputed';END//DELIMITER ;

调用该储存过程：

CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed);SELECT @shipped,@canceled,@resolved,@disputed;

执行结果：