MySQL存储过程

发表于

定义

存储过程是SQL语句与控制语句的预编译集合，以一个名称存储作为一个单元处理

输入SQL命令，MySQL引擎对命令进行分析，查看输入的语法是否正确，如果正确，再进行编译，编译成MySQL引擎可识别的命令，最后再进行执行，并将执行结果返回给客户端。

如果省略了语法分析和编译的阶段，则效率可提高。

存储过程：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理（类似函数）。存储过程存储在数据库内，可以由应用程序调用执行，而且允许用户申明变量以及进行流程控制。存储过程可以接受输入类型的参数和输出类型的参数，并且可以存在多个返回值。只在第一次调用时进行语法分析和编译，以后的调用直接调用编译的结果，效率大大提高。

优点

  1. 增强SQL语句的功能和灵活性：可以通过控制语句对流程进行控制和判断
  2. 实现较快的执行速度：客户端第一次调用存储过程时，MySQL引擎会对其进行语法分析、编译等操作，然后将编译结果存储到内存中，所以第一次和之前的效率一样，然而以后会直接调用内存中的编译结果，效率提高
  3. 减少网络流量：例如删除一个记录，我们原本要输入DELETE FROM xx WHERE …; 要传输的字符较多，如果写成存储过程，就只要调用存储过程的名字和相应参数就行，传输的字符数量较少，所以减少了网络流量。

创建储存过程

1
2
3
4
CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]]) //可以带0到多个参数
[characteristic ...] routine_body

其中参数

1
2
proc_parameter:
[IN | OUT | INOUT] param_name type

IN, 表示该参数的值必须在调用存储过程时指定
OUT, 表示该参数的值可以被存储过程改变，并且可以返回
INOUT, 表示该参数的值调用时指定，并且可以被改变和返回

特性

1
2
3
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语句
  • 复合结构可以使用条件、循环等控制语句

创建无参的存储过程

1
2
3
CREATE PROCEDURE sp1() SELECT VERSION(); //创建
CALL sp1; //调用
CALL sp1();

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

1
2
3
4
5
6
7
8
9
10
11
12
//若带的参数与数据表中的字段一致，则删除整个数据表。
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
SELECT * FROM users;
CALL removeUserById(22);
SELECT * FROM users WHERE id = 22;

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

1
2
3
4
5
6
7
8
9
10
11
DELIMITER //
CREATE PROCEDURE removeUserAndReturnUserNums(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
//
DELIMITER ;
CALL removerUserAndReturnUserName(10,@nums); 
SELECT @nums; 
SET @i = 7;

INTO将SELECT语句结果返回给userNums变量
通过@或SET设置的变量称为用户变量
@nums 所代表的就是用户变量，可用 SELECT @nums 输出

局部变量与用户变量的区别
用 DECLARE 声明的变量是局部变量，局部变量只能存在于 BEGIN…END 之间，
且声明时必须置于 BEGIN…END 的第一行。而通过 SELECT…INTO…/SET @id = 07
设置的变量称用户变量，只能存在于当前用户所使用的客户端有效。

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

明确OUT类型的参数的定义与作用

OUT类型参数，表示在调用存储过程时，值可被存储过程改变，并且返回一个值。通常变量用“@”符号开头的变量，在BEGIN和END之中的变量是局部变量，在调用存储过程时写入的变量是“用户变量”。比如有一个存储过程的名称叫做“addUser()”，调用时addUser(@sum),sum就是用户变量。

ROW_COUNT()函数

相当于PHP的MySQL函数库中的mysql_affected_rows()数作用相同，计算增删改查的个数总和。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(
IN p_age SMALLINT UNSIGNED,
OUT deleteUsers SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age=p_age;
SELECT ROW_COUNT() INTO deleteUsers; #返回被增删改查的记录总数
SELECT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER ;
CALL removeUserByAgeAndReturnInfos(20,@a,@b);
SELECT @a,@b; #a是删除的记录数，b是剩余的记录数

修改存储过程

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

调用存储过程

1
2
3
4
CALL sp_name([parameter[,...]]) 
//带参数的存储过程的调用
CALL sp_name[()] 
//不带参数的存储过程调用

删除存储过程

1
2
3
DROP PROCEDURE [IF EXISTS] sp_name;
DROP PROCEDURE removeUserById;

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

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