存储过程

概念和结构

先了解SQL命令的处理过程：

如果省略了语法分析和编译的环节，那么速度就会快很多

概念

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理，相当于省略了那两个环节，效率要高

优点

• 增强SQL语句的功能和灵活性
• 实现较快的执行速度
• 减少网络流量

语法结构

 CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_paramenter[,...]]) [characteristic ...] routine_bodyproc_parameter:[IN | OUT | INOUT] param_name type

DEFINER：创建者，可省略，默认是当前登录MySQL的用户

sp_name:存储过程的名字，可带参数

IN：表示该参数的值必须在调用存储过程时指定（不能被返回只能进不能出）

OUT：表示该参数的值可以被存储过程改变，并且可以返回

INOUT：表示该参数在调用时指定，并且可以被改变和返回

特性（characteristic）：

COMMENT ‘ string ’
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}

解释：
COMMENT：注释
CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句
NO SQL：不包含SQL语句
READS SQL DATA：包含读数据的语句
MODIFIES SQL DATA：包含写数据的语句
SQL SECURITY{DEFINER|INVOKER}：指明谁有权限来执行

过程体

• 过程体由合法的SQL语句构成
• 过程体可以是任意SQL语句
• 过程体如果为复合结构则使用BEGIN…END语句
• 复合结构可以声明，循环，控制结构

创建存储过程

创建不带参数的存储过程

举个例子，创建一个显示版本信息的存储过程：

mysql> CREATE PROCEDURE sp1() SELECT VERSION();Query OK, 0 rows affected (0.00 sec)mysql> CALL sp1();+-------------------------+| VERSION()               |+-------------------------+| 5.7.15-0ubuntu0.16.04.1 |+-------------------------+1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)

如上所示，调用存储过程用CALL

创建带有IN类型的存储过程

mysql> delimiter //mysql> CREATE PROCEDURE rmid(IN p_id INT UNSIGNED)    -> BEGIN    -> DELETE FROM users WHERE id = p_id;    -> END    -> //Query OK, 0 rows affected (0.00 sec)

参数名字不能和数据表中记录名相同

创建带有IN和OUT类型的参数的存储过程

我们准备删除users表中不固定的记录，并且返回剩余的记录数：

mysql> CREATE PROCEDURE rm2(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED) BEGIN DELETE FROM users WHERE id = p_id; SELECT count(id) FROM users INTO userNums; END//Query OK, 0 rows affected (0.01 sec)

这里的SELECT… INTO…是指把SELECT结果的表达式放入到后面的变量

然后进行调用：
mysql> CALL rm2(1,@nums);
查看接收到的变量：

+-------+| @nums |+-------+|     3 |+-------+

@nums 代表的是变量，通过DECLEAR生成的变量称为局部变量，它的作用范围只在BEGIN和END的语句块之间

除了SELTCT IN传递变量，也可以用SET @x = …方式生成用户变量，用户变量是和MySQL客户端绑定的，只对当前用户所使用的客户端生效

创建带有多个OUT类型参数的存储过程

先介绍一个函数：

ROW_COUNT()

单独使用没有意义，从字面上看是得到行数，其实是得到插入/删除/更新 被影响的记录总数

创建一个根据年龄删除用户，要返回两个信息：删除的用户，剩余的用户，所以需要一个IN类型和两个OUT类型：

mysql> CREATE PROCEDURE rmage(IN p_age SMALLINT UNSIGNED,OUT del_users SMALLINT UNSIGNED,OUT user_counts SMALLINT UNSIGNED) BEGIN DELETE from users WHERE age = p_age; SELECT ROW_COUNT() INTO del_users; SELECT COUNT(id) FROM users INTO user_countS; END//Query OK, 0 rows affected (0.00 sec)#进行调用：# 说明已经删除了2个，还剩下5个 mysql> CALL rmage(13,@a,@b);Query OK, 1 row affected (0.06 sec)mysql> SELECT @a,@b;+------+------+| @a   | @b   |+------+------+|    2 |    5 |+------+------+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 [IF EXISTS] sp_name

存储过程和自定义函数的区别

• 存储过程实现的功能要复杂一些，函数的针对性更强
• 存储过程可以返回多个值，而函数只能返回一个值
• 存储过程一般独立的来执行，而函数主要作为其他SQL语句的组成部分来出现