概念和结构
先了解SQL命令的处理过程:
如果省略了语法分析和编译的环节,那么速度就会快很多
概念
存储过程是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理,相当于省略了那两个环节,效率要高
优点
- 增强SQL语句的功能和灵活性
- 实现较快的执行速度
- 减少网络流量
语法结构
CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name
([proc_paramenter[,...]]) [characteristic ...] routine_body
proc_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语句的组成部分来出现