mysql存储过程

存储过程的定义（stored procedure）

SQL的执行流程如下图：

存储过程时SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理（省略了上图语法分析和编译的过程）

存储过程的优点

1. 增强SQL语句的功能和灵活性
2. 实现较快的执行速度（存储过程时预编译的，第一次调用存储过程的效率是和以前相同的，再次调用就直接从内存执行，省略语法分析和编译过程）
3. 减少网络流量（如果把SQL存储为存储过程的话，只需传输存储过程的名字和参数，减少了字符量的传输）

存储过程的缺点

1. 不能用缓存
2. 不能处理复杂业务
3. 移植困难，数据库语法有区别

创建存储过程

CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,......]])
[characteristic ......] rotine_body

1. 存储过程的参数
   in 表示该参数的值必须在调用存储过程时指定（传入的参数不能与表中的字段名相同）
   out 表示该参数的值可以被存储过程改变，并且可以返回
   inout 表示该参数的调用时指定，并且可以被改变和返回
2. 存储过程语句的特点：
   过程体可以是任意SQL语句；（过程体的语句不可以创建数据库，只能是对数据的增删改查和连接语句）
   过程体如果为复合语句则使用BEGIN…END语句；
   复合结构可以包含声明，循环，控制结构；

一、创建不带参数的存储过程

mysql> CREATE PROCEDURE sp1()
    -> select VERSION();
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp1();
+-----------+
| VERSION() |
+-----------+
| 5.6.17    |
+-----------+
1 row in set (0.00 sec)

二、创建带有IN类型参数的存储过程
我们创建一个存储过程，传入一个t_id，删除test1表中的记录

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeTest1ById(IN t_id TINYINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM test1 where id = t_id;
    -> END
    -> //

调用存储过程removeTest1ById前查看test1表记录：

mysql> DELIMITER ;
mysql> SELECT * FROM test1;
+----+--------+-----------+------+
| id | name   | parent_id | code |
+----+--------+-----------+------+
|  2 | 北京   |         1 | tep  |
|  7 | London |         0 | UK   |
|  5 | 汕尾   |         2 |      |
|  6 | 真是   |         2 |      |
+----+--------+-----------+------+
4 rows in set (0.00 sec)

调用存储过程removeTest1ById

mysql> CALL removeTest1ById(2);
Query OK, 1 row affected (0.00 sec)

查看调用存储过程removeTest1ById后的test1表，记录2被删除

mysql> SELECT * FROM test1;
+----+--------+-----------+------+
| id | name   | parent_id | code |
+----+--------+-----------+------+
|  7 | London |         0 | UK   |
|  5 | 汕尾   |         2 |      |
|  6 | 真是   |         2 |      |
+----+--------+-----------+------+
3 rows in set (0.00 sec)

三、创建带有IN,OUT的存储过程
我们在该例子中实现传入记录t_id,删除指定t_id的记录，返回剩余记录数目

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeDataReturn(IN t_id INT UNSIGNED,OUT remain_sum TINYINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM test1 WHERE id = t_id;
    -> SELECT COUNT(id) FROM test1 INTO remain_sum;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

调用存储过程removeDataReturn前：

mysql> SELECT * FROM test1;
+----+--------+-----------+------+
| id | name   | parent_id | code |
+----+--------+-----------+------+
|  7 | London |         0 | UK   |
|  5 | 汕尾   |         2 |      |
|  6 | 真是   |         2 |      |
+----+--------+-----------+------+
3 rows in set (0.00 sec)

调用存储过程removeDataReturn后，删除了记录7，removeDataReturn返回的变量是@a，
查询返回的参数：SELECT @nums;（@为用户变量）

mysql> CALL removeDataReturn(7,@a);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+------+-----------+------+
| id | name | parent_id | code |
+----+------+-----------+------+
|  5 | 汕尾 |         2 |      |
|  6 | 真是 |         2 |      |
+----+------+-----------+------+
2 rows in set (0.00 sec)

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

修改存储过程

存储过程只能修改一些简单的特性，并不能修改过程体，如果发现过程体写错了，只能删除存储过程重新创建。

ALTER PROCEDURE sp_name[characteristic ...]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}

删除存储过程

语法 DROP PROCEDURE sp_name;(记得过程名称后面不许加());

mysql> DROP PROCEDURE if exists removeDataReturn;
Query OK, 0 rows affected (0.00 sec)

*.自定义函数和存储过程的区别
1）存储过程复杂一些，函数的针对性强
2）返回值个数不一样函数为一个，存储过程可以返回多个
3）存储过程都是独立运行的，而函数一般结合SQL一起使用