mysql的存储过程

（一）存储过程是存储在数据库目录中的一段声明性SQL语句。

在MySQL 5.0版本以后，存储过程，存储函数，触发器和事件这些功能才被添加到MySQL数据库引擎。

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

让我们来详细地说明上述存储过程：

• 第一个命令是DELIMITER //，它与存储过程语法无关。 DELIMITER语句将标准分隔符 - 分号(;)更改为：//。 在这种情况下，分隔符从分号(;)更改为双斜杠//。为什么我们必须更改分隔符？ 因为我们想将存储过程作为整体传递给服务器，而不是让mysql工具一次解释每个语句。 在END关键字之后，使用分隔符//来指示存储过程的结束。 最后一个命令(DELIMITER;)将分隔符更改回分号(;)。

• 使用CREATE PROCEDURE语句创建一个新的存储过程。在CREATE PROCEDURE语句之后指定存储过程的名称。在这个示例中，存储过程的名称为：GetAllProducts，并把括号放在存储过程的名字之后。

• BEGIN和END之间的部分称为存储过程的主体。将声明性SQL语句放在主体中以处理业务逻辑。 在这个存储过程中，我们使用一个简单的SELECT语句来查询products表中的数据。

2、使用CALL语句调用存储过程，例如调用GetAllProducts()存储过程，则使用以下语句：

CALL GetAllProducts();

3、显示存储过程字符

使用SHOW PROCEDURE STATUS语句如下：

SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];

实例：SHOW PROCEDURE STATUS WHERE name LIKE '%product%'

显示存储过程的源代码

要显示特定存储过程的源代码，请使用SHOW CREATE PROCEDURE语句如下：

SHOW CREATE PROCEDURE stored_procedure_name
SQL

在SHOW CREATE PROCEDURE关键字之后指定存储过程的名称。例如，要显示GetAllProducts存储过程的代码，请使用以下语句：

SHOW CREATE PROCEDURE GetAllProducts;

（二）存储过程的变量

1、变量必须先声明，才能使用。

使用DECLARE语句来声明变量，格式：

DECLARE variable_name datatype(size) DEFAULT default_value;

MySQL允许您使用单个DECLARE语句声明共享相同数据类型的两个或多个变量：

DECLARE x, y INT DEFAULT 0;

2、使用SET语句为变量赋值，还可以使用SELECT INTO语句将查询的结果分配给一个变量。

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

3、变量范围(作用域)

BEGIN END块内声明一个变量，那么如果达到END，则变量的作用域结束。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

（三）存储过程的参数

在MySQL中，参数有三种模式：IN，OUT或INOUT。

• IN - 是默认模式。在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
• OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。请注意，存储过程在启动时无法访问OUT参数的初始值。
• INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

参数定义格式：mode为IN，OUT或INOUT。

MODE param_name param_type(param_size)

如果存储过程有多个参数，则每个参数由逗号(,)分隔。

USE `yiibaidb`;DROP procedure IF EXISTS `GetOfficeByCountry`;DELIMITER $$USE `yiibaidb`$$CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255)) BEGIN SELECT *  FROM offices WHERE country = countryName; END$$DELIMITER ;

假设我们想要查询在美国(USA)的所有办事处，我们只需要将一个值(USA)传递给存储过程，如下所示：

CALL GetOfficeByCountry('USA');

要开发返回多个值的存储过程，需要使用带有INOUT或OUT参数的存储过程。

以下存储过程接受客户编号，并返回发货(shipped)，取消(canceled)，解决(resolved)和争议(disputed)的订单总数。

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