MySQL存储过程

在MySQL中使用存储过程，可以将相关的数据操作语句打包在一起，减少了应用程序到数据库之间的连接，相应地提高了性能。当然如果要编写大量的存储过程，维护调试也是个问题。因此需要自己权衡利弊。

1、语法

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `test`.`my_procedure`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
    　　SQL statement
    END$$
DELIMITER ;

2、参数类型

 MySql存储过程的参数主要有3种类型：IN、OUT、INOUT。IN表示输入参数，OUT表示输出参数，INOUT既可以当做输入参数也可以当做输出参数。

3、变量定义以及作用范围

 变量定义(declare)：

DECLARE sum_var INT(4) DEFAULT 0;

 

变量的作用范围在BEING ... END 之间，注意BEGIN ... END 还可以嵌套BEGIN ... END。变量赋值的方式有两种：使用关键字SET， set sum_var = 10; 另一种是 select xx into sum_var from tb_xx ; 

4、案例

创建一个简单的商品表

CREATE TABLE product(
    product_id INT(10) NOT NULL AUTO_INCREMENT,
    product_code VARCHAR(10),
    product_name VARCHAR(20),
    product_desc VARCHAR(100),
    PRIMARY KEY(product_id)
) CHARSET=utf8 ;

#插入数据
INSERT INTO product VALUES (0,'Phone','手机','IPhone') ,(0,'Apple','水果','苹果');

 

 只有IN参数的存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS getProductsByCode$$
CREATE
    PROCEDURE `test`.`getProductsByCode`(IN productCode VARCHAR(10))
    COMMENT 'get products by code'
    BEGIN
    　　SELECT * FROM product WHERE product_code = productCode;
    END$$
DELIMITER ;

 

存储过程的调用方式为：call procedure_name(...);

mysql> call getProductsByCode('Phone');
+------------+--------------+--------------+--------------+
| product_id | product_code | product_name | product_desc |
+------------+--------------+--------------+--------------+
|          1 | Phone        | 手机         | IPhone       |
+------------+--------------+--------------+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

 

IN、OUT参数

DELIMITER $$
DROP PROCEDURE IF EXISTS procedure_add$$
CREATE
    PROCEDURE `test`.`procedure_add`(IN a INT(4) , IN b INT(4) , OUT c INT(4))
    BEGIN
    　　DECLARE sum_ INT(4) DEFAULT 0;
    　　SET sum_ = a + b;
    　　SET c = sum_;
    END$$
DELIMITER ;

 

mysql> call procedure_add(2,3,@sum);
Query OK, 0 rows affected (0.00 sec)

mysql> select @sum;
+------+
| @sum |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

 

在客户端调用存储时，OUT参数需要使用@xx 来接收，delimiter $$ (可自定义) 定义的字符串是告诉MySQL只有遇到$$才会认为编写的SQL结束。因为MySQL默认的是分号(；)，这就会导致语义不正确。